Please review the OOTB "Throughput in past week by work type" report (Throughput Reports category). The data view shows the number of cases by worktype and the number of Resolved. The associated Report Def is CPMWEEKLYTHROUGHPUT.
If this doesn't meet your exact needs, you should be able to modify it accordingly.
Thank you for your reply. I did already look in to OOTB RD Work-.pyWeeklyThroughput rule. This RD does show throughput data for Past Week but when I am trying to do this on a daily basis I do not find a way.
Please see attached images for the RD configuration I did and also the result I am getting. The problem here is that if a case is created say yesterday and resolved today then it is being counted as created and resolved as well in today's date column where as it should have been counted as 'Created' yesterday and 'Resolved' today.
PS - I used OOTB sql function - pxUpdateDateTimeIfTargetIsNull to populate the top header and passed pxCreateDateTime / pyResolvedTimeStamp.
Let me know if you know a way to get this working.
You can try with creating two summary reports which returns the created count and resolved count respectively for a given day. Now you can use these reports as sub reports in a main report by passing the day part of pxCreateDateTime in the work table and refer the sub report count result in the select. Removing duplicates should give you the counts for each day.
I created 2 reports which fetches Label, Create date & Key and Label, Resolved date & Key for cases created in last 7 days & cases resolved in last 7 days respectively. Then I am using both these reports as sub reports in a main report but still it does not work.
Your sub reports should summarize the count for each day. First reports gives no of cases created each day and the other one gives no of cases resolved each day. Then you can create another report to join the results of these two reports using day.
I tried this. The 2 sub reports works perfectly fine but I am unable to join them to produce the expected result. Kindly see attached file where in I have attached screenshots of all the 3 reports and the results I am getting. It will be great if you help me solve this problem.
I didn't notice that you also need pyLabel in query statement. Try with adding join condition on label field. Sub reports should give you the number of cases resolved/created for given day and label combination.