Visual Studio 2008 SSRS Default Value When Only One Choice Available
How do I get a parameter in an SSRS report to select a default value from a dataset if the dataset returns only one choice?
For example, I have a parameter for State that pulls from a Dataset all distinct states that we have business in. If I set the Default Value to be the same, then it will be a multi-list and will automatically select the first choice in that list. I do not
want the default value to set at all if there are multiple choices, but if there is one and only one choice, it makes sense for the end user not to have to go the extra step of clicking and selecting just one choice from the available values list.
I have tried to create a dataset that performs a count on the number of distinct states. Then created a parameter to hold the count. Then inside the state parameter, I've tried to set the default value to "Specify values", and inside my expression area tried
to do something like this:
=IIF(Parameters!Matches.Value = "1", Parameters!State.Value, " ")
but I'm getting an error message such as: The DefaultValue expression for the report parameter 'State' contains an error: The expression that references the parameter 'Matches' does not exist in the Parameters collection....
I am using the correct case. Is this not possible? Any recommendation will help.
Thank you.
June 10th, 2011 6:33pm
Hi Anedra,
Thanks for your question. According to your question, I understand that you want to control the parameter’s default select state, if the
parameter has one available value the parameter select the single value by default, else none of the values will be selected, right? If I misunderstand your question, please let me know.
It is logical that adding one parameter to store the count number then using it as the Judgment conditions in the State’s expression,
however, you couldn’t define the parameter by itself if the parameter haven’t been initialized, in other words, you can’t use =
Parameters!State.Value expression to define the parameter State’s default value.
To resolve this issue, you must add another parameter to store the single value, then using it and the count number parameter in the State parameter’s
expression. In addition, you must ensure that the count number parameter and the single value parameter being defined before the parameter of State. I will give you an example about how to use these three parameters, detail steps as below,
Supposing that the report has two Datasets named Dataset1 and Dataset2, Dataset1 contains one data field Named
State to store all of the distinct States, Dataset2 has one field of countNumber to store the count number of the distinct States,
there T-SQL query like below,
Dataset1: select
distinct State from DatabaseTable
Dataset2: select
Count(distinct State)
as countNumber from DatabaseTable,
1. In the
Report Data window, right-click Parameters, select
Add parameter, type countNumber in the
Name and Prompt Textboxes, select Integer in the
Data type drop-down list. Click Default
values in the left pane, select Get values
from a query, select Dataset2 in the
Dataset drop-down list, select countNumber in the Value
field drop-down list, click OK.
2. In the
Report Data window, right-click Parameters, select
Add parameter, type SingleValue in the
Name and Prompt Textboxes, Check Allow
multiple values.
3. Click
Default values in the left pane, select
Get values from a query, select Dataset1 in the
Dataset drop-down list, select State in the Value
field drop-down list, click OK.
4. In the
Report Data window, right-click Parameters, select
Add parameter, type State in the Name and
Prompt Textboxes, check Allow multiple
values.
5. Click
Available values in the left pane, select
Get values from a query, select Dataset1 in the Dataset drop-down list, select State in the
Value field drop-down list and Label
field drop-down list.
6. Click
Default values in the left pane, select
Specify values, click Add button, click the fx button, type in the
Expression dialog box like this: =Iif(Parameters! countNumber.Value=1,Parameters!
SingleValue.Value(0), nothing), click
OK.
If you have any other question, please feel free to let me know.
Thanks,
Sharp Wang
Best Regards. Sharp Wang
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2011 3:32am