April 1, 2016 at 7:21 am
I have a report layout that works fine. The users want to have multiple parameters to filter the data. They would like to have 5 checkboxes; one for each cost center.
Checkbox 1 = Cost Center A
Checkbox 2 = Cost Center B
Checkbox 3 = Cost Center C
Checkbox 4 = Cost Center D
Checkbox 5 = Cost Center E
If checkbox 1 is selected, they only want to see the results for Cost Center A. If checkbox 1 and 5 are selected, they want to see the results for cost center A and E in the results.
I assume it would be an expression with IIF statements but I'm not quite sure how to get started with this. I have another report with a simple filter that only selects records where a field is greater than 0. This seems more complicated to me and something I have not ever done before. Any help would be greatly appreciated.
We are using Report Builder 3.0 and SQL Server 2008 R2
April 1, 2016 at 8:47 am
Why do you have multiple checkboxes instead of allowing to have one parameter that allows multiple values?
April 1, 2016 at 8:54 am
That was a request from the project leader. I can also build a drop down that would allow multiple selections. If that is easier but I still need help with that expression as well. Thanks for helping
April 1, 2016 at 8:59 am
That is a much better look. The drop down with multiple values gives them what they want. Now the question is how do I create an expression that uses multiple selections.....Thanks again
April 1, 2016 at 9:13 am
mschaper (4/1/2016)
That is a much better look. The drop down with multiple values gives them what they want. Now the question is how do I create an expression that uses multiple selections.....Thanks again
Your query should look like this:
SELECT column_list
FROM TableOrView
WHERE filter_column IN ( @Parameter)
Of course, you need to modify as needed, it's just the main idea.
Since you used the option to allow multiple values, SSRS will take care of that.
If you're using a stored procedure, things might have to be different.
April 1, 2016 at 9:15 am
The value of a multi-value parameter is an array of the selected items.
You can use Array.IndexOf to test if a value is in the array, i.e. selected in the multi-value parameter.
Array.IndexOf returns the index of the value if it exists or -1 if it does not.
For example:
=Array.IndexOf(Parameters!MultiVal1.Value, "Cost Centre 1") > -1
Would return true if the value was selected in the param. You can use this on it's own or in a switch statement if required.
April 1, 2016 at 9:21 am
Spiff (4/1/2016)
The value of a multi-value parameter is an array of the selected items.You can use Array.IndexOf to test if a value is in the array, i.e. selected in the multi-value parameter.
Array.IndexOf returns the index of the value if it exists or -1 if it does not.
For example:
=Array.IndexOf(Parameters!MultiVal1.Value, "Cost Centre 1") > -1
Would return true if the value was selected in the param. You can use this on it's own or in a switch statement if required.
And if someone decides to add a new Cost Center, the report must change.
I definitively wouldn't like to need to change if the option to make it dynamic is available.
April 1, 2016 at 12:40 pm
I've tried to set up a filter for my report based on a multi value parameter (REPOERT_FILTER). This is what I came up with:
Expression (Text): =iif(Array.IndexOf(Parameters!REPORT_FILTER.Value, 0) > -1, mid(Fields!Adjusting_GPBR.Value,1,2),"")
Operator: =
Value: @GRP
The multi value parameter looks like this:
Select All
Home Country Value = 1
Home Branch Value = 2
Other Country Value = 3
Other Branch Value = 4
Shared Services Value = 5
Possible values in the "Adjusting_GPBR" field = F599, 7699, V399, etc
The user selects F5 in the GRP parameter. In the multi value parameter, they select "Home Country"
what I'm trying to do is only display records that start with F5 in the Adjusting_GPBR field
When I run the report, without this filter, it returns lots of records with F599. When I run it with the filter, I do not get any records. I assume the expression is not correct but any help would be greatly appreciated. Thank You
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply