Skip to Content
1

Staging the result of a formula in an input ready query

Jan 24 at 09:16 AM

57

avatar image
Former Member

Hi folks,

Has anybody found a way to write back to DB the result of a Formula in an Input-Ready query?

In a simple scenario, I have 3 members of 1 dimension (or in embedded terms, 3 values of 1 InfoObject):

  • BASE = Baseline
  • ADJ = Adjustment
  • ADJ_BASE = Adjusted Baseline

where ADJ_BASE = BASE + ADJ

Using "Inverse Formula" functionality I can build an Input-Ready query with 3 key figures

  • BASE (Not Input-Ready)
  • ADJ (Input-Ready)
  • Formula = BASE + ADJ (Input-Ready)
  • ---- Inverse Formula ADJ = Formula - BASE

I'd like to be able to write back the result of the Formula into the 3rd member ADJ_BASE

I was wondering if this is possible to be achieved from the Query definition itself.

I'm aware there are other workarounds like using FOX formulas or VBA Macros, but it'd be really amazing if this functionality was delivered similarly to "Inverse Formulas" where you can just say the result of this formula is to be post against this Input-Ready Key Figure.

Thanks for your time.

Gab

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Gregor Dieckmann
Jan 25 at 11:47 AM
1

Hi Gabriel,

no, in BW only values of base key figures are saved on DB. Also saving the result of formulas has the danger of inconsistencies, because the operands may be changed by other means and then the formula value may not be in sync with the operands.

Technically one would need and additional step in the inverse formula definition to copy the result of the formula back to a base key figure. But this is not implemented yet because of the way inverse formulas are calculated (after aggregation) and disaggregation is only on base key figures; so one has the problem that formula calculation and disaggregation does not 'commute'; the result would be that OLAP aggregation and calculation would again change the value. For formulas using only operators -,+ and base key figures with aggregation SUM this would be no issue (except rounding effects in disaggregation).

By now, you have to use FOX or VBA.

Regards,

Gregor

Share
10 |10000 characters needed characters left characters exceeded