SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Specify Values in SSRS parameter


Specify Values in SSRS parameter

Author
Message
PJ_SQL
PJ_SQL
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3723 Visits: 659
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.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103000 Visits: 15045
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
Consultant Straight Path Solutions
Dont 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 QuestionHow 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
MicVog
MicVog
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 40
What about setting the parameter to multivalue? Then you should have a "Select all"-Entry in your drop down.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103000 Visits: 15045
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
Consultant Straight Path Solutions
Dont 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 QuestionHow 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
MMartin1
MMartin1
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15896 Visits: 2079
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.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search