March 17, 2008 at 3:50 pm
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
March 18, 2008 at 11:06 am
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.
April 1, 2008 at 4:40 pm
Thanks... I am new to reporting services and had to tackle this problem right off the bat. Worked lick a champ!!!!:w00t:
April 1, 2008 at 4:41 pm
sorry for the typo... "like a champ"
April 4, 2008 at 10:36 pm
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
April 9, 2008 at 12:36 am
This is exactly what I've been looking for, but with an array of integers in a "WHERE blob =" substatement.
Any ideas?
April 9, 2008 at 6:47 am
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
April 9, 2008 at 7:05 am
Hi
Try "Where blob in (" rather than "Where blob = "
You are bringing a list into your sql
April 9, 2008 at 10:20 am
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.
April 9, 2008 at 10:59 am
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.
September 4, 2008 at 4:51 pm
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
September 4, 2008 at 4:59 pm
Did you ever figure out this issue/ If so can you please post your soultion
Your help is greatly appreciated
September 8, 2008 at 9:37 am
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!
September 8, 2008 at 9:46 am
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,
September 8, 2008 at 9:55 am
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,
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply