The Main problem is that SSRS rejects all default values in an multiselect parameter if only one value isn't in the list of default values.
Because of that simply using a statement like this is not possible because "null" doesn't exist within the available values:
select Projekt,
case when Projekt like 'LT%' then Projekt else null end as ProjektDefault
from ...
Fortunately the default values don't have to be unique. Because of this the following workaround is possible:
a) Add a field containing the default value and one field containing the "minimum" of the default values using the transact sql "over()" Statement
select distinct
projekt.t_dsca as Projekt,
case when projekt.t_dsca like 'LT%' then projekt.t_dsca else null end as ProjektDefault,
MIN(case when projekt.t_dsca like 'LT%' then projekt.t_dsca else null end) OVER() AS MinProjektDefault
from ...
or:
select Projekt,
ProjektDefault,
MIN(ProjektDefault) OVER() AS MinProjektDefault
from (
select projekt.t_dsca as Projekt,
case when projekt.t_dsca like 'LT%' then projekt.t_dsca else null end as ProjektDefault
from ...
) b
or (pure sql):
select Projekt,
isnull(ProjektDefault, MinProjektDefault) as ParameterProjektDefault
form (
select Projekt,
ProjektDefault,
MIN(ProjektDefault) OVER() AS MinProjektDefault
from (
select projekt.t_dsca as Projekt,
case when projekt.t_dsca like 'LT%' then projekt.t_dsca else null end as ProjektDefault
from ...
) b
) b
b) Add a calculated field to your dataset wich contains either the default or the minimum default value. (unless done in sql)
=IIF( IsNothing(Fields!ProjektDefault.Value),
Fields!MinProjektDefault.Value,
Fields!Projekt.Value
)
c) Use the same dataset for default and available values within your Parameter
