February 18, 2015 at 4:21 pm
I have a parameter MonthlySet having INT datatype with possible values 1,2, or 3 and I have a report with 3 tablix objects:
How to write an expression to display the first tablix object named "CurrentYr_0Monthly" based on the following 3 conditions:
if 1 OR 1 & 2 OR 1 & 2 & 3 are checked for the Parameter MonthlySet THEN display CurrentYr_0Monthly tablix object
The rules for the other two objects.
2 conditions: IF 1 & 2 OR 1 & 2 & 3 are checked THEN display CurrentYr_1Monthly
1 condition: IF 1 & 2 & 3 are checked THEN display CurrentYr_2Monthly
February 18, 2015 at 7:48 pm
Looks like the simple answer to this is to use 3 separate Boolean parameters instead of one Multi-valued integer.
Just to test my theory, I tried creating a multi-valued parameter and referring to Parameters!MVP(2).Value, it failed.
=Parameters!MVPInteger.Value(0) & " " & Parameters!MVPInteger.Value(1) & " " & Parameters!MVPInteger.Value(2)
If any has no value, then it fails, because it will be dimensioned for each of the true values. (Weird as hell, in my opinion!)
If you use 3 separate booleans, it works a champ.
February 19, 2015 at 1:05 am
...
--Quote me
February 19, 2015 at 1:09 am
Hi pietlinden,
pietlinden (2/18/2015)
Looks like the simple answer to this is to use 3 separate Boolean parameters instead of one Multi-valued integer.Just to test my theory, I tried creating a multi-valued parameter and referring to Parameters!MVP(2).Value, it failed.
=Parameters!MVPInteger.Value(0) & " " & Parameters!MVPInteger.Value(1) & " " & Parameters!MVPInteger.Value(2)
If any has no value, then it fails, because it will be dimensioned for each of the true values. (Weird as hell, in my opinion!)
If you use 3 separate booleans, it works a champ.
I verified my MonthlySet parameter Allowed Mulitple Values = yes. Left it as Integer and specified on Available Values tab the label/value pairs 0:0,1:1,2:2.
In Visibility Tab of 'CurrentYr_0Monthly' Tablix Object>Tablix Properties I used same statement as yours:
=Parameters!MonthlySet.Value(0) & "" & Parameters!MonthlySet.Value(1)&""&Parameters!MonthlySet.Value(2)
Error:
The Hidden expression for the tablix 'CurrentYr_0Monthly' contains an error: Index was outside the bounds of the array.
What do you mean "If you use 3 separate Booleans"?
Do you mean like this?
=iif(Parameters!MonthlySet.Value(0)=1 & " " & Parameters!MonthlySet.Value(1)=2 &" "& Parameters!MonthlySet.Value(2)=3, false, true)
any other suggestions for what I may be doing wrong?
February 19, 2015 at 1:10 am
I tried this for first tablix:
=iif(Parameters!MonthlySet.Value(1)=1, false, true)
this for second tablix:
=iif(Parameters!MonthlySet.Value(1)=1 & " " & Parameters!MonthlySet.Value(2)=2, false, true)
this for third:
=iif(Parameters!MonthlySet.Value(1)=1 & " " & Parameters!MonthlySet.Value(2)=2 &" "& Parameters!MonthlySet.Value(3)=3, false, true)
error is same:
The Hidden expression for the tablix "CurrentYr_0Monthly" contains an error: Index was outside the bounds of the array.
Yet, the integer values I assigned to labels 1, 2, 3 are 1,2,3 (not 0,1,2)
I get same error for other two tablix objects.
February 19, 2015 at 1:52 am
What I found is that multi-valued parameters just plain don't work the way I expected them to. They work fine if you're stuffing them in an IN() clause in your filter against your dataset, but if you're doing them for control flow, they work in unexpected ways.
Once I started playing with SSRS and using a multi-valued parameter like yours, my filtering went sideways. If I don't check one of the 3 values, it doesn't get interpreted as "this value, parameter(x), is false". SSRS just doesn't add that parameter to the collection. (so if you only check two of the possible values, the Count property will show 2, not 3.)
For fun, add a textbox to your report, and set it's source to:
=JOIN(Parameters!MVPInteger.Value," OR ")
or try
=Parameters!MVParamName.Count
That will return the number of parameters that are CHECKED, so in a roundabout way, it's saying that UNCHECKED != FALSE.
The only way I could get SSRS to interpret the parameters as T/F like I intended was to have 3 separate parameters defined as booleans. Maybe see if HappyCat59 has some ideas, because that's the best I could come up with.
February 19, 2015 at 7:11 am
Yooohoooo, HappyCat59, come in HappyCat59 , SSRS, ahem multi valued parameters for conditional settings of tablix visibility....HappyCat59 !
February 19, 2015 at 7:30 am
The only way I could get SSRS to interpret the parameters as T/F like I intended was to have 3 separate parameters defined as booleans. Maybe see if HappyCat59 has some ideas, because that's the best I could come up with.
pietlinden, oh I get what you're advising! Thank you for coming up with an alternative which will also get me desired functionality.
In that case I will do 2 Boolean parameters: MonthlySet2, MonthlySet3 with MonthlySet1 set to always show.
Like this?
The expressions on visibility tabs for tablix 2 and 3 'Set expression for: Hidden' are:
=iif(Parameters!MonthlySet2.Value=1 , false, true)
=iif(Parameters!MonthlySet3.Value=1, false, true)
At this point MonthlySet1 is showing....but whether I select radio button True or False for Boolean params MonthlySet2 and MonthlySet3...the associated tablix 2 and 3 are not showing.....
at this point I know I'm close (thanks) and if you have idea about why those two expressions not eliciting response, let me know. I will post as soon as I figure out.
(I would like to know of a drop down menu way to control tablix items, too, if anyone knows)
February 19, 2015 at 8:04 am
am not able to bust past cause of even the Boolean parameter + expression not working for controlling visibility of a single tablix item.
expression for default state of hidden in the visibility tab of tablix object:
iif (Parameters!ShowTablix.Value= 1, false, true) which I think translates to if radio dial 'true' is selected then do not hide the tablix.
but this doesn't work. No error, but tablixes just don't repond.
February 19, 2015 at 2:20 pm
adjustment to expression that I made and worked:
iif(parameter = 0, true, false)
February 19, 2015 at 3:51 pm
Cool! Thanks for the report.
July 6, 2017 at 4:35 pm
Had Same Problem I worked through with a junior developer. As usual with SSRS the desired result was not what you would normally expect to code "logically.
Using the JOIN operator in SSRS was the key for us.
Like you we wanted to hide or show tablix objects depending on a parameter choice/choices.
Our Parameter Choices were Daily, Monthly, and Yearly.
If the Visibility property of our Tablix object we "Show or Hide Based On an Expression".
And our expression was as follows for our "Daily" Tablix object:
=Iif (JOIN(Parameters!DisplayTablix.Value,"") LIKE "*Daily*", False, True)
And our expression was as follows for our "Monthly" Tablix object:
=Iif (JOIN(Parameters!DisplayTablix.Value,"") LIKE "*Monthly*", False, True)
And finally the Year Tablix:
=Iif (JOIN(Parameters!DisplayTablix.Value,"") LIKE "*Yearly*", False, True)
The JOIN Operator will join the selected values with the second parameter being the separator. We chose no separator in this example.
When all three parameter values were selected the 'joined' value of the MultiSelect parameter was 'DailyMonthlyYearly'.
Finally, unlike SQL Syntax the wildcard for SSRS is the asterisk sign '*'.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply