Rules

I am looking to create a FX Rate finance rule or calculate to go get the planning scenario's last actual month's closing rate from the ClosingRate FX table. I am using the consolidation member C#Aggregated in the planning scenarios.  I know that C#Aggregated executes business rules at the base level only. Is there a workaround here or if I use C#Aggregated am I stuck creating FX rate tables for each planning scenario and copying last actual month rates forward to the planning periods? Appreciate any help or guidance.  Thanks 

I am trying out a complex expression in Time Data Source. The issue is the next: I have a Forecast scenario type with input frequency monthly and on the other hand I have a Long Term scenario type with input frequency yearly. Therefore, the colum time in the file is yearly, so I developed a complex expression where I can load depending on neither scenario type year 2024 or WFTime (forecast for this case) or scenario type year 2024 + M12 or WFTime + M12 (Long Term for this case). This is my complex expression (that is not working): Dim vYear As String = args.Value Dim scenId As Integer=api.CurrentDataClusterKey.ScenarioIDDim scenType As ScenarioType = BRApi.Finance.Scenario.GetScenarioType(si,scenId) If scenType=ScenarioType.Forecast ThenReturn vYear + "M12"ElseReturn vYearEnd If I appreciate all your comments, Thank you.

Hi, This formula is working, but it's returning 100% for each Entity. Is there something I'm missing regarding Entity? Will this work in a member formula or only in a business rule? Dim entity As String = api.pov.entity.NameDim entityID As String = api.pov.entity.MemberIDDim OwnValue As Decimal = api.entity.percentOwnership(entityID) If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Thenapi.Data.Calculate("A#NCI_IS:V#Periodic:F#EndBalLoad:O#None:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None= "& _"RemoveZeros(A#Income_Loss_before_Noncontrolling_Interests:V#Periodic:C#Local:F#EndBalLoad:O#Top:I#Top:U1#Top:U2#Top:U3#None:U4#None:U5#None:U6#None:U7#Top:U8#None * ( ("& OwnValue &")))")End If

Hi, has any one used DocumentFormat.OpenXml.dll coming v8 to create excel docs? It looks like it is working from standard extender BR once you add reference to DocumentFormat.OpenXml.dll and System.IO.Packaging.dll but it does not work when you do in a file within Workspace assembly as it complains about not finding the referenced System.IO.Packaging.dll even if the dependency is added   Thanks  

I am trying to get the journal post group in the workflow profile properties using JournalsEventHandler Business rule.  Does anyone have done this before? Is it possible to get the journal post group value from the Workflow Profile?

I am trying to call a database stored procedure from an extensibility rule.  The stored procedure returns an OUTPUT parameter, but I'm unsure how to reference that OUTPUT parameter, using the DBParamInfo object and ParameterDirection setting.  Can someone give guidance on the proper usage for OUTPUT. Stored-Procedure CREATE OR ALTER PROCEDURE [dbo].[TEST_OUTPUT_PARAM_PROC] ( @OUT_PARAM_VALUE VARCHAR(100) OUTPUT ) AS BEGIN --Do stuff that needs to be done here SELECT @OUT_PARAM_VALUE = 'Param from Procedure'; END GO Extensibility Rule 'definition DB Call To stored procedure Dim OutParamValue As String = "" Dim tsSQL_CallProcwithOutputParamter As String = "DECLARE RC INT EXECUTE RC = TEST_OUTPUT_PARAM_PROC @OUT_PARAM_VALUE OUTPUT" Dim parameters As New List(Of DbParamInfo) parameters.Add(New DbParamInfo("@OUT_PARAM_VALUE", OutParamValue,ParameterDirection.Output)) 'paramDirection: Using dbC

Hello Everyone, I need a Literal parameter which will dynamically calculate something like the prior forecast based on another parameter so i ended up writing an XFBR for that Literal parameter, something like below XFBR(HelperRule,GetPriorForecast). And now i need this Prior Forecast value for some Finance calculations. So I tried to retrieve the value of Parameter in the finance rule by using  BRApi.Dashboards.Parameters.GetLiteralParameterValueBut it returns the same "XFBR (Helper Rule, Get Prior Forecast)" rather than the Actual String Value. Strangely enough, if i use the same XFBR for Scenario Filter in a DM step, it picks up the proper string value. Curious Why would it not work when I try to retrieve its value in a finance BR?

Hello OneStream Experts: I created an account called MyAcct to store calculation. I run the business rule in Data Management step and put Net Income to MyAcct. api.Data.Calculate("A#MyAcct = A#NETINC") After I run the business rule, the Calc Status in View dimension show CA for both NETINC and MyAcct. However, after I run right click the data cell to run consolidation or Calculation, the value in MyAcct got lost, the calc Status for NETINC became OK. I wonder why my calculated account lost the data value after I run consolidation. Thank you advance for any answers.

Hello OneStream experts: I created and account=MyAccount and put member formula. api.Data.Calculate("A#MyAccount = A#NETINC") I set AccountType=Dynamic Calc Formula Type=Formula Pass 1 I am able to compile the syntax and run the calculation successfully. But MyAccount account member showing no data from my quick view excel sheet. My NETINC account has data. I wonder why the calculation is not working. Thank you for any feedbacks. 

Hello - Does anyone used the AWS SDK (Dot Net) to download the file to the OS batch harvest folder? using SIC if so, could you please provide high level steps?   Any Help would be appreciated.

Hi , I used the journal event handler extensibility rule available on the forum in order to segregate the approval and post process. The issue that i am having is that once the user submits the journal or quick posts it the page does not refresh on its own and the user has to manually refresh the page to the see the journal status . Basically it should show the submitted status and approval and reject buttons highlighted but it only shows that after the user manually refreshes. Is there a way we can refresh the page after the message is displayed?  

Hi, I am trying to create an account member formula where I have Account A divided by Account B, but the scenario in the denominator should be decided based on Text3 in the POV Scenario. E.g. A#10000:S#Actual / A#20000:"& SourceScenario &" SourceScenario = IF Pov.Time + 12  > Scenario Text 3, ThenSourceScenario = Forecast, ElseSourceScenario = Actual   This is how far Ive come but it is not working yet: 'Use FC as scenario if POV time is more than 12 months more than last seeded actual month in FC.If api.Pov.Scenario.Text(3) > api.Time.AddTimePeriods(12, True) ThenDenominatorScenario = ":S#FC_WIP"ElseDenominatorScenario = ":S#ActualL4LAtRealRate"End If

Hello! Any inputs or documentation for configuring the SFTP process with SIC?Recently we have installed the SIC and configured successfully and was able to pull the data. We are facing issues with the SFTP process with SIC, followed the SIC admin guide and configured by creating a new gateway and copied the Gateway info to the OneStream Smart Integration Connector Local GatewayServer Configuration Utility, but the status in OneStream shows as 'offline'.TIA!!

Hi, I am using the LoadCustomTableUsingExcel function to load an Excel file to an application table. The function seems to be working fine in most cases. However, I am having issue with tables having unique constraints. The table has Payroll, PeopleGroup and Amount columns. The unique constraint was added because users can also insert into the table using an SQL editor component, so we want to avoid them do be able to insert duplicated rows. When trying to load a row which already exist in the table using the LoadCustomTableUsingExcel  function I get the following error (example): “Column ‘Payroll, PeopleGroup' is constrained to be unique. Value 'Payroll_AE, PplGroup001' is already present.” This issue only appears when loading in Merge. Replace works fine. Normally (with table not having a unique constraint) it is not an issue loading rows that are already in the application table because the Merge tag in the Excel file make so that only new rows are added to the table while rows

I am trying to get the Journal Post Group from Workflow Profile Properties Using JournalsEventHandler Business rule. Does anyone have done this before? Is it possible to get the Journal Post Group from the Workflow Profile?  

I am trying to revamp the data integration between OneStream and Oracle Financial Cloud. The implementation partner used HttpWebRequest throughout the integration business rules but under platform 8.2 this functionality is now obsolete. Is there a working sample from OneStream on how to employ HttpClient with Oracle? 

Hello ... Under platform 8.2, we get warnings in business rules that reference function QueueDataMgmtSequence. Does anyone know what function should be utilized instead?  1) Warning at line 114: 'Public Overloads Function QueueDataMgmtSequence(si As SessionInfo, sequenceName As String, customSubstVars As Dictionary(Of String, String)) As TaskActivityItem' is obsolete: 'This is a temporary function used by Marketplace Solutions for backwards compatibility with old XF versions. Please change your code to use supported functionality.'.

I am looking for a sample code for SqlTableEditorSaveData function to restrict saving No value from my SQLtable editor drop down. I have Yes, No  and Yes_optional in my drop down but I don't want user to save No option as yes and no are based on some value criteria but they can select Yes_Optional.      

Hello All, Is there a quicker way to get the list of entities having data in a particular cube for a certain scenario and year combination, without using a finance business rule? One of the options I am thinking is to query the DataRecordsxxxx tables, but I believe that is not recommended...

Hello Experts, I am looking to write a Dashboard Extender rule by which I can fetch the list of all the dashboards under a Dashboard Group and set the value as a delimited list to a Parameter.  Is it possible ?  The idea is to dynamically populate a list with new Dashboards as they are added.  Please suggest other workarounds if the above is not feasible.    Many Thanks   

Hello Everyone,We have been using ExecuteFileHarvestBatch to automate our Data Loads. However I could not make it work for Direct Load profiles.Is anyone aware if we have api support to automate Direct Loads?

Hi, is there a limit on the number of cells that can be updated through Table Views?  We have a large table, 250 rows by 140 columns.  When we try to update all values at once, there are many cells that do not update.  When we only load 10 or so rows at a time, it updates all values correctly. thanks, Greg

OneStream is the best EPM app for consolidation and everything planning - it is going to kill Hyperion / Essbase !! Does anyone have a sample business rule BR for sorting rows in a column based on values in a column.  e.g. in screen shot, I want to sort by ascending order of values (NR) in column B  in Cubeview? I probably have a written something similar long time ago, but would like to see if someone else wrote a better BR.

Hi Team, Is there any way to add/remove users/groups via business rule(dynamically).  i want to add user dynamically i.e not by clicking manually System---Security---Create user. Thank you in Advance really appreciate.   

Unposting