Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Total of open Incidents on a day. Post Reply Post New Topic
Author Message
willemvdvliet
Newbie
Newbie


Joined: 22 Sep 2011
Online Status: Offline
Posts: 2
Quote willemvdvliet Replybullet Topic: Total of open Incidents on a day.
    Posted: 23 Sep 2011 at 1:25am

Dear forum members,

 

I would like to define a report which shows the current open Incidents of a day.

As Incident management system we are using Peregrine ServiceCenter based on Oracle 10 / Crystal Reports 8.5.

The incidents are stored in the probsummary table, every incident has an open_time and when the incident gets closed the close_time will be filled.

 

Everyday we want to measure how many total of incidents are open:

Incident nr

Open Time

Close Time

IM0193611

3-1-2011  8:11:20

26-1-2011  17:30:57

IM0193612

3-1-2011  8:13:14

3-1-2011  10:04:04

IM0193615

3-1-2011  8:22:38

4-5-2011  10:36:11

IM0193616

3-1-2011  8:24:13

7-1-2011  14:39:08

IM0193617

3-1-2011  8:29:53

4-5-2011  10:36:12

IM0193618

3-1-2011  8:48:55

3-1-2011  14:19:07

IM0193627

3-1-2011  8:59:27

4-5-2011  10:36:12

 

For example the Incident IM0193611 has been opened on 3-1-2011 and is closed on 26-1-2011, when the close time is not filled the currentdatetime can be used.

So if we take IM0193611 which is opened on 3-1-2011 and is closed on 26-1-2011 then it also has to be counted for the days between till it is closed.

If you see the number for 4-1-2011 then you should see 7 incidents in total.
When you check the numbers on the 27-1-2011 then you should see 3 incidents.
Does anybody know how I can accomplish this?

Best Regards,

Willem

IP IP Logged
Robotacha
Groupie
Groupie
Avatar

Joined: 11 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 97
Quote Robotacha Replybullet Posted: 23 Sep 2011 at 2:15am
So basically, You want a report to show how many incidents were opened on a specified day?
 
You need to think how you want the data.
 
Do you want a parameter in the report so you can choose a specific date?
 
Do you want the report to be automated, so each morning you get an email with YESTERDAYS report?
 
either way should be fairly easy.
 
I will let someone else comment on the best way to do that,
 
but personally, I would simply do a select expert formula along the lines of:
 
{probsummary.opentime} = date(currentdate)-1
 
this will show all incidents opened yesterday.
 
same for parameters..
 
{probsummary.opentime} = {?dateparameter}
 
Regards,

Michael Jones
IP IP Logged
Robotacha
Groupie
Groupie
Avatar

Joined: 11 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 97
Quote Robotacha Replybullet Posted: 23 Sep 2011 at 2:18am
and to count the number of incidents, you simply do a COUNT calculation on your IMincident field.

Edited by Robotacha - 23 Sep 2011 at 2:18am
Regards,

Michael Jones
IP IP Logged
willemvdvliet
Newbie
Newbie


Joined: 22 Sep 2011
Online Status: Offline
Posts: 2
Quote willemvdvliet Replybullet Posted: 23 Sep 2011 at 5:58am

 

Dear Robotcha,

I will tell you something about the history.
We’ve made an report which shows the open incidents.
An employee refreshes the report everyday and put the number of open incidents for that day in a Excel chart. After a while they have a Matrix where they can see a trend.

So referring to the earlier post, they want to see how many incidents are open on that day.

3-1-2011

7

4-1-2011

6

5-1-2011

6

6-1-2011

6

7-1-2011

6

8-1-2011

5

9-1-2011

5

 

Is it possible to make a group in Crystal based on day without any relation to the opentime?
Within that group a counts happens with the Incidents between open_time and close_time?
So then your be able to see how many incidents are open on that specific day.
Finally I can make a graph with a trend chart to show them if the workload is increasing or decreasing?

Best Regards,

Willem

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Sep 2011 at 12:25pm
as long as the value that you want to group on is 'readily' available to CR, then it will work.
 
so you can easily group your data on a date, but that date has to be in the data, so, for a given date that is not the start or stop, it would be harder...not impossible.
 
I would think that you could create a Command Object or maybe your database already has a table that is nothing but a list of dates.  Then you could do a select from the data joining to the Command Object that will give a date that you can then use to display on the report.  Yes, items will be duplicated, but that is what you are after.
 
HTH
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.039 seconds.