chul
Contributor III

Have you run into the situation where a customer has a KPI or statistical account that they want OneStream to dynamically calculate for one Scenario Type and users to enter for another? If you’ve worked with OneStream’s dimension library, you know that varying Account Type by Scenario Type isn’t possible. There is, however, a workaround if the account needs to be reported for both Scenario Types on the same line.

To set up our dilemma, we create an account called DynamicActInputBud. We set the Account Type and Formula Type as Dynamic Calc and Allow Input as True. The formula on this account, Return api.Data.GetDataCell("A#15000:O#Top/A#22000:O#Top"), is entered only on the Actual scenario and the Budget scenario formula is left blank.

In the cube view, the formula result comes through for Actual, but the input for the Budget is not permitted.

chul_1-1676497670582.png

This is due to the Account Type being Dynamic Calc as it doesn’t store data to the database.

So we go back and change the Account Type to Non Financial (leave the formula type as Dynamic Calc) and run the cube view again. This time the Budget allows input but the formula result doesn’t come through for Actual.

chul_13-1676497804582.png

If the customer is adamant about having that data on a single line that functions both as a dynamic calc as well as allowing input, you’re out of luck (as illustrated above). On the other hand, if it’s simply a reporting line and the dynamic calculation/input are completed at different points in the process, then there is a simple workaround.

Basically, the solution involves setting up independent accounts for each use (Actual as Dynamic Calc and Budget as Non Financial) as well as a “pointer” account that will retrieve the value that’s sitting in each of the respective accounts.

The pointer account would be set up as a Dynamic Calc with Allow Input set to False.

The dynamic account (for Actual) would be set up as a Dynamic Calc with Allow Input set to False.

The input account (for Budget) would be set up as a stored Account Type with Allow Input set to True.

Then you reference the pointer account on the cube view and it would return the respective values of each independent account based on the scenario.

1. Set up the dynamic account (for Actual): DynamicAct

 chul_14-1676497868952.png          chul_15-1676497868953.png

Enter the Formula you want calculated on this account.

2. Set up input account (for Budget): InputBud

chul_16-1676497986233.png

Select whatever account type is required for your application.

3. Set up the pointer account: DynamicActInputBud

chul_17-1676498015563.png          chul_18-1676498015564.png

On the Actual Scenario type: Return api.Data.GetDataCell("A#DynamicAct")

chul_19-1676498037653.png

On the Budget Scenario type: Return api.Data.GetDataCell("A#InputBud")

chul_20-1676498048203.png

NOTE: Be sure to write the Formula for Calculation Drill Down as well!

4. On your cube view, call A#DynamicActInputBud for the account. In the screenshot below, the account rows are:

chul_21-1676498073640.png

And the columns are:

chul_22-1676498089617.png

5. Run the cube view and here are the results:

chul_23-1676498116859.png

6. You can go one step further: to prevent entering data into the Actual Scenario on the InputBud account, add it to a Conditional NoInput rule (not shown in this blog post):

chul_24-1676498130453.png

This is just one example of using some basic creativity to solve for a customer’s reporting requirement.