CODEWITHSUNDEEP
crystal-reports
user2694655
user2694655

Reputation: 13

Counting open tickets for each day for a given period - Crystal Reports 11.0.0.895

I am tasked with creating a report which counts open service tickets each day for the last 365 days. A ticket is classified as open for any given day if there is no completed date, or the completed date is greater than the day being evaluated and the created date is less than or equal to the day being evaluated. My problem is that I have no date field to group on in the report as I cannot group on the created or resolved date fields given that I am counting open tickets and potentially no ticket was created or completed on a given day.

I have tried creating a command which lists everyday and then adding it to the report with no join which of course creates a Cartesian product but this blows the report up and takes way to long to run. How can I create a group for each day for a given period (in this case the last 365 days) in which I can place my formula to count open tickets?

Is there a solution to this problem, any help greatly appreciated. I am using Crystal reports version 11.0.0.895

Upvotes: 0

Views: 258

Answers (1)

dd4711
dd4711

Reputation: 799

This is a task you probably best solve in the database (or a query/command in Crystal Reports). As a sketch:

  1. Have a row for each day you want to look at (maybe use a date table)
  2. For each day from that table select the number (count) of tickets that match your criteria

This way you will have a resultset that always contains 365 rows, so there will be no problems in your report. Some of the rows may contain 0 if there are no tickets for that given day.

Below is an example. It uses a datetable. It shows two ways to get your ticket information to a given date:

  • a subselect: this provides the count of open tickets per date, no more no less
  • the tickets joined to the date. Using this way all ticket information can be/is available in your resultset. Counting and grouping may be done in SQL or in your report (group open tickets by severity, assigned user, ...)

Have a look at the where/join conditions (opened <= adate and (closed is null or closed >= adate)) whether they represent exactly what you want (should a ticket closed on date be counted as open for that day, will a ticket open and closed on the same date ever count as open, ....

create table #dates (adate date);
insert into #dates (adate) values 
('2017-06-23'),
('2017-06-22'),
('2017-06-21'),
('2017-06-20'),
('2017-06-19')


create table #tickets (id int, opened date, closed date);
insert into #tickets (id, opened, closed) values
(1, '20170620', null),
(2, '20170620', '20170622'),
(3, '20170621', '20170622'),
(4, '20170624', null)

-- just the open tickets per day
select 
    adate 
    ,(select count(id) from #tickets where opened <= adate and (closed is null or closed >= adate)) open_tickets
from #dates
left outer join #tickets on opened <= adate and (closed is null or closed >= adate)

-- tickets joined to date, all ticket information available
select 
    adate 
    ,#tickets.*
from #dates
left outer join #tickets on opened <= adate and (closed is null or closed >= adate)

drop table #tickets
drop table #dates

Upvotes: 0

Related Questions