Post reply

How to use filter funtion in Expression of calculated member in ssas

  • July 2, 2008 at 12:24 am

    #194603

    what is the use of FILTER function(inbuilt) in cubes...could u plz tell me correct way to use the same....I tried by following syntax but it's not working

    Filter

    (

    Filter([Date].[Calendar].[Month].MEMBERS, [Date].[Calendar].Properties("Month of Year") = "January")

    please suggest the correct way of using this...

  • July 3, 2008 at 7:38 am

    #837836

    Filter has a wide range of uses. Generally, it's to constrain a set (short a set) to a list of members meeting a certain criteria.

    In your case, its the condition you've specificed:

    [Date].[Calendar].Properties("Month of Year") = "January"

    In this case, you've specified a "property" for the "hierarchy" [Calendar]. But hierarchies don't have properties.

    My guess is that you wanted to specify:

    [Date].[Calendar].[Month].Properties("Month of Year") = "January"

    But again, while the level or attribute has the property "Month of Year" (I'm assuming), the question is which element in the set are you testing for "January".

    What you want to do is test against each member, as you evaluate the set:

    [Date].[Calendar].CurrentMember.Properties("Month of Year") = "January"

    Now, as the filter set is evaluated, it checks the current member to see if it's property equates to January.

    NB: if you're evaluating against normal dimensional attributes (i.e. [Measures].[Dollars], [Periods].[Month].[Month].&[January]) , you don't need to speciy the target dimension (it's assumed since you're itertating over a set). But in this case, we needed a handle to point to the the property.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply