In my previous article: Average Session Count by Day and Hour: The Query, I dove into a query that shows the number of sessions on a farm at any given hour. Next, we go onto the creation of the report. For this I use SQL Server Business Intelligence Development Studio (BIDS). To begin, I created a new report to contain a matrix which runs the query that I created above. For this report to run in BIDS and EdgeSight, the entirety of the following should be placed in the query window:
DECLARE @UTCNow DATETIME
DECLARE @LocalStartDate SMALLDATETIME
DECLARE @LocalEndDate SMALLDATETIME
DECLARE @CompId INTEGER
DECLARE @Filter INTEGER
SET @CompID = 1
SET @Filter = 1
SET @UTCNow = GETUTCDATE()
SET @LocalStartDate = dbo.udf_core_get_offset_date(1, @Start, 1, 0, @UTCNow)
SET @LocalEndDate = dbo.udf_core_get_offset_date(1, @End, 0, 0, @UTCNow)
SELECT SUM(vw_ctrx_archive_system_perf.active_sessions_peak + vw_ctrx_archive_system_perf.inactive_sessions_peak) AS TotalSessions,
CONVERT(varchar(10), DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 111) AS Date, CONVERT(varchar(10),
DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 108) AS Time
FROM vw_ctrx_archive_system_perf INNER JOIN
company ON vw_ctrx_archive_system_perf.compid = company.compid INNER JOIN
timezone ON company.tzid = timezone.tzid
GROUP BY vw_ctrx_archive_system_perf.time_stamp, timezone.standard_bias
HAVING (DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp) >= @LocalStartDate) AND (DATEADD(mi, timezone.standard_bias,
vw_ctrx_archive_system_perf.time_stamp) < DATEADD(d, 1, @LocalEndDate))
ORDER BY Date, Time
In the “Design the Matrix Wizard” Add Time2 to the Columns, Date to the Rows, and Total Sessions to the Details value. Click finish.
The first necessary items that need to be added to the report in order for this report to run successfully are parameters. On the data or layout tab of your new report, open the Report Parameters window. Two parameters need to be added to the report “Start” and “End”. They both need to be string values. EdgeSight reads these parameters on the reports and allows you to choose what dates you want to run the report for. Go ahead and preview the new report. If it doesn’t work, take a break, watch an episode of Dexter, and be glad you work on computers, where everyone gets a second shot. Let’s continue down the steps to making this a report with a bit more useful data.
In the rows where the Date is, I needed to add the day of the week to display days like the following: 8/1/2011 – Monday. To do this I changed the formula for the cell to: =Fields!Date.Value & ” – ” & WeekdayName(Weekday(Fields!Date.Value))
In the columns where time2 resides I needed to show the hours and not show the integer. I’m not a big VB guy and am still working on growing my coding. For that reason I have change the cell to the impeccableformula of: =Fields!Time2.Value & “:00:00”. This shows the hours of the day in military format.
Good enough for me.
In BIDS, to show the average of rows and columns, a bit of code needs to be added to the report. In the Report Layout window, you can add the subtotals to the rows and columns by simply right clicking on any row and column and selecting “Subtotal”. BIDS will add a new row or column to your report that shows the subtotal of the row or column. The problem is that BIDS 2005, I don’t know about any newer version, only subtotals are shown and not the average. To show the average of the columns instead of the subtotals, open the “report properties” and go to the code tab. Based on a how-to from All About Business Intelligence add the following code:
Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
If InScope Then
If Not avgValue Is Nothing Then
Return String.Format(avgFormat, CDbl(avgValue))
Next you have to change the details cell to the following code:
InScope("matrix1_Date") and InScope("matrix1_Time"),
Go ahead and preview the new results of your report.
After a few aesthetic changes, you can find an example data output of my finished report here:
Download – Session Count by Date and Hour – Please note that this file is set to a .doc and needs to be renamed to a .rdl in order to be uploaded into EdgeSight.
***Update 8/19/11 – I did some playing around with this report and query and have noticed that I only applied the timezone offset to the time and not the date structure. If a user runs the report on GMT-5:00, then instead of Friday 7:00PM showing up as the time, it will show Saturday 7:00PM. I am correcting the query as soon as I can, and will update this post and the report once it is corrected.
****Update 8/20/11 – I fixed the query and the report. The new version of the report converts time correctly. You can download it here