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.
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.
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
Enter the Formula you want calculated on this account.
2. Set up input account (for Budget): InputBud
Select whatever account type is required for your application.
3. Set up the pointer account: DynamicActInputBud
On the Actual Scenario type: Return api.Data.GetDataCell("A#DynamicAct")
On the Budget Scenario type: Return api.Data.GetDataCell("A#InputBud")
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:
And the columns are:
5. Run the cube view and here are the results:
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):
This is just one example of using some basic creativity to solve for a customer’s reporting requirement.
