Multi Value List parameter in MDX

[dudinatrix]

I'm trying to figure out how to implement a Multi Value List (or Multi Selection Button) MDX parameter in Report Designer 3.7. I managed to get it working with a single value only. Once I try using a multi value parameter it breaks.

Here is the MDX I'm trying to parameterize:


select NON EMPTY Crossjoin({[Measures].[Sales]}, {[TimeYTD].[2010], [TimeYTD].[2011]}) ON COLUMNS,
NON EMPTY Crossjoin({[Customer.Customer Rep (AR)].[#null].[RA], [Customer.Customer Rep (AR)].[#null].[RAT]}, {[Time.Time (Month Axis)].[January], [Time.Time (Month Axis)].[February], [Time.Time (Month Axis)].[March], [Time.Time (Month Axis)].[April], [Time.Time (Month Axis)].[May], [Time.Time (Month Axis)].[June], [Time.Time (Month Axis)].[July], [Time.Time (Month Axis)].[August], [Time.Time (Month Axis)].[September], [Time.Time (Month Axis)].[October], [Time.Time (Month Axis)].[November], [Time.Time (Month Axis)].[December]}) ON ROWS
from [BookedSales]
where {[Order.Order Sale Type].[C], [Order.Order Sale Type].[K], [Order.Order Sale Type].[O], [Order.Order Sale Type].[R], [Order.Order Sale Type].[S]}


Specifically I want to parameterize the where clause. I'm trying to make a list of all the options (C, K, O, R, S, etc.) so the user can select as many as they'd like to include.

I have the SINGLE value parameter working when I use this MDX. Parameter is called "SELECT_SALE_TYPE", type String. The value is just a single character, such as "C". This works fine:

select NON EMPTY Crossjoin({[Measures].[Sales]}, {[TimeYTD].[2010], [TimeYTD].[2011]}) ON COLUMNS,
NON EMPTY Crossjoin({[Customer.Customer Rep (AR)].[#null].[RA], [Customer.Customer Rep (AR)].[#null].[RAT]}, {[Time.Time (Month Axis)].[January], [Time.Time (Month Axis)].[February], [Time.Time (Month Axis)].[March], [Time.Time (Month Axis)].[April], [Time.Time (Month Axis)].[May], [Time.Time (Month Axis)].[June], [Time.Time (Month Axis)].[July], [Time.Time (Month Axis)].[August], [Time.Time (Month Axis)].[September], [Time.Time (Month Axis)].[October], [Time.Time (Month Axis)].[November], [Time.Time (Month Axis)].[December]}) ON ROWS
from [BookedSales]
where {[Order.Order Sale Type].[${SELECT_SALE_TYPE}]}

How can I convert this to support the Multi Value List or Multi Selection Button? I'm just learning MDX so maybe I'm stuck thinking of it in terms of SQL.. any help would be appreciated.

[dudinatrix]

I figured this out, posting the solution here for anyone else who runs into this issue. Credit to this post (http://forums.pentaho.com/showthread.php?73316-Generic-mdx-columns&p=241118#post241118) for finally leading me in the right direction.

Here's what I did:

1.) Create a JDBC data set to query the parameter value selection list. Here I concatenated the appropriate MDX syntax for each typecode which will be used as the parameter value. I also included the second (original) field so I have a clean value to put in as the display name for each option.

SELECT
DISTINCT CONCAT("[Order.Order Sale Type].[",sale_type_code,"]") as typecodes,
sale_type_code
FROM
`dim_orders`
Which produces something along the lines of:


typecodes sale_type_code
[Order.Order Sale Type].[C] C
[Order.Order Sale Type].[K] K
[Order.Order Sale Type].[O] O
[Order.Order Sale Type].[R] R
[Order.Order Sale Type].[S] S


2.) Create a report parameter: mySaleType
* Type: OBJECT
* Display Type: Multi Selection Button
* Query: Use above SQL query
* Value: typecodes
* Display Name: sale_type_code

3.) Create a report parameter: mySaleTypeArray
* Type: STRING
* Post-Processing Formula: ="{" & CSVTEXT([mySaleType];false(); ",") & "}"
* Hidden

4.) Update the MDX query. My final MDX query looks like this:

WITH SET [saleTypes] AS ${mySaleTypeArray}
select NON EMPTY Crossjoin({[Measures].[Sales]}, {[TimeYTD].[2010], [TimeYTD].[2011]}) ON COLUMNS,
NON EMPTY Crossjoin({[Customer.Customer Rep (AR)].[#null].[RA], [Customer.Customer Rep (AR)].[#null].[RAT]}, {[Time.Time (Month Axis)].[January], [Time.Time (Month Axis)].[February], [Time.Time (Month Axis)].[March], [Time.Time (Month Axis)].[April], [Time.Time (Month Axis)].[May], [Time.Time (Month Axis)].[June], [Time.Time (Month Axis)].[July], [Time.Time (Month Axis)].[August], [Time.Time (Month Axis)].[September], [Time.Time (Month Axis)].[October], [Time.Time (Month Axis)].[November], [Time.Time (Month Axis)].[December]}) ON ROWS
from [BookedSales]
where
{[saleTypes]}



Both parameters are necessary here. The mySaleType parameter generates an OBJECT containing all of the selected values in proper MDX syntax. Then the mySaleTypeArray parameter converts the object to a string. This is used to define a SET in the MDX, which is referenced in the WHERE to filter the list as expected.

Hope someone else finds this useful.

[nirmal baven]

Thanks a lot dudinatrix becoz it helped me . One more thing in the report how we can display the result as per the user selection. One more thing how the dynamic increase in the column can be achieved. I want to know how the data is displayed in the report, I used the formula editor for getting result but its throughing only one result for all the columns. Please help me i need a reply in urgent.