locked
Calculation based on Account Range RRS feed

  • Question

  • Hello All,

     

    I need to find all the Actual Amounts which is a measure in my cube, whenever the accounts in my COA AHC dimension is within a certain range. The attribute Account is NOT in any hierarchy. Here is what I have:

     

    CASE [COA AHC].[Account].CurrentMember

    WHEN{[COA AHC].[Account].[400000]:[COA AHC].[Account].[899999]}

    THEN 

    [Measures].[Actual Amount]

    ELSE 0

    END

    I keep getting the error " the level [400000] was not found in the cube when the string [COA AHC].[Accounts].[400000] was parsed. 

    I am trying to do something really straight forward, but I keep getting errors. What am I doing wrong? Please help.

    Thanks,

    KJ

    Wednesday, March 23, 2011 8:18 PM

Answers

  • You could convert the Member_Key intrinsic property to an int and then specify the criteria.

    WITH SET [Accounts in Range] AS

          Filter(

                [COA AHC].[ACCOUNT].Members,

                VBA!CInt( [COA AHC].[Account].CurrentMember.MEMBER_KEY) >= 400000

                  AND VBA!CInt( [COA AHC].Account].CurrentMember.MEMBER_KEY <= 899999

          )

    MEMBER [Measures].[Test] AS

          SUM(

                [Accounts in Range],

                [Measures].[Actual Amount]

          )

    ...

    HTH,

    Martin


    http://martinmason.wordpress.com
    • Marked as answer by KJ007 Monday, March 28, 2011 11:10 PM
    Monday, March 28, 2011 5:47 PM

All replies

  • 1. Setting the value to 0 and then getting the members adds no value and also performance deteriorates. Even in this case it is better to set as NULL
    
    2. This error may be because of referring a member that is not there in the cube yet. I guess in future it would come. In such a case you might want to set the MDXMissingMemberMode and additionally have the member in the STRTOSET. I have addressed a similar issue in this thread. So may be useful
    
    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/462648c8-a3cd-473a-962c-dc91e1ac7547
    
    3. If you are setting value to 0 for the members that are of no interest, i would rather prefer not bringing it in query output. if such is the case you can achieve this without using the case statement.
    
    Here is one example for AW:
    WITH SET ProductSet AS
    EXISTS( [Product].[Product].MEMBERS , {[Product].[Product].&[344]:[Product].[Product].&[345]} )
    
    SELECT {[Measures].[Reseller Sales Amount]} ON 0,
    NonEmpty(ProductSet,[Measures].[Reseller Sales Amount]) ON 1
    FROM [Adventure Works]
    WHERE([Product].[Product Categories].Bikes)


    vinu
    Wednesday, March 23, 2011 11:58 PM
  • Hi ,

    I stated my question a little incorrectly.  I want sum of all the Actual Amounts whenever the account range is between 400000 and 899999. Here is what I have based on your response above:

    WITH MEMBER Measures.TEST AS
    SUM
    ( {[COA AHC].[ACCOUNT].&[400000]:[COA AHC].[ACCOUNT].&[899999]},[Measures].[ACTUAL Amount])


    SELECT {MEASURES.TEST} ON 0,
    [COA AHC].[ACCOUNT] ON 1
    FROM [FINANCE]

     

     But I keep getting null. I checked if it at all recognises the account range 400000-899999 below:

    WITH SET ACCOUNTSet AS
    EXISTS( [COA AHC].[ACCOUNT].MEMBERS , {[COA AHC].[ACCOUNT].&[400000]:[COA AHC].[ACCOUNT].&[899999]} )

    SELECT ({[Measures].[ACTUAL Amount]})ON 0
    --nonempty([COA AHC].[ACCOUNT ID].MEMBERS) ON 1
    FROM [FINANCE]

    and it gives me actual amount for all the accounts even outside the 400000-899999 range.

    All I am looking for is again sum for actual amounts (measure) when the account range is within 40000-899999 range where account is an attribute in COA AHC dimension.

    Please advise. I am v new to MDX.

    Thanks,

    KJ


     
    Thursday, March 24, 2011 10:00 PM
  • You need to use the ACCOUNTSet  on Row axis. that is --nonempty(ACCOUNTSet) ON 1. This is because this is the filter applied set. if you use [COA AHC].[ACCOUNT ID].MEMBERS then it will get sum for all members.
    vinu
    Friday, March 25, 2011 1:12 AM
  • Thanks Vinu. I am still having problems. I did investigate on why I am getting back all the account ID's even outside my range. I am actually trying to write this syntax in the "form" view of Visual Studio to create calculations. I notice that Account ID 400000 and 899999 does not exist in the database. That is why it gives me back values for everything. I am looking for a range

    >= 400000 and <= 899999 and for some reason when these values dont exist, it completely ignores my condition and gives me back all values. But when I specify the starting and ending account that actually exists, it gives me back the right data. Is there a way for MDX to still recognize the range even when the specific 400000 and 899999 do not exist? I still want this range as the client adds accounts at any given point in their database. That is why I still need to specify 400000 as it can exist anytime in future. Please advise. Thanks for all your help.

    Currently my syntax is

     SUM( {[COA AHC].[ACCOUNT].&[401000]:[COA AHC].[ACCOUNT].&[815185]},[Measures].[ACTUAL Amount])

    when I try to do [COA AHC].[ACCOUNT]>=.[400000], it gives me a syntax error. What is the correct syntax?

    Thanks,

    KJ 

    Monday, March 28, 2011 4:43 PM
  • You could convert the Member_Key intrinsic property to an int and then specify the criteria.

    WITH SET [Accounts in Range] AS

          Filter(

                [COA AHC].[ACCOUNT].Members,

                VBA!CInt( [COA AHC].[Account].CurrentMember.MEMBER_KEY) >= 400000

                  AND VBA!CInt( [COA AHC].Account].CurrentMember.MEMBER_KEY <= 899999

          )

    MEMBER [Measures].[Test] AS

          SUM(

                [Accounts in Range],

                [Measures].[Actual Amount]

          )

    ...

    HTH,

    Martin


    http://martinmason.wordpress.com
    • Marked as answer by KJ007 Monday, March 28, 2011 11:10 PM
    Monday, March 28, 2011 5:47 PM
  • Hi Martin,

    Thanks for your response! It really helped me. This was exactly what I was looking for. Also the post on this thread below also helped me.

    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/d8aef58b-116e-4cfb-84a2-caa6f7c4bfb6

    I did the following:

    ( (

    FILTER([COA AHC].[ACCOUNT].[ALL].CHILDREN,[COA AHC].[ACCOUNT].ITEM(0).MEMBERVALUE >=400000
     

    AND

    ([COA AHC].[ACCOUNT].ITEM(0).MEMBERVALUE<=799999))),[Measures].[ACTUAL Amount])

    This solved my issue.

    Thanks a lot,

    KJ

     

     

     

    Monday, March 28, 2011 11:10 PM