locked
geeting error on this mdx query RRS feed

  • Question

  • Hello,

              i want to create one calculated measure which requires to add range of accounts, i wrote this

    (

    sum([Dim Account].[Account].&[10100]:[Dim Account].[Account].&[14780],[Measures].[Base Amount])+sum([Dim Account].[Account].&[20010]:[Dim Account].[Account].&[26550],[Measures].[Base Amount]))

    But i am getting result like #value!, then i found the problem that one account in that range, 14780 has no data so instead of using that i used the next max which is 14779 and then i got the result but in future if data come for 14780 then i need to reconfigure again but i want to make it dynamic so is there any possibility, i can use? I heard about EXISTS command but i don't know how to use that here? please help it's really urgent.

    Tuesday, February 1, 2011 8:11 PM

Answers

  • it says account #14780 is not found in a CUBE

    --> but i need to use that formula only because in future they will fill for that account so what to do?


    ANKIT PUROHIT

    Your account dimension should have every account in it, not just those with data. At the very least you should have accounts in the dimension that you are referencing in formulas.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by ank hit Friday, February 4, 2011 1:54 PM
    Friday, February 4, 2011 4:46 AM

All replies

  • If there is a member like 14780, 10100 etc. You don't need to reconfigure your code or don't need to write a dynamic code.

    Sum
    (
    {[Dim Account].[Account].&[10100]:[Dim Account].[Account].&[14780]}
    ,[Measures].[Base Amount]
    ) 
    + 
    Sum
    (
    {[Dim Account].[Account].&[20010]:[Dim Account].[Account].&[26550]}
    ,[Measures].[Base Amount]
    )
    

    or you can use simply

    Sum(
    {
    {[Dim Account].[Account].&[10100]:[Dim Account].[Account].&[14780]}
    +
    {[Dim Account].[Account].&[20010]:[Dim Account].[Account].&[26550]}
    }
    ,
    [Measures].[Base Amount]
    )

    Tuesday, February 1, 2011 9:24 PM
  • but there is a problem with your or my code, we defined a range over here say 10010 to 14780 and if for some reason 14780 is not there then you will get #value! as aresult. So my question was, how can we use EXISTS in here so if account exists, it will give u result


    ANKIT PUROHIT
    Wednesday, February 2, 2011 2:10 PM
  • Can you check your Account dimension if you have 14780 member or not? If you have the member in the dimension it mustn't return #value!. If you have any filter on the query that excludes the member it returns an empty cell not #value!. Also can you mouse over check if #value!  cell has a error message
    Wednesday, February 2, 2011 10:58 PM
  • but there is a problem with your or my code, we defined a range over here say 10010 to 14780 and if for some reason 14780 is not there then you will get #value! as aresult.

    If for some reason 14780 is not there then it will use NULL instead by default. So the query will be:

    sum([Dim Account].[Account].&[10100]:null,[Measures].[Base Amount])

    It may return wrong result but shouldn’t generate error for you. So could you double click that cell and then post the error message?

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Thursday, February 3, 2011 5:39 AM
  • it says account #14780 is not found in a CUBE

    --> but i need to use that formula only because in future they will fill for that account so what to do?


    ANKIT PUROHIT
    Thursday, February 3, 2011 2:40 PM
  • it says account #14780 is not found in a CUBE

    --> but i need to use that formula only because in future they will fill for that account so what to do?


    ANKIT PUROHIT

    Your account dimension should have every account in it, not just those with data. At the very least you should have accounts in the dimension that you are referencing in formulas.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by ank hit Friday, February 4, 2011 1:54 PM
    Friday, February 4, 2011 4:46 AM