Scouring the forum this morning I almost had an 'instant message' session with Darice, we were pinging the forum back and forth so quickly - a great way to communicate and really shows the value of the forum. There were problems getting counting working when re-grouping. We ended up exchanging emails and I think things are going to be working now.
Another thread on counting came up today too and is worthy of mention here. It came from Vadim (nagornyi):
Here is sample XML:
<ROWSET>
<ROW>
<DEPT>A1</DEPT>
<DATA>2112</DATA>
</ROW>
<ROW>
<DEPT>A1</DEPT>
<DATA>7985</DATA>
</ROW>
<ROW>
<DEPT>B1</DEPT>
<DATA>8452</DATA>
</ROW>
<ROW>
<DEPT>C2</DEPT>
<DATA>2159</DATA>
</ROW>
</ROWSET>
Using Table Wizard, I am getting this report:
A1 2112
7985
B1 8452
C2 2159
Everything OK, but at the report header I need to count the number of DEPT, i.e. 3. How to get it? Thanks.
I have to admit I rushed at this one and missed the crux of the issue - Im making amends here and sharing a hidden feature of the RTF templates. I missed the fact that Vadim wanted the number of unique DEPTs not the total number ie 3 not 4.
There are ways to do this programmatically in XSL but its nasty, very nasty. Thats why we have a function that makes life tres simple - 'distinct_values'. Its a Publisher extension that does all the heavy lifting for you in the background, it takes the format:
xdoxslt:distinct_values(Node name)
and returns a numeric value.
In Vadim's case
<?count(xdoxslt:distinct_values(/ROWSET/ROW/DEPT))?>will do the trick and return '3' rather than '4'.
A word of caution, its expensive, just like DISTINCT in SQL its going to require a lot of processing to read the node tree in and process it eliminating duplicates. There are other slightly less expensive ways to achieve the same using XPATH but for now, just use it wisely.
An update - Nilanshu came up with the XPATH solution I was aluding to above ... remember the data must be ordered by the data you want to count and remove the duplicates from.
If your data is ordered, then you may simply count all ROWS that have a different DEPT than the previous one, keeping into account the first row of course. This might give you a better performance for large XML files.
<?count(ROW[position()=1 or ./preceding-sibling::ROW[1]/DEPT!=DEPT])?>
XPATH 2.0 does provide something similar, but AFAIK XPATH 2.0 does not work in Oracle XMLP yet.
<?count(distinct-values('DEPT')?>
