If you have developed a few SSRS reports in BIDs, you must have noticed that BIDs uses a data cache to speed up your development work. Your design-preview-design-preview cycle is fast enough that you probably have never complained . Not yet.
One of my reporting Services reports is still in development phase. After receiving a Success email notification from my ETL job in the morning, I opened up the BIDs, and tried to preview my report. I was expecting data from yesterday, but I didn’t see it.
I am using BIDs 2008. The SSRS report is accessing an Analysis Services database using MDX. I checked my fact table which has data from yesterday. I know my cube processing was successful. Browsing the cube in SQL Management Studio also showed data from yesterday. However, in BIDs, when previewing the cube, I didn’t see my data from yesterday.
A Quick Solution
I understand the data cache feature. So I didn’t panic. right click on the dataset, select Query.
On the Query Designer, click the link to execute the query.
This will pull fresh data from the back-end data source. Now I see data from yesterday on Preview.
Another Even Quicker Solution
After I published this post the first time, Mark W, a regular reader, pointed out that the Refresh button is an even quicker way to retrieve the most recent data from the back-end data source. Thanks Mark!
Reasons for this blog
There are three reasons for this post. One is for the benefit of new SSRS report developers who might be unaware of the data cache feature in BIDs.
Second I am still not sue if BIDS 2008 behaves differently from BIDS 2005 in terms of data cache, or it behaves differently when accessing a cube.
Third reason is to give you a link to this blog post Disable Reporting Services Data Cache in Development. In this blog, the author pointed out that you can permanently disable the data cache feature in BIDs. I personally will not do this, because I really like the data cache feature.
In case somebody wants to give it a try, here is what the author suggested.
Find the config file that controls the Report Designer in BIDS. For SQL Server 2008 SSRS, this should be:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config
Then change CacheDataForPreview to “false”.
In my previous post, SSRS #43 – Problem with undocumented SQL procedure AddEvent when kicking off a Timed Subscription, I mentioned that the report link from the Subscription is not a valid link. Our architecture team informed me that using undocumented procedure AddEvent probably is not the best way to kick start a Reporting Services Subscription. The recommendation is to use the exposed web services provided by the reporting Services within my SSIS package to kick start the Subscription.
Well so far I couldn’t get it to work because I couldn’t even get the ReportingService class to work in either a Script Task or the Web Service Task. There are many articles talking about how to get the ReportingService class reference to work in a SSIS package. This one seems to have a very detailed instruction on how to make it work, Consuming the Reporting Services web service inside SSIS. Unfortunately so far it’s not working for me because I could not find the wsdl.exe file locally
According to this article on MSDN, Web Services Description Language Tool (Wsdl.exe), the file wsdl.exe is part of the.NET Framework 2.0 Software Development Kit. Once installed, the tool (wsdl.exe) can be found in the folder: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. Since I don’t have the SDK Kit installed, no solution for me so far. I don’t want to install it right now either, since this is not on my priority list.
My priority regarding managing Subscriptions on Reporting Services
What is on my priority list:
1) Continue to use the AddEvent procedure to kick start the Subscription, but do not hard code the SubscriptionID. This should be easy to achieve. See below.
2) I need to switch from Subscription to Data Driven Subscription.
Reasons for the above priority
If you have created a Timed Subscription on Reporting Services, you must have noticed how “easy” it is to select values for your report parameters. You must have also noticed a few times when the automatic emails did not fire off. You scratched your head, and couldn’t figure out why. Later you will find out that it’s because the values you picked on the Subscription are no longer valid, or you have added more report parameters, or have deleted a few parameters. In another word, the Subscription you have created is no longer valid.
Retrieve SubscriptionID Dynamically
I need to do the first thing on the list if I am going to continue to use the Subscription, instead of the Data Driven Subscription, so at least I have a valid SubscriptionID if I changed something on the Subscription.
This is a relatively easy task. I would create two Execute SQL Tasks, and a package variable varSubscriptionID.
The variable varSubscriptionID will be populated in the first Execute SQL Tasks with a query like this:
In the second Execute SQL Tasks , I will use this to kick start the Subscription by passing the variable varSubscriptionID to it (represented by the question mark).
Data Driven Subscription
There are many articles about why we need to use Data Driven Subscription VS. just the plan Subscription extension on Reporting Services. Most bloggers have done a great job. I don’t need to report them here. To me, I need to use it because I do not want to hard code the values for my report parameters. I found out what would happen if the values are no longer valid. I also want to be able to customize the subscriptions for different business groups. The idea is the same as my first priority, I want to manage the subscriptions in a dynamic way.
I will blog about the Data Driven Subscription in the future.