X

An Oracle blog about BI Publisher

New Skool Crosstabbing

A while back I spoke about having to go back to BIP's original crosstabbing solution to achieve a certain layout. Hok Min has provided a 'man' page for the new crosstab/pivot builder for 10.1.3.4.1 users. This will make the documentation drop but for now, get it here!

The old, hand method is still available but this new approach, is more efficient and flexible. That said you may need to get into the crosstab code to tweak it where the crosstab dialog can not help. I had to do this, this week but more on that later.

The following explains how the crosstab wizard builds the crosstab and what the fields inside the resulting template structure are there for.

To create the crosstab a new XDO command "<?crosstab:...?>" has been created.













































XDO Command: <?crosstab: ctvarname; data-element; rows; columns; measures; aggregation?>


Parameter


Description


Example


Ctvarname


Crosstab variable name. This is automatically generated by the Add-in.


C123


data-element


This is the XML data element that contains the data.


"//ROW"


Rows


This contains a list of XML elements for row headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the row header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.

There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.


"Region{,o=a,t=t}, District{,o=a,t=t}"

In the example, the first row header is "Region". It is sort by "Region", order is ascending, and type is text. The second row header is "District". It is sort by "District", order is ascending, and type is text.


Columns


This contains a list of XML elements for columns headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the column header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.

There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.


"ProductsBrand{,o=a,t=t}, PeriodYear{,o=a,t=t}"

In the example, the first column header is "ProductsBrand". It is sort by "ProductsBrand", order is ascending, and type is text. The second column header is "PeriodYear". It is sort by "District", order is ascending, and type is text.


Measures


This contains a list of XML elements for measures.


"Revenue, PrevRevenue"


Aggregation


The aggregation function name. Currently, we only support "sum".


"sum"


Using the Oracle BI Publisher Template Builder for Word add-in, we are able to construct the following Pivot Table:

pivot1.gif

The generated XDO command for this Pivot Table is as follow:

<?crosstab:c547; "//ROW";"Region{,o=a,t=t}, District{,o=a,t=t}"; "ProductsBrand{,o=a,t=t},PeriodYear{,o=a,t=t}";

"Revenue, PrevRevenue";"sum"?>

Running the command on the give XML data files generates this XML file "cttree.xml". Each XPath in the "cttree.xml" is described in the following table.



























































































































































































































































Element


XPath


Count


Description


C0


/cttree/C0


1


This contains elements which are related to column.


C1


/cttree/C0/C1


4


The first level column "ProductsBrand". There are four distinct values. They are shown in the label H element.


CS


/cttree/C0/C1/CS


4


The column-span value. It is used to format the crosstab table.


H


/cttree/C0/C1/H


4


The column header label. There are four distinct values "Enterprise", "Magicolor", "McCloskey" and "Valspar".


T1


/cttree/C0/C1/T1


4


The sum for measure 1, which is Revenue.


T2


/cttree/C0/C1/T2


4


The sum for measure 2, which is PrevRevenue.


C2


/cttree/C0/C1/C2


8


The first level column "PeriodYear", which is the second group-by key. There are two distinct values "2001" and "2002".


H


/cttree/C0/C1/C2/H


8


The column header label. There are two distinct values "2001" and "2002". Since it is under C1, therefore the total number of entries is 4 x 2 => 8.


T1


/cttree/C0/C1/C2/T1


8


The sum for measure 1 "Revenue".


T2


/cttree/C0/C1/C2/T2


8


The sum for measure 2 "PrevRevenue".


M0


/cttree/M0


1


This contains elements which are related to measures.


M1


/cttree/M0/M1


1


This contains summary for measure 1.


H


/cttree/M0/M1/H


1


The measure 1 label, which is "Revenue".


T


/cttree/M0/M1/T


1


The sum of measure 1 for the entire xpath from "//ROW".


M2


/cttree/M0/M2


1


This contains summary for measure 2.


H


/cttree/M0/M2/H


1


The measure 2 label, which is "PrevRevenue".


T


/cttree/M0/M2/T


1


The sum of measure 2 for the entire xpath from "//ROW".


R0


/cttree/R0


1


This contains elements which are related to row.


R1


/cttree/R0/R1


4


The first level row "Region". There are four distinct values, they are shown in the label H element.


H


/cttree/R0/R1/H


4


This is row header label for "Region". There are four distinct values "CENTRAL REGION", "EASTERN REGION", "SOUTHERN REGION" and "WESTERN REGION".


RS


/cttree/R0/R1/RS


4


The row-span value. It is used to format the crosstab table.


T1


/cttree/R0/R1/T1


4


The sum of measure 1 "Revenue" for each distinct "Region" value.


T2


/cttree/R0/R1/T2


4


The sum of measure 1 "Revenue" for each distinct "Region" value.


R1C1


/cttree/R0/R1/R1C1


16


This contains elements from combining R1 and C1. There are 4 distinct values for "Region", and four distinct values for "ProductsBrand". Therefore, the combination is 4 X 4 è 16.


T1


/cttree/R0/R1/R1C1/T1


16


The sum of measure 1 "Revenue" for each combination of "Region" and "ProductsBrand".


T2


/cttree/R0/R1/R1C1/T2


16


The sum of measure 2 "PrevRevenue" for each combination of "Region" and "ProductsBrand".


R1C2


/cttree/R0/R1/R1C1/R1C2


32


This contains elements from combining R1, C1 and C2. There are 4 distinct values for "Region", and four distinct values for "ProductsBrand", and two distinct values of "PeriodYear". Therefore, the combination is 4 X 4 X 2 è 32.


T1


/cttree/R0/R1/R1C1/R1C2/T1


32


The sum of measure 1 "Revenue" for each combination of "Region", "ProductsBrand" and "PeriodYear".


T2


/cttree/R0/R1/R1C1/R1C2/T2


32


The sum of measure 2 "PrevRevenue" for each combination of "Region", "ProductsBrand" and "PeriodYear".


R2


/cttree/R0/R1/R2


18


This contains elements from combining R1 "Region" and R2 "District". Since the list of values in R2 has dependency on R1, therefore the number of entries is not just a simple multiplication.


H


/cttree/R0/R1/R2/H


18


The row header label for R2 "District".


R1N


/cttree/R0/R1/R2/R1N


18


The R2 position number within R1. This is used to check if it is the last row, and draw table border accordingly.


T1


/cttree/R0/R1/R2/T1


18


The sum of measure 1 "Revenue" for each combination "Region" and "District".


T2


/cttree/R0/R1/R2/T2


18


The sum of measure 2 "PrevRevenue" for each combination of "Region" and "District".


R2C1


/cttree/R0/R1/R2/R2C1


72


This contains elements from combining R1, R2 and C1.


T1


/cttree/R0/R1/R2/R2C1/T1


72


The sum of measure 1 "Revenue" for each combination of "Region", "District" and "ProductsBrand".


T2


/cttree/R0/R1/R2/R2C1/T2


72


The sum of measure 2 "PrevRevenue" for each combination of "Region", "District" and "ProductsBrand".


R2C2


/cttree/R0/R1/R2/R2C1/R2C2


144


This contains elements from combining R1, R2, C1 and C2, which gives the finest level of details.


M1


/cttree/R0/R1/R2/R2C1/R2C2/M1


144


The sum of measure 1 "Revenue".


M2


/cttree/R0/R1/R2/R2C1/R2C2/M2


144


The sum of measure 2 "PrevRevenue".


Lots to read and digest I know!

Customization

One new feature I discovered this week is the ability to show one column and sort by another. I had a data set that was extracting month abbreviations, we wanted to show the months across the top and some row headers to the side. As you may know XSL is not great with dates, especially recognising month names. It just wants to sort them alphabetically, so Apr comes before Jan, etc.


A way around this is to generate a month number alongside the month and use that to sort. We can do that in the crosstab, sadly its not exposed in the UI yet but its doable.

Go back up and take a look a the initial crosstab command. especially the Rows and Columns entries. In there you will find the sort criteria.

"ProductsBrand{,o=a,t=t}, PeriodYear{,o=a,t=t}"

Notice those leading commas inside the curly braces? Because there is no field preceding them it means that the crosstab should sort on the column before the brace ie PeriodYear. But you can insert another column in the data set to sort by. To get my sort working how I needed.

<?crosstab:c794;"current-group()";"_Fund_Type_._Fund_Type_Display_{_Fund_Type_._Fund_Type_Sort_,o=a,t=n}";"_Fiscal_Period__Amount__._Amt_Fm_Disp_Abbr_{_Fiscal_Period__Amount__._Amt_Fiscal_Month_Sort_,o=a,t=n}";"_Execution_Facts_._Amt_";"sum"?>

Excuse the horribly verbose XML tags, good ol BIEE :0) The emboldened columns are not in the crosstab but are in the data set. I just opened up the field, dropped them in and changed the type(t) value to be 'n', for number, instead of the default 'a' and my crosstab started sorting how I wanted it.

If you find other tips and tricks, please share in the comments.

Join the discussion

Comments ( 5 )
  • ethel Monday, April 5, 2010
    Thank you. I have been searching for a solution to my report for a couple of months now. It works perfect. Thanks!! You and Hok Min deserve a 10.
  • Vandana Wednesday, May 19, 2010
    Any idea how to display non-numeric value in Pivot table
  • Vandana Wednesday, May 19, 2010
    The fields ATTR_VAL_TXT and CMT_TXT has to display a text field like "A Top priority" or "Highly knowledgable" but it prints NaN
  • guest Friday, March 24, 2017

    Awesome this is really helpful.


  • krishna Friday, March 24, 2017

    Is there an option to edit the format of dates or numbers in the rows,columns or the Measurement or the totals. I tried format-currency,format number and also the field edit options nothing seems to work.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.