How to join two completely different cubes in MDX?

Question

I want to join two completely different cubes in mdx (I am using MS SSRS 2008). I am really new to mdx and I have no idea how to do it. I want to join by SKU if possible. Can any body please tell me how to do it?

mdx Query 1

 SELECT NON EMPTY { [Measures].[Sales], [Measures].[Quantity] } ON COLUMNS, 
        NON EMPTY { ([Date YMD].[Day].ALLMEMBERS * 
                     [Regions And Stores].[Store Name].[Store Name].ALLMEMBERS * 
                     [Products].[Products].ALLMEMBERS * 
                     [SKU].[SKU].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
 FROM [Super] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

mdx Query 2

SELECT NON EMPTY { [Measures].[Quantity] } ON COLUMNS, 
       NON EMPTY { ([Store Name].[Store Name].ALLMEMBERS * 
                     [Products].[Products].ALLMEMBERS * 
                     [SKU].[SKU].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [Inventory Activity] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Any help will highly appreciated.

Thank you

Strawberryshrub Strawberryshrub · Accepted Answer · 2019-01-16T06:18:31

In SSRS you need to load two Datasets in your report and join them in the tablix. For example:

Load Dataset1 and Dataset2 into your report, with the column ID which links Dataset1 to Dataset2. Then put a tablix in your report. Display Dataset1 in your tablix. Now add a new column to your tablix and add the following expression:

=Lookup(Fields!Dataset1ID.Value, Fields!Dataset2ID.Value, Fields!SalesAmount.Value, "Dataset2")

The expression works as follows: - First argument is the foreign key column from Dataset1

Second argument is corrspondending key column from Dataset2
Third argument is the column you want to display in the tablix which is from Dataset2
Forth argument is the name from the dataset you want to join with (Dataset2)

Here is the reference for the Lookup() function: https://docs.microsoft.com/de-de/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-2017

How to join two completely different cubes in MDX?

2 Answers