4
Building a Report with an XML Pluggable Data Source

Reports Builder enables you to use any data source you wish. In this chapter, you will learn how to use the XML pluggable data source that is provided with Oracle9i Reports.

About Pluggable Data Sources

The information you must publish is often derived from data in various corporate data sources. These data sources may be SQL-based (relational databases) or non-SQL-based, such as XML, OLAP, and the like. Often, you must combine data from one or more of these data sources to publish meaningful information. For example, you may need to combine data that exists in a relational database with data from a multi-dimensional database to compare trends and performance.

Oracle9i Reports enables you to leverage capabilities, such as aggregation, summarization, formatting, and scheduling, on data from any data source. You can leverage the PDS (pluggable data source) architecture to connect to your own data source, as well as to the data sources available with Oracle9i Reports (XML, JDBC, text, and Express).

For more information on pluggable data sources, refer to the Reports Builder online help and the Javadoc documentation for the PDS APIs.

Example Scenario

Suppose you have an international business with warehouses in the United States and overseas. These warehouses are running a de-centralized management system that stores the operational data locally at each site. The inventory of the warehouses are managed by the local managers. However, for planning purposes, a team at corporate headquarters needs to access the inventory data (in SQL), including the most recent data, of every warehouse. The warehouse data is only available as an XML stream. In this example, you will learn how to combine data from a local database (i.e., the warehouse data) and data from an XML feed to create a Web report.

In this example, you will use static XML files that we've provided for you. The report will access the XML feed online using the business-to-business interface of your order entry system.

Table 4-1 Features Demonstrated in the XML PDS Example

Feature	Location
Manually create a SQL query.	Section 4.2.1, "Create a SQL query for your new report"
Use the Data Wizard to create an XML query.	Section 4.2.2, "Create an XML query to access your XML data source"
Create a data link between a SQL query and an XML query.	Section 4.2.3, "Create a data link between two queries"
Use the Report Wizard to create a layout for your report.	Section 4.2.4, "Create a layout for your report using the Report Wizard"
Use format triggers and procedures to apply alternating row colors.	Section 4.2.5, "Apply alternating row colors to your report"
Use a group filter to sort your XML data.	Section 4.2.6, "Filter your XML data using groups"

4.1 Prerequisites for this example

To build the examples in this manual, you must have the example files we've provided and access to the sample schema that comes with the Oracle9i database.

4.1.1 Example files

If you haven't already done so, you can download the files you'll need to complete this example from the Oracle Technology network (http://otn.oracle.com/products/reports/) and install them on your machine.

To download and install the example files:

1. Go to the Oracle Technology Network Web site (http://otn.oracle.com/product/reports/).

2. Click Getting Started with Oracle9i Reports.

3. Click Index, then find the "Building a Report using an XML Pluggable Data Source" example.

4. Download the file XML_PDS.zip into a temporary directory on your machine (e.g., "d:\temp").

5. Unzip the contents of the file, maintaining the directory structure, into an examples directory on your machine (e.g., d:\orawin90\examples).

   This zip file contains the following files:

   Table 4-2 Files necessary for building this sample report using XML PDS

   File	Description
   Examples\XML_PDS\result\inventory_report.pdf	The final PDF version of the paper report.
   Examples\XML_PDS\result\inventory_report.rdf	The final RDF version of the paper report.
   Examples\XML_PDS\result\warehouse_inventory.rdf	A version of the final report which we've enhanced and formatted.
   Examples\XML_PDS\source\inventory_report_NB.rdf	The source file for the report. Running this RDF in Reports Builder will display a paper report in the Paper Design view.
   Examples\XML_PDS\scripts\XMLPDS_SQL.txt	The various SQL statements you will use in this report.
   Examples\XML_PDS\scripts\warehouse_inventory.xml	The XML data source for the query in your report.
   Examples\XML_PDS\scripts\warehouse_inventory.xsd	The XML data stream for your report.

   Note: The index.html file and assets directory are used as part of the Getting Started with Oracle9i Reports Web site. Please do not delete or move these files

4.1.2 Access to the sample schema

If you don't know if you have access to the sample schema provided with the Oracle9i database, contact your database administrator. You should have access to the "Order Entry" portion of the schema to complete this example. Typically, you can log into this schema by using the user ID and password "oe/oe", then enter the name of the database.

4.2 Create a report manually with SQL and XML queries

When you create a report, you can either use the Report Wizard to assist you or create the report yourself. To build this report, you'll need to create two queries: a SQL query and an XML query.

4.2.1 Create a SQL query for your new report

When creating the SQL query, you'll need access to the Order Entry part of the sample schema provided with the Oracle9i database. If you don't have access, contact your database administrator. Typically, you can log in using the connection string "oe/oe@<database name>".

To create a SQL query:

1. In Reports Builder, choose File > New > Report.

2. In the New Report dialog box, select the Build a new report manually radio button, then click OK.

   Your new report displays in the Object Navigator as something like "MODULE 2." You will also see the Data Model view of your new report.

3. In the Data Model view, click the SQL Query icon in the tool palette.

4. Draw an area on the canvas to create a query.

5. In the SQL Statement field, type the following code:

   
   select   W.WAREHOUSE_ID,
            W. WAREHOUSE_NAME,
            L.CITY,
            L.STATE_PROVINCE,
            C.COUNTRY_NAME
   from     WAREHOUSES W
            HR.LOCATIONS L
            HR.COUNTRIES C
   where    ( W.LOCATION_ID = L.LOCATION_ID(+) )
     and    ( L.COUNTRY_ID = C.COUNTRY_ID(+) )
   order by C.COUNTRY_NAME, W.WAREHOUSE_NAME
   
   

   Note: You can also copy and paste the code from the text file we've provided, xmlpds_sql.txt. Open the file in a text editor, then copy the List of Warehouse query) into the SQL Statement text box.

6. Click OK.

   Note: If the Connect dialog box displays, enter the user ID, password, and name of the database that contains the sample schema.

   The data model displays in the Data Model view, and should look something like this

Figure 4-1 Data Model for the XML PDS Example SQL Query

Text description of the illustration orbrxml_sqldm.gif

Save your report as inventoryreport_xml_<your initials>.rdf.

You have created a SQL query to retrieve the data for your report.

4.2.2 Create an XML query to access your XML data source

In this section, you will create a query to access the XML data source. You can view the resulting report we've provided to make sure your query is correct. Please note, though, that you must update the paths to the Data Definition files with the location of the example files we provided to you.

To create an XML query:

1. In the Data Model view, create another query by choosing Insert > Query.

2. When the Data Wizard displays, click Next on the Welcome page.

3. On the Query Name page, click Next.

4. On the next page, click XML Query, then click Next.

5. On the Data Source Definition page, click Query Definition.

6. In the Define XML Query dialog box, under Data Definition, click Browse to locate the XSD file we've provided, warehouse_inventory.xsd.

7. When you've located the file, choose it, then click Open.

8. In the Define XML Query dialog box, under Data Source, click Browse to locate the XML file we've provided that contains your data, warehouse_inventory.xml.

   If you want to compare your data definition to the one we provided, make sure that you replace the data definition locations with the locations of your files.

9. When you've located the file, choose it, then click Open.

10. In the Define XML Query dialog box, click OK.

11. In the Data Wizard, click Next.

12. On the Groups page, click the PRODUCT_ID group, then click the right arrow button.

13. Click Finish.

    The Data Model view displays your two queries, and should look something like this:

Figure 4-2 Data Model for the XML PDS Example with XML and SQL Queries

Text description of the illustration orbrxml_xmldm.gif

Save your report as inventoryreport_xml_<your initials>.rdf.

You have created an XML query to access the XML data source we've provided.

4.2.3 Create a data link between two queries

You will now need to link the SQL query and the XML query so that you can access your corporate data as well as the data for each of the local warehouses.

To create a data link:

1. In the Data Model view for your report, click the Data Link icon in the tool palette.

2. Click the WAREHOUSE_ID column in your first query (called Q_1).

3. Drag your cursor until your cursor is over the WAREHOUSE_ID1 column in the second query (called Q_2).

   Your data model should now look something like this:

Figure 4-3 Data Model with a Data Link between a SQL Query and an XML Query

Text description of the illustration orbrxml_dl.gif

You'll notice that the WAREHOUSE_ID column is now highlighted at the bottom of Q_1, with a line pointing to the WAREHOUSE_ID1 column. You can close the Property Inspector for the link.

Save your report as inventoryreport_xml_<your initials>.rdf.

You have created a data link between the WAREHOUSE_ID columns in the two queries.

4.2.4 Create a layout for your report using the Report Wizard

Before you can run any report, you must define a layout. The easiest way to do this is to use the Report Wizard.

To create a paper layout:

1. In the Data Model view for your report, right-click the canvas, then choose Report Wizard from the menu.

2. On the Report Type page, select the Create Paper Layout only radio button, then click Next.

3. On the Style page, select the Group Above radio button.

4. On the Groups page, make sure the G_WAREHOUSE_ID and G_WAREHOUSE_ID1 groups are listed in the Displayed Groups list as going Down.

5. On the Fields page, click the double arrows to display all the fields.

6. On the Templates page, click Blue, then click Finish.

   Your report displays in the Paper Design view, and should look something like this:

Figure 4-4 Paper Design View of your XML PDS Example Report

Text description of the illustration orbrxml_pap.gif

Save your report as inventoryreport_xml_<your initials>.rdf.

You have created the layout for your paper report.

4.2.5 Apply alternating row colors to your report

Now that you've created the report, you can make it more user-friendly by using a summary column to apply alternating row colors.

4.2.5.1 Create a summary column to count the rows

To create a summary column:

1. In the Data Model view of your report, click the Summary Column icon in the tool palette.

   If you are still in the Paper Design view, you can click the Data Model icon in the tool palette to display the Data Model view.

2. Click in the XML query group (G_WAREHOUSE_ID1) to create a summary column.

3. In the Property Inspector, name your new summary column LineNo.

4. Make sure the Column Type property is set to Summary.

5. From the Datatype property drop-down box, choose Number.

6. Under Summary, from the Function property drop-down box, choose Count.

7. From the Source property drop-down box, choose PRODUCT_NAME.

8. Close the Property Inspector for the new summary column.

4.2.5.2 Create a procedure that changes the line colors

To create a procedure:

1. In the Object Navigator, under your report name, click the Program Units node, then click the Create icon.

2. In the Name field, type linecolors.

3. Under Types, make sure the Procedure radio button is selected, then click OK.

4. Type the following code in the editor (existing text is in bold);

   
   PROCEDURE LineColors IS
   BEGIN
   if (:LineNo mod 2 = 0)
   then
      srw.set_text_color('TextColor');
   else srw.set_text_color('black');
   end if;
   END;
   
   

   Note: You can copy and paste this code from the procedure provided in the xmlpds_sql.txt file. Just copy the text under Line Colors Procedure.

5. Click Compile to compile the procedure.

   If any errors display, make sure the code is correct, and that you created the summary column in Section 4.2.5.1, "Create a summary column to count the rows".

6. Click Close.

4.2.5.3 Create a format trigger for each field that calls the procedure

To create a format trigger:

1. In the Object Navigator, under your report name, expand the Paper Layout node and navigate to: Body/M_G_WAREHOUSE_ID_GRPFR/R_G_WAREHOUSE_ID/M_G_WAREHOUSE_ID1_GRPFR/R_G_WAREHOUSEID1.

2. Select the first field, F_PRODUCT_ID, and open the Property Inspector.

   Note: If you can't find a particular field, use the Find field at the top of the Object Navigator.

3. Under Advanced Layout, click the field next to the Format Trigger property.

4. In the resulting code editor, type the following code (existing text is in bold):

   
   function F_PRODUCT_IDformatTrigger return Boolean is
   begin
   

   LineColors;
   

   return (TRUE);
   end;
   
   

5. Perform steps 3 through 5 for the three remaining fields:
   • F_PRODUCT_NAME

   • F_QUANTITY_ON_HAND

   • F_WAREHOUSE_ID1

6. Save your report.

7. Run your report to paper by clicking the Run Paper Layout icon in the toolbar.

You have now applied alternating row colors to your report.

4.2.6 Filter your XML data using groups

If you have a lot of data in your XML file, you might want to consider sorting and filtering it. You can do so by creating a group filter and a hierarchy.

4.2.6.1 Create a group filter on the repeating frame

To create a group filter:

1. In the Object Navigator, double-click the Paper Layout node.

2. Under the Paper Layout node, double-click the Main Section node.

3. Under the Main Section node, double-click the Body node.

4. Right-click the R_G_WAREHOUSE_ID group and choose Property Inspector from the pop-up menu.

   Note: If you can't find the repeating frame, use the Find field at the top of the Object Navigator.

5. In the Property Inspector, under Group, choose PL/SQL from the Filter Type property drop-down list.

   The PL/SQL Filter property displays.

6. Next to the PL/SQL filter property, click the field to display the code editor.

7. Type the following code in the editor that displays (the existing text is in bold):

   
   function G_WAREHOUSE_IDGroupFilter return boolean is
   begin
   

   if (:LineNo mod 2 = 0)
     then 
       srw.set_background_fill_color('TableCell');
     end if;
     if (:LineNo mod 2 = 1)
     then     srw.set_background_fill_color('Totals'); 
     end if;
   

   return (TRUE);
   end;
   
   

8. Click the Compile button to compile the code.

9. If any errors display, check to make sure you copied the text exactly.

   Note: If you are not familiar with compiling PL/SQL, refer to a PL/SQL reference manual.

10. When the code is compiled, click Close.

11. Save your report.

4.2.6.2 Create a hierarchy for the XML query

To create a hierarchy:

1. In the Data Model view for your report, find the XML query.

2. In the XML query, click the PRODUCT_ID column, and drag it between the query name and the group.

   Your new data model should look something like this:

Figure 4-5 Data Model with Hierarchy

Text description of the illustration orbrxml_dmgp.gif

Note: By dragging the PRODUCT_ID column above the rest of the query, you've created a hierarchy for the XML query.

Run your report to paper.

You can also run the RDF we've provided (Examples\XML_PDS\source\inventoryreport.rdf) to view the results in Reports Builder.

Save your report as inventoryreport_xml_<your initials>.rdf.

You have now created a group filter that sorts your XML data.

4.3 Run your report to paper

To run your paper report:

1. In the Object Navigator, make sure your report (inventoryreport_xml_<your initials>.rdf) is selected.

2. Click the Run Paper Layout icon to run your report to paper.

3. Your report displays in the Paper Design view, and should look something like this:

Figure 4-6 Final Paper Design View of the XML PDS Example Report

Text description of the illustration orbrxml_fin.gif

4.4 Summary

Congratulations! You have successfully used an XML data source for a paper report. You now know how to:

• Create a SQL query from scratch

• Use the Data Wizard to create an XML query

• Create a data link between a SQL query and an XML query

• Create a layout for your report using the Report Wizard

• Apply alternating row colors to your report using format triggers and procedures

• Filter your XML data using a group filter and hierarchy

For more information on using XML as a data source, refer to Getting Started with Oracle9i Reports or the Reports Builder online help.