Actually Business is looking for a single report,which gives the present timeliness of a assignment ,despite of assignment is present in work basket or work list.
Do you suggest here ,to built a report on work table ,and do a left outer join with Assign-Workbasket and Assign-Worklist class?.
I'm thinking of 'Case When' function does not help here ,as in single column ,we should get the current timeliness of assignment irrespective of the assign table.
I'm just assuming ,like make a single function alias(Embed-userfunction) which takes the details of the assignment(pxGoalExecute,pxDeadlineExecute) ,and long with type of assignment class ,so that we can determine the status like "WithInGoal",PassedGoal,PassedDeadline".
The challenge over here is to pass work list & work basket class to Embed-use function.
Please suggest if we have any better solution to accomplish it.
As Rajiv suggested, you should use View and not a dedicated table as you will have issues while maintaining them like you are having now with deletion of records.
Just create a view in Database which does a union of both workbasket and worklist assignments. Create a class in Pega and map to that view. And you should be done once you write reports against that class.
With view you don't have to worry about maintaining data or deletion of assignments as it is just a dummy view and not the actual data.
You don;t need Declare triggers also as view will again take care of fetching all results.