CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
Rainhider
Rainhider

Reputation: 836

The "NegativeNumbers" parameter is missing a value

I have a report in SSRS that has two datasets. One dataset NegativeNumbers returns one column with 0 or more values that are used in the main dataset.

The problem comes when @NegativeNumbers has no value to be passed I get the error:

The NegativeNumbers parameter is missing a value.

The NegativeNumbers query will not always return a value (this is correct), so I need the main dataset to execute correctly still when there is no value. I thought checking "Allow empty values" would take care of that, but no.

The parameter @NegativeNumbers is hidden, so it won't take a null value, even though I have it checked to allow empty values and multiple values. The default value is set to the NegativeNumbers dataset values. Hidden ones don't like available values.

Here is the basic code for NegativeNumbers dataset: 

select colName
where  colName < 0 -- returns all values less than 0 as values
                   -- for parameter "NegativeNumbers"

Here is the basic code for the main dataset: 

select bunchOfColumns
from   mytable
where  aColumnName in (@NegativeNumbers)
       -- this returns only the values where the column value is a match for one
       -- of the values returned by the @NegativeNumbers dataset

The actual query is much more complicated than this and includes a union all statement, but this illustrates what I am trying to do. If the first query does not return any values, then the second gives me the error. Why would it not just substitute "in ('')" for the value of the @NegativeNumbers if the dataset is empty and the "allow empty value" is checked?

I have scoured Google. No luck. I have played with the different parameter options. No luck. I have rebuilt the report. And nothing. I always get this error, but I don't know how to fix it.

*beating my head on my desk, questioning the meaning of my coding existence.
*sigh

This is some advanced SSRS stuff and I am not the best at explaining so if there are any questions that would help clarify what I am trying to do, ask away.

Upvotes: 1

Views: 484

Answers (1)

Rainhider
Rainhider

Reputation: 836

So I did a workaround to where my NegativeNumbers will always return a dataset.

    if exists (select query)
    then select query
    else '2'         
     //my key will never equal 2 so the main dataset will 
    //always return the results from  only the positives

Yay for easy workarounds!

Upvotes: 2

Related Questions