Rules

Hi - I have created a routine in Connector Rule to fetch the Worker data from HCM and I was able to assign it to a variables and able to print it successfully.   My question is below    1. How I can assign the variables to the List because I am trying to use this connector rule in Data Source and trying to bring the data to the OS stage Area ? is it possible, My function return type is task for the RESTAPI call.   2. Or I can create a Extender Rule and create a Table in the Backend and import into the table and create an another connector rule to bring it to the stage.   Any suggestion would be appreciated.   Thanks Krishna

Hi All, I'm looking to filter a dynamic calculation by the base of my income statement and balance sheet accounts. The result of this dynamic calc will be leveraged within a confirmation rule.  Do you have any suggestion on how to effectively filter this?  Thank you in advance.

Hi Experts,   I have the following rule to translate my opening balance adjustment (entered manually in the current year) at an opening rate.   If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then 'Only run for foreign currency Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId Dim prioryearid As Integer = api.Time.GetLastPeriodInPriorYear(timeid) Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability() ' get closing rate from prior year Dim pycloRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeClo,prioryearid) 'use closing rate from PY to translate this member api.Data.calculate("F#ObalAdj = C#Local:F#ObalAdj*" & pyclorate,"A#Bs.Base") End If   While this works perfectly for data entered via Forms, it doesn't work for adjustments entered via journals. When a journal is posted against ObalAdj member, I see  2 things happening:   1. The AdjInput member translates at opening rate ba

  The items below detail the specific list of tasks that are executed for each data unit during the calculation process. Data Unit Calculation Sequence (DUCS) Clear previously calculated data  If Using Hybrid Scenarios  - the copy will run here Run Scenario Member Formula Run reverse translations by calculating Flow members from other Alt Cur. Input Flow members Execute Finance Business Rules (1 & 2) Run Formula Passes (1 – 4 ) Account, Flow, UD1, UD2, … UD8 (Member Formula Execution) Execute Finance Business Rules (3 & 4) Run Formula Passes (5 – 😎 Account, Flow, UD1, UD2, … UD8 (Member Formula Execution) Execute Finance Business Rules  (5 and 6) Run Formula Passes (9 – 12) Account, Flow, UD1, UD2, … UD8 (Member Formula Execution) Execute Finance Business Rules  (7 and 8 ) Run Formula Passes (13 – 16) Account, Flow, UD1, UD2, … UD8 (Member Formula Execution)

Customer has intercompany transactions in local currency. They want to eliminate sales and cost of sales and have the remaining margin (in local currency) at the Sales entity - not in an elimination entity - and be able to view in local currency.  For the entity purchasing the goods, it should be zero. Is it possible to do this in rules? Or some other way?    thank you

The attached file will help plan rules from HFM to OneStream

Hello How does one write an aggregation business rule? I know Accounts aggregate automatically, but a business  rule is needed for Entities, correct? In Essbase we could have something like:   FIX ("2025","Jan":"Dec","Actual") Calc DIM ("Account","Source","Product","ResponsibilityCenter","Interco","Entity","Location"); ENDFIX;

Hi, I'm trying to set up some new Cash Flow accounts, and as usual I'm looking at the existing CF accounts to see how they've been set up. However, I'm pretty new to OneStream, and I'm struggling to make sense of some of the Syntax.  I've had a look in the 'OneStream XF Reference Guide.pdf'  but there isn't much info there.   Where can I find more info on Member Formula Syntax?  Is there another document that I'm missing?

Equity Accounting And Methods   A consolidation method is just a way to treat an entity during consolidation based on how much of it you own or control.  It makes sense, if you only own 10% of an entity, and another entity has an asset recorded for the value of that investment, you would be overstating your value if you let it add up at 100%.  The following are the most common methods you might see.   Subsidiary: Entity owned at >50% The Subsidiaries financial statements are consolidated and eliminated.  The Equity associated with the subs financial statements are eliminated against the Holding companies Investment in the sub.  If the Sub is not 100% owned, the owned portion of Equity is eliminated and the unowned portion is reclassed to Minority Interest.   Equity: 50% >Entity owned >20% An Entity held, as Equity Method is NOT consolidated.  The Net Income of the Entity is multiplied by the Equity holding and the result is booked to the

Equity pickup (EPU) is a method of reevaluating the investments owned by a holding company allowing the parent to realize changes in equity. This results in the holding company’s balance sheet showing the current value of the corresponding share in the equity of the subsidiary. Investments are normally shown at historical or acquisition cost.  But an invested company has a value that can vary based on profit and loss they are incurring.  The equity pickup adjustment offsets the historical cost in local currency, showing the actual value of the equity owned.  Direct Ownership Percentage * Equity of Owned Entity = Current Equity Value - Investment = Equity Pickup Adjustment You can calculate the adjustment by simply taking the direct ownership percentage multiplied by the equity of the entity owned then subtract the investment in the parent.  In OneStream the ownership percentage can be entered directly on the entity or by creating a Ownership cube.  But once thi

Does anyone has a NotePad++ language definition file for OneStream rules? Thanks in advance.

OneStream Platform releases will periodically include an update to the Business Rules compiler, which is noted in each version’s Release Notes.  The enhancements typically make the compiler stricter in detecting syntax or other conditions, which are surfaced through Error or Warning messages.     Error messages must be resolved, as the Business Rules will not complete the compile process. Warning messages are exposed to provide guidance to the Administrator.  The displayed line items will still function but should be updated to support the latest compiler’s requirements.  The method to resolve the Warning will vary.  In some cases, a replacement function may be available, or there may be a change to a function’s properties. Example   The above error message informs the Administrator of a Warning on the LookupRowFieldValue function having a property change.  By reviewing the current rule, and by looking at the current Function Definition, th

Hello Everyone,  Looking for a quick help Please. Here I am trying to write a Dynamic Calculation code which will follow the exact logic of PVA translation however will use rates from previous year. So kind of a Constant currency solution. However when I am in Time period 2, I am unable to pull data from different periods, This calculation works If I make my T#Hardcoded e.g. 2020M1/2020M2. Current outcome is all zeros 😞 Please help with any solution anyone has or can point me to right direction - since unable to understand what is missing.   Thanks Satish    

Hi all, I have a dynamic calc that displays zeros instead of null when the source cell is null. In the image below, MTD_CF is dynamic and it shall display None data under certain conditions. When None is null, MTD_CF is showing 0. Is there a way to avoid this so the dynamic calc shows nulls as well? I've tried with RemoveZeros, like this: Return api.Data.GetDataCell("RemoveZeros(V#" & view & ":U1#None)").CellAmount The code returns the result, but does not remove the zeros. Any ideas? Thanks in advance. GSF

SOURCE: ONESTREAM CHAMPIONS Hi All,We are at the point in our implementation where we are thinking about managing security and our userbase. We are going to have ~600 users as we are very decentralized with our financial management, and we currently have our SSO setup with SAML. We use ServiceNow internally for all things workflow/helpdesk related and were going to be building the workflow for user creation/modification and their roles. Knowing that OneStream also uses ServiceNow for their integration I am wondering if there’s any thing out there to automate certain things from ServiceNow, or if we can script a .xml file out of ServiceNow to our staging database and have that loaded to OneStream to make the changes. If anyone has any ideas or experience I’d love to hear, as we are going to be having our OneStream system as an “in-scope” software from an audit perspective and want to have some tight controls around user management. Thanks,Jeff

Hi all Onestream stores a lot of information using an integer ID instead of a name. And most of the time we also deliver a simple way, to get objects (or names of objects) using an api or brapi call. Somehow getting the name of a dimension from its ID is an exception. Is there a simple way to do it? Cheers  

Hello Experts, I am trying to export Cell Details to a CSV using a business rule function as shown below:     If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Export_CellDetails_CSV") Then Dim wfUnitInfo As WorkflowUnitInfo = api.Workflow.GetWorkflowUnitInfo() Dim SRCTimeName As String = wfUnitInfo.TimeName Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si) Dim timeStamp As DateTime = DateTime.Now '.UtcNow Dim AESTNow = System.TimeZoneInfo.ConvertTime(timeStamp, TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time")) Dim timeString As String = AESTNow.ToString("yyyy_MM_dd_HHmm") Dim xfolderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.fileShareRootFolder, si.AppToken.AppName) Dim csvFilePath As String = xfolderPath & "\" & "Cell_Details.csv" If File.Exists(csvFilePath) Then File.Delete(csvFilePath) End If

Hello, I am working on a CTA plug account and also historical overrides. Right now for the plug and overrides to run I have to force calculate and translate. Is there a way the user would be able to run the consoldiation and the formulas run correctly without having to go through a whole sequence of steps? Basically, is there an easier way to run the translation logic within consolidation? These are the current formulas I have: CTA Plug with formula pass 8 being used: If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) ThenDim entityDimPk As DimPk = api.Pov.EntityDim.DimPk Dim entityId As Integer = api.Pov.Entity.MemberPk.MemberId If Not api.Members.HasChildren(entityDimPk, entityId, Nothing) Then api.data.calculate("A#313001:F#TB_ENDBAL:I#None = A#TotalAssets:F#TOT_USGAAP:I#Top - A#TOTALLIABEQUITY:F#TOT_USGAAP:I#Top") End If End If and for overrides I only have one formula being used for “Historical Override Prior” on Formula pass 1. I think this may be part

SOURCE: ONESTREAM CHAMPIONS Hi all, I have a very random request for anyone who may have the time to review and hopefully point me in the right direction. The formula calc is an on account and is unbalanced - I’m specifying more dimensions on the right side of the equation than the left (T#, E# and C#) which is giving me a data explosion error. I have examples of formulas currently in use where T# is on one side but not the other and the formula works fine so I’m guessing it’s an issue with E# and C# but I can’t seem to figure out how to make it work Here’s the formula:If Not api.Entity.HasChildren And api.Cons.IsLocalCurrencyForEntity ThenDim vEnt As String = api.Pov.Entity.NameSelect Case vEntCase “ENT_1095”api.Data.Calculate(“A#520_211010:V#Periodic:O#Import:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None =A#ACC_SAP_134450:T#POVPriorYearM12:E#ENT_2000:C#CAD:V#Periodic:O#Import:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#N

SOURCE: ONESTREAM CHAMPIONS Hi all, I’m largely self taught with vb, but have hit a wall with this, and thought I’d share here rather than ask the OS helpdesk, as it seems to be something that could prove useful to others. I’m trying to write a calculated account which refers an entity Text field, using multiple values varied by time in the calculation. We set a value for Cost of production for certain categories of products (spares and accessories) by % on a varied by time basis on the entity. This can vary from year to year (in theory could vary in year) Specifically, I want to find the value that is valid at prior year end and subtract the current value from that. The value may have been set earlier M12 may conain no value but M8 of the same prior year does, for example. I’ve introduced a loop to try and go back through periods until a value is found, but can’t see whether that’s working as I’m still getting an error message. Dim PeriodPOV As Integer = api.POV.Time.MemberId Dim Prio

Hi All, I'm trying to call out a XFBR string w/in a XFBR string in the column set of my CV (snip below).  Our consultant initially setup the first XFBR so that we could report on completed weeks of the month while rendering zero for the future weeks.  The logic works great but is dependent on manually entered parameters.  This is when I tried to introduce the second XFBR string to dynamically determine the prior week so that we can automate distribution of this output.  After pushing in the second XFBR string I get an error when running the CV of, 'Input string was not in a correct format' (see second snip).  I've tried to pass in the week value as an integer, a string, and as an Int32 value but nothing has worked yet.  I've also tried with and without the brackets [ ] on the CV column set, but no luck.  Any suggestions out there?  Help would be greatly appreciated. Thanks, Bryan       

Source:   Migrated from Champions Hello all, I’m creating a Data Adapter that is using SQL to poll an external database. I can get it to work when I’m just supplying the strings for dates. However, I wanted to get fancy and be able to parse the effective dates using variables. When I go to an online SQL tester (https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_left), I can get the below code to return ‘121181’, which is the Julian date I need in querying JDE. But the OS SQL Query returns an error saying ‘int’ is an invalid token. does anyone know if there’s a way to use variables in this way? Declare @YearRun intDeclare @YearPart intDeclare @MonthRun intDeclare @Global varchar(100)Declare @GetTime datetimeDeclare @RunTime varchar(100)Set @Global = ‘2021M6’set @YearRun = LEFT(@Global, 4)Set @YearPart = Right(@YearRun,2)set @MonthRun = Right(@Global, Len(@Global)-CHARINDEX(‘M’,@Global))Set @GetTime = EOMONTH(CONCAT(@MonthRun, ‘/’, 1, ‘/’, @YearRun))S

This is causing me a headache! Here's the situation.   We have an Actual Scenario and a Forecast scenario In the Actual Scenario,  we have Cash Flow accounts that have formulas so that they pull data from Income Statement and Balance Sheet accounts.  For example CF1 pulls from the IS,  and CF2 pulls from the BS.  The CF accounts are all set to Flow account type, and the ones pulling from the BS have Flow Constraints set and those Flows have Switch Type set to True. In  Actual Scenario,  the Rate Type for Revenues and Expenses is set to AverageRate,  and Rate type for Assets and Liabilities is set to ClosingRate. In Actual,  IS accounts correctly translate at AverageRate,   BS accounts correctly translate at ClosingRate, and all Cash Flow accounts translate at AverageRate.    First question,  how does the Cash Flow translate at AverageRate?  According to the OneStream XF Reference Guide,  Flow type

Originally posted by Thomas Regehr Hello,  We have a report that needs to transition between the Budget scenario and Actual scenario as we move through the year.  Ideally the user will select a period, and depending if the period in the displayed column is current or in the past it will show Actual, else it will show Budget.  I was asked to complete this without creating a new scenario.   I tried the below, but didn't have any success.       Public Function PastBudgetCurrFutActual(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As String Try Dim currTime = args.NameValuePairs.XFGetValue("CurrentTime") Dim timeId As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Time, currTime) Dim colTime = args.NameValuePairs.XFGetValue("ColTime") Dim timeId2 As Integer = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Time, colTime) If timeId >= TimeId2 Return "S#Actual" Else R

Hi all How can I avoid the ampersand in string operations? I need to concatenate a pov for a calculation, but with all the ampersands, it looks complicated: "A#" & accountName & ":F#" & flowName & ":UD2#" & ud2Name & ":UD4#" & ud4Name