SSRS: Line Chart, Grouping by Day

Question

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:

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:

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

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

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

Answer 1

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