| Author |
Message |
skyliner34
Newbie
Joined: 20 Oct 2011
Online Status: Offline
Posts: 18
|
  
Topic: Count of repeated status?
Posted: 20 Oct 2011 at 4:36am |
Hello,
looking for a bit of help please. I have an Oracle based Remedy database and from it I need to extract incidents over lastfullmonth that have been Resolved twice in the Status.
For example a call is logged, assigned, worked on and Resolved, then after a short time the status changes from Resolved to assigned (in essence re-opened, due to the fix not working), a bit more work and then resolved for a second time.
I know that the simple solution is to apply a new field to Remedy called "Re-Opened" and then Crystal can directly report from that, but the Admin will not put any development time into it, so I am stuck with trying to work a solution from within Crystal.
Any thoughts? Edited by skyliner34 - 20 Oct 2011 at 4:44am
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Oct 2011 at 4:47am |
one possible way
group on the primarykey (call_id)
create a formula to flag "resolved"
if table.status='resolved' then 1
sum this at the group level
sum(@flag,table.call_id)
add a goup select criteria (in the select expert, group selection)
sum(@flag,table.call_id)>1
|
| IP Logged |
|
skyliner34
Newbie
Joined: 20 Oct 2011
Online Status: Offline
Posts: 18
|
Posted: 20 Oct 2011 at 5:55am |
|
Thanks :) I will give that a try
|
| IP Logged |
|
skyliner34
Newbie
Joined: 20 Oct 2011
Online Status: Offline
Posts: 18
|
Posted: 20 Oct 2011 at 10:04pm |
|
Afraid all I can get the report to do is to display the final call status of "Resolved" as the figure 1 (if the status is "closed" then a 0 is shown). I cant get it to count how many times the call was at the status of Resolved greater than 1
|
| IP Logged |
|
skyliner34
Newbie
Joined: 20 Oct 2011
Online Status: Offline
Posts: 18
|
Posted: 21 Oct 2011 at 12:33am |
I have found this within another thread here
local numbervar iIndex := instr({HPD_HelpDesk.Status}, "Resolved");
local numbervar iCount:=0;
while iIndex <> 0 do
(
if iIndex <> 0 then iCount := iCount + 1;
iIndex := instr(iIndex + 1, {HPD_HelpDesk.Status}, "Resolved")
);
iCount
But once again will only display the figure "1" when the final status is "Resolved", but not how many times the status of a call was at Resolved (I.E Opened, Resolved, Assigned, Resolved. Should show a figure of "2" as for all intents and purposes the call was reopened)
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Oct 2011 at 2:36am |
|
Perhaps I am not understanding your data. I was under the impression that you have multiple rows of data per but do you have one row with a single field that gets changes appended to it?
Can you post some dummy sample rows?
|
| IP Logged |
|
skyliner34
Newbie
Joined: 20 Oct 2011
Online Status: Offline
Posts: 18
|
Posted: 21 Oct 2011 at 2:57am |
Sorry yes, perhaps I didnt make my situation very clear.
I have 1 row called HPD_HelpDesk.Status which will only display 1 statement at a time, but changes often depending on the progress of the call.
Here are some lines from the report. You will see the column "reopened" and to the right, the status field (of which is displaying the current status). What I would like to try to do is to `drill` down into the status to determine if a call was ever at "Resolved" more than once.
| Case_ID_ |
Case_Type |
Create_Time |
Category |
reopened |
Status |
Resolved_Time |
| SD526881 |
Incident |
28/09/2010 15:39:10 |
Software |
1.00 |
Resolved |
27/09/2011 16:16:33 |
|
|
|
|
|
|
|
| SD537854 |
Incident |
01/12/2010 13:20:18 |
Software |
0.00 |
Closed |
01/09/2011 14:55:01 |
| SD539170 |
Incident |
09/12/2010 10:54:18 |
Software |
1.00 |
Resolved |
28/09/2011 10:49:17 |
| SD540762 |
Incident |
20/12/2010 10:19:32 |
Software |
0.00 |
Closed |
06/09/2011 13:10:23 |
|
|
|
|
|
|
|
| SD548206 |
Incident |
05/02/2011 06:14:21 |
Smart421 |
0.00 |
Closed |
05/09/2011 16:23:10 |
| SD551859 |
Incident |
28/02/2011 10:18:04 |
Hardware |
0.00 |
Closed |
06/09/2011 15:29:17 |
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Oct 2011 at 3:14am |
|
So I assume there are multiple rows that have the same caseid, correct?
Can you show samples of this?
|
| IP Logged |
|
skyliner34
Newbie
Joined: 20 Oct 2011
Online Status: Offline
Posts: 18
|
Posted: 21 Oct 2011 at 3:27am |
|
No, each row is unique by its case_id. The query will return cases that were either closed or resolved during a given time (in this case lastfullmonth)
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Oct 2011 at 3:35am |
|
I am thinking that you are under the impression that since crystal is hooked into the db that it can utilize any data in it. It only has access to what you bring into the report after select statements are applied. I order to know which records have more than one resolution you have to bring in all the records. Once you filter these out there is no way to reference that data.
Another possibility is that your data set is one row that gets updated on each change. Unless you have another history table that stores the changes that accur and use that as your source you are out of luck. Crystal only knows what data is pulled into the report.
Does this make sense?
|
| IP Logged |
|
|
|
