8
Building a Report with an XML Pluggable Data Source

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

8.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, choose Insert > Query to display the Data Wizard.

2. If the Data Wizard Welcome page displays, click Next.

3. On the Query page, click Next.

4. On the Data Source page, click XML Query, then click Next.

5. On the Data page, click Query Definition to display the Define XML Query dialog box.

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

7. Under Data Source, click Browse to locate the XML file we've provided that contains your data, warehouse_inventory.xml and open it.

   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.

8. Click OK.

9. In the Data Wizard, still on the Data page, click Next.

10. On the Groups page, click PRODUCT_ID in the Available Fields list, then click the right arrow (>) to move this field to the Group Fields list.

11. Click Finish to display your data model in the Data Model view. It should look something like this:

Figure 8-3 Data model for the XML PDS example with XML and SQL queries

Text description of the illustration orbrxml_xmldm.gif

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

8.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, click the Data Link tool in the tool palette.

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

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

   Your data model should now look something like this:

Figure 8-4 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 have created a data link between the WAREHOUSE_ID columns in the two queries.

8.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, right-click on the canvas, then choose Report Wizard.

2. In the Report Wizard, on the Report Type page, select Create Paper Layout only, then click Next.

3. On the Style page, select Group Above.

4. On the Groups page, make sure the G_WAREHOUSE_ID and G_WAREHOUSE_ID1 groups are listed in the Group Fields list with a Down Print Direction.

5. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list.

6. On the Template page, select Predefined Template and click Blue, then click Finish to display your report output in the Paper Design view. It should look something like this:

Figure 8-5 Paper Design view for the XML PDS report

Text description of the illustration orbrxml_pap.gif

You have created the layout for your paper report.

8.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.

To create a summary column to count the rows:

1. In the Data Model view, click the Summary Column tool in the tool palette.

   If you are still in the Paper Design view, you can click the Data Model button in the toolbar to display the Data Model view.

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

3. Double-click the new summary column object (CS_1) to display the Property Inspector, and set properties:
   • Under General Information, set the Name property to LineNo.

   • Under Column, set the Column Type property to Summary, and set the Datatype property to Number.

   • Under Summary, set the Function property to Count, and set the Source property to PRODUCT_NAME.

To create a procedure that changes the line colors:

4. In the Object Navigator, click the Program Units node for your report.

5. Click the Create button in the toolbar to display the New Program Unit dialog box.

6. In the New Program Unit dialog box, type linecolors as in the Name field.

7. Select Procedure, and click OK to display the PL/SQL Editor for the new program unit.

8. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

   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.

9. Click Compile to compile the procedure.

If any errors display, make sure the code is correct, and that you created the summary column in the steps above.

To create a format trigger for each field that calls the procedure:

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. Double-click the properties icon for F_PRODUCT_ID to display the Property Inspector.

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

3. In the Property Inspector, under Advanced Layout, click the Format Trigger property field to display the PL/SQL Editor.

4. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

   
   function F_PRODUCT_IDformatTrigger return Boolean is
   begin
   

   LineColors;
   

   return (TRUE);
   end;
   
   

5. Repeat the previous steps for the three remaining fields:
   • F_PRODUCT_NAME

   • F_QUANTITY_ON_HAND

   • F_WAREHOUSE_ID1

6. Save your report.

7. Click the Run Paper Layout button in the toolbar to your report to paper.

You have now applied alternating row colors to your report.

8.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.

To create a group filter on the repeating frame:

1. In the Object Navigator, expand the Paper Layout node, then the Main Section node, then the Body node.

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

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

3. In the Property Inspector, under Group:
   • Set the Filter Type property to PL/SQL.

   • Click the PL/SQL filter property field to display the PL/SQL Editor

4. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

   
   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;
   
   

5. Click Compile, and fix any errors.

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

6. When the code compiles successfully, click Close.

7. Save your report.

To create a hierarchy for the XML query:

1. In the Data Model view, find the XML query.

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

   Your new data model should look something like this:

Figure 8-6 Data model with hierarchy

Text description of the illustration orbrxml_dmgp.gif

You can also run the provide file Examples\XML_PDS\source\inventoryreport.rdf to view the results in Reports Builder.

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

8.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 button in the toolbar to run your report to paper.

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

Figure 8-7 Final Paper Design view of the XML PDS example report

Text description of the illustration orbrxml_fin.gif

8.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 any of the wizards, views, or properties used in this example, refer to the Reports Builder online help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Oracle9i Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.