New Skool Crosstabbing
- by Tim Dexter
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:
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.
