CODEWITHSUNDEEP
reporting-services
Michael Robinson
Michael Robinson

Reputation: 1132

How to handle a parameter that has no value in SSRS

I have a report that has two datasets in it. The first dataset is used to populate a parameter to a second dataset. Everything works fine until the first dataset has no values, whereupon I get a parameter X is missing a value message.

Is there a way to get around this?

Upvotes: 0

Views: 1973

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

Assuming you're using Cascading Parameters, you can use a query similar to the following to populate the second parameter's DataSet:

if exists (select 1 from Param2 where paramVal = @Param1)
begin

    select paramVal, paramDetail
    from Param2
    where paramVal = @Param1

end
else
begin

    select paramVal = 'None', paramDetail = 'None'

end

This is just a workaround using an if...else construct to return a dummy row if there aren't any other rows to return - that way you'll always get at least one row returned even if there are no matches for the first parameter value.

You can handle these dummy rows as required later in the report.

As above this is a workaround to address your specific issue, but hopefully will be of some help.

Added after comment:

I just tested now - it works just the same with multi-valued parameters; just change =@Param1 to in (@Param1).

If there are one or more matches in the second DataSet it returns those rows, otherwise just the dummy row.

I did need to shut/reopen the report in VS, but that could have been unrelated.

Upvotes: 3

Related Questions