Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS List & Blank Parameters Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 2:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

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!
Post #1464498
Posted Wednesday, June 19, 2013 9:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 236, Visits: 2,659
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.
Post #1465239
Posted Thursday, June 20, 2013 2:48 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:50 PM
Points: 486, Visits: 1,210
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.



Post #1465947
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse