Thursday, September 17, 2015

PeopleSoft BI Publisher Pivot Wizard

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: ctvarnamedata-elementrowscolumnsmeasuresaggregation?>
Parameter
Description
Example
Ctvarname
Crosstab variable name. This is automatically generated by the Add-in.
c72515
data-element
This is the XML data element that contains the data.
"//Payroll"
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.
"EMPLID{,o=a,t=t}, NAME{,o=a,t=t}"
In the example, the first row header is "EMPLID". It is sort by "EMPLID", order is ascending, and type is text. The second row header is "NAME". It is sort by "NAME", 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.
"CHECK_DT{,o=a,t=t}"
In the example, the first column header is "CHECK_DT". It is sort by "CHECK_DT", order is ascending, and type is text.
Measures
This contains a list of XML elements for measures.
"ANNUAL_RT"
Aggregation
The aggregation function name. Currently, we only support "sum" and “count”.
"sum"

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



Row layout can be Inline or Outline.



















Row/Column totals


Table defined in RTF template

C H ACCEMPLID
NAME
G CHECK_DTE
Total
G RS EMPLID
G NAME
G 999E
999E


G 999E
999

Code generated by the Pivot Wizard


XML file






















……
……
……






















Output

EMPLID
NAME
5/31/2009
7/31/2011
8/31/2011
9/30/2011
3/31/2019
4/30/2019
Total
 01234
Jon Doe
4167
4167
4167
4167
4167
4167
500000
 99999
Jane Bond
0
3472
3472
3472
0
0
250000


4167
7639
7639
7639
4167
4167
750000