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.

Comments:

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.

Posted by ethel on April 05, 2010 at 05:16 AM MDT #

Any idea how to display non-numeric value in Pivot table

Posted by Vandana on May 19, 2010 at 01:10 PM MDT #

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

Posted by Vandana on May 19, 2010 at 01:13 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