SSRS Parameters conditional statement

Question

I created an SSRS parameter that accepts user input as an integer value. I need to check the user input and make sure it is within a certain range. How can I do that in SSRS.

The parameter is ReportingPeriod, in this parameter properties I tried to set the default value of the parameter as an expression. My requirement is to make sure that the user input in the textbox is an integer value between 1 and 45 days. Since I am new to SSRS, I created something like below based on the examples in the parameter expression description.

=Switch(Parameters!ReportingPeriod.Value = 1, 1,
    Parameters!ReportingPeriod.Value = 10, 10,
    Parameters!ReportingPeriod.Value = 45, 45)

But this gave me the following error.

The DefaultValue expression for the report parameter 'ReportingPeriod' contains an error: 
The expression references the parameter 'ReportingPeriod', which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case.

As per my understanding, I believe the issue is that the input parameter ReportingPeriod is not yet initialised or something and thus throwing error, but not sure.

Any idea how to resolve this?

I got my above solution reference from this post.

Answer 1

The error you are getting is as you thought: you are trying to set the default value for ReportingPeriod by referencing the value of ReportingPeriod, which is not possible because it does not exist yet.

Since you are only looking for an integer between 1 and 45, custom code would be overkill in my opinion. You can accomplish what you are trying to do simply by setting the Available Values of the parameter. The only downside to this compared to custom code is that the user will not be able to enter the value into a textbox. They will instead have to select it from a dropdown.

Since you know you only want to allow 1-45, you could specify each value to allow individually. But I would suggest getting the available values from a query.

To get the available values from a query, you will need to create another dataset which contains only the values you wish to allow for that parameter. So you will want a dataset that contains the integers 1 through 45. If you have a numbers table this would be trivial, just something like select top(45) * from NumbersTable. But if not, you can write a simple query to accomplish this as well. I'm sure this query would not be the absolute best way, but it would work:

select top(45)
    rn = row_number() over(order by object_id)
from
    sys.all_columns

You will then go to the parameter properties, and in the Available Values, select "Get values from a query", set the dataset to the one you just created, and set the value and label fields to the only column from that dataset.