How to use PageList property values in Report Definition Query as IN clause?
I am in need of one Report Definition, where the query should built like below.
Select Column1, column2.. From Table TableName
Where UserID IN ('abc','def','ghl',.....,'xyz')
So in WHERE Clause I need to use values of one property from PageList. something like Code-Pega-List.pxResults(*).UserID, So i need to get the results for all the userids present in that pagelist. I don't have another report-definition to use as sub-report, I need this with Page-List values only.
Could you Please help, how can we achieve this.
Thank you very much in advance for the help.
Thanks,
Ramarao.
Accepted Solution
IKOR
AU
Hi Ramarao,
If you are planning to invoke the report definition either from Data page or an activity, then there is a way to address your problem.
Before invoking the report definition,construct the string for the where clause in a param variable and pass the variable to report definition.At run time, report definition captures the param value and execute the report for you.
Cheers,
Naren
Incessant technologies
IN
HI Naren
Could you please explain the above approach in detail, like what parameter I have to use set the where clause value.
I am using calling pxRetriveReportData activity from my activity and I do not see any parameter for where clause.
Thanks
Raja
Truviq Systems Private Limited
IN
Hi Rajasekhar,
Set the List property values in the form of comma separated values to a parameter in the activity. (use the same parameter name in the report definition too)
Also set the required parameters for pxRetrieveReportData in the same step.
Use Pass current parameter page while calling pxRetriveReportData activity.
Hope this helps.
Thanks,
Srikanth.
Pegasystems
IN
There is no direct support for a page list property in report definition rule in the filter conditions. You can use the option suggested by @NPotnuru or you can use a value list property instead.
The option to provide page list property in report definition rule directly is an enhancement that is there is our backlog for a future release. Please reach out to your Pega account executive if you need this enhancement.
Accenture
IN
Hi Rajeev,
I have a similar requirement but a bit less complex. I am setting a list of values in a property as comma separated string and need to pass it as a filter criteria in report definition where it will generate the query like select x,y,z from table name where id in (comma separated values). Can you suggest me how I can achieve it as I am not calling the report definition in activity.
Thanks,
Chiranjeet
Incessant Technologies
GB
Hi,
I've tried this and working fine for string type of columns. But this approach is not working for integer type of columns. Any alternative for this?
