0 
0 Last week I experimented with XML-Publisher a bit. The goal was to figure how to built a matrix report with simular layout as an Oracle Reports matrix report. Would it be easier, better I was not sure but curious to find out. As most developers know building an Oracle matrix report can be painfull when you want to make changes in a later stage to the layout. You realy have to know which frame tp select when you want to change the size of the fields. So what do you need to know and do
First create the query In the data modelwhich will retrieve the data for the X-column, Y-column and the grid cell. The most simple query will retrieve three columns. In my case I used the datamodel of one of my customers.
select r.component as comp
, r.text_value as text_value
, r.sample_id as sample_id
from nais_results r
where r.submission_id =:P_SUBMISSION_ID
As you can see I used the standard way to declare a parameter in the query P_SUBMISSION_ID
Now I have to define a list of values based needed to populate the parameter P_SUBMISSION_ID which I will declare in one of the next steps
Select submission_id
from nais_submissions
order by 1
Now I can define the parameter P_SUBMISSION_ID. The parameter will be of the type MENU so I can asign the list of values I created prior.
We can now focus on the layout after we have exported the data from the view tab. We can load the data using the plugin XML-Publisher desktop created in MS Word.
- <ROWSET>
- <ROW>
<COMP>OQ comp 1 ABSORBANCE UNITS</COMP>
<TEXT_VALUE>1</TEXT_VALUE>
<SAMPLE_ID>20070125-200</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ Comp 2 PERCENT</COMP>
<TEXT_VALUE>2</TEXT_VALUE>
<SAMPLE_ID>20070125-200</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ comp 1 ABSORBANCE UNITS</COMP>
<TEXT_VALUE>4</TEXT_VALUE>
<SAMPLE_ID>20070125-201</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ Comp 2 PERCENT</COMP>
<TEXT_VALUE>6</TEXT_VALUE>
<SAMPLE_ID>20070125-201</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ comp 1 ABSORBANCE UNITS</COMP>
<TEXT_VALUE>#</TEXT_VALUE>
<SAMPLE_ID>20070125-202</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ Comp 2 PERCENT</COMP>
<TEXT_VALUE>#</TEXT_VALUE>
<SAMPLE_ID>20070125-202</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ comp 1 ABSORBANCE UNITS</COMP>
<TEXT_VALUE>#</TEXT_VALUE>
<SAMPLE_ID>20070125-203</SAMPLE_ID>
</ROW>
- <ROW>
<COMP>OQ Comp 2 PERCENT</COMP>
<TEXT_VALUE>#</TEXT_VALUE>
<SAMPLE_ID>20070125-203</SAMPLE_ID>
</ROW>
</ROWSET>
Start with an empty Word document and save the document in the rtf format. Insert a table with the dimension 2×2 (2 columns and 2 rows) in the document and type the following text in each column using either the basic RTF method or the Form field method. In this example I will use the basic RTF method. Type the following code in column 2 of row 1:
<?for-each-group@column:ROW;./COMP?><?COMP?><?end for-each-group?> Note that the fields in red reference nodes in my XML output. The code creates a repeating frame for the comp fiels, diplays in the frame the comp field and closes the group
Type the following code in column 1 row 2:
<?for-each-group@section:ROW;./SAMPLE_ID?><?sort:SAMPLE_ID;’ascending’;data-type=’text’?><?SAMPLE_ID?><?variable@incontext:SAM_ID;SAMPLE_ID?> Note that again the fields in red are reference nodes in my XML output file and the field in green a declaration is of the parameter SAM_ID. This parameter is needed to determine if I need to populate a matrix cell. The code creates a group for each Sample_id field ordered ascending, then the sample_id field is displayed nd a variable SAM_ID is defined which will hold the sample_id for this group
Type the following code in column 2 row 2
<?for-each-group@cell://ROW;./COMP?><?if:count(current-group()[SAMPLE_ID=$SAM_ID])?><?current-group()[SAMPLE_ID=$SAM_ID]/TEXT_VALUE?><?end if?><?end for-each-group?><?end for-each-group?> Note that again the fields in red are reference nodes in my XML output file and the field in green a declaration is of the parameter SAM_ID. This parameter is needed to determine if I need to populate a matrix cell. We create a group for each comp and check if the sample_id of this group is the same as the parameter SAM_ID. If this is the case than we display the text_value in the matrix cell. We close all groups and if statements with the proper tags.
now we finished the layout and after saving the report and uploading the template you will be able to run it.
Changing the layout of the matrx is easy now. We can change the cell size, table layout, colors and fonts in a blink.
Hi
I face small problem while I am generating XML Report i.e
for one item we have two ingredients  ,i introduce  above tags in this scenario ,it display only one item ,second item not displayed,
pls help me
Thanks
Harish
Hi,
I have problem.
I need to build matrix report with 2 columns are repeating in group of Vendors.
VENDOR NAME1 VENDOR NAME2 VENDOR NAME3
LINE NUM PRICE UNIT PRICE PRICE UNIT PRICE PRICE UNIT PRICE
1 25 5 10 2 50 10
2 4 2 8 4 12 6
Thank’s
Masha
Excellent post. The 10.1.3.2 version of BI Publisher (new name of XML Publisher) has a drag-and-drop crosstab builder. This became generally available yesterday (http://www.oracle.com/corporate/press/2007_jan/012907-OBIEE.html) and should be available for download soon.