Getting error in the rule when Consolidation Priority is set to Base element level

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Getting error in the rule when Consolidation Priority is set to Base element level

    Hello Friends,

    I am getting error "#Value!" when I try to create rule and set consolidation priority to Base Element level and When I select Consolidation priority to All Element level. it provides me same error in all consolidated elements but gives me a correct figure for all base elements.

    Context of the problem
    I was trying to work out on Currency Translation scenario to automate the translation process of our financial statements.Now According to Accounting Rules, all Balance Sheet Items need to be translated using Month End Exchange Rate while all Profit and Loss Items need to be translated using Monthly Average Rate. The difference arise from translation is posted to a separate accounts named Currency Translation Account (CTA)

    So in order to calculate currency translation I have created two cubes one for input Exchange rate and another for Currency Translation.In Account Dimension I have used Exchange Rate indicator to indicate whether it need to be translated using Month End Rate or Average Rate by creating an attribute.and create an element name "CTA" as consolidated element so that total effect of currency translation is calculated automatically.

    I have created following rule in Currency Translation Cube to translate amounts.

    Source Code

    1. ['USD'] = PALO.DATA("Database", "Fx_Rate", "AUD", PALO.DATA("Database", "#_Account", "Ratetype", !'Account'), !'Year', !'Period') * PALO.DATA("Database", "Currency_Translation", !'Account', !'Period', !'Datatype', !'Company', "AUD", !'Year')

    Can anybody guide me on this issue


  • Hi Pratik,
    you can try to right click on the cell with #Value! to show Jedox error. You can also check OLAP log file.
    I do not know much about the model, but I'd say that you can have some missing attribute "Ratetype" for some consolidated elements of dimension Account.
    in the part: PALO.DATA("Database", "#_Account", "Ratetype", !'Account')

    Fastest way to find error in rules returning error in current version is to simplify the rule and try to recalculate. Do it to the simpliest parts of the rule and you will find it.
    You can start with
    ['USD'] =
    PALO.DATA("Database", "Fx_Rate", "AUD", PALO.DATA("Database", "#_Account", "Ratetype", !'Account'), !'Year', !'Period')
    ['USD'] =
    PALO.DATA("Database", "Currency_Translation", !'Account', !'Period', !'Datatype', !'Company', "AUD", !'Year')

    Be patient, We are already working on some user friendly rule debugger