A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Thursday, January 17, 2013

SSRS 2008 R2 Gotcha: Default Multi-valued Parameters

Recently a customer contacted me with a mysterious symptom in SQL Server Reporting Services (SSRS): a report displayed different results in VisualStudio than it did in Report Manager. He had verified the data source was the same for both contexts. He had verified that the RDL file was identical in both contexts. All the user-selected parameters were identical. And yet in Visual Studio certain values were included in the output that were not appearing when the exact same report ran in Report Manager. He had redeployed this report several times while troubleshooting, with no change in the output of the report in Report Manager.

After I connected to his system with a remote assistance session, I started drilling down through the report management options in Report Manager (report > Manage). I discovered that there were a couple of hidden parameters in the report design, and shortly after that I discovered that one of the hidden parameters was a multi-select parameter for which the Visual Studio report had two values selected by default and the Report Manager had only one.

Apparently the Report Manager context had cached the default setting for the parameter with one value only and redeploying the report did not change the setting. The only method we found that could fix the hidden parameter was to manually update it in the Report Manager context. Only then did the online report output match the offline report output in Visual Studio.

Later I tested the process by deploying a report that had a multi-valued parameter that used a query to select all the possible values by default. This parameter was not hidden. This report deployed normally and showed the parameter as having a default that was generated by query, the correct setting. But then I altered the report in Visual Studio to have a manually-defined default value instead of a query-based value. When I deployed the report and checked it again in Report Manager, the parameter had no default value at all. Changing the default value to a different manual value and redeploying had no effect; the online report continued to have no default value defined.

The bottom line appears to be that in SSRS 2008 R2 setting manual default values for a multi-valued parameter in Visual Studio is not a guarantee that the deployed report will have the same values set as defaults. The only way to be sure you have the correct manual default values for multi-valued parameters is to set them using the the Parameters property of the online report in Report Manager.

4 comments:

  1. I'm running into a similar situation but I'm unable to resolve the issue. I have a multi-value parameter on my report and I want it to default to a subset of the available values. The proper values are selected when I run the report in my desktop development environment. After publishing the report several times to the server, the values are never selected even though the default value is checked for the parameter and the query returns the correct values. I even went so far as to point the connection source to the same database that I was using on my desktop, again to no avail.

    ReplyDelete
    Replies
    1. I found that multivalue default parameter values are always correct if they are populated by a query and not by manually entering the values. If you can set the multivalue parameter's default setting to a dataset query instead of a manually-entered set of values, the problem should be resolved. Even if the dataset query is nothing more than "select value = 1 union all select value = 2 union all select value = 3" (essentially a manual list of values) I think it will populate the defaults correctly when you deploy.

      Delete
  2. I've been wrestling with this exact problem for two days. I solved it by setting the default values directly in Report Server but, I wasn't comfortable with it. Your explanation helped a lot.

    Thanks,

    Loke

    ReplyDelete
  3. http://themulle.blogspot.de/2014/01/ssrs-multiselect-parameter-dynamic.html

    ReplyDelete

Followers