Solved

Evaluating a multi-value SSRS parameter with varying count

Posted on 2016-10-19
3
27 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 50

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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