How to disable Reporting Services data cache in Development

Friends,

Microsoft provided data caching option which is a real great boon to the developers and few times the real curse too. Let me explain both the cases here.

Let’s say we have written a query which returns 1 million records from database and using the same output a SSRS report is designed. As you all know it takes minutes to get 1 million records from database and more time to display the same in SSRS report. The data is tested and it is perfectly working and not the work is on Look and Feel of the report like setting colors, fonts and more. When ever you make any change to the Format of the report then you have to see the preview of the report. Here comes the advantage of having data cache in SSRS designer. When ever I click on preview after making changes then the SSRS will uses the data cache option and displays the caches data with new format instead of hitting the database to fetch the data fresh. This helps us in saving our time while development. If this data cache option is not available then for every change the query will be hit and the data will be fetched from database. This will not have much impact if the query is fetching less data but when the data is huge then it is gonna kill the time during development. So, in simple when the report dataset is fetching huge data then better to have data cache enabled to save time during development.

Now, let’s see the disadvantages of having data cache  enabled. If the data is changed in your  database and you want to see it then this data cache should be disabled else it will pull and show the same old cached data again and again. Let me explain a scenario which killed my time. I was new to SSRS and when i was working on PROTOTYPE, I designed a report on DUMMY data and everything was working fine. When I have given demo to my manager he asked me to add more records before we give demo to user. I added more data and everything was set. When the demo to users started, we were surprised by seeing the same old junk data in report(including my name and my gf’s name .. :p). As I was not aware of this option it ruined my day. After long search in google I cam eotknow about this option.

So, it is very important to be aware of this option. This can be Enabled/Disabled in the file present in the below given folder.

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config

In that file you can see an option “CacheDataForPreview” and you have to turn this off/on to disable/enable this feature. PFB the screenshot showing the same.

This is a simple  feature but very useful one to developer. So, it is very useful to keep an eye on this while developing SSRS reports.

That’s it .. Hope you understood.

Regards,

Roopesh Babu V