Cross Tab/Pivot analysis -RTF Templates

Pivot table or Cross tab analysis can done in following way in the RTF templates

Assume we have xml data like below structure.

<ROWSET>
<FRUIT_SALES>
<FRUIT>Mangos</FRUIT>
<YEAR>2004</YEAR>
<SALES>123</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Mangos</FRUIT>
<YEAR>2005</YEAR>
<SALES>23</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Water Melons</FRUIT>
<YEAR>2004</YEAR>
<SALES>143</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Water Melons</FRUIT>
<YEAR>2005</YEAR>
<SALES>43</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Apples</FRUIT>
<YEAR>2004</YEAR>
<SALES>145</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Apples</FRUIT>
<YEAR>2005</YEAR>
<SALES>45</SALES>
</FRUIT_SALES>
</ROWSET>

From this XML we will generate a report that shows each Fruit and total 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:

Text to Display	Form Field Help Text	Description
FRUIT HEADER	<?horizontal-break-table:1?>	Defines the first column as a header that should repeat if the table breaks across pages.
for1	?for-each-group@column:FRUIT_SALES;YEAR?	Uses the regrouping syntax 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
for2	<?for-each-group:FRUIT_SALES;FRUIT?>	Begins the group to create a table row for each FRUIT
FRUIT	<?FRUIT?>	Placeholder for the FRUIT element.
for3	?for-each-group@cell:current-group();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)	<?sum(current-group()//SALES)?>	Sums the sales for the current group (YEAR).
end	<?end for-each-group?>	Closes the for-each-group loop
end	<?end for-each-group?>	Closes the for-each-group loop

Note that 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.