Search

Optional SSRS Parameters by bt_avanade

Active

1
0
Sign in
to vote
Type: Suggestion
ID: 771255
Opened: 11/15/2012 8:02:40 AM
Access Restriction: Public
0
Workaround(s)
There have been a lot of posts regarding this request over the years, and most have workarounds involving either "inclusive/exclusive" parameter values and/or logic blocks (IF) in your SQL or stored procedures. I do however have a case where I think this would be of value. Please suspend logic for a moment as I'm trying to keep it simple. If a table contains records for the following mix of vehicles and color
Car                 Red
Car                 Blue
Truck             Red
Truck             Black
ATV                Red
ATV                Green
and users are provided with 2 parameters that limit vehicles and colors to those for which a user might be primarily responsible (not used to restrict users' access to records but as a guide and to improve the user experience; assuming there are 50 vehicle types and 1000 colors), what might allow users to filter on ALL Red vehicles (including ATV) if their parameter options for vehicle are limited to Car and Truck?

Just an example, but I'm sure there are others that would benefit from a checkbox that enables/disables the use of parameters. Of course, the underlying SQL/stored procedure would have to accommodate the presence or absence of the filter criteria.
Details (expand)

Product Language

English

Category

Reporting Services

Proposed Solution

Simple suggestion, I'm sure there are other more sophisticated options: A checkbox that enables/disables the use of parameters and, potentially, an interface (in the Dataset properties) that supports logic to direct the dataset to use one of a set of optional stored procedures based on the criteria selected ... if users don't want to develop dynamic SQL statements or IF logic blocks.

Primary Benefit

Improved User Interface

Other Benefits

Simplify development

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Marlon Ribunal on 11/30/2012 at 10:05 AM
Will this be solved through TSQL. You can make a parameter optional (my example here: http://dbalink.wordpress.com/2009/08/11/how-to-report-authoring-in-ssrs-2005-%E2%80%93-part-v-pimp-your-parameters/) and inlcude "ALL" parameters. My example on "ALL" parameter is not complete but it a good place to start, check http://dbalink.wordpress.com/2008/10/18/the-all-parameter-in-sql-server-reporting-services-2005/
Sign in to post a workaround.