Changing Signs in OneStream

Any data loaded in OneStream has to be logged against an Account. Typically, the Account Type property of the account member will determine how the data is treated, but there are a number of other factors that the system will consider. Let's have a brief overview of the most common ways to control control the direction of our data.

Account Type

Let us imagine a Frankfurt entity, a manufacturing centre that includes and analyses their data. In the example below, cash is seen as an Asset for the Frankfurt Entity in the Account Dimension.

This setting means that when Frankfurt imports data and logs it against that member, the data is marked as a debit or a credit in a straightforward manner. There are other Account Types we could use, one for any major accounting concept - Asset, Revenue, Expense, and Liability - plus more neutral types like Balance, BalanceRecurring, Flow, NonFinancial, and the special Group and DynamicCalc.

Imports

Imagine Frankfurt imports its trial balance data against this account. They use Data Sources and Transformation Rules in a Workflow, to get the data into the Cube for consolidation.

Someone may ask - Is there any way that we could include a debit into such an Account, if needed ?

The answer to that is yes, absolutely we can 😊.

Transformation Rules targeting Accounts can “flip signs,” to create that flexibility with records as they are being imported.

Flows

Frankfurt has now been including data for the first quarter and is reviewing their assets from within the balance sheet. For Frankfurt, most of the movement has been for additions to non-current assets, but they have had one disposal. Flow members are available to highlight movements within assets; we can hence assign that movement to another Flow member to treat it differently. If we want to switch signs, we will ensure the relevant property is set to “true”.

Now, when we log positive movements against ActivityCalc, they become negative on aggregation.

In more complex cases, when we might want to roll up a value in different ways to different parents, we can achieve the same effect by setting the Aggregation Weight property of a specific parent-child relationship to -1 instead. Base data will be multiplied by that value when aggregating to the specific parent, hence effectively flipping sign.

In Flows, we also have the chance to change how data must be treated from an Account perspective. For example, we might want to log everything in a certain dataset against a specific Asset account, but actually treat a few of the included transactions as they were logged against a Revenue account instead. We can do that by pointing these to a different Flow member that has the Switch Type property set to True. This can be useful when treating roll forward accounts as income statement accounts in the balance sheet .

Reviewing

Let’s imagine that, once we have imported the data, Frankfurt’s accounts team determine that the situation has changed. It turns out some inventory never reached the customer, so figures must be reloaded!

We might see these sorts of changes coming through if we use the Preserve Data functionality, load the data again, and then compare the two datasets to identify any variations. One of many Standard Reports will show us that.

Now that these changes have been made, data can be reviewed with Cube Views. At this point we may wish to drill down from the “Top” value to see how that amount was determined. Will we be able to see what was the result of sign-flipping?

In addition to obviously displaying different Flow members, if we used Transformation Rules to change data they will show in the drill-downs of transaction details! OneStream shows the data trail with absolute transparency.

In some cases, you might want to actually flip signs only at reporting time. You can do this in a Cube View on a per-row or per-column basis, simply specifying a property in Cell Format.

And that's it! Note that there are several other ways to flip signs, typically going through Member Formulas or Business Rules, to suit even the most complicated of processes.

If you want to learn more, take our new Administrator course or Building Basic Reports course, to see how much control a customer can gain!