I couldnt really say 'Crosstabs on Steroids' now could I, not with 755 being hit over the weekend and all the controversy surrounding that particular batter, what happended to 'innocent until proven guilty' - I guess the modern day press and media happened. I used to attend Giants games resonably regularly even before the 'Balcogate' mess and the excitement that Barry Bonds generated in the park when he stepped up to bat was infectious. Even if he did 'enhance' his game physically, I do not think there is a drug out there that can improve you ability to see and react to a ball being thrown at near 100 miles per hour, in my humble opinion its still an astounding feat.
Anyhoo, moving on to an even more exciting and thought provoking subject, good question from Chrissy on the forum this weekend that I think deserves an investigation and an answer:
With the Analyzer I can create a report for my data with two column fields.
I would like to rebuild this in my RTFTemplate, but with the cross table assistant it's not possible to do this and I haven't found anything about this in the documentation.
Can anybody give me some hints & tips how to do it?
When creating a pivot table in excel you can add 2 data items. Can this also be realized in BI Publisher?
The Template Builder for Word does provide a dialog to help you build a cross tab but that will only help with a single 'measure' or 'data' value. Assuming we have the following data:
<?xml version='1.0' encoding='UTF-8'?>
<SALES>
<SALE>
<YEAR>2000</YEAR>
<REGION>Americas</REGION>
<SOFTWARE>1200</SOFTWARE>
<HARDWARE>850</HARDWARE>
<SERVICES>2000</SERVICES>
</SALE>
<SALE>
<YEAR>2000</YEAR>
<REGION>EMEA</REGION>
<SOFTWARE>1000</SOFTWARE>
<HARDWARE>800</HARDWARE>
<SERVICES>1100</SERVICES>
</SALE>
<SALE>
<YEAR>2000</YEAR>
<REGION>APAC</REGION>
<SOFTWARE>900</SOFTWARE>
<HARDWARE>1200</HARDWARE>
<SERVICES>1500</SERVICES>
</SALE>
<SALE>
<YEAR>2001</YEAR>
<REGION>Americas</REGION>
<SOFTWARE>2200</SOFTWARE>
<HARDWARE>950</HARDWARE>
<SERVICES>2100</SERVICES>
</SALE>
<SALE>
<YEAR>2001</YEAR>
<REGION>EMEA</REGION>
<SOFTWARE>1100</SOFTWARE>
<HARDWARE>900</HARDWARE>
<SERVICES>1300</SERVICES>
</SALE>
<SALE>
<YEAR>2001</YEAR>
<REGION>APAC</REGION>
<SOFTWARE>1000</SOFTWARE>
<HARDWARE>1400</HARDWARE>
<SERVICES>1200</SERVICES>
</SALE>
</SALES>
Line of business (LOB) sales data across multiple regions and years.
With the dialog we can build a crosstab with Years as our column headers and Regions as our row headers.
We can generate something like
Now, as Chrissy asks what if we wanted something like this:
The dialog can not help you here, we are going to have to dive into code. Let me say here that we are looking to address this in a future release of the template builder but for now, we're coding.
To get this to work I had to beef up the RTF layout to accomodate the 3 column sets (HARDWARE,SOFTWARE and SERVICES).
The graphic above shows the original 'wizard' generated cross tab at the top and the new and improved 'ripped' crosstab below. Things of note:
1. The embedded LOB table with the LOB titles, these need to be in a cell that will get repeated with the YEAR component.
2. The extra columns to handle the extra two LOB values
The other changes are hidden in the fields.
1. On the 999.00 - fields on the REGION row, notice the G and E wrap the three columns now. Each field contains:
<?sum ($G1[(./YEAR=current()/YEAR)]/<<LOB_NAME>>)?>
2. Similarly on the 'Total' row we have G and E wrapping the 3 columns, each total value containing:
<?sum(current-group()/<<LOB_NAME>>)?>
3. In the 'Total' column we need some XPATH to ensure we get the right numbers summed. For the regular totals:
<?sum ($G1/*[name()="HARDWARE"or name()="SOFTWARE" or name()="SERVICES"])?>
The XPATH is ensuring that the HARDWARE, SOFTWARE and SERVICES values are summed.
Same for the bottom right cell:
<?sum ($T/*[name()="HARDWARE"or name()="SOFTWARE" or name()="SERVICES"])?>
Thats it, not too much effort, files here. Yes, I admit you need some XPATH experience and a little XSL but other than that you too can achieve 'ripped abs', sorry 'crosstabs'.
