July 15, 2010 at 8:08 pm
Hi all,
I am new to these reporting tools so please forgive my ignorance. I have already made a heap of simple reports with quite a lot of ease but now that users are asking for more and more they seem to be getting more complicated and I am making up for my lack of knowledge in SRSS with my sql query knowledge.
I current have a report that runs against our helpdesk software, I needed it to count all jobs opened between two dates and list in a table "Jobs" "Jobs Outside SLA" against "Location"
I have managed to do this. But I was wanting a small info box up the top with the following details.
Total number of jobs logged: XXX
Number of jobs closed within 3 days: XXX
Number of jobs closed within 3 - 14 days: XXX
Number of jobs closed after 14 days: XXX
The first two I have managed to do with the following query:
SELECT COUNT(p.id) AS jobcount, l.locname, SUM(CASE WHEN DATEDIFF([day], p.start_date, CASE WHEN p.close_date IS NULL THEN getdate() ELSE p.close_date END)
> 3 THEN 1 ELSE 0 END) AS sla
FROM dbo.tblUsers AS t INNER JOIN
dbo.problems AS p LEFT OUTER JOIN
dbo.locations AS l ON l.location_id = p.ulocation RIGHT OUTER JOIN
dbo.categories AS c ON p.category = c.category_id ON t.sid = p.rep
WHERE (p.start_date >= @Param7) AND (p.start_date <= @Param8)
GROUP BY l.locname
ORDER BY l.locname
But the other two elude me... I really don't want to complicate the query more and was wondering if there was some way in SRSS to do this... And FYI I am really sucky at exceptions inside SRSS
Thanks a heap...
Caz
July 15, 2010 at 9:05 pm
I have a sideways solution to this.
I just made a heap of datasets, one for each piece of data I wanted to represent and then edited this query slightly each time:
SELECT COUNT(id) AS jobcount, SUM(CASE WHEN DATEDIFF([day], p.start_date, CASE WHEN p.close_date IS NULL THEN getdate() ELSE p.close_date END)
>3 THEN 1 ELSE 0 END) AS sla_3
FROM dbo.problems AS p
WHERE (start_date >= @Param7) AND (start_date <= @Param8) AND (breakfix = 1)
Caz
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply