Question
Tech Mahindra
IN
Last activity: 24 Mar 2023 12:09 EDT
Get data from two different tables using report definition
Hello Guys,
We have a scenario where we have 2 case types, one is for X region and other is Y region. they don't have common data model also.
But I want to have a report definition for fetching some basic data(lives and mapped to different page and property in 2 tables) for dashboard that shows data from two cases and need to show the results in a table layout.
Any suggestions please.
Accepted Solution
Updated: 24 Mar 2023 12:09 EDT
Bits in Glass
IN
@BATTINIRAMESH can you try this option in data page and source that dpage to the table?
LTIMindtree
IN
@BATTINIRAMESH You can also use sub report in your report definition. Please refer below links for details steps
https://academy.pega.com/topic/combining-data-different-classes-using-subreport/v1
Updated: 21 Mar 2023 0:23 EDT
Tech Mahindra
IN
@VIGNESHP1028 hello
I need to show worklist of an operator with cases from two different case types which are assigned to an operator. Is it possible with sub reports? I am thinking to have my report definition in Assign-WorkList class and be using class joins to show some data from case in dashboard columns, not sure how to show data from 2 different case types cases in same column.
if possible please let me know how in short.
Updated: 21 Mar 2023 0:56 EDT
Bits in Glass
IN
@BATTINIRAMESH yes right, assign-worklist will have all the assignments assigned to him, it will have case key reference too. You can class join with work class using this key.
.pxrefobjkey of assign-worklist with . pzinskey of work class.
So->
1st assignment-> it will join with work class using -> gets the case by joining-> you can map the case columns in query tab.
2nd assignment-> follows the same
Data model is different-> can you elaborate this?
If you want to access complex clipboard structure, you can go for 2 subreports(for 2 case typed), here also you will be joining with pxrefobjkey. You can optimize the page properties to access those in the subreport. When you are passing pxrefobjkey to the 2 sub reports, finally it gets matched only to 1 subreport as you are joining with case key(pxrefobjectkey) and the result of that particular Subreport will only get joined. You can use that in the query tab of the main report.
Coforge
IN
You can create RD in the Assign-WorkList class.
In data access tab have 2 joins with your 2 work classes. Use Filter Condition as shown in Screenshot with join type as "Only include matching rows" for both the joins.
To Get columns from respective case types, use prefix.column name in Query Tab to get results. (As in Screenshot Prefix is CLW for 1st case type & C for 2nd case type)
Use pxAssignedOpertorID in the filter to get results for cases assigned to a particular operator.
Thanks
Let me know if it helps.
Tech Mahindra
IN
@abhirajg Hello, Thanks
I already tried this, but it is not giving results. Seems it is checking the join conditions from 2 tables to be satisfied and this will not be possible because two tables have different columns and no link between them.
Coforge
IN
Can you show what filter condition you have given on both of your Joins?
Bits in Glass
IN
@BATTINIRAMESH In addition to above you can create a database view and report directly on the class mapped to the view. will be easier to configure in Pega. You can also explore Materialized views(if using Postgres) for better performance.