Print Page | Close Window

Total of open Incidents on a day.

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14466
Printed Date: 31 Mar 2025 at 12:34am


Topic: Total of open Incidents on a day.
Posted By: willemvdvliet
Subject: Total of open Incidents on a day.
Date 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


Replies:
Posted By: Robotacha
Date 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

Posted By: Robotacha
Date Posted: 23 Sep 2011 at 2:18am
and to count the number of incidents, you simply do a COUNT calculation on your IMincident field.

-------------
Regards,

Michael Jones

Posted By: willemvdvliet
Date 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

Posted By: lockwelle
Date 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


Print Page | Close Window