E-PG: Performance Issue When Running Pivot Grids With Tree (Doc ID 2233920.1)

Last updated on JULY 27, 2022

Applies to:

Symptoms

Pivot Grid models created using Tree Option may face performance issues.

Error

-------------

The following error is received when running a pivot grid created with Tree Option:
"An error has occurred."

Replication Steps

-------------------------
1. Create a new view called GL_ACCT_TEST_VW

SELECT A.BUSINESS_UNIT
, A.AFFILIATE
, A.CURRENCY_CD
, A.BASE_CURRENCY
, A.ACCOUNT
, A.DEPTID
, A.PRODUCT
, SUM( A.POSTED_TRAN_AMT)
, SUM( A.POSTED_BASE_AMT)
, B.DESCR
, A.LEDGER
FROM PS_LEDGER A
, PS_GL_ACCOUNT_TBL B
WHERE ( A.ACCOUNT = B.ACCOUNT
AND B.EFFDT = (
SELECT MAX(B_ED.EFFDT)
FROM PS_GL_ACCOUNT_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.ACCOUNT = B_ED.ACCOUNT)
AND A.LEDGER NOT LIKE 'CON%')
GROUP BY A.BUSINESS_UNIT, A.AFFILIATE, A.CURRENCY_CD, A.BASE_CURRENCY,
A.ACCOUNT, A.DEPTID, A.PRODUCT, B.DESCR, A.LEDGER
ORDER BY 5, 3

2. Navigate to PeopleTools > Security > Query Security > Query Access Manager.
3. Add the view to a tree.

4. Navigate to Reporting Tools > Query > Query manager.
5. Create public query GL_ACCT_TEST.

SELECT A.BUSINESS_UNIT, A.AFFILIATE, A.CURRENCY_CD, A.BASE_CURRENCY,
A.ACCOUNT, A.DEPTID, A.PRODUCT, A.POSTED_TRAN_AMT, A.POSTED_BASE_AMT,
A.DESCR, A.LEDGER
FROM PS_GL_ACCT_TEST_VW A

6. Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard.
7. Create a new pivot grid, GL_ACCT_TEST_PG.
8. Select the newly create query as a data source for the pivot grid and leave all the fields selected as default in step 2 of the wizard.
9. Specify data model values under General Options tab.
10. Under Tree options tab attach a tree for account and for department.
11. Go to step 4 of the Wizard.
12. Select "Pivot Grid Only" as view.
13. Click on Next.
14. Results returned, when click Save user was logged out.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.