Lookup function on a multi-value parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Lookup function on a multi-value parameter

Author

Topic

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-15 : 12:25:28

I have multi-value report parameter called MultiValue. When the report is called from a parent report, MultiValue can contain a list of values from the parent's dataset or a single value, "*". The idea is that, when the linked report runs, it filters the results basded on the contents of MuliValue. However, there are two choices:

1. When MultiValue contains a list of values from the dataset, it filters on those values
2. When the MultiValue contains "*", it returns all rows from the dataset.

Ideally, I want something like:

if Field!datasetField in MultiValue or MultiValue(0) = "*"
then return the row

I can't find an easy way to do this in an expression.

First of all, multi-value report parameters do not seem to have Contains properties or anything similar.

Second, the Lookup function doesn't seem to work if the "dataset" is a multi-value report parameter.

So...what's another way to tackle this problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-16 : 01:20:19

something like below should be filter expression

=Fields!YourField.value In =IIF(Parameters!MultivalueParam.value(0)="*",Fields!YourField.value,Join(Parameters!MultivalueParam.value))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-11-18 : 11:46:06

quote:
Originally posted by visakh16

something like below should be filter expression

=Fields!YourField.value In =IIF(Parameters!MultivalueParam.value(0)="*",Fields!YourField.value,Join(Parameters!MultivalueParam.value))

Cool!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-19 : 01:41:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources