mercredi 25 février 2015

SSRS: Line Chart, Grouping by Day


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.


EDIT: Attempted usage of suggested query. 'Created' and 'Closed' are both Datetime data.



Operand type clash: date is incompatible with int

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 Created, count(TicketNumber) as TicketsCreated
from kasadmin.vSDTicket
group by Created) tcr on dt.dt = tcr.TicketsCreated
left join (select Closed, count(TicketNumber) as TicketsClosed
from kasadmin.vSDTicket
where Closed is not null
group by Closed ) tcl on dt.dt = tcl.TicketsClosed




Aucun commentaire:

Enregistrer un commentaire