New to reporting services, need help doing a report that shows PC logins by the Hour

Question

Post reply

New to reporting services, need help doing a report that shows PC logins by the Hour

rkelly58

SSCrazy

Points: 2151
More actions
September 9, 2015 at 3:28 pm

#321131

Hi all,
I'm new to reporting services and i'd appreciate some help with this Reporting Services report.
Data on PC logins and logouts are captured in an SQL Table. The login date and time is in 1 field, as is other fields like IP address, Hostname etc.
I've been tasked with creating a couple of reports that allow the user to select a Start_Date and End_Date, then choose a Room, and then display a report that shows the Total number of logins by each HOUR (24 hours) in each Room.
i.e. Room A101
Time Number of PC logins
00:00 20
01:00 15
...... ......
23:00 1
Can anyone please help me with a step by step on how to achieve this?
As a new user of reporting services, I've learn how to do a report by using the filter between the dates, but I have no idea how to get the times displays as mentioned above.
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63594 More actions · Answer 1

So you need to use DATEPART() to get the time the user logged in:

SELECT LoginDate

, DATEPART(hour,LoginDate) AS LoginHour

FROM UserLogins;

Then it's just a count of Logins per LoginHour, and you're home free. If you need to show all hours, whether they have logins or not, you need to do something a bit more complicated...

SELECT x.LoginHour

, COUNT(*) As Frequency

FROM

(SELECT 0 As TheHour UNION ALL

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12 UNION ALL

SELECT 13 UNION ALL

SELECT 14 UNION ALL

SELECT 15 UNION ALL

SELECT 16 UNION ALL

SELECT 17 UNION ALL

SELECT 18 UNION ALL

SELECT 19 UNION ALL

SELECT 20 UNION ALL

SELECT 21 UNION ALL

SELECT 22 UNION ALL

SELECT 23) t

LEFT JOIN

(SELECT LoginDate

, DATEPART(hour,LoginDate) AS LoginHour

, LoginID

FROM UserLogins) x ON t.TheHour = x.LoginHour

GROUP BY x.LoginHour

ORDER BY x.LoginHour;

Of course, if you can't do this in T-SQL, you'll have to create calculated columns in your dataset and go that route. (Except you really need the LEFT JOIN to force all the time periods to exist in your dataset).

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 2

Regarding the parameters:

Tutorial: Adding Parameters to a Report (SSRS)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rkelly58 SSCrazy Points: 2151 More actions · Answer 3

Hi,

Thank you for your help, really appreciated.

I copied the top part into a Query and that worked fine to show the Loginhour in a separate column and changed the Select LogonDate to my field name and changed the from to my Table Name. I left everything else as per your code

I then added your other code underneath (in the same query) and it doesn't work. Do I add this elsewehere? It complains about the LoginID not found and "cannot be represented graphically"

Really sorry, but I've super new to this, your help is really really appreciated.

Thanks,

RK