How to make a report showing the number of open incident at any given time

lkudsk
lkudsk used Ask the Experts™
on
Hallo everybody,

I whould very much like to make a report (in Crystal reports) showing the number of open incident at any given time. For example per month

Open incident january 1th = XXX
Open incident february 1th = XXX

The report should show the history of open incident per month

I have tried to make a report myself my the problem is that, as I can see it, Crystal report is only showing an instant image - an image of how the status of the incident is right now.
Example:
I run a report in january and it showed that I have 270 open incident.
If I run the same report february 1th Some (50 incident) of the incident is closed in the meantime (the period between January 1th and February 1th)

Therefore the report will show 220 if I run the report February 1th. The problem is that the report that I ran Januar 1th also shows 220 open incident (because the status of the incident have changede)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You want to be able to run a report today and see how many incidents were open on a given date in the past.

Example on Jan 1st you have 200 incidents open
During January, you close 50 and open 20 new ones
Feb 1st shows 170 open incidents.

You want to run a report today to show the same results for Jan 1st and Feb 1st.

Is that correct?

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this idea

Selection formula

IsNull({ClosedDateField}) OR {ClosedDateField} >= Date(2012,1,1)

Create formulas for each month

If {DateOpened} < Date(2012,1,1) AND {ClosedDateField} > Date(2012,1,1) then
     1
Else
      0

Similarly for each month.

You can then summarize the formulas to get the totals you want.

mlmcc

Author

Commented:
That is correct. I wil try your solution and get back to you as quick as possible
/Kudsk
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
Should the formula for Febryar be like this

If {probsummarym1.open.time} < Date(2012,1,2) AND {probsummarym1.close.time} > Date(2012,1,2) then
     1
Else
      0


I am not safe with how I make the dates

Author

Commented:
Do you want me to summarize Januar and Febraury to get how many incident open at Febraury 31st.?

Author

Commented:
I have my repot here. I don't understand how to make it. Sorry

Would it be possible for you to make a soloution I can see?

thanks in advance
Antal--bne-sager-NU.rpt
If you right-click on one of the month columns (Jan, Feb, etc.) and use Insert > Summary to create a grand total for that column, does that give you the figure that you need for that month?

 I believe mlmcc's idea was to create a formula for the first of each month that would produce a 1 if an incident was opened before the first of that month ({open date} < Date (2012, 1, 1), for Jan) and closed after the first of that month ({Close date} > Date (2012, 1, 1), for Jan).  Then if you do summaries on those formulas, you should get a count of those incidents for each month.

 However, I think that only counts the closed incidents.  If you want to include the incidents that haven't been closed, that will require some changes to the monthly formulas, or maybe you'll need separate formulas to count the open incidents.

 If you want to include open incidents in the counts, then one question is, how many different records could you have for an incident?  Is there just one record for each incident, which will have a close date if it has been closed, or no close date if it has not?  Or could there be different records for an incident, perhaps for status changes or something like that?  If there could be multiple records for an incident, but you only want to count each incident once, then the formulas will presumably have to handle that somehow.

 James
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
T]James is right.

Try

If {DateOpened} < Date(2012,1,1) AND (IsNull({ClosedDateField}) OR {ClosedDateField}  >= Date(2012,1,1) then
     1
Else
      0

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial