Thursday, March 29, 2012

Passing two Multi-Value report parameters to a MDX Query...

When we write a MDX Query to filter data, we may want to filter the data set using two multi value report parameters. As an example we may want to get the Sales value for multiple Branches and for multiple Sales Persons.
In such kind of a situation the MDX Query will be as below.

SELECT NON EMPTY { [Measures].[Sales]} ON COLUMNS, NON EMPTY { ([DimBranch].[Branch].ALLMEMBERS * [DimEmployee].[SalesPerson].ALLMEMBERS) } ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!Branch.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!SalesPerson.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM [Sales]))

Branch and SalesPerson are the Multi-Valued report parameters and Sales is the Cube.

Also when you set the Value field for those parameters, set it as the field taken from considering UNIQUENAME. Otherwise if you have the parameter value as a string which has spaces, then it will gives an error since it only consider the first word before the space.
As an example if you send the branch value as Head Office, it only take the Head part. Therefore you have to pass the UNIQUENAME.

As an example you can take the data set for the Branch using below query.

WITH MEMBER [Measures].[ParameterCaption] AS [DimBranch].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [IDimBranch].CURRENTMEMBER.UNIQUENAME
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS , [DimBranch].[Branch].ALLMEMBERS ON ROWS FROM [Sales]

Then you can use the ParameterCaption as the Label field and ParameterValue as the Value field for the Branch parameter.

1 comment:

  1. function expects a tuple set expression for the 1 argument. A string or numeric expression was used. when i am designing the mdx query on my ssrs report
    here is my MDX Query

    SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount], [Measures].[Freight], [Measures].[Extended Amount] } ON COLUMNS,
    NON EMPTY { ([Customer].[Customer Key].[Customer Key].ALLMEMBERS * [Currency].[Currency Key].[Currency Key].ALLMEMBERS * [Currency].[Currency Name].[Currency Name].ALLMEMBERS * [Customer].[Gender].[Gender].ALLMEMBERS ) }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
    FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!CurrencyCurrencyKey.Value,",") & "}') ) ON COLUMNS FROM [AWDW]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
    any idea?????

    ReplyDelete