July 22, 2014 at 9:05 am
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.
July 22, 2014 at 10:51 am
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
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
July 22, 2014 at 11:16 pm
What about setting the parameter to multivalue? Then you should have a "Select all"-Entry in your drop down.
July 23, 2014 at 7:12 am
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
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
August 14, 2014 at 12:47 pm
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.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply