Sorting outer group based on inner group in BI Publisher

Consider the following dataset

<top>

<data_node> <text_field>d</text_field> <num_field>1</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>2</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>3</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>4</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>5</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>6</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>7</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>9</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>8</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>10</num_field> </data_node>

</top>

The requirement is to group the text_field data together and to order it based on num_field. In the above XML data source, text_field ‘d’ has the smallest number value i.e. 1. So, according to the requirement, D should be displayed on the top with all its num_fields i.e. (1,2,11). After D, C should be  displayed since C has a num_field value of 2. C should again be followed by all its num_fields i.e. (2,8,10)

So the output should be

D

1,2,11

C

2,8,10

A

3,5,9

B

4,6,7

After a day of dealing with arrays, variables and tons of rearrangement, the following solution looked most elegant

<?for-each-group:data_node;./text_field?><?sort:current-group()/num_field;'ascending';data-type='number'?>

<?text_field?>

<?for-each@inlines:current-group()/num_field?><?sort:current-group()/num_field;'ascending';data-type='number'?><?.?>,

<?end for-each?>

<?end for-each-group?>

 

Magic is done by the for-each-group statement at the top. When the field on which grouping is done (text_field in our case) is different from the field on which sorting is done(num_field in our case), BI Publisher sorts the group-field based on the sort-field

The sort statement inside for-each:current-group() statement is to sort the num_field data inside each text_field group

Simple RTF Template report in BI Publisher

RTF- Rich Text Format is a document file format which is used for common word processing applications such as Microsoft Word. Word file is saved as a RTF just by giving ‘.rtf’ extension to the file name. This file format is very useful and easy to create reports for business scenario accomplished with oracle’s Business Intelligence Publisherknown as BI publisher.

This is a very powerful and efficient tool for creating business reports. There is tons of content to learn about BI publisher. We will start learning this one by one so let’s start with creating one simple report.

For that we require one XML file as input or to load into RTF. Consider a book.xml which is easily available on web and you can easily access it for practice. I paste some sample XML data from book.xml so that you can identify and use same XML.

Now we have to create report considering following one simple requirement.

Requirement:

Get the detail list of the books of ‘Price’<10.

Solution:

Now for the given XML there are 12 books. We have to filter out those books from the list which has their price less than 10. First of all we will list the total book-list then will try to apply this filter to get the requirement work. Now to get the list of total books we will proceed step by step.

Step #1: Create RTF file.

Open a blank word document. Save that word document with ‘.rtf’ extension. Lets give here ‘books.rtf’.

Step #2: Load the XML.

Now we have to load that Books.xml into created Books.rtf. To do this, click on the ‘Add-Ins’ from the title bar of word file. Following window will pop up.

Click on ‘Data’ and then ‘Load XML Data’. Refer below image.

Then you have to select the XML which you want load into RTF. If the XML load is successful then following window will pop up.

Step #3: Insert Table.

As the XML is loaded successfully next step we have to get the table form of this XML data. To do so navigate to ‘Insert’ tab which is next to ‘Data’. Refer above image. After that select Table wizard as shown in below image.

After selecting ‘Table Wizard’ following window will pop up.

Select ‘Table’, click on ‘Next’. You will see the hierarchies available in loaded XML. But for our XML there is only one so its showing that one as default. Refer below image. Click ‘Next’

Above image shows list of all the field names for particular Book ID tab. You can select which are necessary. In our case we have to get detailed list so will select all field names. Those which are selected will get shifted to the right pane as below.

If we click on ‘Next’, click on ‘Finish’. Following table will get loaded in your RTF pane.

You can adjust the column width, font, font size, column header etc. as per your requirement.

Step #4:After this table get inserted we have to check it’s giving correct output or not. To do so navigate through ‘Preview’ tab. You will find options like PDF, HTML etc. refer below image. You will get the idea.

These PDF. HTML, EXCEL etc. options gives facility to show output in different format. Let’s take our output in PDF format. Click on ‘PDF’. If your word document is not saved as RTF file it will ask you to save it in RTF format. Just save it using ‘.rtf’ extension. Otherwise it will show your PDF output as below.

Step #5: Work on requirement.

Now we have to get our requirement work. For that we have to apply some logic like “If price < 10 then show corresponding records”. Follow the following procedure. You will get the clear idea.

1. As we have to deal with ‘price’, double click on it, following window will pop up.

2. Click on ‘Advance’ Tab.

This symbol <? ?> gives the value of field whichever is inside it. As per now we are getting ‘price’ value because of it.

3. Now we have to apply our logic. To do so write following code or query in advance tab as shown in following image.

4. Now run the report by navigating through ‘Preview’ -> ‘PDF’. You will get detailed list of books of having price less than 10 as below. All the record of having price > 10 will get omitted.

I hope this will help you to create your BIP report. This is efficient and easy to understand method I followed when I started working on BIP. There are another method to put ‘If’ condition also but it needs some modification. We will see those in detail along with “Group by”, “Sort”, “Color formatting” etc. from next article.

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.

Dept	Analyst	Clerk	Manager	Total
10		1300	2450	3750
20	6000	1900	2975	10875
30		950	2850	3800
Total	6000	4150	8275	18425

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>

http://www.apps2fusion.com/training_demo/kishorer/cross_tab/QUARTER.XML

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?>

Modified RTF template:

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

Default Text Entry	Form Field Help Text	Description
header column	<?horizontal-break-table:1?>	Defines the first column as a header that should repeat if the table breaks across pages. 1 in the tag refers the number of columns of the header that should repeat.
for:	<?for-each-group@column: RESULTS;YEAR?>	Uses the regrouping syntax (see Regrouping the XML Data, page 2-74) to group the data by YEAR; and the @column context command to create a table column for each group (YEAR).
YEAR	<?YEAR?>	Placeholder for the YEAR element.
end	<?end for-each-group?>	Closes the for-each-group loop.
for:	<?for-each-group@section:RESULTS;./ INDUSTRY?> <?variable@incontext:IND;INDUSTRY?>	Begins the group to create a table row for each INDUSTRY. Store the industry value in a variable IND using variable tag. When displaying the corresponding results for a year, the value in the variable IND is used to compare the value in the INDUSTRY element.
INDUSTRY	<?INDUSTRY?>	Placeholder for the INDUSTRY element.
for:	<?for-each-group@cell://RESULTS;YEAR?>	Uses the regrouping syntax to group the data by YEAR; and the @cell context command to create a table cell for each group (YEAR).
sum(Sales)	<?if:count(current-group()[INDUSTRY=$IND])?> <?sum(current-group()[INDUSTRY=$IND]/SALES)?> <?end if?>	Compare the industry with the value in the variable IND, if the count>0, then it returns TRUE, so the code written inside IF clause executes i.e. Sums the sales for the current group (YEAR) is displayed in the cell.
end	<?end for-each-group?>	Closes the for-each-group statement.
end	<?end for-each-group?>	Closes the for-each-group statement.

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.

RTF Template for that example can be downloaded from here

XML file can be downloaded from here