How to concatenate a column values coming from a sub-report and show it in the main report via a Report Definition?
Hi All,
I have a Report Definition on Organization table joining multiple tables. I have a sub-report to run on some other set of tables. Now, for each record in main report, I might get multiple records on joining with sub-report because each Organization may have multiple Firms associated to it.
The requirement says that the multiple firm names should be shown as comma separated values in one column in the main report. How to achieve this?
Although there is no OOTB functionality which can do this, it isn't impossible. If you need to achieve this with a bit of overhead, do this.
1. Create a property of mode Single Page and in Data Access of this property, use DataPage.(Eg: Property: .CaseContent of Single Page) and another property of type text (Eg: .CaseContentConcatenate)
2. Create a DataPage in which the DataSource is an activity which refers to the SubReport that you needed to use in the report definition.
3. In the Datapage activity, do the concatenation or any other functions that you needed to do. (Eg: Append to string CaseContentConcatenate)
4. Use this property in the Report Defintion. (.CaseContent.CaseContentConcatenate)
Note: Make sure that the data page is parameterised and the join property that you were supposed to use in the Report Defintion sub report is passed both to the DataPage and the Reportdefinition being called in the DataPage activity).
Your requirements needs recursion in SQL (or a different aggregate function) which is currently not available in Report Definitions. Please raise an enhancement request (or follow up with your Pega account executive) and product management will consider your use case. If you have more use cases which require this, please shre them in your request.