Query Design Considerations

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

Any prompt values for the query will be used for the Pivot Grid filter.
A number of PSQueries may be run to render data in the grid and the chart, so you should consider these two points when constructing a PSQuery for a Pivot Grid model:
- PSQuery should be conducive to manipulation.
- PSQuery performance should be efficient enough to render quickly in the grid and in the chart.

For example, you want to plot organization data attributes, such as Employee Information, Department Information, Location Information, Country Information, and so on, in a pivot grid. The base database table for this information contains the codes EMPLID, DEPT_ID, LOCATION_ID, and so on, but the descriptions for all these attributes are in different tables. When a PSQuery is created, all these tables are joined. Additionally, the related language tables, security records, effective date, and so on are relevant. As a result, the PSQuery is complex and its performance may not be efficient. This kind of PSQuery is not suitable for creating a Pivot Grid model. In this case, when table indexes are not defined properly, you have two options:

Define a fact table that contains all the data and use it to create a Pivot Grid model.
Define a SQL view that joins all these tables and provides a simple view of the data.
You can then use this view as a source for the PSQuery. However, creating a SQL View on top of multiple tables using joins might degrade performance.

For both options, you should consider effective date criteria, related language tables, and security records.

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 and Pivot Grid pagelets.

Pivot Grid uses PSQuery as the data source, and the aggregated results of the query appear in a grid and chart. While displaying the results, Pivot Grid does an ad hoc runtime manipulation of the query to get the desired results. Pivot Grid executes different types of queries at runtime for various uses.

For example, Pivot Grid executes a:

Query with a GROUP BY clause and multiple fields to retrieve a unique list of values for the filters and axis fields.
Query with a GROUP BY clause, ROLLUP clause, GROUPING clause, and multiple fields to retrieve data to be displayed in a grid in Pivot Grid.
Query with a GROUP BY clause and the chart axis fields to retrieve the data to be displayed in a chart in Pivot Grid.
Query with a DISTINCT clause and a single field to retrieve a unique list of values for a primary filter that was last saved by the user.

Given these manipulations being done by Pivot Grid on the query and the complexity of PSQuery itself, the query that is used as a data source for Pivot Grid has limitations, which are:

Query with the UNION clauses.

Query with the UNION clauses are 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.
Query with the JOINS on the value (fact) fields.
Query with the JOINS on the value (fact) 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.

For example, consider a query where the resulting SQL looks like this:
```
SELECT TO_CHAR(A.ST_DT,'YYYY-MM-DD'),
  A.ST_ID_NUM,
  A.CRSPD_CUST_ID,
  A.REMIT_ADDRESS1,
  A.REMIT_ADDRESS2,
  A.CUST_ID
FROM PS_AR32001_TMP A,
  PS_RUN_CNTL_AR B,
  PS_COUNTRY_TBL C,
  PS_COUNTRY_TBL D,
  PS_CUSTOMER E,
  PS_STATE_TBL F
WHERE (B.OPRID = 'VP1'
  AND (A.ST_ID_TYPE = 'O'
  AND A.DRAFT_FLG <> 'Y'
  AND A.ST_ID_NUM = B.ST_ID_NUM
  AND B.RUN_CNTL_ID = :1
  AND B.OPRID = :2
  AND A.AG_PRINT_FLAG <> 'Y'
  AND C.COUNTRY = A.COUNTRY
  AND D.COUNTRY = A.REMIT_COUNTRY
  AND A.CRSPD_SETID = E.CRSPD_SETID
  AND A.CRSPD_CUST_ID = E.CRSPD_CUST_ID
  AND A.CUST_ID = E.CUST_ID
  AND A.CUST_ID LIKE :3
  AND F.COUNTRY = A.COUNTRY
  AND F.STATE = A.STATE ) )
ORDER BY 3, 50, 36, 35, 57, 37, 38, 54, 52 DESC, 51
```
Note: A.CUST_ID is in the SELECT statement, and A.CUST_ID = E.CUST_ID is in the WHERE statement.

Suppose that a Pivot Grid model is built using this query and the field A.CUST_ID is selected as a value type of field in the Pivot Grid model with an aggregation function of COUNT. This SQL will fail at runtime because the aggregation COUNT will be applied to both the SELECT field and the JOIN criterion.

In this case, you should use separate fields as the SELECT fields and the JOIN criterion.
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. The query will fail because the Pivot Grid uses a GROUP BY clause for the axis fields, and the database fails to perform a GROUP BY comment on the same field more than once.

For example, consider a view that has the following SQL:
```
SELECT A.HRS_PERSON_ID
 , A.HRS_RCMNT_ID
 , A.HRS_PROFILE_SEQ
 , A.OPRID
 , %DatePart(A.HRS_SUBMITTED_DTTM)
 , A.HRS_JOB_OPENING_ID
 , A.POSTING_TITLE
 , A.STATUS_CODE
 , B.DESCR
 , B.DESCR
 , Z.RECRUITER_ID
 , Z.MANAGER_ID
 , Z.JOB_FAMILY
 , Z.BUSINESS_UNIT
 , Z.DEPTID
 , Z.HRS_PRM_LOCATION
 , %DateNull
 , %DateNull
FROM PS_HRS_MY_APP_VW A LEFT OUTER JOIN PS_HRS_JOB_OPENING Z ON A.HRS_JOB_OPENING_ID = Z.HRS_JOB_OPENING_ID, PS_HRS_STS_REC_I B , PS_HRS_RCMNT C
WHERE B.STATUS_CODE = A.STATUS_CODE
  AND B.STATUS_AREA = '3'
  AND A.HRS_PERSON_ID = C.HRS_PERSON_ID
  AND A.HRS_RCMNT_ID = C.HRS_RCMNT_ID
```
In this view, the same field B.DESCR is selected twice in exactly the same way. This view was used in a query with both fields in the SELECT list of the query. If a Pivot Grid model was built using this query and again both these fields are selected as the axis type of fields in the Pivot Grid model, then the model will fail and not return the results.

In this case, you should use different fields in the view creation. If the same field has to be used, one of them can be used with functions such as UPPER, TRUNCATE, and so on.
Query with value fields used as a criterion or a runtime prompt.
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 in a criterion in the query, then the query will fail and the Pivot Grid model will not render. It will fail because when rendering the Pivot Grid model, aggregation functions are applied on the value fields and the same aggregation will be applied on the criterion. This issue does not only result in a wrong criterion, but the query will also fail syntactically because the WHERE clause contains an aggregation and a normal criterion will not work. Note that you need a HAVING clause for the aggregation.

For example, consider a query such as this:
```
SELECT DISTINCT A.SETID
	, A.VENDOR_ID
	, A.VENDOR_NAME_SHORT
	, A.BUSINESS_UNIT_GL
	, A.NET_BALANCE_AP
	, A.TXN_CURRENCY_CD
	, A.NET_BALANCE_AP * B.RATE_MULT/ B.RATE_DIV, B.TO_CUR
FROM PS_AP_DB_VNDBAL_VW A, PS_RT_DFLT_VW B
WHERE (B.EFFDT =
  (SELECT MAX(B_ED.EFFDT)
   FROM PS_RT_DFLT_VW B_ED
   WHERE B.FROM_CUR = B_ED.FROM_CUR
     AND B.TO_CUR = B_ED.TO_CUR
     AND B.RT_TYPE = B_ED.RT_TYPE
     AND B_ED.EFFDT <= SYSDATE)
     AND B.RT_TYPE = 'CRRNT'
     AND A.TXN_CURRENCY_CD = B.FROM_CUR
     AND A.SETID = :1
     AND B.TO_CUR = :2
     AND A.NET_BALANCE_AP * B.RATE_MULT/ B.RATE_DIV >= :3 )
```
The expression A.NET_BALANCE_AP * B.RATE_MULT/ B.RATE_DIV is used as a value field in the Pivot Grid model. The query after aggregation will apply the aggregation function in the SELECT list as well as in the criterion. These issues cause a failure.

In these cases, you should have two fields, one representing the value column in the Pivot Grid model and another one that is used in the criterion.
Query with the expressions that are used as axis fields in the Pivot Grid model.
If a query has expressions and these expressions are used as the axis fields in a Pivot Grid model, Pivot Grid will display an error while rendering results because query expressions are not supported in the ROLLUP clause in Pivot Grid, which may lead to unpredictable results.

In this case, you should build a view on top of the query SQL with expressions, and then use this view in the Pivot Grid model.
Limitations in Microsoft SQL server.
Microsoft SQL server database platform supports ROLLUP and CUBE with a compatibility mode of 100 or more. If the compatibility mode in the database is set to a value that is less than 100, then all the queries associated with Pivot Grid models will fail.

This table summarizes the different query scenarios, results, and recommendations.

Query Scenario	Result	Recommendation
Query with UNION clauses.	An error message appears to indicate that the query execution has failed.	Build a view on top of the query SQL with UNION clauses and then use this view in the Pivot Grid model.
Query with JOINS on value (fact) fields.	An error message appears to indicate that the query execution has failed.	Use separate fields as the SELECT fields and JOIN criterion.
Query with underlying Record Views selecting the same field.	Usually, an error message appears to indicate that the query execution has failed. Occasionally, the Pivot Grid model will display the results incorrectly.	Use different fields in the view creation. If the same field must be used, use one of them with functions such as UPPER, TRUNCATE, and so on.
Query with value fields used as a criterion.	An error message appears to indicate that the query execution has failed.	Use two fields, one as the value column in the Pivot Grid model and the other one in the criterion.
Query with expressions used as the axis fields in Pivot Grid model.	An error message appears to inform users that expressions are not allowed.	Build a view on top of the query SQL with expressions, and then use this view in the Pivot Grid model.
Microsoft SQL server database with a compatibility setting of less than 100.	An error message appears while rendering the Pivot Grid model.	Set the compatibility mode for the Microsoft SQL server database to a value of 100 or greater.

Query Design Considerations

Query Limitation for Pivot Grid