Pivot Grids in PeopleSoft

Pivot grids are simple yet powerful analytics tool introduced from PeopleTools 8.52. Although the Pivot Grids are at skeleton form at release 8.52, it has been enhanced much better in the latter release 8.53.

Pivot grids enable users to slice and dice the data enabling the users to get multiple dimensions for the same data. It also offers interactive charts, which will be refreshed as and when the grid data is redrawn. The combination of the grid and chart offers a powerful mechanism to interpret complex data. This eliminates the need for exporting the data to excel and pivoting it over there. Another added advantage is that user could configure related actions to the pivot grid data, thereby making it easy to take context sensitive actions.

Let us look into how we can create a pivot grid from scratch. It is much easier than any of you might have thought about.

Here are the steps you need to follow.

Step 1: Create a PSQUERY which will fetch the required data that needs to be pivoted. Note. All the query security applicable for the PS Query will be applicable for the Pivot Grid as well.

Step 2: Go to- Reporting Tools > Pivot Grid > Pivot Grid Wizard.  Provide a name to the Pivot Grid and click on Add.

Step 3: On the First step of the wizard provide the below information.

Pivot Grid Title – This will be the Title appearing on the final pivot grid created.

Description – A description to identify the purpose of the pivot grid.

Pivot Grid Type - Select “Public” if you want other users to access your pivot grid, otherwise select “Private”.

Owner - Select the owning module.

Now click on next to proceed to the next step.

Step 4: On this step, leave the “Data Source Type” as it is (it is intended for future developments). Select the query you created in the Step 1 in the field “Query Name”. Now you should select all the query columns which you want to use/display in the pivot grid. Click on next.

Step 5: You need to configure each field in the “Select Data Source Information” group box to align it with the usage of the pivot grid.

Column Type – we have 3 options. Use “Display” only if you want to display this field in the detailed drill down. If you select this, this field will not be present on the pivot grid or the chart.

Select “Axis” option if you are going to take a report on this parameter. In the chart you can select the X-axis from these fields only.

Select “Value” option if it is to be count or aggregate amount that needs to be selected. Usually the chart Y-axis will be selected from this Value fields.

Total – Select this option if you want your pivot grid to display the total value for the field. If it is unchecked each item will be displayed as separate rows.

Aggregate – This option is available only for value fields. This is selected to display how the value needs to be displayed. “Average” will display the average value, “Count” will display the count of rows, “Maximum” will display the maximum value, “Minimum” will display the minimum value & “Sum” will display the sum of values.

You can provide the default prompt values for the underlying PS Query in the “Select Query Prompt Values” space. If you do not want the user to change the prompt value from the final pivot grid, you can uncheck the “Visible Prompt” from the corresponding prompt field.

Now click on Next.

Step 6: This is the final step where you decide how your pivot grid/chart appears. From the default view, select an option. “Grid Only” contains the pivot grid alone, “Chart Only” contains pivot chart alone &  “Grid & Chart” option displays both the grid and chart on the same page.

“Specify Axis Information” – This is the space where you configure which field behaves which way. Let us check how we can configure it.

Grid Axis – This has 3 options. Select the “Column” option if you want to display this field as a column in the grid. Select “Row” option if you want the field to be displayed as row in the pivot grid.

	Business Unit	Department
Revenue
Expenses

Here BU & Department are column fields and Revenue & Expenses are row fields.

The third option is “Filter”, if you select it, this field will come on top of the grid/chart as a filter based on whose values you can filter the entire grid/chart. If you do not select any value for this field, it become a drill down option, so that when user clicks on the chart or grid user can drill on this field value.

Chart Axis – This field has 4 options and is used to set the axis for the chart. Select “X-axis” if you want to set the field as x axis. Select “Y-axis” if you want to set the field as y axis. Select “Overlay” if you want to overlay the field in the y axis. This option is particularly good if you want to draw to values on the same Y axis. The overlay will appear as a line chart. Leave this field blank if you want to make it as a drill down. Select “Filter” if you want to show this field as a filter on the top of chart.

On the Grid Options: Select “Collapsible Data Area” if you want to collapse the grid. Select “Expanded State” to make the grid expanded by default. The third one is important one. Use “No Drag and Drop” if you do not want the user to drag and drop the columns or fields across the grid.

In the “Chart Options”, you can configure the chart title, chart type, axis labels. In the advanced options, you can even set the height & width of the chart, whether or where to show the legends, precession of the Y axis and if it is a pie chart you can make it even an exploded pie making visually appealing if the segments are smaller.

In the “Viewer Options” you can configure the options that are visible for the end user when it is accessed from anywhere else.

Step 7:  Now you can save the pivot grid and click on next to preview the grid.

On the preview page of the wizard, you have options to publish the pivot grid as a pagelet so that it can be used in other places such as dashboards, home pages, workcenters & other peoplesoft pages. Also you can attach related actions to the pivot grid from this page. The related actions and pagelet will be covered in a different section.