Aeon Default SSRS Reports

Follow
AtlasBI was launched and implemented over the last year replacing Atlas Systems’ hosted SQL Server Reporting Services (SSRS) for Aeon and Ares. Atlas hosted SSRS will be decommissioned on 8/31/2019.

Self-hosted customers can take advantage of AtlasBI by allowing firewall/access to AtlasBI IP addresses and providing a read-only SQL account that can query your database.

All Aeon sites using SSRS are given some basic reports to work with as part of their SSRS installation. This set of reports, described below, can be run against all Aeon servers regardless of whether they are single or shared or have reading rooms configured. You can create your own reports in the Report Builder if you like, and Atlas Systems can create custom reports as needed.

The following reports are stored in a .zip file available at Aeon 4.0 Downloads. Please contact your customer service agent at service@atlas-sys.com if you have questions and/or to request report installation.

You may need to add atlas-sys.com to the compatibility settings for your IE browser for best performance. In the browser, go to Settings > Compatibility View Settings, there is an option to add websites to a list in Compatibility View. Add atlas-sys.comIn some versions of Internet Explorer, Compatibility Settings may be under Tools.

User Reports

Characteristics of All Users

This reports the number and characteristics of all users who visited (were signed in) during a given period.

  • Begin and end date parameters (default to previous fiscal year)
  • Reading Room parameter and grouping option for institutions with multiple reading rooms defined
  • User grouping options - Status, Department, Organization, AuthType (affiliation), State and Country
Characteristics of All Users Query
exec sp_executesql N'SELECT 

  Users.UserName AS [Users UserName]

  ,Users.Status

  ,Users.Department

  ,Users.Organization

  ,Users.AuthType

  ,Users.[State]

  ,Users.Country

  ,Users.Cleared

  ,ReadingRoomHistory.ID AS [ReadingRoomHistory ID]

  ,ReadingRoomHistory.Username AS [ReadingRoomHistory Username]

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeIn) AS TimeIn

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeOut) AS TimeOut

  ,ISNULL(ReadingRoomHistory.ReadingRoom, ''N/A'') AS ReadingRoom

  ,ISNULL(ReadingRoomHistory.Location, ''N/A'') AS Location

  ,ReadingRoomHistory.SignedInBy

  ,ReadingRoomHistory.SignedOutBy

  ,ISNULL(CustomDropDown.LabelName, ''N/A'') AS ReadingRoomName

FROM

  Users

  LEFT OUTER JOIN ReadingRoomHistory

    ON Users.UserName = ReadingRoomHistory.Username

  LEFT OUTER JOIN CustomDropDown

    ON ReadingRoomHistory.ReadingRoom = CustomDropDown.LabelValue AND CustomDropDown.GroupName = ''ReadingRoom''

WHERE 

(''N/A'' IN (N''ReadingRoom'') OR ReadingRoomHistory.ReadingRoom IN (N''ReadingRoom''))

AND ReadingRoomHistory.TimeIn BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))',N'@BeginDate datetime,@EndDate datetime',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'


 

Characteristics of New Users

This reports the number and characteristics of new (i.e. first-time) users who visited during a given period (based on the first reading room sign-in for a user).

  • Begin and end date parameters (default to previous fiscal year)
  • Reading Room parameter and grouping option for institutions with multiple reading rooms defined
  • User grouping options - Status, Department, Organization, AuthType (affiliation), State and Country
Characteristics of New Users Query
exec sp_executesql N'SELECT

  Users.UserName

  ,Users.LastName

  ,Users.FirstName

  ,Users.DateOfBirth

  ,Users.ID

  ,Users.IDType

  ,Users.AltID

  ,Users.AltIDType

  ,Users.Status

  ,Users.Department

  ,Users.Organization

  ,Users.EMailAddress

  ,Users.Phone

  ,Users.Fax

  ,Users.Password

  ,Users.PasswordHint

  ,Users.AuthType

  ,Users.RegistrationStatus

  ,Users.NotificationMethod

  ,Users.DeliveryMethod

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Users.LastChangedDate) AS LastChangedDate

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Users.ExpirationDate) AS ExpirationDate

  ,Users.Address

  ,Users.Address2

  ,Users.City

  ,Users.[State]

  ,Users.Zip

  ,Users.Country

  ,Users.BillingCategory

  ,Users.RSSID

  ,Users.SAddress

  ,Users.SAddress2

  ,Users.SCity

  ,Users.SState

  ,Users.SZip

  ,Users.SCountry

  ,Users.Cleared

  ,Users.RequestLimit

  ,Users.ResearchTopics

  ,Users.ResearchTopicsSharing

  ,Users.ImageID

  ,Users.UserInfo1

  ,Users.UserInfo2

  ,Users.UserInfo3

  ,Users.UserInfo4

  ,Users.UserInfo5

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Users.CreationDate) AS CreationDate

  ,ReadingRoomHistory.ReadingRoom

  ,ISNULL(CustomDropDown.LabelName, ''N/A'') AS ReadingRoomName

  ,MIN(ReadingRoomHistory.TimeIn) AS VisitDate

FROM

  ReadingRoomHistory

  INNER JOIN Users

    ON ReadingRoomHistory.Username = Users.UserName

  LEFT OUTER JOIN CustomDropDown

    ON ReadingRoomHistory.ReadingRoom = CustomDropDown.LabelValue

GROUP BY

  Users.UserName

  ,Users.LastName

  ,Users.FirstName

  ,Users.DateOfBirth

  ,Users.ID

  ,Users.IDType

  ,Users.AltID

  ,Users.AltIDType

  ,Users.Status

  ,Users.Department

  ,Users.Organization

  ,Users.EMailAddress

  ,Users.Phone

  ,Users.Fax

  ,Users.Password

  ,Users.PasswordHint

  ,Users.AuthType

  ,Users.RegistrationStatus

  ,Users.NotificationMethod

  ,Users.DeliveryMethod

  ,Users.LastChangedDate

  ,Users.ExpirationDate

  ,Users.Address

  ,Users.Address2

  ,Users.City

  ,Users.[State]

  ,Users.Zip

  ,Users.Country

  ,Users.BillingCategory

  ,Users.RSSID

  ,Users.SAddress

  ,Users.SAddress2

  ,Users.SCity

  ,Users.SState

  ,Users.SZip

  ,Users.SCountry

  ,Users.Cleared

  ,Users.RequestLimit

  ,Users.ResearchTopics

  ,Users.ResearchTopicsSharing

  ,Users.ImageID

  ,Users.UserInfo1

  ,Users.UserInfo2

  ,Users.UserInfo3

  ,Users.UserInfo4

  ,Users.UserInfo5

  ,Users.CreationDate

  ,ReadingRoomHistory.ReadingRoom

  ,CustomDropDown.LabelName

HAVING MIN(ReadingRoomHistory.TimeIn) BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate)) 

AND (''N/A'' IN (N''ReadingRoom'') OR ReadingRoomHistory.ReadingRoom IN (N'ReadingRoom''))',N'@BeginDate datetime,@EndDate datetime',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'


Visit Reports

Visits Per Day

This reports the total number of visits per day and counts the number of distinct users signed in by day.

  • Excluding duplicate visits by the same patron to the same reading room on the same day
  • Begin and end date parameters (default to previous fiscal year)
  • Reading Room parameter and grouping option for institutions with multiple reading rooms defined
  • Date grouping options - week, month, quarter, year
  • User grouping options - Status, Department, AuthType (affiliation)
Visits Per Day Query
exec sp_executesql N'SELECT DISTINCT

  Convert(date, dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeIn)) AS VisitDate

  ,ReadingRoomHistory.ReadingRoom

  ,Users.UserName AS [Users UserName]

  ,Users.Status

  ,Users.Department

  ,Users.Organization

  ,Users.AuthType

  ,Users.Cleared 

  ,ISNULL(CustomDropDown.LabelName, ''N/A'') AS ReadingRoomName

FROM

  Users

  INNER JOIN ReadingRoomHistory

    ON Users.UserName = ReadingRoomHistory.Username

LEFT JOIN CustomDropDown ON ReadingRoomHistory.ReadingRoom = CustomDropDown.LabelValue

WHERE

  (''N/A'' IN (N''ReadingRoom'') OR ReadingRoomHistory.ReadingRoom IN (N''ReadingRoom''))

AND ReadingRoomHistory.TimeIn BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate)) AND (CustomDropDown.GroupName = ''ReadingRoom'' OR CustomDropDown.GroupName IS NULL)',N'@BeginDate datetime,@EndDate datetime',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'

Visitors by Day

This reports who visited on a given day and displays the names of users signed in on a given day.

  • Displaying all seating locations for each user for each day if seating locations are defined
  • Begin and end date parameters (default to previous seven days) or single date parameter option
  • Reading Room parameter for institutions with multiple reading rooms defined
Visitors by Day Query
exec sp_executesql N'SELECT

  ReadingRoomHistory.ID AS [ReadingRoomHistory ID]

  ,ReadingRoomHistory.Username AS [ReadingRoomHistory Username]

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeIn) AS TimeIn

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeOut) AS TimeOut

  ,ISNULL(ReadingRoomHistory.ReadingRoom, ''N/A'') AS ReadingRoom

  ,ReadingRoomHistory.Location

  ,Users.UserName AS [Users UserName]

  ,Users.LastName

  ,Users.FirstName

  ,CustomDropDown.GroupName

  ,CustomDropDown.LabelName

  ,CustomDropDown.LabelValue

FROM

  Users

  INNER JOIN ReadingRoomHistory

    ON Users.UserName = ReadingRoomHistory.Username

  LEFT JOIN CustomDropDown

    ON ReadingRoomHistory.ReadingRoom = CustomDropDown.LabelValue

WHERE

  (''N/A'' IN (N''ReadingRoom'') OR ReadingRoomHistory.ReadingRoom IN (N''ReadingRoom''))

          AND (ReadingRoomHistory.TimeIn BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))

          OR convert(date, dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeIn), 1) = @Date)',N'@Date nvarchar(4000),@BeginDate datetime,@EndDate datetime',@Date=NULL,@BeginDate='2017-11-22 00:00:00',@EndDate='2017-11-29 00:00:00'




exec sp_executesql N'SELECT

  ReadingRoomHistory.ID 

  ,ReadingRoomHistory.Username

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeIn) AS TimeIn

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeOut) AS TimeOut

  ,ISNULL(ReadingRoomHistory.ReadingRoom, ''N/A'') AS ReadingRoom

  ,ReadingRoomHistory.Location

FROM

  ReadingRoomHistory

WHERE

  (''N/A'' IN (N''ReadingRoom'') OR ReadingRoomHistory.ReadingRoom IN (N''ReadingRoom''))

  AND (ReadingRoomHistory.TimeIn BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))

  OR convert(varchar, dbo.ufn_Aeon_ConvertUTCDateTimeToLocal(ReadingRoomHistory.TimeIn), 1) = @Date)',N'@Date nvarchar(4000),@BeginDate datetime,@EndDate datetime',@Date=NULL,@BeginDate='2017-11-22 00:00:00',@EndDate='2017-11-29 00:00:00'

Visit Length

This reports the total numbers of hours each patron was signed in each day, the total number of hours of all patron visits to the reading room, and length of average visit per day.

  • Total number of hours and the average number of hours per patron per grouping option
  • Begin and end date parameters (default to previous fiscal year)
  • Reading Room parameter and grouping option for institutions with multiple reading rooms defined
  • Date grouping options - week, month, quarter, year
  • User grouping options - Status, Department, AuthType (affiliation)
Visit Length Query
exec sp_executesql N'SELECT
 ReadingRoomHistory.ID AS [ReadingRoomHistory ID]
 ,ReadingRoomHistory.Username AS [ReadingRoomHistory Username]
 ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeIn) AS TimeIn
 ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (ReadingRoomHistory.TimeOut) AS TimeOut
 ,ReadingRoomHistory.ReadingRoom
 ,ReadingRoomHistory.Location
  ,Users.UserName AS [Users UserName]
  ,Users.Status
  ,Users.Department
  ,Users.Organization
  ,Users.AuthType
  ,Users.Cleared
 ,ISNULL(CustomDropDown.LabelName, ''N/A'') AS ReadingRoomName
 ,CustomDropDown.LabelValue
FROM
  Users
  INNER JOIN ReadingRoomHistory
    ON Users.UserName = ReadingRoomHistory.Username
  LEFT JOIN CustomDropDown
    ON ReadingRoomHistory.ReadingRoom = CustomDropDown.LabelValue
WHERE
  (''N/A'' IN (N''ReadingRoom'') OR ReadingRoomHistory.ReadingRoom IN (N''ReadingRoom''))
AND ReadingRoomHistory.TimeIn BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))',N'@BeginDate datetime,@EndDate datetime',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'

Visitors by Time

This reports the average number of visitors signed into a reading room at a particular time of day over the time period selected.

  • Begin and end date parameters (default to previous fiscal year)
  • Reading Room parameter and grouping option for institutions with multiple reading rooms defined
  • Days of the Week and Hours parameter options allowing staff to analyze reading room use patterns on specific days and during specific hours
Visitors by Time Query
exec sp_executesql N'IF NOT (@BeginDate <= @EndDate)

BEGIN

	RAISERROR(N''Start Date must precede End Date'', 11, 1);

END



DECLARE @DatesInRange TABLE([Date] DATETIME);

DECLARE @d DATETIME;

SET @d = @BeginDate;


WHILE @d <= @EndDate

BEGIN

	INSERT INTO @DatesInRange ([Date]) VALUES (@d);

	SET @d = @d + 1;

END



DECLARE @Days INT;

SELECT @Days = Count([Date]) FROM @DatesInRange WHERE (DATEPART(dw, [Date]) IN (2,3,4,5,6));



WITH 

[Hours] ([Hour]) AS

(

	SELECT [Hours].[Hour] 

	FROM (VALUES

			(0), (1), (2), (3), (4), (5),

			(6), (7), (8), (9), (10), (11),

			(12), (13), (14), (15), (16), (17),

			(18), (19), (20), (21), (22), (23)

		 ) [Hours]([Hour])

                WHERE [Hours].[Hour] IN (8,9,10,11,12,13,14,15,16)

),

[ReadingRoomCodes] ([ReadingRoomName], [ReadingRoomCode]) AS

(

	SELECT [LabelName] AS [ReadingRoomName],

                             [LabelValue] AS [ReadingRoomCode]

	FROM [CustomDropDown] 

	WHERE [GroupName] = ''ReadingRoom'' 

                UNION SELECT ''N/A'', ''N/A''

),

[DataPoints] ([Hour], [ReadingRoomName], [ReadingRoomCode]) AS

(

	SELECT [Hours].[Hour], 

                             [ReadingRoomCodes].[ReadingRoomName],

                             [ReadingRoomCodes].[ReadingRoomCode]

	FROM [Hours] 

	CROSS JOIN [ReadingRoomCodes]

                WHERE [ReadingRoomCodes].[ReadingRoomCode] IN (N''BallRoom'',N''Ellison Room'',N''ReadingRoom'')            

),

[AbridgedReadingRoomHistory] ([HourIn], [HourOut], [ReadingRoomCode]) AS

(

	SELECT DATEPART(hh, dbo.ufn_Aeon_ConvertUTCDateTimeToLocal([TimeIn])) AS [HourIn],

		   DATEPART(hh, dbo.ufn_Aeon_ConvertUTCDateTimeToLocal([TimeOut])) AS [HourOut],

		   ISNULL([ReadingRoom], ''N/A'') AS [ReadingRoomCode]

	FROM [ReadingRoomHistory]

	WHERE [TimeIn] IS NOT NULL AND 

	      [TimeOut] IS NOT NULL AND                       dbo.ufn_Aeon_ConvertUTCDateTimeToLocal([TimeIn]) >= @BeginDate AND 

                      dbo.ufn_Aeon_ConvertUTCDateTimeToLocal([TimeOut]) < @EndDate + 1 AND

DATEPART(dw, dbo.ufn_Aeon_ConvertUTCDateTimeToLocal([TimeIn])) IN (2,3,4,5,6)

),

[UsageStats] ([Hour], [ReadingRoomName], [ReadingRoomCode], [UserCount]) AS

(

	SELECT [DataPoints].[Hour], 

		   [DataPoints].[ReadingRoomName], 

                                   [DataPoints].[ReadingRoomCode],

		   COUNT(*) AS [UserCount] 

	FROM [DataPoints] 

	INNER JOIN [AbridgedReadingRoomHistory]

	ON [DataPoints].[Hour] >= [AbridgedReadingRoomHistory].[HourIn] AND

	   [DataPoints].[Hour] <= [AbridgedReadingRoomHistory].[HourOut] AND

	   [DataPoints].[ReadingRoomCode] = [AbridgedReadingRoomHistory].[ReadingRoomCode]

	GROUP BY [DataPoints].[Hour], 

                                   [DataPoints].[ReadingRoomName],

                                   [DataPoints].[ReadingRoomCode]

)

SELECT [DataPoints].[Hour], 

	   [DataPoints].[ReadingRoomName], 

	   CONVERT(decimal, ISNULL([UsageStats].[UserCount], 0)) / @Days AS [UserCount] 

FROM [DataPoints] 

LEFT JOIN [UsageStats]

ON [DataPoints].[Hour] = [UsageStats].[Hour] AND

   [DataPoints].[ReadingRoomCode] = [UsageStats].[ReadingRoomCode]

ORDER BY [DataPoints].[Hour]',N'@BeginDate datetime,@EndDate datetime',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'

Transaction Reports

Items Checked Out by Day

This reports the total number of items checked out in the reading room by day.

  • Excluding Activity check outs
  • Begin and end date parameters (default to previous fiscal year)
  • Site parameter option for institutions with multiple sites defined
  • User status parameter option to exclude certain types of users (e.g. staff users)
  • The parameter to include or exclude duplicate check outs of the same transaction on the same day
  • Date grouping options - week, month, quarter, year
  • Transaction information grouping options - Document Type and Location fields
Items Checked Out by Day Query
exec sp_executesql N'SELECT

  Transactions.TransactionNumber AS [Transactions TransactionNumber]

  ,Transactions.Site

  ,Transactions.DocumentType

  ,Transactions.Location

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Tracking.ChangedDate) AS ChangedDate

  ,Users.Status

  ,Users.Cleared

  ,Accounts.ID AS BillingAccountID

  ,Accounts.Description AS BillingAccountDescription

FROM

  Transactions

  INNER JOIN Users

    ON Transactions.Username = Users.UserName

  INNER JOIN Tracking

    ON Tracking.TransactionNumber = Transactions.TransactionNumber

  INNER JOIN Queues

    ON Tracking.ChangedTo = Queues.ID

  LEFT JOIN TransactionAccountLinks 

    ON Transactions.TransactionNumber = TransactionAccountLinks.TransactionNumber

  LEFT JOIN Accounts

    ON TransactionAccountLinks.AccountID = Accounts.ID

WHERE

  Queues.StateCode = 55

AND Tracking.ChangedDate BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))

AND Users.Status IN (N''Faculty'',N''Graduate'',N''Independent Researcher'',N''Staff'',N''Undergraduate'')

AND (''N/A'' IN (@Site) OR Transactions.Site IN (@Site))',N'@BeginDate datetime,@EndDate datetime,@Site nvarchar(3)',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00',@Site=N'N/A'

Frequency of Circulation

This reports the total number of requests that resulted in checkouts for each item requested during a given time period (e.g. call number y was checked out 8 times in the past year).

  • Only counts unique transactions, not multiple checkouts for one transaction
  • Including Activity and non-Activity requests, but with the option to select only Activity or non-Activity requests
  • Begin and end date parameters (default to previous fiscal year)
  • User status parameter option to exclude certain types of users (e.g. staff users)
  • Parameter option to choose the field to use for grouping requests for frequency counts - Title, Author, Call Number, Location, Item Number
  • Parameter option to limit the report to items circulated x number of times or more
Frequency of Circulation Query
exec sp_executesql N'SELECT

  Transactions.TransactionNumber AS [Transactions TransactionNumber]

  ,Transactions.Site

  ,Transactions.TransactionStatus

  ,Transactions.ItemAuthor

  ,Transactions.ItemTitle

  ,Transactions.ItemNumber

  ,Transactions.ReferenceNumber

  ,Transactions.CallNumber

  ,Transactions.Location

  ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Tracking.ChangedDate) AS ChangedDate

  ,Users.Status

  ,Accounts.ID AS BillingAccountID

  ,Accounts.Description AS BillingAccountDescription

FROM

  Transactions

  INNER JOIN Users

    ON Transactions.Username = Users.UserName

  INNER JOIN Tracking

    ON Tracking.TransactionNumber = Transactions.TransactionNumber

  INNER JOIN Queues

    ON Tracking.ChangedTo = Queues.ID

  LEFT JOIN TransactionAccountLinks 

    ON Transactions.TransactionNumber = TransactionAccountLinks.TransactionNumber

  LEFT JOIN Accounts

    ON TransactionAccountLinks.AccountID = Accounts.ID

WHERE

  ((@TransactionType = ''All'' AND Queues.StateCode IN (55,60)) OR

   (@TransactionType = ''NonActivity'' AND Queues.StateCode = 55) OR

   (@TransactionType = ''Activity'' AND Queues.StateCode = 60))

AND Users.Status IN (N''Faculty'',N''Graduate'',N''Independent Researcher'',N''Staff'',N''Undergraduate'')

AND Tracking.ChangedDate BETWEEN dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate) AND dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate)) 

AND (''N/A'' IN (@Sites) OR Transactions.Site IN (@Sites))',N'@TransactionType nvarchar(3),@Sites nvarchar(3),@BeginDate datetime,@EndDate datetime',@TransactionType=N'All',@Sites=N'N/A',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'	

Activity Reports

Characteristics of All Activities

This reports the number and characteristics of all Activities that began during a given period

  • Includes average numbers of items requested for each Activity, excluding cancelled requests
  • Includes Activities without Requests
  • Includes total attendance numbers for each Activity
  • Begin and end date parameters (default to previous fiscal year)
  • Grouping options - Month, Activity Type, Location
Characteristics of All Activities Query
exec sp_executesql N'

          SELECT DISTINCT

          Activities.ID

          ,Activities.Name

          ,Activities.ActivityType

          ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Activities.BeginDate) AS BeginDate

          ,dbo.ufn_Aeon_ConvertUTCDateTimeToLocal (Activities.EndDate) AS EndDate

          ,Activities.Location AS [Activities Location]

          ,Activities.BillingCategory

          ,Transactions.TransactionNumber

          ,Transactions.ActivityID

          ,Transactions.TransactionStatus

          FROM

          Activities

          JOIN ActivityHistory

          ON Activities.ID = ActivityHistory.ActivityID

          LEFT OUTER JOIN Transactions

          ON Activities.ID = Transactions.ActivityID

          WHERE

          (Transactions.TransactionStatus IS NULL OR Transactions.TransactionStatus NOT IN (25, 26))

          AND ((Activities.BeginDate IS NOT NULL AND

          Activities.BeginDate > dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate)) OR

          (Activities.BeginDate IS NULL AND

          ActivityHistory.HistoryDate > dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (@BeginDate)))

          AND ((Activities.EndDate IS NOT NULL AND

          Activities.EndDate < dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))) OR

          (Activities.EndDate IS NULL AND

          ActivityHistory.HistoryDate < dbo.ufn_Aeon_ConvertLocalDateTimeToUTC (DATEADD(day, 1, @EndDate))))',N'@BeginDate datetime,@EndDate datetime',@BeginDate='2016-07-01 00:00:00',@EndDate='2017-06-30 00:00:00'


Questions?

If this article didn’t resolve your issue, please take a moment and answer a few questions to help improve our documentation:

Feedback