Reporting Services Extras Bug - Date Sort Problem

Total article views: 211 | Views in the last 30 days: 26

 Rate this |   Join the discussion |  Briefcase

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.

Total article views: 211 | Views in the last 30 days: 26