Mittwoch, 15. Januar 2014

SSRS Multiselect Parameter - Dynamic Default Values

Basic idea behind this post is to use the same dataset for default an available values.
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

 



Keine Kommentare:

Kommentar veröffentlichen