MDX: Combining Results for Different Criteria
I am new to MDX queries and am working in SSAS. I have two queries that work as expected, but I want their result to be combined into a single result. The two queries differ in the cities selected by their proposals, but they both come from the same cube and use the same measure.
A_to_B : supply city A to consumer city B
SELECT { [Measures].[Quantity - Transactions] } ON COLUMNS,
{ [Tb Product].[Name].[Name].ALLMEMBERS } ON ROWS
FROM [Cube]
WHERE ([Tb Supplier].[City].&[A],
[Tb Consumer].[City].&[B])
B_to_A : supplier city B to consumer city A
SELECT { [Measures].[Quantity - Transactions] } ON COLUMNS,
{ [Tb Product].[Name].[Name].ALLMEMBERS } ON ROWS
FROM [Cube]
WHERE ([Tb Supplier].[City].&[B],
[Tb Consumer].[City].&[A])
Is there a way to have these queries output side-by-side with Product like this? In SQL, I would use FULL OUTER JOIN, but I cannot figure out the equivalent in MDX.
| | A_to_B | B_to_A | | ProductA | 10 | 2 | | ProductB | 100 | 0 | | ProductC | 0 | 99 |
source to share
Just move you. Where are the instructions in the columns:
Select
{[Measures].[Quantity - Transactions]} *
{
([Tb Supplier].[City].&[B], [Tb Consumer].[City].&[A]),
([Tb Supplier].[City].&[A], [Tb Consumer].[City].&[B])
} on 0,
{[Tb Product].[Name].[Name].AllMembers} on 1
From [Cube]
You can create design members to combine two tuples:
With
Member [Tb Supplier].[City].[B2A] as
Aggregate([Tb Supplier].[City].&[B], [Tb Consumer].[City].&[A])
Member [Tb Supplier].[City].[A2B] as
Aggregate([Tb Supplier].[City].&[A], [Tb Consumer].[City].&[B])
Select
{[Tb Supplier].[City].[A2B],[Tb Supplier].[City].[B2A]} on 0
From [Cube]
Where ([Measures].[Quantity - Transactions])
source to share
I tested this script and it was throwing an exception:
SELECT
{[Measures].[Internet Sales Amount]}
*
{
{[Geography].[Geography].[Country].&[United States] * [Product].[Category].&[1]}
,{[Geography].[Geography].[Country].&[France] * [Product].[Category].&[3]}
} ON 0
,{[Date].[Calendar].[Date].&[20070801]} ON 1
FROM [Adventure Works];
This message:
Request (5, 7) The function expects an expression of a set of tuples for 1 argument. A string or numeric expression was used.
This is because you need to make both sides of the cross join a specific set - without extra parentheses, it doesn't know this and throws an exception.
So this is the "ideal" version of the script:
SELECT
{[Measures].[Internet Sales Amount]}
*
{
{[Geography].[Geography].[Country].&[United States]} * {[Product].[Category].&[1]}
,{[Geography].[Geography].[Country].&[France]} * {[Product].[Category].&[3]}
} ON 0
,{[Date].[Calendar].[Date].&[20070801]} ON 1
FROM [Adventure Works];
I'd rather move the measure to the WHERE clause, and also get rid of some of the redundant curly braces:
SELECT
{
{[Geography].[Geography].[Country].&[United States]}
*
{[Product].[Category].&[1]}
,
{[Geography].[Geography].[Country].&[France]}
*
{[Product].[Category].&[3]}
} ON 0
,[Date].[Calendar].[Date].&[20070801] ON 1
FROM [Adventure Works]
WHERE
[Measures].[Internet Sales Amount];
Transferred to your cube:
SELECT
{
{[Tb Supplier].[City].&[B]} * {[Tb Consumer].[City].&[A]}
,
{[Tb Supplier].[City].&[A]} * {[Tb Consumer].[City].&[B]}
} ON 0
,[Tb Product].[Name].[Name].ALLMEMBERS ON 1
FROM [Cube]
WHERE
[Measures].[Quantity - Transactions];
source to share
Based on the others, zeros followed by zeros in the output can be found here.
With
Member [Tb Supplier].[City].[B2A] as
Aggregate([Tb Supplier].[City].&[B], [Tb Consumer].[City].&[A])
Member [Tb Supplier].[City].[A2B] as
Aggregate([Tb Supplier].[City].&[A], [Tb Consumer].[City].&[B])
Member [Measures].[Quantity_Transactions] as
Iif( IsEmpty( [Measures].[Quantity - Transactions] ),
0,
[Measures].[Quantity - Transactions] )
SELECT
{ [Measures].[Quantity_Transactions] } *
{ [Tb Supplier].[City].[B2A],
[Tb Supplier].[City].[A2B] } on COLUMNS
, [Tb Product].[Name].[Name].ALLMEMBERS ON ROWS
FROM [Cube]
source to share