Amount and Pct variance confirmation rules for all base accounts

Question

Hi all,&nbsp;
I'm putting together confirmation rules for various forms, and have got a few working where the explained items clears a check to zero. These checks are isolated to a few accounts, and are pretty straightforward.&nbsp;
I am a bit stuck on creating the rules for the Balance Sheet variance form. The idea is that the users will provide commentary onto all Asset and Liability base accounts where the movement is over 50,000 Euros and over 5%. I started building my rule and ran some tests to see that the logic worked, but I haven't progressed much. See rule below. Sampling it on S_CashatBank account, I know there is a movement of 15,000 Euros in this workflow on one entity, so should pass above 100,000 but fail below 10,000. It fails on both amounts.&nbsp;

Also, where there is 1 confirmation rule looking at multiple accounts, how do people get to show on the confirmation page the different accounts that have failed the rule? Let's say there were 5 base members under I wasn't sure how to show this in the Information 1/2 boxes.&nbsp;
Hopefully this is clear, and hoping there can be someone out there to help.&nbsp;
Thanks!
&nbsp;
&nbsp;

Henning · Answer

Hi,
The first thing that comes to mind is that you are checking data in EUR and hard-coded the threshold like this: "10000.00". If the user culture of the user executing this is in US format, this means ten thousand as the decimal point is a period sign. Since you are using EUR, and in many countries using the EUR, the period sign is a thousand separator and a comma (,) is a decimal point, the above code will not work properly if not all users are using an US based user culture to format their numbers.
Here is an explanation on the Microsoft homepage with some more information:
How Culture Affects Strings - Visual Basic | Microsoft Learn
This might help getting the number format right:
Solved: Format output of confirmation rule - OneStream Community (onestreamsoftware.com)
Here is a post with a simple example on how to loop through accounts and add all 'fail' accounts to the information box:
Confirmation Rule help - OneStream Community (onestreamsoftware.com)
And for a lot more information and examples, please refer to this in addition to the general documentation that is available (the presentation is quite old, but should still work mostly fine):
Question: Is it possible to loop through members within confirmation rules? - OneStream Community (o...
&nbsp;
And one additional note is that using the "/" to divide data will error out when the divisor is zero. Using the divide function will not error out and is generally recommended to be used. You can find a quick example here:
Net Profit Percent - OneStream Community (onestreamsoftware.com)

Henning · Answer

Hi,
The first thing that comes to mind is that you are checking data in EUR and hard-coded the threshold like this: "10000.00". If the user culture of the user executing this is in US format, this means ten thousand as the decimal point is a period sign. Since you are using EUR, and in many countries using the EUR, the period sign is a thousand separator and a comma (,) is a decimal point, the above code will not work properly if not all users are using an US based user culture to format their numbers.
Here is an explanation on the Microsoft homepage with some more information:
How Culture Affects Strings - Visual Basic | Microsoft Learn
Here is an excellent presentation on that topic tailored to OneStream implementations (I hope you have access):
Building International Applications - October 2022 - OneStream Community (onestreamsoftware.com)
If you do not have access, this might help getting the number format right:
Solved: Format output of confirmation rule - OneStream Community (onestreamsoftware.com)
Here is a post with a simple example on how to loop through accounts and add all 'fail' accounts to the information box:
Confirmation Rule help - OneStream Community (onestreamsoftware.com)
And for a lot more information and examples, please refer to this in addition to the general documentation that is available (the presentation is quite old, but should still work mostly fine):
Question: Is it possible to loop through members within confirmation rules? - OneStream Community (onestreamsoftware.com)
&nbsp;
And one additional note is that using the "/" to divide data will error out when the divisor is zero. Using the divide function will not error out and is generally recommended to be used. You can find a quick example here:
Net Profit Percent - OneStream Community (onestreamsoftware.com)

ajackett · Answer

Thanks Henning - much appreciated. A lot of useful tips in there, but I still can't quite nail what I need. I guess this is where my lack of vb.net knowledge comes in...
I've used manage to source some of the code I need so that I can filter the base accounts, and then I have created a failure range of above 1,000,000 and below -1,000,000 (just to trial rules for now).&nbsp;
2 things I am stuck on here:
1. When I run the databuffer, I understand this gives me the current balance. I need the movement for the month however, and I tried to add T#WFPrior1 into the dbf equation, but it didn't seem to change the amounts. Maybe the databuffer doesn't sum? I thought using the movement flow would work, but our accounts are YTD so the mvmt flow beginning balance is always the start of the year, not month.&nbsp;
2. How can I make the variance limit an absolute value with a databuffer? Here I have had to put the range in.&nbsp;
Thanks in advance for anyone that can help.&nbsp;

&nbsp;

Forum Discussion

Amount and Pct variance confirmation rules for all base accounts

Related Content

Confirmation Rules

Cube View Variance Calculation Issue

Variance Formula

Quick View - Annotations/Audit Comments/Variance Explanations

NonFinancial Account