Variable in a rule?

      Variable in a rule?

      i have a question regarding rules in Jedox:
      is it possible to use a variable (from a selection in a report) in a rule?

      To elaborate: i´m building a report (i´m still learning Jedox, so it´s a test report) with some combobox selection, where i can select a currency in which i want the report to show
      the values, and a date of the FX rate to calculate the values with. The original values are in different currencies also.
      I have a cube with FX Rates for, let´s say, 90 days and some 5 different currencies. So, i want to be able to choose a currency with a date and show those values from the cube on the
      report in this selected currency, calculated from the value in original currency (no matter what the original currency is).

      Thank you for any idea.
      Hi teletaubi,

      rules are server-side calculations, that means that they are triggered each time you query a cell that is covered by the rule.

      I guess that what you try to achieve can be solved using a combination of dimensions and rules. Can you please post a more detailed excerpt of your model so that we can analyse it

      Post hoc, non est propter hoc
      Hi Laloune

      I have some firms with different accounts with values (let´s say those are sums of account payments at the end of the day) in differenct currencies.
      On the report i want to show a sum of all accounts for a month, quarter or year for one (or more) firm (which can have many accounts in different currencies).
      As there are several currencies involved, i want to know, how much a have in the same currency, so i want the user to pick a currency, in which to display results.
      Example: in january i got payments as follows: 100€, 200$ and 50GBP. How much is the single pament and/or the sum in €?
      So far, no problem (i solved this with a separate currencies cube, where i have all the currency ratios).I can do this with these rules:

      Source Code

      1. ['RCur':'EUR'] = IF((!'Currencies') == "EUR",['Original'],CONTINUE())
      2. ['RCur':'USD'] = IF((!'Currencies') == "USD",['Original'],CONTINUE())
      3. ['RCur':'GBP'] = IF((!'Currencies') == "GBP",['Original'],CONTINUE())
      4. ['RCur':'EUR'] = ['Original'] * PALO.DATA("","CurrencyCube",!'Currencies',"EUR")
      5. ['RCur':'USD'] = ['Original'] * PALO.DATA("","CurrencyCube",!'Currencies',"USD")
      6. ['RCur':'GBP'] = ['Original'] * PALO.DATA("","CurrencyCube",!'Currencies',"GBP")

      First three are to minimize calculating (if selected_currency==value_currency, just take original value, otherwise proceed with the next rule)
      Next three are the calculations, where i multiply the original value with an FX ratio.
      But, the currency ratios change every day, so i want the report to show me, how much the balance on the account was on a given day (that the user can pick from a combo box).
      So, i came up with these:

      Source Code

      1. ['RCur':'EUR'] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',"EUR",!'FxRateDates')
      2. ['RCur':'USD'] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',"USD",!'FxRateDates')
      3. ['RCur':'GBP'] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',"GBP",!'FxRateDates')

      which works fine if i add the fxratedates dimension to the data cube also - but only on the dates of the rates, not user selected dates - not really what i want.
      I want something like this (i´ll write only the second set of rules, that do the actual calculation and are relevant):

      Source Code

      1. ['RCur':'EUR'] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',"EUR",$varSelectedFxRateDate)
      2. ['RCur':'USD'] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',"USD",$varSelectedFxRateDate)
      3. ['RCur':'GBP'] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',"GBP",$varSelectedFxRateDate)

      or even better:

      Source Code

      1. ['RCur':$varSelectedTargetCurrency] = B:['Original'] * PALO.DATA("","FXRatesCube",!'Currencies',$varSelectedTargetCurrency,$varSelectedFxRateDate)

      while $varSelectedFxRateDate and $varSelectedTargetCurrency both obviously being a variable (like selection from a combobox in the report).

      I did solve the problem using a separate cube acting like a parameter cube, where i stored the selection. But it is not viable, as the report can be multiuser and the parameters
      could be overwritten by the other user.

      Maybe i am on a totaly wrong path with my model, i don´t know. I´m out of ideas for now.

      Thank you.

      Post was edited 2 times, last by “teletaubi” ().

      Hi teletaubi,

      Instead of setting up a cube rule, it might be easier (and less complex) if you perform the calculations on the workbook, based on the combo box selections. It would be easy to setup the formulas as you already have them ready with you as rule formulas.