Evaluating a multi-value SSRS parameter with varying count

I have a multi-value, text report parameter called EngUnit, which gets its available and default values from a stored procedure dataset. Sometimes it will just return one EngUnit "MBTU", other times it will return both "MBTU" and another volumetric one like "Gallons". At this point I don't think there is an order to this set, but likely it's alphabetic. Important note: sometimes the volumetric one could be "MGal", so I can't guarantee MBTU's position, which depends on the other EngUnit.

I have 2 other datasets for the report that are exactly the same, other than the Eng_Unit input parameter to the underlying SP. For dataset A, which needs to be based on the MBTU data, I first was using this expression for the Eng_Unit:

=IIF(InStr(Parameters!EngUnit.Value(0), "MBTU")>0,
Parameters!EngUnit.Value(0),
Parameters!EngUnit.Value(1))

The problem with this is that if "MBTU" (or something containing MBTU) is the only EngUnit, then EngUnit.Value(1) does not exist, and I get an "index out of bounds" error.

I then tried to use this, but I still get the out of bounds error, presumably because it still attempts to evaluate EngUnit.Value(1):

=IIF(InStr(Parameters!EngUnit.Value(0), "MBTU")>0,
Parameters!EngUnit.Value(0),
IIF(Parameters!EngUnit.Count>1,
Parameters!EngUnit.Value(1),
"Garbage"))

....where "Garbage" simply returns an empty dataset, which is the intention. (I can't pass an empty string, or it returns ALL EngUnit data.)

Is my only option to evaluate the 'optional' EngUnit.Value(1) to use Code?

In case you're questioning the requirement, dataset B would effectively take care of the volumetric (non-MBTU) case for Eng_Unit:

=IIF(InStr(Parameters!EngUnit.Value(0), "MBTU")=0,
Parameters!EngUnit.Value(0),
IIF(Parameters!EngUnit.Count>1,
Parameters!EngUnit.Value(1),
"Garbage"))

Thanks.
jdallen75Asked:
Who is Participating?
 
jdallen75Author Commented:
I found a solution that works actually, at least in this case:

=IIF(InStr(Parameters!EngUnit.Value(0), "MBTU")>0,
Parameters!EngUnit.Value(0),
IIF(Parameters!EngUnit.Count>1,
Parameters!EngUnit.Value(Parameters!EngUnit.Count-1),
"Garbage"))

I'll leave this question posted in case it helps someone else out...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just chose your own comment as solution so this question can be closed and archived.
0
 
jdallen75Author Commented:
Found a solution that works shortly thereafter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.