Solved

Evaluating a multi-value SSRS parameter with varying count

Posted on 2016-10-19
3
25 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 49

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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