PeopleSoft nVision Drilldown Error with Query Prompts

I’ve come across an issue in PeopleSoft with Tools release 8.56 where nVision Drilldowns are failing if the parent instance used a prompted query.

This works as expected in release 8.52. I’ve opened a case with Oracle hoping for a resolution, but there hasn’t been much progress after ten months of back-and-forth.

Issue Description

The parent nVision Report Request runs to Success from the portal. The layout contains a query that expects an answer to one or more prompts. The user answers those prompts in the Report Request definition prior to runtime. After the instance is generated, the user DrillDowns on an eligible cell, but the DRILLDWN process will run to Error status with “Prompt value missing”.

Log file contents:

OprID = [redacted]
PS_TOKEN=[redacted]
OprID = [redacted]
PS_TOKEN=[redacted]

Prompt value missing. Please provide all the prompt values and re-run the report. (28,241)
Message Set Number: 28
Message Number: 241
Message Reason: Prompt value missing. Please provide all the prompt values and re-run the report. (28,241)

In 8.52, the Drilldown would inherit the user’s answers to the prompt in the Query Prompts tab. The inheritance comes from the PSNVSDRLPROMPTS table, which gets populated after the nVision instance is completed.

> SELECT TOP 1 * FROM PSNVSDRLPROMPTS

+------------+-------+------+---------+-------+---------+
|PRCSINSTANCE|QRYNAME|BNDNUM|FIELDNAME|BNDNAME|BINDVALUE|
+------------+-------+------+---------+-------+---------+
|         314|NVS_SUM|     1|bind1    |LEDGER |BUDGETS  |
+------------+-------+------+---------+-------+---------+

The PSNVSDRLPROMPTS table is still being populated during runtime of the parent report, and in the SQL trace the table is being queried during runtime of the drilldown with the process ID of the parent report. At some point, the DrillDown process is failing to use the data returned by the select on the PSNVSDRLPROMPTS table.

Issue Replication

1. If one doesn’t exist, create a query suitable for a PS/nVision layout that includes prompts.
2. Apply the query to a .nvs layout.
3. Create a PS/nVision Report Request using the new layout and Run. The first attempt will fail with the expected “Prompt value missing” error.
4. Go back and answer the query prompts in the Query Prompt tab of the Report Request.
5. Run the report again to Success.
6. Open the generated report instance. DrillDown on an eligible cell.
7. The DrillDown will fail with the same “Prompt value missing” error, despite there being no prompts in the DrillDown interface.

Work Around

Option 1: Use multiple iterations of the query with the prompt answers hard-coded into the query. In the case of the prompt being the GL ledger, this is easy to accomplish.

Option 2: Run the report and DrillDowns in 2-Tier. The same lack-of-inheritance behavior is observe, but the interface will prompt the user for an answer and the DrillDown will run to Success. This assumes the user has 2-Tier access.