cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Report to report Monthly Open Incidents

Former Member
0 Kudos

We currently have an Incident Management System to log our Support issues. Incidents have a Created and Closed Date.

I have been asked to write a report that returns all Opened calls grouped by Month for the last 12 months. What I'm having difficult in is working out how to determine whether an Incident was open in one of those months.

Anyone have any ideas?

I'm trying to set this up as a Cross-Tab report with Months listed across the top and totals on 2nd row

Any help would be greatfully received

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

I have done something like this in a manual crosstab for an extablished time period of 1 year, not for the last 12 months so my report is not dynamic other than the year. You will need a counter for each month of your time period that determines if the record was open during that month.

Edited by: Debi Herbert on Apr 27, 2011 8:30 AM

Former Member
0 Kudos

Hi, I don't suppose I could have a copy of the file to take a look at could I. although I did Crystal a few years ago I'm trying to reunderstand it all.

Also the code you sent, where would I put that in my cross tab or is it supposed to be a formulae?

Thanks

Nathan

Former Member
0 Kudos

What you are trying to achive is very difficult in Crystal. I had to do a similar report for a service desk application and ended up doing it in a SQL command using a CrossJoin.

This is the query I developed for SQL sever it might give you some pointers


select StartDate, EndDate, mm, 
sum ( case when daccepted < StartDate and ( dcleared >= StartDate or dcleared = '1900-01-01') then 1 else 0 end ) as Brought_forward, 
sum ( case when daccepted between StartDate and EndDate then 1 else 0 end ) as Opened, sum ( case when dcleared between  StartDate and EndDate then 1 else 0 end ) as Closed, 
sum ( case when daccepted <= EndDate and ( dcleared > EndDate or dcleared = '1900-01-01') then 1 else 0 end ) as Carried_forward, 
sum ( case when daccepted between StartDate and EndDate and ( dcleared > EndDate or dcleared = '1900-01-01') then 1 else 0 end ) as ThisMonths_Carried_forward, 
sum ( case when daccepted between StartDate and EndDate         and dcleared between  StartDate and EndDate  then 1 else 0 end ) as OpenResolvedThisMonth
from calldetails
cross join (     Select     dateadd( mm, mm * -1 , today - day(today ) ) + 1 as StartDate    , dateadd( mm, ( mm - 1) * -1 , today - day( today )  ) as EndDate    , mm    from         
( select 1 as mm 
union all select 2 
union all select 3       
 union all select 4
 union all select 5 
union all select 6        
union all select 7 
union all select 8 
union all select 9       
union all select 10 
union all select 11 
union all select 12        ) cal 
cross join (select convert(datetime,convert(char(10),getdate(),121)) as today) tt) DateRange
where ( dcleared = {ts '1900-01-01 00:00:00'} /* open calls, regardless of when started */
or daccepted >=  dateadd ( yy, -1, getdate() - day(getdate() ) ) /* all calls at least one year */)
group by StartDate, EndDate, mm
order by StartDate Desc

Ian

Former Member
0 Kudos

Thank you, I've changed the code to what I think reflects my Incident table however I get the error below:

Msg 207, Level 16, State 1, Line 22

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 23

Invalid column name 'daccepted'.

Msg 209, Level 16, State 1, Line 24

Ambiguous column name 'StartDate'.

Msg 207, Level 16, State 1, Line 2

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 2

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 2

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 4

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 4

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 4

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'daccepted'.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'dcleared'.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'dcleared'.

Msg 209, Level 16, State 1, Line 25

Ambiguous column name 'StartDate'.

My code looks like this:

select ReportedDate, CompletionDate, mm,

sum ( case when daccepted < ReportedDate and ( dcleared >= ReportedDate or dcleared = '1900-01-01') then 1 else 0 end ) as Brought_forward,

sum ( case when daccepted between ReportedDate and CompletionDate then 1 else 0 end ) as Opened, sum ( case when dcleared between ReportedDate and CompletionDate then 1 else 0 end ) as Closed,

sum ( case when daccepted <= CompletionDate and ( dcleared > CompletionDate or dcleared = '1900-01-01') then 1 else 0 end ) as Carried_forward,

sum ( case when daccepted between ReportedDate and CompletionDate and ( dcleared > CompletionDate or dcleared = '1900-01-01') then 1 else 0 end ) as ThisMonths_Carried_forward,

sum ( case when daccepted between ReportedDate and CompletionDate and dcleared between ReportedDate and CompletionDate then 1 else 0 end ) as OpenResolvedThisMonth

from Incidents

cross join ( Select dateadd( mm, mm * -1 , today - day(today ) ) + 1 as StartDate , dateadd( mm, ( mm - 1) * -1 , today - day( today ) ) as EndDate , mm from

( select 1 as mm

union all select 2

union all select 3

union all select 4

union all select 5

union all select 6

union all select 7

union all select 8

union all select 9

union all select 10

union all select 11

union all select 12 ) cal

cross join (select convert(datetime,convert(char(10),getdate(),121)) as today) tt) DateRange

where ( dcleared = {ts '1900-01-01 00:00:00'} /* open calls, regardless of when started */

or daccepted >= dateadd ( yy, -1, getdate() - day(getdate() ) ) /* all calls at least one year */)

group by StartDate, EndDate, mm

order by StartDate Desc

Thank you

Nathan

Former Member
0 Kudos

Nathan,

It is a pretty ugly report with a zillion formulas based on formulas. I don't mind sharing it, but have nowhere to post it for you to view nor should you post your email address here. If I look at your profile, and it is there, I could send the report, but even I have trouble reviewing the formulas-it is not a pretty sight. I have learned a few new tricks since writting it and one day I might find time to clean it up somewhat.

Ian, I am going to look closely at your SQL statement. Thanks.

(Don, I'm a bad girl suggesting I could send this directly, but I am willing to share this with who ever would like to see it)

Debi

If you can get Ian's solution to work, it is much prettier.

Edited by: Debi Herbert on Apr 27, 2011 12:09 PM

Former Member
0 Kudos

Nathan,

How about something a little easier on the eyes... Try this... (Using SQL Server syntax...)


DECLARE @Month1 INT, @Month2 INT, @Month3 INT, @Month4 INT, @Month5 INT, @Month6 INT,
@Month7 INT, @Month8 INT, @Month9 INT, @Month10 INT, @Month11 INT, @Month12 INT
SET @Month1 = DatePart(mm, DateAdd(mm, DateDiff(mm, 0, GetDate()), 0)) +1
SET @Month1 = CASE WHEN @Month1 > 12 THEN @Month1 - 12 ELSE @Month1 END
SET @Month2 = CASE WHEN @Month1 +1 > 12 THEN @Month1 - 11 ELSE @Month1 +1 END
SET @Month3 = CASE WHEN @Month2 +1 > 12 THEN @Month2 - 11 ELSE @Month2 +1 END
SET @Month4 = CASE WHEN @Month3 +1 > 12 THEN @Month3 - 11 ELSE @Month3 +1 END
SET @Month5 = CASE WHEN @Month4 +1 > 12 THEN @Month4 - 11 ELSE @Month4 +1 END
SET @Month6 = CASE WHEN @Month5 +1 > 12 THEN @Month5 - 11 ELSE @Month5 +1 END
SET @Month7 = CASE WHEN @Month6 +1 > 12 THEN @Month6 - 11 ELSE @Month6 +1 END
SET @Month8 = CASE WHEN @Month7 +1 > 12 THEN @Month7 - 11 ELSE @Month7 +1 END
SET @Month9 = CASE WHEN @Month8 +1 > 12 THEN @Month8 - 11 ELSE @Month8 +1 END
SET @Month10 = CASE WHEN @Month9 +1 > 12 THEN @Month9 - 11 ELSE @Month9 +1 END
SET @Month11 = CASE WHEN @Month10 +1 > 12 THEN @Month10 - 11 ELSE @Month10 +1 END
SET @Month12 = CASE WHEN @Month11 +1 > 12 THEN @Month11 - 11 ELSE @Month11 +1 END

SELECT 
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month1 THEN 1 END) AS Month1,
MAX(@Month1) AS M1,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month2 THEN 1 END) AS Month2,
MAX(@Month2) AS M2,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month3 THEN 1 END) AS Month3,
MAX(@Month3) AS M3,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month4 THEN 1 END) AS Month4,
MAX(@Month4) AS M4,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month5 THEN 1 END) AS Month5,
MAX(@Month5) AS M5,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month6 THEN 1 END) AS Month6,
MAX(@Month6) AS M6,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month7 THEN 1 END) AS Month7,
MAX(@Month7) AS M7,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month8 THEN 1 END) AS Month8,
MAX(@Month8) AS M8,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month9 THEN 1 END) AS Month9,
MAX(@Month9) AS M9,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month10 THEN 1 END) AS Month10,
MAX(@Month10) AS M10,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month11 THEN 1 END) AS Month11,
MAX(@Month11) AS M11,
COUNT(CASE WHEN DatePart(mm, i.ReportedDate ) = @Month12 THEN 1 END) AS Month12,
MAX(@Month12) AS M12

FROM Incidents i
WHERE 1 = 1
AND i.ReportedDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -11, 0) AND 
	DATEADD(mm, DATEDIFF(mm, 0, GetDate()) +1, 0)
AND i.CompletionDate IS NULL

The resulting out put will give you 24 columns. The Month# columns will hold your monthly counts. The M# columns provide the month number for each columns.

Use the M# columns to create label formulas for each of the Month# columns.


MonthName({Command.M1})

This will give you dynamic column labels for your report.

HTH,

Jason

Former Member
0 Kudos

Nathan

daccepted, ReportedDate and dcleared are all fields from my database, you will need to change to fields and tables relevant to your database.

Ian

Answers (0)