Cross Tab in BIP

The columns of a cross-tab report are data dependent. At design-time you do not know how many columns will be reported, or what the appropriate column headings will be. Moreover, if the columns should break onto a second page, you need to be able to define the row label columns to repeat onto subsequent pages.

You can use the built-in function of Oracle BI Word Plugin to create an Cross Tab. If you want more or less functionality, you have to edit your existing cross tab. So you have to know what every tag means or how to realize.. The following example shows how to design a simple cross-tab report.

This example uses the following XML sample:

&ltROWSET&gt &ltRESULTS&gt &ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt &ltYEAR&gt2008&lt/YEAR&gt &ltQUARTER&gtQ1&lt/QUARTER&gt &ltSALES&gt3000&lt/SALES&gt &lt/RESULTS&gt &ltRESULTS&gt &ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt &ltYEAR&gt2008&lt/YEAR&gt &ltQUARTER&gtQ2&lt/QUARTER&gt &ltSALES&gt2400&lt/SALES&gt &lt/RESULTS&gt &ltRESULTS&gt &ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt &ltYEAR&gt2007&lt/YEAR&gt &ltQUARTER&gtQ1&lt/QUARTER&gt &ltSALES&gt1200&lt/SALES&gt &lt/RESULTS&gt &ltRESULTS&gt &ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt &ltYEAR&gt2007&lt/YEAR&gt &ltQUARTER&gtQ2&lt/QUARTER&gt &ltSALES&gt3400&lt/SALES&gt &lt/RESULTS&gt &ltRESULTS&gt &ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt &ltYEAR&gt2006&lt/YEAR&gt … &lt/RESULTS&gt &ltRESULTS&gt &ltDEPARTMENT&gtHuman Resources&lt/DEPARTMENT&gt … &lt/RESULTS&gt &ltRESULTS&gt &ltDEPARTMENT&gtIT&lt/DEPARTMENT&gt … &lt/RESULTS&gt &lt/ROWSET&gt

From this XML we will generate a report that shows each department and totals the sales by year as shown in the following figure:

The template to generate this report is shown in the following figure. The form field entries are shown in the subsequent table.

The form fields in the template have the following values:

Note: only the first row uses the @column context to determine the number of columns for the table. All remaining rows need to use the @cell context to create the table cells for the column.

Additional Information:

Defining Columns to Repeat Across Pages:
If your table columns expand horizontally across more than one page, you can define how many row heading columns you want to repeat on every page. Use the following syntax to specify the number of columns to repeat:

&lt?horizontal-break-table:number?&gt

where number is the number of columns (starting from the left) to repeat.

Note that this functionality is supported for PDF output only..

Regrouping the XML Data:
The RTF template supports the XSL 2.0 for-each-group standard that allows you to regroup XML data into hierarchies that are not present in the original data. With this feature, your template does not have to follow the hierarchy of the source XML file. You are therefore no longer limited by the structure of your data source.

Using the Context Commands:
To support this requirement, BI Publisher provides a set of context commands that allow you to define the context (or placement) of the processing instructions. For example, using context commands, you can:

• Specify an if statement in a table to refer to a cell, a row, a column or the whole table.
• Specify a for-each loop to repeat either the current data or the complete section (to create new headers and footers and restart the page numbering).
• Define a variable in the current loop or at the beginning of the document.

BI Publisher supports the following context types:

About these ads