Writeback data from AS cell formulas into cube

Hello, I need to model some formulas in EPM. These are particularly complex formulas that involve for example positional calculations and built-in functions like IRR.

The input data for these formulas will be directly entered into Application Studio (there is no externally acquired data).

The formulas are dynamic concerning the time dimension (the user can choose the time horizon in terms of the number of periods like years, quarters or months). I would prefer not to model these formulas as OLAP rules, even considering the complexity, as their maintenance would not be very straightforward.

What I would like to do is write the calculation logic using the spreadsheet interface of Application Studio, As a requirement, I would like to write the output (a vector of n values corresponding to the selected, and variables, number of periods) into a cube.

As a starting point, I used a CELLWRITE placed in an a hyperblock action.

To execute the command only once (it seems that the action hyperlink works only for current selected column) , I have added a maximum number of CELLWRITE with their respective OFFSET. For example, on a monthly time horizon, I have added 12 CELLWRITE).

Is there a more scalable and performant way to approach a problem like this in EPM, while maintaining the assumption of writing the calculations as Application Studio formulas?

Thanks in advance.

Alberto

Find more posts tagged with

Infor EPM Discussions

Comments

Kai Reischl

Hi Alberto,

this sounds like a typical use case for Application Engine. (Basically any time you find yourself writing lots of CELLWRITEs that depend on only a handful of user inputs, AppEngine will be more performant.)

Is there a particular reason you want to keep everything in AppStudio?

Br Kai

Peter Mayall

Hi Alberto,

You can wrap the CELLWRITE with a IF statement to check if the calculation is different from the stored value and only if it is different then perform the CELLWRITE. There should be no need to use the OFFSET formula. It might be useful to have hidden columns containing the ROC formula for stored values and another for the calculated value. This is also better for performance. Note when checking for the difference, round the amounts before performing the check.

Peter.

apiazza

Hi Kai and Peter,

and thanks for your suggestions. The idea behind using CELLWRITE with OFFSET is to wrap the write-back inside an action for a better process control (used with an Hyperlink or Edit Cell action mode).
Since in my case all calculations are positional (think about it, for example, like an YTD expression) I need a way to trigger alwasys all colmuns within the hyperblock not just the first column or the edited one.
So far i wasn't able to set up a refresh mode that trigger an hyperblock action for all columns so I use the "multi-CELLWRITE with OFFSET" approach that unfortunaly is not very flexible since it'll require to create a maximun number of condition. Let me say that, in this case, a built-in action mode like "Edit Row" or "Edit Column" would do the job.

I knon, I can place the CELLWRITE as a plain formula inside a cell and wrapping it with an IF is a good suggestion for perfomances (we also tried the RUNPROCESS to execute an application engine script as suggested) but this will still be an "always on" approach.
Now let me say that in our specific case it will be acceptable (we have a very few element to calcuate) but we are asking it there could be best of both world (a controlled and fully general one solution).

A CELLWRITE with a non-immediate writeback could be tryied but here again the refresh condition should be setup carefully.

I'm interested in your opinions.

Thanks

Alberto

Copyright © 2025 Infor. All rights reserved.