Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Specify Values in SSRS parameter Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2014 9:05 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, February 9, 2016 2:40 PM
Points: 102, Visits: 377
Hi!

I have a SSRS report , parameter @A uses 'specify values' (integer 1 and 2) as available values.

What I need now is dropdown 'All' or 'Total' which would give me total result from 1 and 2 which are integer values.
Currently I am able to get for individual values only.

How can I include result from 1 and 2 if clicked 'All' dropdown?

Thanks in advance.
Post #1595134
Posted Tuesday, July 22, 2014 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 10,711, Visits: 14,453
If you are calling stored procedure then in the procedure I'd do it like this:

IF @parameter IS NULL -- assuming that you pass NULL when ALL is selected
BEGIN;
SELECT * FROM TABLE;
END;
ELSE
BEGIN;
SELECT * FROM TABLE WHERE COLUMN = @parameter;
END;

If you are not calling a stored procedure but have embedded sql I'd do something like this in the dataset:

="SELECT * FROM  Table " & IIf(! ISNOTHING(Parameters!ReportParameter1.Value), "WHERE Column = " & Parameters!ReportParameter1.Value, "" )" (




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1595184
Posted Tuesday, July 22, 2014 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 21, 2015 5:12 AM
Points: 38, Visits: 40
What about setting the parameter to multivalue? Then you should have a "Select all"-Entry in your drop down.
Post #1595339
Posted Wednesday, July 23, 2014 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 10,711, Visits: 14,453
MicVog (7/22/2014)
What about setting the parameter to multivalue? Then you should have a "Select all"-Entry in your drop down.


That still can require some special handling as the "ALL" really just selects each item and passes them as a delimited list, it doesn't pass a specific value and it wasn't clear that 1 and 2 were the only possible values for the column in the database but may be the key values that are necessary to separate searches on.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1595476
Posted Thursday, August 14, 2014 12:47 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 729, Visits: 1,481
I've noticed in the case of a multi value parameter, the Select ALL option shows when the code is embedded in the reports. That is, a stored procedure is not being called.
Post #1603453
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse