Question
Pegasystems Inc.
JP
Last activity: 10 Feb 2016 17:54 EST
7.1.9 How to pass parameters from main report to sub report
According to prhelp document it is possible to pass parameters from main report to sub report.
| Subreport parameters |
If the subreport has input parameters, select the Auto-populate parameter values from the main report box, if you want the subreport parameters to have the same values as the corresponding parameters in the main report. This assumes that these parameters exist in both reports. Otherwise, specify values for the parameters of the subreport. |
However, I couldn't make it happen on my local PE. Am I missing something here?
According to prhelp document it is possible to pass parameters from main report to sub report.
| Subreport parameters |
If the subreport has input parameters, select the Auto-populate parameter values from the main report box, if you want the subreport parameters to have the same values as the corresponding parameters in the main report. This assumes that these parameters exist in both reports. Otherwise, specify values for the parameters of the subreport. |
However, I couldn't make it happen on my local PE. Am I missing something here?
| Main report | Sub Report |
|---|---|
 |
 |
 |
 |
 |
 |
|
|
Below is the SQL which is found in the clipboard page, "pyReportContentPage".
SELECT "PC0".pxhistoryforreference AS "pxHistoryForReference" ,
"PC0".pxtimecreated AS "pxTimeCreated" ,
"PC0".pyperformer AS "pyPerformer" ,
"PC0".pymemo AS "pyMemo" ,
"OP".SRCOL3 AS "pyAccessGroupAdditional"
FROM personaledition.pr_history_data "PC0"
INNER JOIN
(SELECT "OP".pyuseridentifier AS "srcol1" ,
"OP".pyusername AS "srcol2" ,
"AG".pyaccessgroupadditional AS "srcol3" ,
"OP".pzinskey AS "pzinskey"
FROM personaledition.pr_operators "OP"
INNER JOIN personaledition.pr_index_operators "AG"
ON ( ( "OP".pzinskey = "AG".pxinsindexedkey )
AND "AG".pxobjclass = ?
AND "OP".pxobjclass = ? )
WHERE "AG".pxindexpurpose = ?
-- AND AG.pyaccessgroupadditional = ? <-- If the parameter had been passed to the sub report, then sub query should have included this filtering condition which is missing.
) "OP" ON ( ( "OP".SRCOL1 = "PC0".pyperformer )
AND "PC0".pxobjclass = ? )
WHERE ( "OP".SRCOL3 = ? )
ORDER BY 1 ASC,
2 DESC
pyPreparedValues(1) AdditionalAccessGroup
pyPreparedValues(2) Index-Operator
pyPreparedValues(3) Data-Admin-Operator-ID
pyPreparedValues(4) SR:Administrators
pyPreparedValues(5) History-Data-Admin-Operator-ID
Pegasystems
IN
I am assuming one of the two things happened
1) The parameter "AccessGroup" was added in the sub report after the main report was configured and in the value column under "Parameters" it is empty
2) The value did not get populated or was cleared under "Parameters"
If you had the value as Param.AccessGroup in the sub report configuration, and save the main report, it should work fine.
Pegasystems Inc.
JP
Hi Rajiv,
I couldn't see any improvements after explicitly specified Param.Accessgroup in the sub report configuration.
Sub report configuration:
Generated SQL
SELECT "PC0".pxhistoryforreference AS "pxHistoryForReference" ,
"PC0".pxtimecreated AS "pxTimeCreated" ,
"PC0".pyperformer AS "pyPerformer" ,
"PC0".pymemo AS "pyMemo" ,
"OP".SRCOL3 AS "pyAccessGroupAdditional"
FROM personaledition.pr_history_data "PC0"
INNER JOIN
(SELECT "OP".pyuseridentifier AS "srcol1" ,
"OP".pyusername AS "srcol2" ,
"AG".pyaccessgroupadditional AS "srcol3" ,
"OP".pzinskey AS "pzinskey"
FROM personaledition.pr_operators "OP"
INNER JOIN personaledition.pr_index_operators "AG"
ON ( ( "OP".pzinskey = "AG".pxinsindexedkey )
AND "AG".pxobjclass = ?
AND "OP".pxobjclass = ? )
WHERE "AG".pxindexpurpose = ?
) "OP" ON ( ( "OP".SRCOL1 = "PC0".pyperformer )
Hi Rajiv,
I couldn't see any improvements after explicitly specified Param.Accessgroup in the sub report configuration.
Sub report configuration:
Generated SQL
SELECT "PC0".pxhistoryforreference AS "pxHistoryForReference" ,
"PC0".pxtimecreated AS "pxTimeCreated" ,
"PC0".pyperformer AS "pyPerformer" ,
"PC0".pymemo AS "pyMemo" ,
"OP".SRCOL3 AS "pyAccessGroupAdditional"
FROM personaledition.pr_history_data "PC0"
INNER JOIN
(SELECT "OP".pyuseridentifier AS "srcol1" ,
"OP".pyusername AS "srcol2" ,
"AG".pyaccessgroupadditional AS "srcol3" ,
"OP".pzinskey AS "pzinskey"
FROM personaledition.pr_operators "OP"
INNER JOIN personaledition.pr_index_operators "AG"
ON ( ( "OP".pzinskey = "AG".pxinsindexedkey )
AND "AG".pxobjclass = ?
AND "OP".pxobjclass = ? )
WHERE "AG".pxindexpurpose = ?
) "OP" ON ( ( "OP".SRCOL1 = "PC0".pyperformer )
AND "PC0".pxobjclass = ? )
WHERE ( "OP".SRCOL3 = ? )
ORDER BY 1 ASC,
2 DESC
Pegasystems
IN
Hi Chunzhi,
I tried to reproduce this in my local 7.1.9 instance and I am getting the expected query.
SELECT
"PC0".pxhistoryforreference AS "pxHistoryForReference" ,
"PC0".pxtimecreated AS "pxTimeCreated" ,
"PC0".pyperformer AS "pyPerformer" ,
"PC0".pymemo AS "pyMemo" ,
"OP".SRCOL3 AS "pyAccessGroupAdditional" ,
"PC0".pzinskey AS "pzInsKey"
FROM datauser.pr_history_data "PC0"
INNER JOIN (
SELECT
"OP".pyuseridentifier AS "srcol1" ,
"OP".pyusername AS "srcol2" ,
"AG".pyaccessgroupadditional AS "srcol3" ,
"OP".pzinskey AS "pzinskey"
FROM datauser.pr_operators "OP"
Hi Chunzhi,
I tried to reproduce this in my local 7.1.9 instance and I am getting the expected query.
SELECT
"PC0".pxhistoryforreference AS "pxHistoryForReference" ,
"PC0".pxtimecreated AS "pxTimeCreated" ,
"PC0".pyperformer AS "pyPerformer" ,
"PC0".pymemo AS "pyMemo" ,
"OP".SRCOL3 AS "pyAccessGroupAdditional" ,
"PC0".pzinskey AS "pzInsKey"
FROM datauser.pr_history_data "PC0"
INNER JOIN (
SELECT
"OP".pyuseridentifier AS "srcol1" ,
"OP".pyusername AS "srcol2" ,
"AG".pyaccessgroupadditional AS "srcol3" ,
"OP".pzinskey AS "pzinskey"
FROM datauser.pr_operators "OP"
INNER JOIN datauser.pr_index_operators "AG" ON (
( "OP".pzinskey = "AG".pxinsindexedkey ) AND
"AG".pxobjclass = ? AND
"OP".pxobjclass = ? )
WHERE ( "AG".pyaccessgroupadditional = ? )
AND "AG".pxindexpurpose = ? ) "OP"
ON ( ( "OP".SRCOL1 = "PC0".pyperformer )
AND "PC0".pxobjclass = ? )
WHERE ( "OP".SRCOL3 = ? )
ORDER BY 1 ASC, 2 ASC
pyPreparedValues(1) QuarksTe:Administrators
pyPreparedValues(2) AdditionalAccessGroup
pyPreparedValues(3) Index-Operator
pyPreparedValues(4) Data-Admin-Operator-ID
pyPreparedValues(5) QuarksTe:Administrators
pyPreparedValues(6) History-Data-Admin-Operator-ID
Here is the activity I used to pass the parameter
Can you share your tracer output when running the report with the parameter value?
Updated: 10 Feb 2016 8:02 EST
Pegasystems Inc.
JP
Hi Rajiv,
I didn't use any activity to run the main report, only used "Action -> Run" menu available on the report definition form.
I have selected "Prompt for filter changes before displaying report" option on the "Report Viewer" tab.
Chunzhi
Pegasystems
IN
The parameter value will be empty and the filter condition will be ignored unless you provide a "default value" in the parameters tab for that parameter.
Pegasystems Inc.
JP
"pyPreparedValues(4) SR:Administrators" is the parameter value, I put in at run-time, and you can see that parameter was treated as the main report's filter condition : WHERE ( "OP".SRCOL3 = ? )
Updated: 10 Feb 2016 9:02 EST
Pegasystems Inc.
JP
1. on Main report rule form click "Actions -> Run"
2. provide parameter on the screen. (I have selected "Prompt for filter changes before displaying report" option on the "Report Viewer" tab)
3. submit
Updated: 10 Feb 2016 17:54 EST
Pegasystems Inc.
JP
I just realized that I did NOT provide any Parameters of the report by entering filter condition at step2!!
I have misunderstood that parameters are provided by modifying the filtering condition to use literal string value inplace of the "param.AccessGroup".