SSRS List & Blank Parameters

• davdam8

  Hall of Fame

  Points: 3620

  More actions

  June 18, 2013 at 2:18 am

  #295452

  Hi geniuses!

  Is there a way to have a report with 3 multivalue parameters: AREA, LOCATION and PROJECT, exposing only 1 project per page, where the user can or not use all the parameters?

  The report has a financial aspect where the info has to be shown :

  or by AREA (where the user only uses the AREA parameter, letting the other 2 BLANK);

  or by LOCATION (where the user uses the AREA and LOCATION parameter, letting the PROJECT blank);

  or by PROJECT (using all the parameters)

  Does it sound doable? Having a list group on - PROJECT - and be able to not use the PROJECT parameter?

  Thanks in advance!

• Scott Murray-240410

  SSCarpal Tunnel

  Points: 4649

  More actions

  June 19, 2013 at 9:00 am

  #1625534

  Unfortunately, SSRS does not allow you to show or hide parameters based on a prior parameter. However, you can get around this issue by created dynamic / dependent parameters boxes along with the default values for those parameters. Thus, for the selections that only require 1 parameter, the other 2 parameters would default to some dummy value.

• Ed Zann

  SSCrazy Eights

  Points: 8694

  More actions

  June 20, 2013 at 2:48 pm

  #1626123

  The way I would typically do this is to include an OR clause in my SQL query such as

  AND (PROJECT = @project OR @project IS NULL)

  AND (LOCATION = @location OR @location IS NULL)

  AND (AREA= @area OR @area IS NULL)

  As far a grouping goes, as long as Project is a field in your dataset you should be able to group on it even if it is left empty in your parms.