Using Pivot Grid Viewer

This chapter provides an overview of Pivot Grid Viewer, discusses how to view a Pivot Grid model using the Pivot Grid Viewer, and provides examples of viewing a Pivot Grid model using the Pivot Grid viewer.

Click to jump to parent topicPivot Grid Viewer Overview

Pivot Grid Viewer is the component that Pagelet Wizard, applications, and so on use to view Pivot Grid models. Pivot Grid Viewer parses the pivot grid attributes (Pivot Grid name, PSQuery runtime prompt values, grid axis information, grid view options, chart type information, and display options) and renders the display on the page. This component enables user to:

Note. If the query used in the Pivot Grid model is changed after the Pivot Grid model is created , you need to modify and save the Pivot Grid model to ensure all changes are properly displayed in the Pivot Grid Viewer.

Viewing Pivot Grid Options

Pivot Grid View Options is the component that facilitates creating new views and updating existing ones for a Pivot Grid model. This component provides a framework to save the Pivot Grid View Name along with all the view options. It enables Pagelet Wizard or applications to pass just the Pivot Grid View Name instead of the whole list of Pivot Grid display preferences as content reference attributes. You can create multiple Pivot Grid View Names and associate them with a single Pivot Grid model.

This diagram shows Pivot Grid view options:

Pivot Grid view options

Note. Whenever a new Pivot Grid model is created, it is always associated with a default view. The default view name is <Pivot Grid Model Name>.View.

Click to jump to parent topicViewing a Pivot Grid Model Using the Pivot Grid Viewer

Use the Pivot Grid Viewer page (PTPG_PSPGVIEWER) to view the Pivot Grid model with different display options: Grid and Chart, Grid Only, or Chart Only.

Note. If the display option is Grid Only, Pivot Grid displays the grid without the chart.

To view a Pivot Grid model using Pivot Grid Viewer page:

  1. Select Reporting Tools, Pivot Grid, Pivot Grid Viewer.

    The Pivot Grid Viewer page appears with three filter options: Pivot Grid Name, Pivot Grid Data Source Name, and Pivot Grid Data Source Type.

  2. Optionally, enter the filter options and perform the search.

    All the available and accessible Pivot Grid models are listed. Invalid Pivot Grid models are not listed for this search.

    Note. In PeopleTools 8.52, Pivot Grid supports only PSQuery data source and PSQuery type.

  3. Select a Pivot Grid model for viewing.

  4. On the Pivot Grid Viewer page, view the Pivot Grid model with different options.

    The Pivot Grid model appears with the grid, chart, or both based on the display option selected.

This example shows the Pivot Grid Viewer page with the default display of Chart Only:

This example shows the Pivot Grid Viewer page with the default display of Grid Only:

Display Chart

The Pivot Grid model appears with the grid, chart, or both based on the Display Chart option selected.

Set Filters

Click to open the Datasource Prompts dialog box, where you can change the values for Query prompts.

Export All Data

Click to export the underlying PSQuery data to Microsoft Excel.

Pivot Grid

You can change the grid layout at runtime by dragging members to a different axis.

Click the Collapse Filter Bar icon to collapse or expand the section that displays the Pivot Grid filter options.

Click the Download icon to download the slice of data seen in the pivot grid to Microsoft Excel.

Click the Show All Columns icon to display all the grid columns.

Click the Help for Drag and Drop icon to display help information for drag-and-drop functionality in the pivot grid.

The axis icon is used to drag and drop the axis to row, column or filter. The axis name is displayed next to this icon when it is in filter. The axis member name is displayed next to this icon when in row or column. The axis name/axis member name in the grid is truncated when it is more than 15 characters. If you place the mouse on the axis icon, the axis name or axis member name is fully displayed.

The value icon is used to drag and drop the values to row, column, or filter. The value name appears next to this icon when in row or column. The value name doesn't appear next to this icon when it is in filter. The value name in the grid is truncated when it is more than 15 characters. If you place the mouse on the axis icon, the value name is fully displayed.

Click the Bar icon to display data in a bar chart.

Click the Line icon to display data in a line chart.

Click the Pie icon to display data in a pie chart.

Chart Options

Click to open the User Charting Options dialog box, where you can change the chart layout, chart axis, and filters.

Note. To drill down on the chart, click the chart data points.

To drill out on the chart, click the drillout link, which appears as a locator link at the top of the chart.

See Using the Chart Options.

View Data

Click to open the Pivot Grid Chart Data dialog box, where you can view the data that is being plotted.

Note. You can change PSQuery runtime prompt values.

Save Layout

Click to save the current grid and chart layout as a user preference for the default view of the current model. The grid and chart layout, the filter values, and PSQuery prompt values are also saved as preferences.

Setting Filters

Open the Datasource Prompts dialog box by clicking the Set Filters link on the Pivot Grid Viewer page.

Use the Datasource Prompts dialog box to change the values for Query prompts.

This example shows the Datasource Prompts dialog box:

Using the Chart Options

Open the User Charting Options dialog box by clicking the Chart Options button on the Pivot Grid Viewer page.

Use the User Charting Options dialog box to change the chart layout, chart axis, and filters.

This example shows the User Charting Options dialog box when the display option is Grid and Chart:

Note. The X Axis, Y-Axis Series, Overlay Field, and Chart Filters fields in the User Charting Options dialog box are not editable if the display option is Grid and Chart.

This example show the User Charting Options dialog box when the display option is Chart Only:

Viewing Chart Data

Open the Pivot Grid Chart Data dialog box by clicking the View Data button on the Pivot Grid Viewer page.

Use the Pivot Grid Chart Data dialog box to view the data that is being plotted.

This example shows the Pivot Grid Chart Data dialog box when the display option is Grid and Chart:

This example shows the Pivot Grid Chart Data dialog box when the display option is Chart Only:

Click to jump to parent topicExamples: Viewing a Pivot Grid Model Using Pivot Grid Viewer

Suppose that a PSQuery was built on the record PS_QE_BAM_FACT_TBL, which stores the number of product units sold, unit cost, and product sales for a set of regions and products by month, as shown in this table:

Region (Key)

Product (Key)

Month (Key)

No. of Units Sold (Units Sold Cube)

Unit Cost (Unit Cost Cube)

Product Sales (Product Sales Cube)

QE_BAM_REGION_FLD

QE_BAM_PRODUCT_FLD

QE_BAM_MONTH_FLD

QE_BAM_UNIT_FLD

QE_BAM_SALES_FLD

QE_BAM_PRDSALES_FL

Click to jump to top of pageClick to jump to parent topicViewing a Pivot Grid Model When the Display Option is Grid and Chart

If the display option is Grid and Chart:

Note. If the display option is Grid Only, Pivot Grid displays the grid without the chart.

The initial grid layout has:

The initial chart view has:

This example shows the view of a Pivot Grid model when the display option is Grid and Chart:

Note. You can change the grid layout at runtime by dragging members onto different axes. The chart layout also changes based on the grid layout to preserve synchronization between the grid and the chart. You can change the chart type and the Y axis of the chart.

Drilling Down on a Region

The Pivot Grid displays detailed data based on the region on which the drilldown is performed. For the chart, the same data appears for the selected Value column. You can select the chart type and change the Y axis by selecting a different Value column to be plotted.

The pivot grid has:

The chart has:

This example shows a Pivot Grid model when the display option is Grid and Chart and drilldown was on a region:

Changing Chart Options

Use the User Charting Options dialog box to change the chart type or Y axis.

Changing Filters

To add a filter at runtime, place the cursor on the circle for the field in the left side of the grid and drag it to the top part of the grid.

To remove a filter, place the cursor on the circle for the filter listed on the top part of the grid and drag it to back to the lefthand column.

Click to jump to top of pageClick to jump to parent topicViewing a Pivot Grid Model When the Display Option is Chart Only

This example of Pivot Grid model was defined using Chart Only. The chart initial view has:

This example shows the initial view of a Pivot Grid model in bar chart format when the display option is Chart Only:

This example shows the initial view of a Pivot Grid model in line chart format when the display option is Chart Only:

This example shows the initial view of a Pivot Grid model in pie chart format when the display option is Chart Only:

Drilling Down on a Region

Suppose you are drilling down on the chart for the HAWAII region. Click the HAWAII bar (for a bar chart; click the HAWAII slice for a pie chart, or click the line for a line chart) and the drilldown dialog box appears:

Select the Product value for the Drilldown Field field; the chart now has:

In this case, a locator link is automatically added on the right side above the chart for the Region member; if you click the locator link, the previous chart layout is restored.

This example shows a Pivot Grid model when the display option is Chart Only and drilldown was on a Region:

Selecting a Series

In this example, the data model specifies Chart Only and is set up as follows:

The chart shows each product with a legend:

Selecting an Overlay Field

You can select an overlay field that plots multiple charts based on the Y axis and the overlay field. If the Unit Cost field is selected as the overlay field, then the first chart is plotted with the Region field as the X axis and the Unit Sales field as the Y axis. The second chart is plotted with the Region field as the X axis and the Unit Cost field as the Y axis.

The chart has:

This example shows the chart with the Region field as the X axis, the Unit Sales field as the Y axis, and the Unit Cost field as the overlay field:

Note. Whenever you select an overlay field, the chart type is limited to Line chart; however, you are still able to select Bar and Pie charts, but these chart types do not display valid data.