sql server reporting services: Multi-Select Parameters in SSRS 2005

Multi-Select Parameters in SSRS 2005 - HELP

Jim
4/28/2006 11:57:03 AM

sql server reporting services:

Hi all â€“

Having a problem with SQL 2005 Reporting Services and Multi-Select
parameters. Itâ€™s seems as though Multi-Select and Allow Nulls are mutually
exclusive. This is a problem as we have some reports that are very complex â€“
using very complex stored procedures that allow for this scenario. Now â€“ put
you hand over your ears for a second â€“ this works fine in Cryatsl â€“ the sproc
is set up to accept a null or a list.

WHERE (@parm is NULL or element = @parm)

There must be a way around this in SSRS

Thanks

Re: Multi-Select Parameters in SSRS 2005 - HELP

Jim
4/28/2006 12:33:02 PM

My bad - I mistated the problem - the problem is not in the sproc - it is in
SRSS Designer. It will not let you set both Multi-Select and Allow Nulls on a
parameter - they are mutually exclusive. If you try to set both - the
designer gives an error and will not save your changes. It will on the other
hand - let you set one or the other individually.

Ths sproc workd just fine as we have sent it a list and a NULL value and it
works both ways as intended.

Thanks...

[quoted text, click to view]

Re: Multi-Select Parameters in SSRS 2005 - HELP

sullins602
5/2/2006 12:58:26 PM

Did you try returning a value of null to the dataset that populates the
list for the parameter?

select
null as 'val',
' - All - ' as 'label'

union

select
realval as 'val',
reallabel as 'lable'
from
mytable

Re: Multi-Select Parameters in SSRS 2005 - HELP

craig.tennill NO[at]SPAM gmail.com
5/2/2006 1:49:32 PM

I just ran into the same problem today with a multi-value and null
parameter. I also thought to add null to the dataset, but when I added
the null to the parameter dataset it did not show up in the report
drop-down box.
Could it be possible this is being filtered out?

One way that does work is to use a actual value instead of a null
(assuming int).
SELECT -1 AS Level1Value, ' - None -' AS Level1Label
UNION ALL
<rest of query>

But I would rather have the option to not require a value to be
selected.
Any other ideas on making this work?

Re: Multi-Select Parameters in SSRS 2005 - HELP

Jim
5/2/2006 1:50:02 PM

I could try that - the only drawback that I see would be that All would then
be part of the mustiselect list - then what would happen if they select all
plus some of the other values - would they not get a sting like
'NULL','Val1','Val2'.....

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.