none
SSAS USE SUBSELECT TO FILTER IN A MDX QUERY RRS feed

  • Question

  • Hello every one i'm new at SSAS and i am trying to use a subselect in order to get only records that have [RECLAMATIONS].[Branch ID] equals to the Branch ID OF THE CURRENT USER here is the code that i use:

     SELECT { [Measures].[Cases Count] } ON COLUMNS,
     { ([RECLAMATIONS].[Branch ID].[Branch ID].ALLMEMBERS ) } 
    ON ROWS 

    FROM ( 
      SELECT ( { 
      [RECLAMATIONS].[Branch ID] = 
      (    SELECT ( { StrToMember("[Users].[Login].&["+username()+"]  ") } ) ON COLUMNS 
    FROM [BCP_DSV]
      )  
    }

    ON COLUMNS FROM [BCP_DSV]

    i get the following error : 

    “Subselects only support the COLUMNS axis”

    Thank you for your help :)



    • Edited by SKABAROUSSE Tuesday, June 11, 2019 11:00 AM
    Tuesday, June 11, 2019 10:58 AM

Answers

  • It sounds like you are after something like the following:

    SELECT { [Measures].[Nombre de réclamations] } ON COLUMNS,
     { [RECLAMATIONS].[Branch ID].[Branch ID].ALLMEMBERS  } ON ROWS 
    FROM ( 
        SELECT EXISTS( [USERS].[Buisness Unit].[Business Unit].Members,  
        StrToSet( "[Users].[Login].&["+username()+"]" ) ) ON COLUMNS
    FROM [BCP_DSV]  )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by SKABAROUSSE Wednesday, June 12, 2019 1:44 PM
    Wednesday, June 12, 2019 11:45 AM
    Moderator

All replies

  • Hi SKABAROUSSE,

    The syntax should be similar to below 

    select ...on columns, ...on rows  from (select ... on columns  from cube)
    You could try to use to see whether it works or not
    SELECT { [Measures].[Cases Count] } ON COLUMNS,
     { ([RECLAMATIONS].[Branch ID].[Branch ID].ALLMEMBERS ) } 
    ON ROWS 
    
    FROM ( 
        SELECT ( { StrToMember("[Users].[Login].&["+username()+"]  ") } ) ON COLUMNS 
    FROM [BCP_DSV]
      )  
    
    Best Regards,
    Zoe Zhi



    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, June 12, 2019 2:34 AM
  • Thank you for the respond,

    In fact i am trying to compare 

    [RECLAMATIONS].[Branch ID].[Branch ID] with the BusinessUnit memeber that is associated to the connected user 

    : "[Users].[Login].&["+username()+"] " returns the Login attribute not the BusinessUnit attribute.

    Something like:

    SELECT { [Measures].[Nombre de réclamations] } ON COLUMNS,
     { ([RECLAMATIONS].[Branch ID].[Branch ID].ALLMEMBERS ) } 
    ON ROWS 

    FROM ( 
        SELECT ( {[USERS].[Buisness Unit] } ) ON COLUMNS 
      
    FROM [BCP_DSV]
     where [Users].[Login].&["+username()+"]
      )


    • Edited by SKABAROUSSE Wednesday, June 12, 2019 8:10 AM
    Wednesday, June 12, 2019 7:41 AM
  • It sounds like you are after something like the following:

    SELECT { [Measures].[Nombre de réclamations] } ON COLUMNS,
     { [RECLAMATIONS].[Branch ID].[Branch ID].ALLMEMBERS  } ON ROWS 
    FROM ( 
        SELECT EXISTS( [USERS].[Buisness Unit].[Business Unit].Members,  
        StrToSet( "[Users].[Login].&["+username()+"]" ) ) ON COLUMNS
    FROM [BCP_DSV]  )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by SKABAROUSSE Wednesday, June 12, 2019 1:44 PM
    Wednesday, June 12, 2019 11:45 AM
    Moderator
  • Thank you very much for the response, i was wondering if there's a training that can help get better in MDX Queries and Expressions.

    Thank you.

    Wednesday, June 12, 2019 1:48 PM