SSRS Report - Last Modified ...

Sometimes you know all the pieces and never think to use them together in a different way.

I've always wanted to add some sort of easy version number/system to my SQL Server Reporting Services (SSRS) 2005 reports.  I thought it would be a nice thing to have for users, especially ones that get well-acquainted with a certain report and want to know if you've published a change they requested.

The solution I came up with isn't perfect, and it suffers from some of the same reasons I resisted rolling our own solution.  Perhaps it will be better addressed in future versions. 

I always knew SSRS keeps a lot of data about the reports in a ReportServer database. Heck, I even wrote my own custom report server as a project for a past ASP.Net class, and used the ReportServer as a guide.  What never occurred to me was that I could use that meta-data on the ReportServer database for the reports themselves.

On a SQL 2005 box with SSRS, there is database called [ReportServer] that contains all kinds of meta information about the server and reports  A table called ReportServer.dbo.Catalog contains a column named "Modifieddate".  Instead of a version number, I can write a query to show the last time the report was modified (i.e. deployed).

First and foremost, your users need to have read access to the ReportServer database.  If they do not, you can't do this because your users will not be authorized.

In the report:
Setup new Dataset:
Name: dsReportInternals
SQL:
SELECT
top 1
[name],
[Path],
Max(Modifieddate) as [Modifieddate]

FROM
ReportServer.dbo.Catalog

WHERE
[Name]=@ReportName
and [Type]=2

GROUP BY
[name],
[Path]
;

If you might have the same report in two different locations on the server, you will want to use the Path column as your guide, add something like this in your WHERE clause:
[Path] = @ReportPath +'/'+ @ReportName

To avoid having too many report parameters, I setup this parameter inside the dataset:
@ReportName =Globals!ReportName

For the report parameters, I setup a new parameter like this:
Name: Global_LastModifiedDate
Internal: true
Available Values: Non-queried (i.e. nothing set)
Default Values:
From query = true
Dataset = dsReportInternals (from above)
Value field: ModifiedDate

Hopefully this will be helpful to someone.  It still has the issue that it's not a dead simple thing to setup for each report, but it's better than nothing.