Reference: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
In MDX, when two or more members of the same set are placed on a slicer, OR logic is automatically applied. For example, if you have two members (e.g. sizes ‘Small’ and ‘Large’) on a slicer, the resulting dataset will contain data where the first, second or both members occurred in the associated fact table. Similarly, a tuple formed by members of multiple hierarchies (e.g. size ‘Small’ and color ‘Red’) will only return results that have data associated to both members; AND logic is applied in this instance.
To summarize, in MDX
- Sets imply logical OR
- Tuples imply logical AND
What if you need to apply OR logic to members of different hierarchies (e.g. tuples)?
In this example, we will combine the results of the following two MDX queries into a single query by implementing OR logic between members of the two hierarchies.
The first MDX query returns the quantity shipped for orders handled by Inside Sales Reps, broken down by fiscal month of year:
SELECT {[Measures].[Quantity Shipped]} ON COLUMNS, NON EMPTY { [GL Date].[Fiscal Month Of Year].MEMBERS } ON ROWS
FROM [MyCube]
WHERE [Sale Source].[ISR].&[Y]
The second MDX query returns the quantity shipped for online orders, again broken down by fiscal month of year:
SELECT {[Measures].[Quantity Shipped]} ON COLUMNS, NON EMPTY { [GL Date].[Fiscal Month Of Year].MEMBERS } ON ROWS
FROM [MyCube]
WHERE [Customer].[Market].&[Online]
To combine the result of the above queries in a single MDX query, we must first take the two members ([Sale Source].[ISR].&[Y] and [Customer].[Market].&[Online]) and convert them into tuples of the same dimensionality. To accomplish this, we combine each member with the ‘All’ or root member of the other dimension as shown below (in this case, we have two tuples):
([Sale Source].[ISR].&[Y], [Customers].[Market].[All])
([Sale Source].[ISR].[All],[Customers].[Market].&[Online])
We then combine these tuples into a set (the tuples are separated by a comma and surrounded by curly brackets that define the set). The set is located in the WHERE clause of our new query:
SELECT {[Measures].[Quantity Shipped]} ON COLUMNS, NON EMPTY { [GL Date].[Fiscal Month Of Year].MEMBERS } ON ROWS
FROM [MyCube]
WHERE { ([Sale Source].[ISR].&[Y], [Customers].[Market].[All]), ([Sale Source].[ISR].[All],[Customers].[Market].&[Online])}
In this case, the [Quantity Shipped] value from our combined query is the sum of the [Quantity Shipped] values from the original two MDX queries since there in no overlap in each queries resulting output.
