Reporting Services, Mulit-Select Parms & Report Filters

Question

Post reply

Reporting Services, Mulit-Select Parms & Report Filters

Chris Schmidt

Say Hey Kid

Points: 686
More actions
March 17, 2008 at 3:50 pm

#184162

Hello
I have a complex Reporting Services Inventory Report containing multiple MDX statements and 15 parameters. Everything works great except for one last annoying thing........
Not every page of the report contains usable data so the users want to filter these pages.
I created a multi-select parameter for the "Deals" property at the leaf level of my product hierarchy. The members are Y,N, or All. Either it's a deal product or it's not.
We want to use this to filter the report. We can't do it in the MDX because we don't want to elminate any products as we roll up the hierarchy.
So, I added a filter to the Data Set which feeds my report. I want to include only the products they want to see. My calculated Member in my MDX returns either a Y or a N for each product. The user can select either a All, Y, or N from my multi-select parm.
My filter is:
Expression: =Rtrim(Ltrim(Fields!Deal.Value))
Operator: IN
Value: =join(Parameters!ProductDeals.Label,",")
I can see the N products when I run the report, but I get no results for either the Y products or the ALL selection. (I have confimred my test data).
It's almost as if it is doing the Value: =join(Parameters!ProductDeals.Label,",") using only my default parmater, but not the user selection.
Can anyone shed any light on this for me?
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Chris Schmidt Say Hey Kid Points: 686 More actions · Answer 1

I am replying to my own post.

Here's the deal....

Selections from a multi-select parameter are saved in an array.

If you want to filter on a multi-select parameter, you have to first join the elements of the array. This creates a string with delimiters you specify. You then need to split the string you created which puts the data elements in a list. Then use the "IN" operator in the filter.

=split(Join(Parameters!ProductDeals.Label,","),",")

Keep in mind that a filter in reporting Services is just like a where clause in sql and chooses items that are contained in a list. As opposed to the Filter() function in MDX which eliminates items if the expression is true......

Let me know if this helps anyone! I couldn't find any other posts on any other forums which dealt with this.

jorallen SSC Journeyman Points: 78 More actions · Answer 2

Thanks... I am new to reporting services and had to tackle this problem right off the bat. Worked lick a champ!!!!:w00t:

jorallen SSC Journeyman Points: 78 More actions · Answer 3

jorallen

SSC Journeyman

Points: 78

April 1, 2008 at 4:41 pm

#796935

sorry for the typo... "like a champ"

JackTheC Old Hand Points: 390 More actions · Answer 4

Hello Chris,

I have a matrix report with parameters which take multiple values. But when I apply the filter on the matrix it displays a blank report. My Filter syntax is very similar to yours. Any thoughts as to what might be missing? Did you do anything extra.

Thanks

Lance Roberts SSC Veteran Points: 250 More actions · Answer 5

This is exactly what I've been looking for, but with an array of integers in a "WHERE blob =" substatement.

Any ideas?

Chris Schmidt Say Hey Kid Points: 686 More actions · Answer 6

Hi JackTheC

I'd need to know more details to help debug your issue. But one idea would be to remove the filter for now, add a text box to display the results of your "Join and split" data and another to display your data element. This way you can see exactly what you are comparing.

Once the filter is back up, make sure you are using the "IN" operator in your filter.

Chris

Chris Schmidt Say Hey Kid Points: 686 More actions · Answer 7

Hi

Try "Where blob in (" rather than "Where blob = "

You are bringing a list into your sql

Lance Roberts SSC Veteran Points: 250 More actions · Answer 8

Actually, I did try it that way, and got some kind of type error (can't remember now which).

I may get back to trying it again, I've been pounding my head on this for three days,

and can't get any array-table thing to work quite right.

Chris Schmidt Say Hey Kid Points: 686 More actions · Answer 9

You are definitely passing a list to your where clause if you are doing the split/join with a multi parameter and you choose more than one parameter. that means that you need the "in" as opposed to an "=".

You are probably having issues with the ticks(') you need around your string data elements.... where blob in ('A','B'). Your sql probably looks something like "where blob in (A,B)" after the parameter is translated. You could build a SQL string using the parameter values and execute the string. Possibly you could concatenate the ticks after the split and before the join, but I'm not sure if that would work.

he wrote:

Actually, I did try it that way, and got some kind of type error (can't remember now which).

I may get back to trying it again, I've been pounding my head on this for three days,

and can't get any array-table thing to work quite right.

woo2gs SSC Rookie Points: 39 More actions · Answer 10

Can you give an example of the in this is my code below and it throws an error everytime:

=" SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY { ([Merchant Data].[Merchant Data].[Merchant Data].ALLMEMBERS * [Merchant Data].[Merchant Name].[Merchant Name].ALLMEMBERS * [Orig Date].[Date].[Date].ALLMEMBERS * [Debit Credit Dim].[Debit Credit Dim].[Debit Credit Dim].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ([Merchant Data].[Merchant Data].&[split(Join(Parameters!MerchantID.Label,","),",") ])ON COLUMNS FROM [Origination] )) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

End of Statement Error expected.

Your help would be greatly appreciated

woo2gs SSC Rookie Points: 39 More actions · Answer 11

Did you ever figure out this issue/ If so can you please post your soultion

Your help is greatly appreciated

Chris Schmidt Say Hey Kid Points: 686 More actions · Answer 12

Hi

I was on Vacation so it took me a few days to get back to you. The solution I posted was in regard to the filter in Reporting Services.

However,I see a couple of issues with the MDX code.

It looks like you are building an MDX string. You have to concatenate the "split/join" code outside of your MDX string.

"SELECT ([Merchant Data].[Merchant Data].&[" + split(Join(Parameters!MerchantID.Label,","),",") + " ])"

But, you also have the "&" which relates to a member key. I think you want to use a member list instead.

Hope this helps!

woo2gs SSC Rookie Points: 39 More actions · Answer 13

Thank you. I am new to MDX queries, but this is what I want to do:

select a, b, c from example where a in ('123',345','5678') standard sql

I don't know how to translate this to MDX. My users need the ability to input multiple values in a parameter; I can get this to work with single value parameters.

Any suggestions is greatly appreciated.

Thanks,

woo2gs SSC Rookie Points: 39 More actions · Answer 14

Thank you. I am new to MDX queries, but this is what I want to do:

select a, b, c from example where a in ('123',345','5678') standard sql

I don't know how to translate this to MDX. My users need the ability to input multiple values in a parameter; I can get this to work with single value parameters.

Any suggestions is greatly appreciated.

Thanks,