MS Reporting Services

[vedicman]

Using MS Reporting Services, I am developing a report that contains several parameters. I want to give the users an option to leave a parameter blank and have the query run without filtering that field. I have tried the following logic, but it does not function correctly:

Where ProgNum = Case
When @ProgNum = '' Then ProgNum
When @ProgNum <> '' Then @ProgNum
End

Any Suggestions?

Thanks.....Franco

 

[vongrunt]

Lemme guess... if you don't get any record then @ProgNum is probably NULL. If true, try this:

Where ProgNum = COALESCE( @ProgNum, ProgNum )

 

[vedicman]

Thanks for the idea!

My code works fine and dandy in SQL Query analyzer, but it does not work in MS Reporting Services.

I tried the coalsece statement that you suggested. It doesn't provide the results I'm looking for in either environment.

Any other ideas?

Franco

 

[vongrunt]

Are you sure @ProgNum becomes '' for blank parameter?

 

[vedicman]

vongrunt

Actually, the first code snippet I posted does function correctly when used in a query that limits the return with additional criteria. It appeared not to be working because it was returning an enormous amount of data and the query sat there forever.

Here it is again, slightly modified:

Where ProgNum = Case
When @ProgNum = '' Then ProgNum
Else @ProgNum
End

Thanks again for your ideas......Franco

 

[JohnEck]

Trying setting up the parameter in RS to allow nulls. If you do that and set the label to all and value to null then you can select all and it will not filter on that field.