CODEWITHSUNDEEP
joinssrs-2008ssasmdx
PrasadD
PrasadD

Reputation: 87

How to join two completely different cubes in MDX?

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

Upvotes: 3

Views: 1818

Answers (2)

Strawberryshrub
Strawberryshrub

Reputation: 3389

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://learn.microsoft.com/de-de/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-2017

Upvotes: 6

MoazRub
MoazRub

Reputation: 2911

Welcome to MDX, I guess you are looking for SQL Join equivalent in MDX. However MDX doesnt support joins like SQL. One way to solve the issue is to retrieve the data via ADOMD into data cells and then join them in memory. However I would like to know the scenario, which requires you to join results from two cubes.

Upvotes: 3

Related Questions