The times I have spent with SSRS 2008 can be likened to that of a married couple’s life story. The moment I first laid my eyes on her during the Microsoft seminars and community previews, I knew that she was going to be the one. I secretly started collecting details about her, as much as possible and awaited her arrival. Needless to say, I was one of the very first to have the trial version installed and in the dates that followed, I was just astounded at her potential – she seemed so full and promising. I even started writing sonnets in her praise (read as reviews), love struck that I was. Eventually, the marriage took place (yups, we got the license too!) and we revelled in the honeymoon period. But once the honeymoon period got over, I started noticing some irritating things about her that I had not noticed before – the story of every married couple’s life. And one of those irritating features was the auto update of report parameter datasets. Everyone who has worked with SSRS 2008 must have experienced this sometime or the other. You use the query builder to make a query with report parameters in them, and then you decide to modify the parameter dataset queries. After that, you play around with the main dataset, make those few important changes that the query builder doesn’t support and then you execute the query. You wouldn’t find anything missing till you actually preview the report and notice that the report parameters are not reflecting the changes that you just did. You double check and yups, the parameter queries have indeed changed and all the hard work that you did to change them has gone for a toss. Well, with this post, this issue is going to be history. For illustrating the problem, I have made a simple report which has a Year and a Quarter report parameter, and the category and Quantity for the selected values in a table. 
Now when I expand the parameter list, I can see that there is an option for All Periods which I would not like to display. 
So I go back and change the query to remove the all period option. 
Now I can preview the report and the All Periods option is not there anymore. 
Looks good huh? Well it does as long as you don’t touch the main dataset query. The moment I decide to go and display only the top 2 categories instead of all the categories by modifying the query, things change. 
Now when I preview the report, I get the following result 
The report now displays only the top 2 categories but then the changes that I had done to the parameter is gone and All Periods is back to haunt me. There are 2 methods to solve this issue – Method 1 Make sure that wherever the particular parameter (whose dataset query you would not like to be auto updated) is referenced in a query, the default value is specified using the fully qualified name or the unique name rather than selecting the dimension, hierarchy and name. An example is shown in the image below 
Please note that you might have to delete the old parameter from the Query Parameters window and then create a new one else you would not be able to leave the Dimension, Hierarchy columns empty as shown above. Method 2 I owe this solution to a blog that I read from Teo Lachev (MVP). Follow the steps below to implement the solution:- 1) Right click on your report and select the View Code option 
2) Find the code section which will have the required dataset name (where you want to prevent the auto update). Go to the line just above the </Query> and paste <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> An example is shown in the image below 
Now that you have got the solution that you had been looking for, why are you still reading on? Oh are you waiting for me to complete the story that I was telling you at the start? 🙂 Ok here it goes. Now that I have spent quality time with SSRS 2008, I have understood her more and have learned to work my way around her when she throws her tantrums. No girl is perfect, and I don’t claim SSRS 2008 to be perfect but for me, she is the best one around and I just love her a lot – the story of every happy married couple’s life 🙂
Auto-update of parameter datasets in SSRS 2008
This is really very intutive and informative article. Keep up the exploring spirit on..