Apps To Fusion

Cross-Tab/Matrix Feature in XML Publisher

A matrix (crosstab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. In other words, a matrix report displays the data in a grid. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.

A simple matrix report might look like the one below, where the jobs are fetched dynamically for the first row and the departments are fetched dynamically for the first column. The salaries of the employees with the job in a particular department are places along the grid. The aggregate totals are displayed in the last row and last column.

Cross-Tabs are covered under advanced report layouts in XML Publisher. It would be helpful to first read the XMLP basics before continuing with cross-tabs.

XMLP Basics Reference Link: XMLP Basics

Cross-Tabs feature in XML Publisher is synonymous to Matrix reports in Oracle Reports 6i. I will first explain the example given in XML Publisher User Guide under the section "Creating RTF Template > Advanced Report layouts> Cross-Tab Support". The RTF template given in user guide doesn’t give the desirable results, so I will explain how the code should be modified to get the correct results.

XML Data File: The following is the xml data file used for our example. The xml data file contains the quarter sales results of different industries over a period of time. I have slightly modified the data such that there are no quarter sales results for “Home Furnishing” industry for the year 2003.

<ROWSET>

<RESULTS>

<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>

<YEAR>2005</YEAR>

<QUARTER>Q1</QUARTER>

<SALES>1000</SALES>

</RESULTS>

<RESULTS>

<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>

<YEAR>2005</YEAR>

<QUARTER>Q2</QUARTER>

<SALES>2000</SALES>

</RESULTS>

<RESULTS>

<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>

<YEAR>2004</YEAR>

<QUARTER>Q1</QUARTER>

<SALES>3000</SALES>

</RESULTS>

<RESULTS>

<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>

<YEAR>2004</YEAR>

<QUARTER>Q2</QUARTER>

<SALES>3000</SALES>

</RESULTS>

<RESULTS>

<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>

<YEAR>2003</YEAR>

<QUARTER>Q1</QUARTER>

<SALES>2500</SALES>

</RESULTS>

<RESULTS>

<INDUSTRY>Home Furnishings</INDUSTRY>

<YEAR>2005</YEAR>

<QUARTER>Q1</QUARTER>

<SALES>1200</SALES>

</RESULTS>

<RESULTS>

<INDUSTRY>Home Furnishings</INDUSTRY>

<YEAR>2003</YEAR>

<QUARTER>Q1</QUARTER>

<SALES>1500</SALES>

</RESULTS>

</ROWSET>

From this xml we will generate a report that shows each industry and total the quarter sales by year as shown in the following figure:

Template from the User Guide:

The template to generate this report is shown in the following figure.

You can download the template from this link
http://www.apps2fusion.com/training_demo/kishorer/cross_tab/sample_rtf.rtf

The form field entries are shown in the subsequent table.

Using the above template, the report will display the data as below.

But the output we get using the template mentioned in the user guide is not proper, we didn’t have the quarter sales results for the year 2004 but the value 1500 which has to be shown under 2003 column is actually shown under 2004.

The reason for the erroneous result is, if the cell value (or results of the industry for a year) doesn’t exist, the cell value would be null. As the cell value is null, the table cell is getting collapsed and the actual values are shown side by side. To overcome the problem, we have to check if industry has results in that year or not.

So to overcome this problem, we have to check if industry exists in that year or not. This can be done, by storing the industry value in a local variable and use this value to compare to the value under the INDUSTRY element if at all it exists for a year. If the values are same, the quarter sales result is displayed under the year else a null value is displayed. By including the null value in the table cells even if the industry element doesn’t exist for a year, the correct values are displayed.

Storing of industry value is done in for field field before INDUSTRY form field. Below is the form field code snippet:

<?for-each-group@section:RESULTS;./ INDUSTRY?>

<?variable@incontext:IND;INDUSTRY?>

In the above code, I’m storing the industry name i.e INDUSTRY in variable called IND.
Now when I display the corresponding quarter sales result for a year, I will check if the industry exists for that year using count function. If the year doesn’t exist in industry, I won’t display any value for sales result. This is handled by code:

<?if:count(current-group()[INDUSTRY=$IND])?>

<?sum(current-group()[INDUSTRY=$IND]/SALES)?>

<?end if?>

The above logic is implemented by modifying some of the tags of the template given in the user guide. The modified RTF template can be found below:

Modified RTF template:

The form field entries are shown in the subsequent table. The modified xml tags are shown in bold.

After the above modifications to the template, the result can be seen below:

For additional reading on “Regrouping the XML Data” and “Using Context Commands”, read the sections "Creating RTF Template > Data Handling > Regrouping the XML Data" and "Creating RTF Template > Advanced Design Options > Using Context Commands" respectively in the XMLP user guide.

You find one more example on cross-tabs here.

Comments (3)

Subscribe to this comment's feed

Reg Header & Footer Display in RTF designed using MS Word 2007
written by Rama Krishna Mateti , September 18, 2008

Hi,

This is Rama Krishna Mateti.
How r u doing?

I have developed an RTF template and I inserted Header and Footer in the template.
When I test with the sample XML data for the template I could not able to see the Header and Footer such as Logo, Page Number...etc in the PDF but I could see
the Output in PDF when I run the same template and input XML file in the other Computer.
When I uploaded the template and register it with Oracle Apps I could succesfully see the Header and Footer report along with Output in the PDF File.
Could you tell me do I need to make any modifications/Setting in the MS Word so that I could see the header & footer in the PDF?
Thanks in advance

report abuse

vote down

vote up

Votes: +1

Reg Header & Footer Display in RTF designed using MS Word 2007
written by William , October 06, 2008

Hi Rama,

Go to word and define header and footer by selecting templates and do the alignment. It works fine for me, and able to view page 1 of 1 and repeating header in all the pdf pages.
Save word file as rtf and upload thru xml publisher administrator.

Regards,
William.

report abuse

vote down

vote up

Votes: +0

Crosstab Report
written by sidarth , October 16, 2008

Hi...

I am developing a cross tab report in which I have row header as the year like 2008,2007,2006....column header as months jan,feb,mar...and the data area contains the respective data...I need to format the background color of the rows and columns which are below the date of the report(report is ran for a particular month like mar-0..I was able to format the background color of all the rows that fall below(ex:0 the year of the report(using #C0D27F)..
and I need to format the 3(till march) columns in the yr 2008.

report abuse

vote down

vote up

Votes: +1

Write comment

Name

Email

Title

Comment

smaller | bigger

Subscribe via email (Registered users only)

Write the displayed characters

Add Comment