locked
Visual Studio 2008 SSRS Default Value When Only One Choice Available RRS feed

  • Question

  • 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.

    Friday, June 10, 2011 10:30 PM

Answers

  • 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
    • Marked as answer by Challen Fu Friday, June 17, 2011 10:43 AM
    Monday, June 13, 2011 7:31 AM