Creating a report with two datasets that reference the same pair of parameters, I’m seeing the following error:
The Value expression for the query parameter ‘DatesYear’ contains an error: The expression references the parameter ‘DatesYear’, which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case. (rsRuntimeErrorInExpression)
—————————-
An error has occurred during report processing. (rsProcessingAborted)
This is a situation that can occur when the report designer tries to hook-up two or more Analysis Services datasets to the same named parameters. Even simple reports can get tricky and complicated very easily.
More and more these days, I’m building dashboard style reports that have multiple data regions in a report that may use multiple datasets that should share the same parameters. With the introduction of report parts and shared datasets in SSRS 2008 R2, this is a design pattern that seems to make sense. However, the report designer can get confused about the dependencies between some of these objects and creates unnecessary dependencies that can result in error conditions. The error reporting in Reporting Services is usually pretty accurate but an error may not tell you what object is causing the error. I’ve spent many late hours racking my brain to diagnose errors like this, thinking that I had exhausted all options, and therefor the error was wrong; only to discover that it was a simple problem and that the error was accurate all along.
According to Sherlock Holmes:
“when you have eliminated the impossible, whatever remains, however improbable, must be the truth”
Here’s a common example…
In this example, I’m building a dashboard style report based with a data source that is a PowerPivot model in SharePoint. As far as we (and the report designer) are concerned, it’s an Analysis Services database (tabular model or multidimensional cube – they all look the same to SSRS).
The first dataset is shown below. Two filters are added to the filter pane and the Parameter checkboxes are checked. This generates parameter expressions in the query and corresponding report parameters that are mapped in the dataset parameters collection.
This all works just peachy until the next step. I add a second dataset using the same data source and drag the same two members into the filters pane to create identical parameters; in this example, named AirlinesSize and DatesYear. When I run the report, I see the error. Checking the dataset parameters, everything seems to be correct. Even more perplexing is that both of the dataset queries work just fine in the query designer. What could possibly be the problem?
The issue lies with the dataset generated to populate the parameter drop-down lists in the report. If you right-click on the Parameters folder in the Report Data window and show hidden datasets, we see that there are two additional datasets to support the parameters:
The report designer has a nasty habit of parameterizing datasets that may have nothing to do with each other. Here, you can see the problem:
Although unnecessary, it would actually be no problem for the AirlinesSize dataset query to reference the DatesYear parameter, but the problem is that a dataset can’t reference a parameter who’s value depends on a dataset lower on the list – because the query hasn’t been executed yet.
I either have to rearrange execution order of the datasets (assuming that this doesn’t upset the delicate balance of other things in the universe), or remove the parameter reference in the query designer – which is what I’ve done. Just keep in mind that if you make any changes, the designer is likely to revert back to the same state later on.
I hope this helps someone. Please let me know if it does.