Solved

Evaluating a multi-value SSRS parameter with varying count

Posted on 2016-10-19
3
30 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

624 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