Solved

Evaluating a multi-value SSRS parameter with varying count

Posted on 2016-10-19
3
20 Views
Last Modified: 2016-10-26
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.
0
Comment
Question by:jdallen75
  • 2
3 Comments
 

Accepted Solution

by:
jdallen75 earned 0 total points
ID: 41851354
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
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41853647
Just chose your own comment as solution so this question can be closed and archived.
0
 

Author Closing Comment

by:jdallen75
ID: 41859978
Found a solution that works shortly thereafter
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now