When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data.

A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The next time you preview the report SSRS will be forced to requery the source.

To save time, I use the following macro to take care of it.

Press ALT+F8 to open the Macro Explorer, and add a new module called “RemoveRDLDataFiles” under MyMacros. Edit the file and add the following code to the file. (This is for SQL Server 2008, you may need to tweak the references for 2005).

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
mports System.Diagnostics
Imports System.IO

Public Module RemoveRDLDataFiles

Sub RemoveRDLDataFiles()

Dim project As Project
Dim Folder As String

project = DTE.ActiveSolutionProjects(0)

Dim fi As New FileInfo(project.FullName.ToString)
Folder = fi.DirectoryName

For Each FileFound As String In Directory.GetFiles(Folder, “*.rdl.data”)
File.Delete(FileFound)
Next

End Sub

End Module

You can then run it by either double clicking on the macro, or assigning a keyboard shortcut to it (via Tools, Customize, Keyboard).

Tags: Cache, Macro, Query Refresh, SSRS

This entry was posted on Wednesday, April 7th, 2010 at 8:32 am and is filed under .Net, Reporting Services. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.