Friday, October 14, 2005

Separating Data Layer from Reporting Layer in RS

How to preview a report without direct access to its data source in SQL Server Reporting Services 2000
You may have noticed that starting with SSRS SP1, when you preview a report which contains data in the designer, a ReportName.RDL.Data file appears on your file system. We store the data that was just displayed by your preview action in this file so we don't have to round-trip to the server over and over again each time you preview the report (assuming you don't change any parameter values, etc.)
If you want, you can use this data file to make your report more "portable" -- Basically taking the report.rdl and files to another machine that doesn't have access to the "real" data store. You can also use this method to send the report/data to a buddy if you need some help troubleshooting the report. Cool hack!
Close Visual Studio and add the following lines to Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE\devenv.exe.config file:

What this entry does is force the preview pane to bypass checking that the cache (data file) is up-to-date and that any report parameter values selected in the report match those used to generate the cached data in the local file.
Next, Create a new VS Report Project and add the existing (rdl) report to it. Drop your file in the same folder.
(This is the strange part) Add a new shared datasource to the project, giving it the SAME name as the old datasource your report used in the other project it lived in previously. You don't need even need to specify connection string in his data source -- it'll be using the cached data in your file.
Finally (if necessary), modify the report so that any parameters in it don't use queried values (which you no longer have data for) , and that all parameters have valid default values. You also need to remove any other datasets that use data not coming from your file.

No comments: