November 28, 2012 at 10:25 am
Hi all,
I need to list all the values of a multivalue ("UnderwriterNames")parameter in a sentence in a report, which basically means in this case concatenating all the selected values with comma and space separators and replacing the last separator with "and". For example, the parameter has three available values "Tom","Dick","Harry". If all three are selected, the expression I am using will return "Tom, Dick and Harry". I can achieve this without problem.
The problem comes when I try to allow for the possibility that the user only selects one option (the parameter cannot be blank or null). For some reason, the False part of the following expression always evaluates to an #Error in the placeholder. Any ideas why?
=IIF
(Parameters!UnderwriterNames.Count > 1
,(strConv
(
LEFT
(
JOIN(Parameters!UnderwriterNames.Value,", ")
,InStrRev(JOIN(Parameters!UnderwriterNames.Value,", "),",") - 1
)
,vbStrConv.ProperCase
)
& " and "
& strConv
(
MID
(
JOIN(Parameters!UnderwriterNames.Value,", ")
,InStrRev(JOIN(Parameters!UnderwriterNames.Value,", "),",") + 2
,LEN(JOIN(Parameters!UnderwriterNames.Value,", "))
)
,vbStrConv.ProperCase
)
)
,Parameters!UnderwriterNames.Value(0)
)
If I just enter "=Parameters!UnderwriterNames.Value(0)" as the expression, no error is returned and I can see nothing wrong with the syntax in the rest of the expression that would cause this one line to error whenever it is called.
This is beginning to drive me up the wall... Any help much appreciated!
November 29, 2012 at 3:10 am
Damn! I'm an idiot (at least... I think I am!) .
This error could be caused by the fact that SSRS evaluates both sides of an IIF statement before choosing which to display... When the multivalue parameter has more than one value, then both sides of the IIF will return a value. If there is only one value in the parameter then this
LEFT(JOIN(Parameters!UnderwriterNames.Value,", "),InStrRev(JOIN(Parameters!UnderwriterNames.Value,", "),",") - 1)
will evaluate as (for instance) LEFT("Tom",-1) and cause the #Error which is returned.
so far so good... so how do I avoid that?
November 29, 2012 at 3:33 am
OK, so the answer was to add an ABS() around the length part of the LEFT() command. This means that the length will never cause an error - if this side of the IIF is relevant then the ABS will make no difference and if it is not relevant, it won't even be returned. There are a few other changes to simplify things a bit, in the course of searching, but the ABS() is what done the fixin'
=strConv(
IIF(
Parameters!UnderwriterNames.Count > 1
,LEFT(JOIN(Parameters!UnderwriterNames.Value,", "),ABS(InStrRev(JOIN(Parameters!UnderwriterNames.Value,", "),",") -1))
& " & " & MID(JOIN(Parameters!UnderwriterNames.Value,", "),InStrRev(JOIN(Parameters!UnderwriterNames.Value,", "),",") + 2,LEN(JOIN(Parameters!UnderwriterNames.Value,", ")))
,Parameters!UnderwriterNames.Value(0)
)
,vbStrConv.ProperCase)
well there we go -- answered my own question but maybe it'll be useful to someone someday..!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply