Ripped Crosstabs

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.


CrossTab2:


We can generate something like


CrossTab3:


Now, as Chrissy asks what if we wanted something like this:


CrossTab2:


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).


CrossTab4:


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'.

Comments:

Hi Tim, I have a question that is related to this. I have very little XPATH or XSL experience, but I used what you have above to create a report that gives me a cross-tab report summing up a count by YYYY-MM. The cross-tab and the summing part works, but the columns are not in the correct order. For example, it is giving me 2010-03 2010-05 2010-04 2009-09 2009-11 2009-06 .... How can I make it ordered like this? 2009-06 2009-09 2009-11 2010-03 2010-04 2010-05 .... Thanks for any help you can give. Jim

Posted by Jim on June 14, 2010 at 02:59 AM MDT #

Hi Jim I think you might have to use a little trickery in your sort column. Can you either 1. Get the date into the format YYYYMM 2. Take the current format YYYY-MM and get it to YYYYMM Once you have that yu can then sort by YYYYMM as a number ascending and the columns should render correctly. Regards Tim

Posted by Tim Dexter on June 14, 2010 at 03:05 AM MDT #

Thanks because of this! I’ve been searching all more than the web for that data.

Posted by never fail list building bonus on August 22, 2010 at 07:45 PM MDT #

HI SEXY.. I DON'T KNOW,BUT YOU WANT TO TALK A LITTLE BIT THOUGH.HOLLA AT ME ... KEYOSHA _GILTON@YAHOO.COM ,

Posted by Guy Bonnel on September 25, 2010 at 07:36 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today