Saturday, January 14, 2012

Clear cache programatically while rendering the report

Last week I was working on a SQL Reporting services report for displaying a report on financial data for one of my client. One of the requirements involved was to include ability to export this report to excel sheet.For this purpose we added a link inside report and added a call to report url to render the excel equivalent version that we created. After deploying we tested and report was working fine. One of associated requirement was user should be able to see changes to some of financial projection figures which they will be doing through UI screen. We were facing an issue on this as the changes done by users were reflected in main report whereas while trying to export to excel, we were still getting old data in it. The reason was we had reporting caching enabled for reports as performance was one of key requirements.We know how to clear cache from report server however didn't know how to get this done automatically while exporting to excel. We did some analysis and finally found out a way to do this which is rather simple. You just need to append the below url argument to the report url to get latest data automatically


http:// your current report url ...&rs:ClearSession=true


This will make sure it renders report with latest data.
You can add to any expression generating report URL to make sure you get latest data rendered