dinsdag 3 mei 2011

SSRS : Report parameters


Currently building a dashboard for a customer and i wanted to understand the concept of parameters a bit better. Especially in relation with MDX queries. Using parameters isn't complicated, but you need to understand them thoroughly when you're building more complex reports. So, lets understand the scope of this post: Parameters. Where do they all appear?

1. Query designer parameters
When you're designing your MDX query in the query designer it's possible to set whether or not you want a parameter.

The MDX query is something like this:

2. Dataset parameters
The dataset is built on a query and is used by the report. 

3. Report parameters
The report parameter is used for prompting to the user. The user can select a value and execute the report.

So i decided to rename the parameter. The first thing i did is renaming the query parameter (@dateCalendarYear into @qryDateCalendarYear) and executing the query and the following error occurs

Parser: The query contains the qryDateCalendarYear parameter, which is not declared. (msmgdsrv)

Then i noticed there is a another window in the query designer where information about a parameter is stored.

So i changed that from DateCalendarYear into qryDateCalendarYear.and now the query is executed.  When i take a look in the parameters window in the dataset properties window an  empty Parameter value is shown. So i choose the suggested option.

Okay let's see whether the reports run and it does.....Now lets change the report parameter and see whether if the report still runs.

An error occurs.....

So let's go the dataset properties window and that indicates that there is a mismatch between the parameter name and parametervalue.

Correcting this will let the report run.

So renaming the different parameters needs some different adjustments. There are four places where you can change something regarding parameters:
  • Query designer
  • Parameter window in the query desgner window
  • Parameters at the dataset properties window 
  • Report parameters in the main report development window.
It's a bit confusing that the parameter name and parameter value ara actually both parameter names (and not values). One is the report parameter name and the other is the query parameter name.

So that's it for now.



2 opmerkingen: