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: ctvarname; data-element; rows; columns; measures; aggregation?>

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

data-element - This is the XML data element that contains the data. e.g. "//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.
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.

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

Measures - This contains a list of XML elements for measures. e.g. "ANNUAL_RT"

Aggregation - The 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