TECH TIP

JANUARY 2017

ORACLE BI (PCM, UNIFIER, P6)

RETRIEVE ALL DATA ROWS TO TEST AND BUILD REPORTS

DOWNLOAD THE PDF HERE

 

Introduction
Starting with Oracle BI Publisher version 11.1.8.0.0 and later, when in the Data Model design window, the “Data” preview tab is now limited to 200 rows of data. See Oracle Document 1679689.1 in the knowledge base for details of this change.

Often, this partial data representation is enough to understand the data structure and design your report. In other instances, ONLY a complete data output allows you to properly design and develop your SQL syntax design and the resulting report layout. Validation of proper grouping, and also being able to see all data rows, can be essential in the report design.

Since there is no option in these later versions to output a complete data set from your query design, the below illustrates the workaround to create a complete data sampling in your XML that can be used when building your report layout (RTF).

Steps

  1. When building the data model, on the Data Sets, Data tab, you have the option to view and export (to XML) your sample data. Typically, you would view and output this sample, then save (export) the data to XML. You would then develop your report layout in BI Publisher Desktop with the XML file used for the data source.
  1. If 200 rows or less is sufficient, then you can proceed as you did before from the data model design output.
  2. If this is insufficient, then follow these steps to output a complete XML data sampling:
    1. Create the Data Model as you did before.
    2. Now, create a simple layout design and then link that layout design to your data model.
    3. Click the View a List in the layout design, select the Output Formats and make sure that Data (XML) option is checked and then SAVE the layout.
  1. Click “View Report”.
  1. Next, click the action dropdown and select Export, XML Data as the option.
  1. Save this XML output on your local machine.
    1. Use this XML to inspect the entire data set for completeness and integrity.
    2. Or use this data output in BI Publisher Desktop (Load Data option).
  1. Once loaded, the BI Preview options (PDF, etc.) will output the report with the complete data as you would expect.

Summary
While simple reports may not require these extra steps, complex reports often can only be validated for accuracy if you run against the entire data set.