Skip Headers

Oracle® Reports Building Reports 10g Release 2 (10.1.2) B13895-01 |
| Library | Product | Contents | Index

Previous | Next |

# 44 Building a Report with an XML Pluggable Data Source

In this chapter, you will learn about reports that include data from an XML pluggable data source (PDS), which is provided with Oracle Reports. By following the steps in this chapter, you can generate the report output shown in Figure 44-1.

Figure 44-1 Report output using an XML PDS

Description of "Figure 44-1 Report output using an XML PDS"

Concepts

- 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, and OLAP. 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 multidimensional database to compare trends and performance.

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

- Valid XML files have a document type definition (DTD) or XML schema and strictly adhere to it. XML files can come from any source. However, Oracle provides you with a number of utilities and methods to convert different types of data to XML data files and their DTD or schema.

- The elements in your XML data file (data source) must follow the same sequence and format of elements specified in the DTD or XML schema file. For example, suppose your DTD or XML schema defines two elements: `WAREHOUSE_ID` first, and `PRODUCT_ID` second. In this scenario, `WAREHOUSE_ID` must come before `PRODUCT_ID` in your XML data file, too. The names of the elements do not need to match. For example, given the following XML data file:

      <WAREHOUSE>
        <INVENTORY>
          <WAREHOUSE_ID>3</WAREHOUSE_ID>
          <PRODUCT_ID>2340</PRODUCT_ID>
          <QUANTITY_ON_HAND>69</QUANTITY_ON_HAND>
          <PRODUCT_NAME>Chemicals - SW</PRODUCT_NAME>
        </INVENTORY>
        <INVENTORY>
          <WAREHOUSE_ID>3</WAREHOUSE_ID>
          <PRODUCT_ID>2365</PRODUCT_ID>
          <QUANTITY_ON_HAND>73</QUANTITY_ON_HAND>
          <PRODUCT_NAME>Chemicals - TCS</PRODUCT_NAME>
        </INVENTORY>
      </WAREHOUSE>

  A valid XML schema with suffix `xsd` for the above XML is as follows:

      </xsd:schema>
      <?xml version="1.0" encoding="UTF-8"?>
      <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
      <xsd:element name="WAREHOUSE" >
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="INVENTORY" minOccurs="0" maxOccurs="unbounded"
          <xsd:complexType>
            <xsd:sequence>
            <xsd:element name="WAREHOUSE_ID" type="xsd:integer"/>
            <xsd:element name="PRODUCT_ID" type="xsd:integer"/>
            <xsd:element name="QUANTITY_ON_HAND" type="xsd:integer"/>
            <xsd:element name="PRODUCT_NAME" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
      </xsd:element>
      </xsd:schema>

- The XML PDS implementation supports only two-dimensional listing of records. Oracle Reports expects the XML data file to be in simple table format with rows and columns. Oracle Reports iterates through the XML sequence at one level below the topmost element in the XML. If there are sequences at lower levels (nested elements), they are not handled. Thus, to generate a tabular report, you must "flatten" your XML file into simple row-column format, as shown in the following examples.

  Example 44-1 shows an XML data file that includes `G_DEPTNO` elements with nested `G_EMPNO` elements. Oracle Reports parses all the departments, but not all the employees within each department. For each department, only one employee record would be shown.

  Example 44-2 shows how you can modify this XML data file to "flatten" the data. In the example, you will see that the `G_DEPTNO` elements include the `G_EMPNO` data, without nesting. Now, Oracle Reports parses all the departments, including all the employees within each department.

  Example 44-3 and Example 44-4 show the corresponding XML schema files for "nested" and "flattened" XML data, respectively.

  Example 44-1 "Nested" XML Data File

      <?xml version="1.0" encoding="WINDOWS-1252"?>
      <EMP>
          <G_DEPTNO>
            <DEPTNO>10</DEPTNO>
              <G_EMPNO>
                <EMPNO>7782</EMPNO>
                <ENAME>CLARK</ENAME>
                <JOB>MANAGER</JOB>
                <MGR>7839</MGR>
                <HIREDATE>09-JUN-81</HIREDATE>
                <SAL>2450</SAL>
                <COMM></COMM>
              </G_EMPNO>
              <G_EMPNO>
                <EMPNO>7839</EMPNO>
                <ENAME>MIKEb</ENAME>
                <JOB>BOSS</JOB>
                <MGR></MGR>
                <HIREDATE></HIREDATE>
                <SAL></SAL>
                <COMM></COMM>
              </G_EMPNO>
          </G_DEPTNO>
          <G_DEPTNO>
            <DEPTNO>20</DEPTNO>
              <G_EMPNO>
                <EMPNO>7369</EMPNO>
                <ENAME>SMITH</ENAME>
                <JOB>CLERK</JOB>
                <MGR>7902</MGR>
                <HIREDATE>17-DEC-80</HIREDATE>
                <SAL>800</SAL>
                <COMM></COMM>
              </G_EMPNO>
              <G_EMPNO>
                <EMPNO>7876</EMPNO>
                <ENAME>ADAMS</ENAME>
                <JOB>CLERK</JOB>
                <MGR>7788</MGR>
                <HIREDATE>12-JAN-83</HIREDATE>
                <SAL>1100</SAL>
                <COMM></COMM>
              </G_EMPNO>
          </G_DEPTNO>
      </EMP>

  Example 44-2 "Flattened" XML Data File

      <?xml version="1.0" encoding="WINDOWS-1252"?>

      <EMP>
          <G_DEPTNO>
            <DEPTNO>10</DEPTNO>
            <EMPNO>7782</EMPNO>
            <ENAME>CLARK</ENAME>
            <JOB>MANAGER</JOB>
            <MGR>7839</MGR>
            <HIREDATE>09-JUN-81</HIREDATE>
            <SAL>2450</SAL>
            <COMM></COMM>
          </G_DEPTNO>
          <G_DEPTNO>
            <DEPTNO>10</DEPTNO>
            <EMPNO>7839</EMPNO>
            <ENAME>MIKEb</ENAME>
            <JOB>BOSS</JOB>
            <MGR></MGR>
            <HIREDATE></HIREDATE>
            <SAL></SAL>
            <COMM></COMM>
          </G_DEPTNO>
          <G_DEPTNO>
            <DEPTNO>20</DEPTNO>
            <EMPNO>7369</EMPNO>
            <ENAME>SMITH</ENAME>
            <JOB>CLERK</JOB>
            <MGR>7902</MGR>
            <HIREDATE>17-DEC-80</HIREDATE>
            <SAL>800</SAL>
            <COMM></COMM>
          </G_DEPTNO>
          <G_DEPTNO>
            <DEPTNO>20</DEPTNO>
             <EMPNO>7876</EMPNO>
            <ENAME>ADAMS</ENAME>
            <JOB>CLERK</JOB>
            <MGR>7788</MGR>
            <HIREDATE>12-JAN-83</HIREDATE>
            <SAL>1100</SAL>
            <COMM></COMM>
          </G_DEPTNO>
      </EMP>

  Example 44-3 Corresponding "Nested" XML Schema File

      <?xml version="1.0"?>
      <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
        <xsd:element name="EMP">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="G_DEPTNO" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="DEPTNO" type="xsd:string"/>
                    <xsd:element name="G_EMPNO" minOccurs="0" maxOccurs="unbounded">
                      <xsd:complexType>
                        <xsd:sequence>
                          <xsd:element name="EMPNO" type="xsd:string"/>
                          <xsd:element name="ENAME" type="xsd:string"/>
                          <xsd:element name="JOB" type="xsd:string"/>
                          <xsd:element name="MGR" type="xsd:string"/>
                          <xsd:element name="HIREDATE" type="xsd:string"/>
                          <xsd:element name="SAL" type="xsd:string"/>
                          <xsd:element name="COMM" type="xsd:string"/>
                        </xsd:sequence>
                      </xsd:complexType>
                    </xsd:element>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:schema>

  Example 44-4 Corresponding "Flattened" XML Schema File

      <?xml version="1.0"?>
      <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema">
        <xsd:element name="EMP">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="G_DEPTNO" minOccurs="0" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="DEPTNO" type="xsd:string"/>
                    <xsd:element name="EMPNO" type="xsd:string"/>
                    <xsd:element name="ENAME" type="xsd:string"/>
                    <xsd:element name="JOB" type="xsd:string"/>
                    <xsd:element name="MGR" type="xsd:string"/>
                    <xsd:element name="HIREDATE" type="xsd:string"/>
                    <xsd:element name="SAL" type="xsd:string"/>
                    <xsd:element name="COMM" type="xsd:string"/>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:schema>

- For more information on pluggable data sources, refer to the Oracle Reports online Help. If your data source cannot use an existing PDS, the PDS API enables you to write your own PDS and incorporate it into Oracle Reports to access your own unique data sources. The API is documented in Oracle Reports Java API Reference, available on the Oracle Technology Network Oracle Reports Documentation page ().

Example Scenario

In this example, you have an international business with warehouses in the United States and overseas. These warehouses are running a decentralized 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. You will learn how to combine data from a local database (that is, the warehouse data) and data from an XML feed to create a Web report. You will use static XML files that are provided.

As you build this example report, you will:

- Create a report manually with SQL and XML queries:

  - Create a SQL query for your new report.

  - Create an XML query to access your XML data source using the Data Wizard.

  - Create a data link between two queries, 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 fromat triggers and procedures.

  - Filter your XML data using a group filter to sort your XML data.

- Run your report to paper.

To see a sample report that uses an XML PDS, open the examples folder named `XML_PDS`, then open `\result\inventory_report.rdf`. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 44-1.

Table 44-1 Example report files

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

## 44.1 Prerequisites for this example

To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to the Order Entry sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

## 44.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 will need to create two queries: a SQL query and an XML query.

### 44.2.1 Create a SQL query for your new report

To create a SQL query:

1. Launch Reports Builder (or, if already open, choose File > New > Report).

2. In the Welcome or New Report dialog box, select Build a new report manually, 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 tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.

4. In the SQL Query Statement field, enter the following `SELECT` statement:

       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 have provided, `xmlpds_sql.txt`. Open the file in a text editor, then copy the List of Warehouse query into the SQL Query Statement field.

5. 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 44-2 Data Model for the XML PDS example SQL query

   Description of "Figure 44-2 Data Model for the XML PDS example SQL query"

6. Save your report as `inventoryreport_xml_``your_initials``.rdf`.

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

### 44.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 have provided to make sure your query is correct. Please note that you must update the paths to the Data Definition files with the location of the example files we provided.

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 have provided, `warehouse_inventory.xsd` and open it.

7. Under Data Source, click Browse to locate the XML file we have 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 Next.

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

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

    Description of "Figure 44-3 Data model for the XML PDS example with XML and SQL queries"

12. Save your report.

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

### 44.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 44-4 Data Model with a data link between a SQL query and an XML query

   Description of "Figure 44-4 Data Model with a data link between a SQL query and an XML query"

   You will notice that the WAREHOUSE_ID column is now highlighted at the bottom of Q_1, with a line pointing to the WAREHOUSE_ID1 column.

4. Save your report.

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

### 44.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 Labels page, adjust the labels as desired.

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

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

   Description of "Figure 44-5 Paper Design view for the XML PDS report"

8. Save your report.

You have created the layout for your paper report.

Note: You can also run the report we have provided in the result directory, called`inventory_report.rdf`. Before you can run the report, double-click the XML query in the Data Model view, and point the XML data source to the appropriate XSD and XML files.

### 44.2.5 Apply alternating row colors to your report

Now that you have 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 the following properties:

   - Under General Information, set the Name property to LineNo.

   - Under Column, make sure the Column Type property is set to Summary, and that the Datatype property is set 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 row colors:

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

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

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

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

5. In the PL/SQL Editor, enter the following PL/SQL code to change the text color of the alternating rows to blue:

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

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

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

7. Click Close.

   Note: Optionally, you can also change the fill colors of the alternating rows by following the steps in the above section, and using the following PL/SQL code. In this example code, we have changed the fill color of alternating rows to red and blue:
       PROCEDURE LineColors IS
       BEGIN
         if (:LineNo mod 2=0)
          then
            srw.set_foreground_fill_color('blue');
            srw.set_fill_pattern('solid');
         else
          srw.set_foreground_fill_color('red');
          srw.set_fill_pattern('solid');
         end if;
       END;

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 Main Section > Body > M_G_WAREHOUSE_ID_GRPFR > R_G_WAREHOUSE_ID > M_G_WAREHOUSE_ID1_GRPFR > R_G_WAREHOUSE_ID1.

2. Under R_G_WAREHOUSE_ID1, double-click F_PRODUCT_ID to display the Property Inspector.

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

3. In the Property Inspector, under Advanced Layout, double-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;

   Note: Make sure you do not touch the boldface text. Simply type in the code below this text to create the format trigger. You can copy and paste this code from the procedure provided in the `xmlpds_code.txt` file. Just copy the text under `Format Trigger Code.`

5. Add a format trigger for the following fields, using the same code as in the previous step. Be sure not to delete the first line of the code, where the format trigger name is defined:

   - 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 run your report to paper. Your report should look something like this:

   Figure 44-6 XML PDS Report with Alternating Row Colors

   Description of "Figure 44-6 XML PDS Report with Alternating Row Colors"

You have now applied alternating row colors to your report.

### 44.2.6 Filter your XML data using a group filter

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. The steps in this section will show you how to create a filter that will only show the inventory items for a user-defined quantity amount. The filter will be based on a parameter that the user can enter at runtime. You will then create a hierarchy in your data model to group the data in your report.

To create a user parameter and a group filter:

1. In the Object Navigator, under the User Parameters node, create a new user parameter called `P_MAXQTY`, with a Datatype of Number, Width of 20, and Initial Value of 50 (see Section 3.11.2, "Creating a user parameter").

2. In the Data Model view, double-click the G_WAREHOUSE_ID1 group in the XML query to display its Property Inspector.

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_ID1GroupFilter return boolean is
       begin
         if :quantity_on_hand < :P_maxqty then
            return (TRUE);
         else
         	 return (false);
         end if;
       end;

   Note: You can also copy and paste this code from the provided file called `xmlpds_code.txt`. Copy the code under the heading "Group Filter Code."

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.

8. Click the Run Paper Layout button in the toolbar to run your report to paper. Notice how a Parameter Form now displays where you can adjust the quantity of items displayed in your report.

   You can also run the provided file `Examples``\XML_PDS\result\inventoryreport.rdf` to view the results in Reports Builder.

9. Save your report.

To create a hierarchy for the XML query:

1. In the Data Model view, click the PRODUCT_ID column in the XML query, then drag it between the query name and the G_WAREHOUSE_ID1 group.

   Your data model should look like this:

   Figure 44-7 Data Model with Group Hierarchy

   Description of "Figure 44-7 Data Model with Group Hierarchy"

   Note: Notice in the above image that a green circle displays above G_WAREHOUSE_ID1. This circle indicates that a group filter has been created for the group.

2. Save your report. You have now created a group hierarchy that sorts the data in your report.

## 44.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. Notice how the Parameter Form now displays, with the initial value of 50.

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

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

Description of "Figure 44-8 Final Paper Design view of the XML PDS example report"

## 44.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 Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".