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?>

Ctvarname Crosstab variable name. This is automatically generated by the Add-in. e.g. c72515

data-elementThis is the XML data element that contains the data. e.g. "//Payroll"

RowsThis 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.
e.g. "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.

ColumnsThis 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.
e.g. "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.

MeasuresThis contains a list of XML elements for measures. e.g. "ANNUAL_RT"

AggregationThe aggregation function name. Currently, we only support "sum" and “count”. e.g. "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



1 comment:

  1. Hi, I am very new to BI Publisher and this was helpful. The pivot table I have created is similar to your example but I was hoping you could provide a solution to hiding the 0 values in the columns or replacing them with blanks. Thank you

    ReplyDelete