locked
Filtering sets where member name contains ... RRS feed

  • Question

  • I am trying to write an MDX query that slices the cube based on whether a dimension Attribute contains a string.   In my case, I have an order dimension with a PO attribute.  When I aggregate amounts by PO, it looks like:

    PO            Amount
    123          10
    124          30
    125F        20
    126           5
    127F        30

    The query I am looking to write needs to filter the above  result set where the PO contains 'F' (ie return 50)   I can do this in the BIDS browser as well as Excel, but I have been unable to find to the MDX syntax for this.  I started to look at the filter function but was unable to craft the appropriate expression to filter where PO like %F.

    Any thoughts/suggestions?

    Thanks!



    Monday, January 5, 2009 4:20 PM

Answers

  • You have a few options.  When browsing you will notice that if you run a trace it is using the instr function to look for members in a dimension.  You could also look at using the analysis services stored procedure project.

    Refer to this link for some additional options and examples - http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/35edea0d-c031-4c6d-a0c7-9cca1561efdc/

    Dan English -- http://denglishbi.spaces.live.com
    Monday, January 5, 2009 5:04 PM
  • If you're looking to aggregate all members of the PO hierarchy with a caption that ends in 'F', the following should create that member.
    WITH MEMBER [PO].[PO].[All].[Members ending in F] AS  
         Aggregate( Filter (  
              { [PO].[PO].[PO].Members },  
              ( VBA!Right([PO].[PO].CurrentMember.MEMBER_CAPTION, 1) = "F" )  
         ))  
     
    Monday, January 5, 2009 7:47 PM

All replies

  • You have a few options.  When browsing you will notice that if you run a trace it is using the instr function to look for members in a dimension.  You could also look at using the analysis services stored procedure project.

    Refer to this link for some additional options and examples - http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/35edea0d-c031-4c6d-a0c7-9cca1561efdc/

    Dan English -- http://denglishbi.spaces.live.com
    Monday, January 5, 2009 5:04 PM
  • If you're looking to aggregate all members of the PO hierarchy with a caption that ends in 'F', the following should create that member.
    WITH MEMBER [PO].[PO].[All].[Members ending in F] AS  
         Aggregate( Filter (  
              { [PO].[PO].[PO].Members },  
              ( VBA!Right([PO].[PO].CurrentMember.MEMBER_CAPTION, 1) = "F" )  
         ))  
     
    Monday, January 5, 2009 7:47 PM
  • the most performant solution would be to create an attribute that is true if the PO contains an 'F' and false if the PO does not
    then scope the true-value and assign 50 to your Amount-measure

    greets,
    gerhard

    Wednesday, January 7, 2009 8:05 PM
    Answerer