Tagged: Reports; triggers;
12/09/2013 at 19:46 #1464
For performance appraisal reviews, my supervisor has asked for data on the number of times POB cases have been transferred from one responsible user to another and how long that original user had the case in their posession before re-assigning.
Does anyone have any ideas on how to accomplishe this?09/02/2014 at 21:29 #1767
our Reporting department gets these figures by querying the History table. There is the only place where all these data are normally kept. I can’t see a way of getting such a report with standard POB features – only via SQL.
Stefan10/02/2014 at 17:18 #1768
Thank you, Stefan. Do you have a SQL query that you can share?10/02/2014 at 20:23 #1769
I’m afraid I can’t give a complete statement (I don’t have the statements our BI department uses), but at least I could help you developing it. Which version are you using (POB 21 or POB G6?) And do you have a separate History Event for the change of Responsibile User? Do you use the Responsible/ResponsibleGroup fields in that event? The whole thing is to extract the information who did it when and who got the responsibility – out of the history lines, queried by the history action ID.02/06/2014 at 17:21 #2056
Stefan – we are on POB G6 and do have a separate History event for the hcange in Responsible User. It keeps track of the from and to information. So I agree that we should be able to get this reported. However, I am struggling with our report writers being able to come up with the report. That is why I was interested if you had one already built. Is it possible to get teh report from your BI department? Even, just the fields they are using? I need to give our report writers some direction so that they cannot use the “it’s too complex” or “I can only give you this…” excuses. So far, all I’ve been able to get from them is a report of the text in the History lines – all of them. So I would have to download to Excel and then manipulate the data to get what I really need. Thanks – Stacy02/06/2014 at 17:40 #2057
a very simple statement counting the reassignments in G6 would look like this:
from History his
inner join History_Action hia on his.history_action_pk = hia.history_action_pk and hia.id=’1005′
inner join Case_Table cas on cas.case_pk = his.root_pk
group by cas.id
‘1005’ needs to be replaced by the history event ID from your system.
And if you want to create a virtual calculated column, showing the reassignment count in your case overview:
select count(*) from History his inner join History_Action hia on his.history_action_pk = hia.history_action_pk and hia.id=’1005′ where his.root_pk=case_pk
Such a column would have the cool effect that you could export your case list including this number at any time even without having a report – plus you can filter for it, define color triggers etc…
Does that help you?
Stefan04/08/2014 at 21:38 #2092
Thanks for your help, Stefan. I was able to share the information you provided with our report writers and they have written a report that is meeting our requirements.
StacyS14/08/2014 at 08:07 #2097
- You must be logged in to reply to this topic.