Technical Article

Reporting Services Extras Bug - Date Sort Problem

,

SQL Server's Reporting Services comes with some pre-built reports to be able to monitor who is doing what with your Reporting Services Server. You can find these reports in the \Extras\Execution Log Sample Reports folder on the product CD-ROM (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_88gy.asp). One of the reports is titled "Todays Reports" (Todays Reports.rdl/pToday Dataset). It should show the reports and the users that executed the reports that were executed within the last day (depending on when the associated DTS package was run). However, during the month of January, the script returns December dates as the "top" dates. The reason for this is that the SQL script is doing an order by on varchar data. Microsoft wanted to have the date in the mm/dd/yy format and remove the time portion from the data, but this will not sort properly due to the way that character strings are interpreted by a sort. Example sort returned by the script provided by Microsoft:

Script:
SELECT DISTINCT CONVERT(varchar(10), TimeStart, 1) AS theDate
FROM ExecutionLogs
ORDER BY theDate DESC

Returns:
12/31/04
12/30/04
12/29/04
12/28/04
01/06/05
01/05/05
01/04/05
01/03/05

The associated script is an alternative solution that will also remove the time portion of the underlying datetime field and also sort by date rather than by the character string.

In Visual Studio Open the "Todays Reports" report and change the SQL of the pToday Dataset to:

SELECT DISTINCT CONVERT(varchar(10), TimeStart, 1) AS theDate, CONVERT(datetime, CONVERT(varchar(12), TimeStart)) AS thedate2
FROM         ExecutionLogs
ORDER BY theDate2 DESC

Rate

2 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

2 (1)

You rated this post out of 5. Change rating