2

I have a fairly specific set of data I am trying to display.

I have a dataset of helpdesk ticket data. I would like to display a line chart that shows tickets opened and tickets closed by day, over the last 30 days. I've been able to display one of these just fine, by adding an expression to my Category group:

=Day(Fields!Created.Value)

The 'Created' line displays just fine, yet the 'Closed' line seems to jump around at random. It seems that I can't further group by day on the 'Closed' column; this breaks the chart even further. Here's an example image:

enter image description here

Please let me know if I haven't been clear in any way.

EDIT: I would like to be clear that I've explored the option of creating a new dataset for this chart. I've been able to group counts by day, but only for 'Created' or 'Closed', not both. For example: enter image description here

If there's a way to combine these into one SQL query, that would fix my problem.

*****SOLUTION UPDATE*****

enter image description here

Here's is the query that has given me the data I need. Thanks, mmarie!

WITH X AS (SELECT      TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number) - 1
FROM          [master].dbo.spt_values AS m1 CROSS JOIN
                        [master].dbo.spt_values AS m2), last30days AS
    (SELECT      dt = cast(DATEADD(DAY, (- 1 * n), getdate()) AS date)
      FROM           X)
    SELECT      dt.dt, CASE WHEN tcr.CreatedCount IS NULL THEN 0 ELSE tcr.CreatedCount END AS 'CreatedCount', CASE WHEN tcl.ClosedCount IS NULL 
                             THEN 0 ELSE tcl.ClosedCount END AS 'ClosedCount'
     FROM           last30days dt LEFT JOIN
                                 (SELECT      COUNT(a.TicketNbr) AS CreatedCount, CAST(a.date_entered AS date) AS 'DayCreated'
                                   FROM          v_rpt_Service a
                                   WHERE       (closed_by LIKE @tech + '%')
                                   GROUP BY CAST(date_entered AS date)) tcr ON dt.dt = tcr.DayCreated LEFT JOIN
                                 (SELECT      COUNT(a.TicketNbr) AS ClosedCount, CAST(a.date_closed AS date) AS 'DayClosed'
                                   FROM           v_rpt_Service a
                                   WHERE       (closed_by LIKE @tech + '%')
                                   GROUP BY CAST(date_closed AS date)) tcl ON dt.dt = tcl.DayClosed
3
  • Are you trying to show tickets closed based upon the created date or the closed date? Can you post the fields that are returned in your query?
    – mmarie
    Feb 24, 2015 at 18:08
  • @mmarie I am trying to show the number of tickets that were opened on a given day, along with the number of tickets that were closed on a given day, over the last 30 days. Related fields I'm using: CreatedDate, ClosedDate, TicketID. I just need a simple count on a day-to-day basis for Created and Closed. Feb 24, 2015 at 18:21
  • Based on the line in your graph, it looks like you have it grouped by more than just day, or your calculation for closed count needs to be tweaked. I'll try to replicate it on my system if you haven't figured it out in a few hours.
    – mmarie
    Feb 24, 2015 at 19:17

1 Answer 1

3

Since I can't see your table structure and I don't know the size of your data, here's something that will return the right answer but may or may not need to be tweaked for performance. You can write a query that groups both created and closed tickets together by date. Although you could just write two queries and do a full outer join, I would guess you want SSRS to accurately represent days with no ticket activity in addition to filtering the data for the date range at the source instead of in the presentation layer, so I would suggest creating a date table/CTE/query. If you already have a date table, use it. If not, here is a way to create what you need for the last 30 days. You could also make it a stored procedure and parameterize it to choose the number of days.

WITH X AS 
(
  SELECT TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number)-1
  FROM [master].dbo.spt_values AS m1
  CROSS JOIN [master].dbo.spt_values AS m2
) 
,last30days as (
SELECT dt = cast(DATEADD(DAY, (-1*n), getdate()) as date)
  FROM X 
)
select dt.dt, tcr.ticketscreated, tcl.ticketsclosed
from last30days dt
  left join (select cast(createddate as date) createdate, count(ticketid) ticketscreated
            from dbo.tickets
            group by cast(createddate as date)) tcr on dt.dt = tcr.createdate
  left join (select cast(closeddate as date) closedate, count(ticketid) ticketsclosed
            from dbo.tickets
            where closeddate is not null
            group by cast(closeddate as date) ) tcl on dt.dt = tcl.closedate

Once you have added that query as a dataset, you can build your chart.

  • Values should be Sum(ticketscreated) and Sum(ticketsclosed)
  • Category Groups should be dt

enter image description here

3
  • 1
    Thanks for the response! I'm going to try to implement this and I'll let you know what I can make from it. To be honest, I'd be a little embarrassed to post the query getting my ticket data. It's a monstrosity of a franken-query. Feb 25, 2015 at 14:17
  • Added information to OP to help explain data. I'm having an issue refactoring your query to fit my fields. Feb 25, 2015 at 14:48
  • Figured it out, based on your query. About to post explanation in OP. It's a monster. Feb 25, 2015 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.