Multi-Select Parameters in SSRS 2005 - HELP

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
ThanksTry doing this...
where (@.parm is null or element in (@.parm))
Cheers|||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...
"sullins602" wrote:
> Try doing this...
> where (@.parm is null or element in (@.parm))
> Cheers
>|||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|||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?|||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'....
"sullins602" wrote:
> 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
>