locked
MDX Query To Fetch Columns From Two Different Dimensions RRS feed

  • Question

  • Hi All,

    I have below mention two dimension tables:

    Country

    Country Country_Description REGION_CODE
    BEI Beijing ASI
    BOM Mumbai ASI
    HKG Hong Kong ASI
    SHA Shanghai ASI
    SIN Singapore ASI
    TOK Tokyo ASI


    Product_Balance

    Branch_Code Product_ID A-Balance B-Balance
    BEI 10312100 56528.07 56528.07
    BEI 12201100 15000 15000
    BOM 10312100 63356.42 63356.42
    BOM 11209100 14051.82 20.98
    BOM 12201100 1178.72 1178.72
    BOM 16100000 316.89 316.89
    BOM 12560000 48907.62 48907.62
    BOM 10200000 742.73 742.73
    BOM 16200000 1474.93 1474.93
    HK 11252100 193132.22 0
    HK 19176106 250000 0
    HK 19176111 16025.93 0
    HK 11166100 823256.57 0
    HK 19176105 962942.46 0


    In SQL Reporting Services I am providing a drop down menu where I would like provide option to select Country (from Country) based on which report will provide balances (from Product_Balance table).

    I want to write a MDX query to display "Country_Description" as Display Caption and pass "Branch_Code". Below is the query which I wrote but its giving cartisian product.


    Howsoever I am looking for something like below


    Not sure how to achieve this. Please suggest me a way.

    Regards


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++




    • Edited by GURSETHI Wednesday, December 21, 2016 7:13 AM correction
    Wednesday, December 21, 2016 4:40 AM

Answers

  • Hi,


    Well this is what I wrote and it resolved my problem

    SELECT
        NON EMPTY
            { [Measures].[SumCashFlowAmt]} ON COLUMNS,
        NON EMPTY
            { ([Branch].[Name].[Name].ALLMEMBERS,[Branch].[Branch_Description].[Branch_Description].AllMembers)  } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
    FROM
    [CashFlow_Summary]


    Later in Reporting Services I used Branch_Description & Nameas below

    Regards

    Gurpreet Sethi



    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++




    • Marked as answer by GURSETHI Wednesday, January 18, 2017 3:59 AM
    • Edited by GURSETHI Wednesday, January 18, 2017 4:10 AM Added screenshots
    Wednesday, January 18, 2017 3:58 AM

All replies

  • Hi GURSETHI,

    Thanks for your question.

    In order to get Branch_code which exist in Region_code ASI, You may use Exist function.

    Your MDX query may like below:

    WITH

    MEMBER [Measures].[ParameterValue]

    AS [Product_Balance].[Branch_code].CURRENTMEMBER.UNIQUENAME

    MEMBER [Measures].[ParameterCaption]

    AS [Product_Balance].[Branch_code].MEMBER_CAPTION

    SELECT

    {[Measures].[ParameterCaption],[Measures].[ParameterValue]} ON COLUMNS,

    {EXISTS([Product_Balance].[Branch_code].[Branch_code].MEMBERS,[Country].[Region_code].[ASI])}

    ON ROWS

    FROM [CsahFlow];

     

    See my following MDX query:

    WITH

    MEMBER [Measures].[ParameterValue]

    AS [Date].[Calendar].CURRENTMEMBER.UNIQUENAME

    MEMBER [Measures].[ParameterCaption]

    AS[Date].[Calendar].MEMBER_CAPTION

    SELECT

    {[Measures].[ParameterCaption],[Measures].[ParameterValue]} ON COLUMNS,

    {EXISTS([Date].[Calendar].[Date].MEMBERS,[Date].[Calendar Week of Year].&[1])}

    ON ROWS

    FROM [Adventure Works]

    where [Date].[Calendar Year].&[2013]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, December 21, 2016 5:38 AM
  • Hi Wilson,

    Thanks for your reply. Further sorry I guess I attached wrong snapshot of expected values (which I have corrected). I am looking for 2 outputs

    Country_Description -> from -> Country dimension

    Branch_Code -> from -> Product_Balanace (in ths form of Member_Caption)

    Regards


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Wednesday, December 21, 2016 7:15 AM
  • Hi GURSETHI,

    In this scenario, you may try sample MDX below:

    WITH 
    MEMBER [Measures].[ParameterValue]
    AS membertostr([Country].[COUNTRY_CODE].currentmember)
    MEMBER [Measures].[ParameterCaption] 
    AS [Country].[Country_Description].currentmember.MEMBER_CAPTION
    SELECT
    {[Measures].[ParameterValue],[Measures].[ParameterCaption]} ON COLUMNS,
    {EXISTS([Country].[COUNTRY_CODE].[COUNTRY_CODE].members*[Country].[Country_Description].[Country_Description].members,[Country].[Region_code].[ASI])}
    ON ROWS 
    FROM [CsahFlow];


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, December 21, 2016 8:33 AM
  • Hi,


    Well this is what I wrote and it resolved my problem

    SELECT
        NON EMPTY
            { [Measures].[SumCashFlowAmt]} ON COLUMNS,
        NON EMPTY
            { ([Branch].[Name].[Name].ALLMEMBERS,[Branch].[Branch_Description].[Branch_Description].AllMembers)  } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
    FROM
    [CashFlow_Summary]


    Later in Reporting Services I used Branch_Description & Nameas below

    Regards

    Gurpreet Sethi



    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++




    • Marked as answer by GURSETHI Wednesday, January 18, 2017 3:59 AM
    • Edited by GURSETHI Wednesday, January 18, 2017 4:10 AM Added screenshots
    Wednesday, January 18, 2017 3:58 AM