Pivot Grids

PeopleSoft Pivot Grid supports operational dashboard reporting to provide a pivot table and chart representation of data using a PSQuery data source. Users are able to view the data in a grid, where pivoting, slicing, and dicing is possible

Query as a Data Source

Pivot Grids can use queries, components or composite queries as a data source. In this course we will cover using queries as the pivot grid data source. When using query as the data source for the pivot grid there are certain considerations that must be taken into account or the pivot grid will fail.

Data Source Queries for Pivot Grid Naming Convention

Queries used as data sources for Pivot Grid should have a _PG designation at the end of the query
name. PGXX_XX_XXXXX

Example: PGFS_AR_ITEM_DST_GL

Query Considerations/Limitations for Use with Pivot Grid:

Before beginning in pivot grid wizard it is extremely important to learn how to select or create queries to use in conjunction with pivot grid wizard. There are certain aspects to take into consideration, as well as certain limitations that must be paid attention to when creating or selecting a query to use with a pivot grid. As pivot grid doesn’t generally give an error message with the reason that a Grid failed to render, it can be especially important to pay close attention to using the correct protocol when creating a query to be used with a pivot grid model in order to successfully create functional pivot grids.

Considerations

When creating a query to use with a pivot grid, keep in mind that:

Any prompt values for the query will automatically be used as a prompt or filter for the pivot grid. Developers will have the option when creating the pivot grid to determine whether end users can change values in the prompt in the rendered pivot grid model.

- - The institution prompt created in PS Query will display as a filter in Pivot Grid

Prompts which limit data based on institution (SETID, Company, Business_Unit, etc) must always be set up with a No Table Edit edit type. Prompts which have an edit type of Prompt Table require a value to be entered when the pivot grid is being developed or the pivot grid may not be saved. If a institution identifying value is saved in the pivot grid, the grid will display the data for that value in the initial view - regardless of whether the end user viewing the grid is part of that institution or not. So, an end user from one college could potentially see data from another college. To avoid this, pivot grids must NEVER be saved with an institution identifying value, which means that all prompts of this type must ALWAYS be saved with the No Table Edit edit type in the pivot grid query data source.

Due to a bug, related prompts will fail in pivot grid viewer. As of this writing, Oracle has not yet released a date by which this will be corrected. A related prompt is one that is dependent on another prompt or criterion. For example, ACAD_CAREER is dependent on INSTITUTION. So while it is possible to filter on ACAD_CAREER when creating the pivot grid model, it is not possible for the query used to prompt on ACAD_CAREER.

The query will be executed to render data on the grid and the chart, so users should consider these two points when constructing a query for a pivot grid model:
- Query should be conducive to manipulation.
- Query performance should be efficient enough to quickly render on the grid and on the chart. An inefficient query will cause pivot grid to render very, very slowly.
Alternatives to Complex Queries
- Request a SQL View to be created by Data Services

Limitations

Query with UNION Clauses.
- Query with the UNION clauses are currently not supported in pivot grid. Pivot grid does runtime manipulation on the SELECT field list in the query and this will not work properly with queries containing UNION clauses.
  - Recommendation: Request a SQL View to be created by Data Services

Query with JOINS on the Value Fields.
- Query with the JOINS on value fields receive an error in pivot grid because pivot grid performs aggregation on the value fields and the same aggregation is used for the JOIN field. This will cause the query to fail syntactically while executing on the database.
  - Recommendation: use different fields for Values and Join criteria.

Query with Underlying Record Views Selecting the Same Field.
- A query built on an underlying View that selects the same field more than once will fail when used as a data source for pivot grid if these fields are used as the axis columns in the pivot grid model. This is because the pivot grid uses a GROUP BY clause for the axis fields and the database fails to perform a GROUP BY on the same field more than once. Generally this will cause the pivot grid not to render but it could also display with incorrect results.
  - Recommendation: Use different fields in the View creation but if that is not possible then change one to use a function such as UPPER or TRUNCATE.
Query with Value Fields Used as Criteria
- Value fields that are used as criteria in the query are not supported. If one of the selected fields in the query is selected as a value column in the pivot grid model and the same field is used as criteria in the query, the query will fail and the pivot grid model will not render. This is because when rendering the pivot grid model, aggregate functions are applied on the value fields and the same aggregation will be applied on the criteria. This issue does not only result in wrong criteria, but the query will also fail syntactically because there is an aggregation in the WHERE clause and normal criteria will not work. Note that a HAVING clause is needed for the aggregation.
  - Recommendation: use two fields, one representing the value column in the pivot grid and the other used as criteria.

Query with Value Fields Used as a Prompt
- Value fields that are used as a prompt in the query are also not supported.
  - Recommendation: use two fields, one representing the value column in the pivot grid and the other used as criteria.

Query with Expressions that are used as Axis Fields in the Pivot Grid Model.
- If a query has an expression and the expression is used as an axis field in a pivot grid model, the pivot grid will display an error while rendering results because query expressions are not supported in the ROLLUP clause in pivot grid and it may lead to unpredictable results.
  - Recommendation: build a View with the Expression which can then be used in the pivot grid model.

Making Updates to a Query Used as a Pivot Grid Data Source

If the Query used for the pivot grid is changed the pivot grid must also be modified to ensure all changes are kept and displayed correctly in the pivot grid viewer and pagelets.

Also note that making excessive changes to a pivot grid model and/or the query data source can cause the model to permanently fail.