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

PeopleSoft Pivot Grid

PT 8.52 introduces the analytical tool called as Pivot Grid. Following is my first shot at using this functionality. The goal was to explore the functionality with minium setup configuration. As of now Pivot Grid supports only a PSQuery data source.

1. Created a query in Query Manager. My Query returns JPM Profile data for Employees in my HCM system.
2. Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard and follow the steps in the wizard.

 















Only valid models are available from the Pivot Grid Viewer

Provide query name and select the columns from the query which will be part of this pivot grid.

At least one Axis and one Value member is required. Total column is available only for the data source column with column type Axis with data type not equal to date. For Char fields aggregate function defaults to Count and cannot be changed. If query has runtime prompts then you should see another section to provide defaults for runtime prompts. In my case there were not prompts defined on the query.

Selecting Grid only option. Specifying the Grid and Chart Axis. Kept the default Grid and Chart Options.



Preview the result. So as per my setup in Step 4 of the wizard, Name is a filter, ID and EFFDT are columns and rows are category and item description.

I can export just the specify data the grid is displaying (by clicking on grid icon in the grid title bar, next to the help icon) or all data to excel (which is similar to downloading results to excel from query).

Via Reporting Tools > Pivot Grid > Pivot Grid Viewer, an end-user can lookup the models. Though I had selected Grid only as the option, when creating my model, I do see a check-box to display a chart in addition to the grid.

 

Have the option to view it as a Bar, Line or Pie graph.

I did get some hard peoplecode errors while designing my model as I was flipping through various options, which tells me that this functionality is not robust as yet. The error messages are not at all user friendly for a functional person to understand.

So thats it for now.

Thanks

Deepak.