How to check if value is in array or mv-param? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Analysis Server and Reporting Services (2005)

How to check if value is in array or mv-param?

Author

Topic

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-12-27 : 12:47:51

I'm working with a dataset something like this:

TypeID Sales($)
------ -------
1 123.45
1 47.98
2 9.21
3 87.23
3 99.88
4 123.43

And a multivalued parameter that lets the user select which TypeIDs specifically he wants to see:

ParamID ParamValue
1 Q1
2 Q2
3 Q3
4 Q4

And in my Report, I have data showing up something like this:

CountofAllSales: 6
SumOfAllSales: 491.18
CountofCustomSales: (count of sales with type specified in parameter)
SumOfCustomSales: (sum of sales with type specified in parameter)

The count and sum of custom sales should show -ONLY- the numbers from the TypeIDs selected in the multi-value parameter. But the CountAll and SumAll show everything, regardless. This is where I run into problems. I can't seem to find an "in" clause in the SSRS expressions. If the TypeID parameter was single value, I could write something like this

Expression for CountOfCustomSales:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value, 1, 0))

However, since its multi-valued, that won't work. You'd have to write something like:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(0), 1, 0)) +
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(1), 1, 0)) +
....
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(length), 1, 0))

And obviously this doesn't work when you don't know exactly how many elements are going to be selected.

What would be ideal would be something like an "in" clause, but I can't find any such functionality or think how to write my own function:
=SUM(iif(Fields!TypeID.Value in
Parameters!TypeID.Values, 1, 0))

Short of modifying the StoredProc itself (and for me, that means red tape. :( :( ) can anyone think of a way to count/sum only the values specified in an MVP??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 14:00:03

One method to deal with this is to create a new dataset which retrieves the same data and having a filter on this parameter and i think you can use In inside filter expression window and compare like this.
Fields!TypeID.Value In Join(Parameters!TypeID.Value)

and this should filter the data based on selected parameter values.

Inside report you can write expressions as

COUNT(fieldname,<new data set name>)

i guess this will provide you with reqd result.Let me know how you get on with this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources