Reputation: 836
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
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