CODEWITHSUNDEEP
ssasmdx
giterdone
giterdone

Reputation: 113

MDX: Combining output for different criteria

I am new to MDX queries, and working in SSAS. I have two queries that are working suitably, but I want their output combined in a single result. The two queries differ in the cities selected by their where clauses, but they both come from the same cube and use the same measure.

A_to_B: Supplier 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 for the output of these queries to be produced side-by-side by Product, like this? In SQL I would have used a FULL OUTER JOIN, but I can't figure out the equivalent in MDX.

|          | A_to_B | B_to_A |
| ProductA |     10 |      2 |
| ProductB |    100 |      0 |
| ProductC |      0 |     99 |

Upvotes: 3

Views: 645

Answers (3)

giterdone
giterdone

Reputation: 113

Building on the others, here is one that replaces nulls with zeros in the output.

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]

Upvotes: 0

Danylo Korostil
Danylo Korostil

Reputation: 1484

Simply move you Where statements to 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 may create calculated members in order to unite 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])

Upvotes: 3

whytheq
whytheq

Reputation: 35567

I tested this script and it throws 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:

Query (5, 7) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

This is because you need to make either side of the cross-join specifically a set - without extra brackets it does not know this and throws an exception.

So this is the "perfect" 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 prefer to move the measure to the WHERE clause and also get rid of some of the redundant 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];

Translated 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];

Upvotes: 1

Related Questions