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

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 46, Visits: 212
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 @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:40 AM
Points: 24, Visits: 33
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 @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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


SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 6:42 PM
Points: 459, Visits: 1,065
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