Tips for using SQL Reporting Server with UniversitySite

Changelog

2024-07-17 added new reporting views:

ReportView_Resources
ReportView_ResourceTags
ReportView_ResourceViews

Background

UniversitySite provides an optional fee-based service that allows you to access a copy of your database synced twice a day so that you can run your own custom SQL reports and queries against it.

If you haven't purchased this service, contact Lawren Finley

Forgot your SQL connection login information? Please contact Daniel Wallace

Important Connection Notes

You can set up a connection from SQL Server Management Studio using the login credentials supplied below.

Servername: rpt.universitysite.com

Please be sure your SQL server can communicate outbound over port 1433 in order to connect and that you encrypt the connection as per the article below.

Everyone using the reporting server should connect with an encrypted session. When connecting with the SQL Server Management Studio, click the “Connection Properties” tab and check the “Encrypt connection” checkbox.

Backup Schedule

Your production data will be transferred to the SQL Reporting server daily as follows:

We back up your production server and push this data to your reporting server SQL database that we host twice daily between 3-6 AM and 1-3 PM EST. During this time, the reporting database will be offline for a short period, and any changes made to the database by reporters will be deleted as new data is pushed.

Last Update Timestamp

You can display the last update timestamp on all your reports using the SQL below.

For example: Last updated {{ backup_finish_date }}

SELECT Max([bs].[backup_finish_date]) AS 'LastUpdated (EST)'
FROM   msdb..restorehistory rs
       INNER JOIN msdb..backupset bs
               ON [rs].[backup_set_id] = [bs].[backup_set_id]
WHERE  [rs].[destination_database_name] = Db_name()

Reporting Views

Use the views we maintain specifically for reporting purposes instead of directly accessing the database tables.

SELECT * FROM   [dbo].[@ReportView_LearningActivity]

SELECT * FROM   [dbo].[@ReportView_TrainingApprovals]

SELECT * FROM   [dbo].[@ReportView_Users]

WARNING: Some database elements are off-limits.

In some cases, your reporting server may provide access to views and tables that are intended for UniversitySite's internal use. Views prefixed with "@ReportView_" are designed for your use and maintained for backward compatibility. Any other views or tables are intended for UniversitySite's internal use and are subject to a continuous flow of changes that are not backwards compatible. Using "off-limits" views or tables will make your reports fragile and will inevitably lead to errors as weekly changes are made to UniversitySite. Please note, updates are underway to prevent access to "off-limits" views and tables.

The SQL Reporting Server extension includes a view for accessing SEIM logs using the following SQL statement:

SELECT * FROM [dbo].[@ReportView_SecurityLogs]

About SQL Reporting

Firewall Considerations

If your company's firewall blocks outgoing TCP connections over port 1433, you will be unable to connect to the reporting server.

Sample Queries

-- list courses expiring in the next 30 days
SELECT *
FROM   [dbo].[@ReportView_Courses]
WHERE  ExpirationDate > Getdate()
       AND ExpirationDate < Dateadd(day, 30, Getdate())

-- list expired courses and courses expiring in the next 30 days
SELECT *
FROM   [dbo].[@ReportView_Courses]
WHERE  ExpirationDate < Dateadd(day, 30, Getdate())

-- list activity for instructor-led training
SELECT *
FROM   [@ReportView_LearningActivity]
WHERE  CalendarEventID IS NOT NULL

-- list activity for on-demand learning
SELECT *
FROM   [@ReportView_LearningActivity]
WHERE  CalendarEventID IS NULL

-- list activity for departed users
SELECT *
FROM   [@ReportView_LearningActivity]
WHERE  LearnerIsDisabled = 1

-- list activity for active users
SELECT *
FROM   [@ReportView_LearningActivity]
WHERE  LearnerIsDisabled = 0

-- Course status for all courses and all users
SELECT *
FROM   [@ReportView_LearningActivity]

SELECT LearnerName,
       Course,
       Date,
       Status
FROM   [@ReportView_LearningActivity]

-- Courses completed successfully for all users
SELECT *
FROM   [@ReportView_LearningActivity]

SELECT LearnerName,
       Course,
       Date,
       Status
FROM   [@ReportView_LearningActivity]
WHERE  Status = 'Completed Successfully'

-- eLearning Course completions for a specific course
SELECT Prefix,
       Number,
       CompletionDate,
       NetworkAlias,
       u.Email
FROM   [@ReportView_OnDemandCourseCompletions] c
       INNER JOIN [@ReportView_Users] u
               ON u.ID = c.UserID
WHERE  CourseID = 5054

-- Total classes by week, month, year
SELECT Datepart(week, StartTime) AS Week,
       Count(EventID)            AS TotalEvents2022
FROM   [dbo].[@ReportView_Events]
WHERE  StartTime >= '20220101'
       AND StartTime < '20230101'
       AND ParentEventID IS NULL
GROUP  BY Datepart(week, StartTime)
ORDER  BY Datepart(week, StartTime)

SELECT Datename(month, StartTime) AS Month,
       Count(EventID)             AS TotalEvents2022
FROM   [dbo].[@ReportView_Events]
WHERE  StartTime >= '20220101'
       AND StartTime < '20230101'
       AND ParentEventID IS NULL
GROUP  BY Datename(month, StartTime),
          Datepart(m, StartTime)
ORDER  BY Datepart(m, StartTime)

SELECT Datename(yyyy, StartTime) AS Year,
       Count(EventID)            AS TotalEvents
FROM   [dbo].[@ReportView_Events]
WHERE  ParentEventID IS NULL
GROUP  BY Datename(yyyy, StartTime)
ORDER  BY Datename(yyyy, StartTime)

-- New Hire classes, assuming "New Hire" is in the title
SELECT Datepart(week, StartTime) AS Week,
       Count(EventID)            AS TotalEvents2022
FROM   [dbo].[@ReportView_Events]
WHERE  StartTime >= '20220101'
       AND StartTime < '20230101'
       AND ParentEventID IS NULL
       AND Title LIKE '%New Hire%'
GROUP  BY Datepart(week, StartTime)
ORDER  BY Datepart(week, StartTime)

SELECT Datename(month, StartTime) AS Month,
       Count(EventID)             AS TotalEvents2022
FROM   [dbo].[@ReportView_Events]
WHERE  StartTime >= '20220101'
       AND StartTime < '20230101'
       AND ParentEventID IS NULL
       AND Title LIKE '%New Hire%'
GROUP  BY Datename(month, StartTime),
          Datepart(m, StartTime)
ORDER  BY Datepart(m, StartTime)

SELECT Datename(yyyy, StartTime) AS Year,
       Count(EventID)            AS TotalEvents
FROM   [dbo].[@ReportView_Events]
WHERE  ParentEventID IS NULL
       AND Title LIKE '%New Hire%'
GROUP  BY Datename(yyyy, StartTime)
ORDER  BY Datename(yyyy, StartTime)

-- Classes per instructor
SELECT u.FirstName + ' ' + u.LastName AS Instructor,
       Count(EventID)                 AS TotalEvents2022
FROM   [dbo].[@ReportView_Events] e
       INNER JOIN [dbo].[CalendarEvents] ce
               ON ce.ID = e.EventID
       INNER JOIN [dbo].[@ReportView_Users] u
               ON u.ID = ce.EventCoordinatorUserID
WHERE  e.StartTime >= '20220101'
       AND e.StartTime < '20230101'
       AND ParentEventID IS NULL
GROUP  BY u.FirstName,
          u.LastName
ORDER  BY u.FirstName,
          u.LastName

-- How many people trained (event attendees only; does not include eLearning)
SELECT Datepart(week, CompletionDate) AS Week,
       Count(RegistrantID)            AS TotalAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants]
WHERE  CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY Datepart(week, CompletionDate)
ORDER  BY Datepart(week, CompletionDate)

SELECT Datename(month, CompletionDate) AS Month,
       Count(RegistrantID)             AS TotalAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants]
WHERE  CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY Datename(month, CompletionDate),
          Datepart(m, CompletionDate)
ORDER  BY Datepart(m, CompletionDate)

SELECT Datepart(yyyy, CompletionDate) AS Year,
       Count(RegistrantID)            AS TotalAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants]
WHERE  RegisteredOrAttendended = 'Attended'
GROUP  BY Datepart(yyyy, CompletionDate)
ORDER  BY Datepart(yyyy, CompletionDate)

-- How many attorneys trained (event attendees only; does not include eLearning)
SELECT Datepart(week, CompletionDate) AS Week,
       Count(RegistrantID)            AS TotalAttorneyAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants] r
       INNER JOIN [dbo].[@ReportView_UserAudiences] ua
               ON ua.UserID = r.UserID
WHERE  ua.Audience = 'All Attorneys'
       AND CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY Datepart(week, CompletionDate)
ORDER  BY Datepart(week, CompletionDate)

SELECT Datename(month, CompletionDate) AS Month,
       Count(RegistrantID)             AS TotalAttorneyAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants] r
       INNER JOIN [dbo].[@ReportView_UserAudiences] ua
               ON ua.UserID = r.UserID
WHERE  ua.Audience = 'All Attorneys'
       AND CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY Datename(month, CompletionDate),
          Datepart(m, CompletionDate)
ORDER  BY Datepart(m, CompletionDate)

SELECT Datepart(yyyy, CompletionDate) AS Year,
       Count(RegistrantID)            AS TotalAttorneyAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants] r
       INNER JOIN [dbo].[@ReportView_UserAudiences] ua
               ON ua.UserID = r.UserID
WHERE  ua.Audience = 'All Attorneys'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY Datepart(yyyy, CompletionDate)
ORDER  BY Datepart(yyyy, CompletionDate)

-- People trained per instructor
SELECT u.FirstName + ' ' + u.LastName AS Instructor,
       Count(RegistrantID)            AS TotalTrained2022
FROM   [dbo].[@ReportView_Events] e
       INNER JOIN [dbo].[CalendarEvents] ce
               ON ce.ID = e.EventID
       INNER JOIN [dbo].[@ReportView_Users] u
               ON u.ID = ce.EventCoordinatorUserID
       INNER JOIN [dbo].[@ReportView_EventRegistrants] r
               ON r.EventID = e.EventID
WHERE  e.StartTime >= '20220101'
       AND e.StartTime < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY u.FirstName,
          u.LastName
ORDER  BY u.FirstName,
          u.LastName

-- People trained by location
SELECT u.OfficeLocation,
       Count(RegistrantID) AS TotalAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants] r
       INNER JOIN [dbo].[@ReportView_Users] u
               ON u.ID = r.UserID
WHERE  CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY u.OfficeLocation
ORDER  BY u.OfficeLocation

-- Attorneys trained by location
SELECT u.OfficeLocation,
       Count(RegistrantID) AS TotalAttorneyAttendees2022
FROM   [dbo].[@ReportView_EventRegistrants] r
       INNER JOIN [dbo].[@ReportView_Users] u
               ON u.ID = r.UserID
       INNER JOIN [dbo].[@ReportView_UserAudiences] ua
               ON ua.UserID = r.UserID
WHERE  ua.Audience = 'All Attorneys'
       AND CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY u.OfficeLocation
ORDER  BY u.OfficeLocation

-- How many by class title
SELECT e.Prefix + '-'
       + CONVERT(VARCHAR(200), e.Number) + ': '
       + e.Title           AS Title,
       Count(RegistrantID) AS TotalAttendees2022
FROM   [dbo].[@ReportView_Events] e
       INNER JOIN [dbo].[@ReportView_EventRegistrants] r
               ON r.EventID = e.EventID
WHERE  CompletionDate >= '20220101'
       AND CompletionDate < '20230101'
       AND RegisteredOrAttendended = 'Attended'
GROUP  BY e.Prefix,
          e.Number,
          e.Title
ORDER  BY e.Prefix,
          e.Number,
          e.Title