Rules

I call a data adapter and store results in DataTable in below snippets. How can I bulk load OneStream data in this DataTable to external database table? External database connection is ready and working in below method. Using dbConnApp As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, externalConnectinoName)brapi.Database.ExecuteSql(dbConnApp, sqlQuery, False) Using ds As DataSet = brapi.Dashboards.Process.GetAdoDataSetForAdapter(si, isSystemLevel, workspaceID, adapterName, resultsTableName, dctVars) If ds.Tables.Count > 0 Then ' If there are >0 tables in the results Using dt As DataTable = ds.Tables(0).Copy() 'Remove unsed columns dt.Columns.Remove("Cube") dt.Columns.Remove("TransformationRuleProfile") For Each Col As DataColumn In dt.Columns BRApi.ErrorLog.LogMessage(si, Col.ColumnName) Next Return dt End Using End If End Using  

I would like to export data from existing OneStream dashboard and load it into external database table. I did some research and below is a draft business rule written in Visual Basic. What it does is to call data adapter and store the results. Next step is to load data from DataTable into external database table. Questions is, it gives a warning "Function GetAdoDataSetForAdapter is obsolete", what is the current supported function?    Dim dctVars As New Dictionary(Of String, String) Using ds As DataSet = brapi.Dashboards.Process.GetAdoDataSetForAdapter(si, False, "AdapterName", "ResultsTable", dctVars) If ds.Tables.Count > 0 Then ' -- If there are >0 tables in the results Using dt As DataTable = ds.Tables(0).Copy() Return dt End Using End If End Using  

Hi, For user maintenance purposes we'd like to extract information like user email address etc, using business rules. I've tried using BRApi.Security.Admin.* but that only seems to support extracting a few of the fields, like name, status and text fields. If anyone has any ideas or suggestions on how to accomplish this, it'd be greatly appreciated if you'd share. Have a good one, Johann

Hi guru's  I have created some memberlist logic which should retrieve a list of entities from a datatable. So far so good, the code seems to work (logging returns my expected entities) but the return function doesn't do anything... Looks like I'm missing something but I do not know what...If someone has the magic solution I'll give my kudos 🙂   Thanks alot!   Rgrds Jorrit   Case Is = FinanceFunctionType.MemberList 'BR String: E#Root.CustomMemberList(BRName=Mars_memberlists, MemberListName=[Mars_CustomMemberList], Striplist=|!prm_StriplistName!|)   If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("Mars_CustomMemberList") Then   Dim sStriplist As String = args.MemberListArgs.NameValuePairs("Striplist") ' Build the header     Dim listHeader As New MemberListHeader("Mars_CustomMemberList") ' Construct the SQL query Dim tempsql As New Text.StringBuilder tempsql.AppendLine("SELECT UN_ID") tempsql.AppendLine("FROM XFC_Mars_Striplists")

I am working with a Dashboard Extender BR and need to get the value of a cell. In another section of the code I am able to get the value of a cell in the V#Annotation but have not been able to figure out how to get the cell value of the V#Periodic. Any thoughts?

I found a C# code library and I'd like to use this class in VB.Net.  Here are the steps I've taken so far. 1. In my VB.Net Rule I've referenced the C# assemblies. 2. In my VB.Net Rule I've created a new object for the C# class 'Create an object for the class defined in External Assembly (Toolbox) so we can use methods in the classDim XIRRWrapper As New OneStream.BusinessRule.Finance.XIRR.CalculationWrapper 3. Now I'm able to create objects in the C# referenced assemblies in VB.Net Dim cashFlows = New List(Of OneStream.BusinessRule.Finance.XIRR.CashFlowDates ) From {New OneStream.BusinessRule.Finance.XIRR.CashFlowDates(-1000, New DateTime(2017, 1, 1)),New OneStream.BusinessRule.Finance.XIRR.CashFlowDates(500, New DateTime(2017, 7, 1)),New OneStream.BusinessRule.Finance.XIRR.CashFlowDates(507.5, New DateTime(2018, 1, 1))} 4. And finally able to run a function (LINE 55 which produces a warning) api.LogMessage( XIRRWrapper.XIRR(cashFlows, 6).ToString() ) I get the correct result.&nb

Hello OneStream Community,  I am wondering if anyone knows of a way to access the journal name and the user who triggered an action on a journal through a Journal Event Handler Business Rule.   Dim returnValue As Object = args.DefaultReturnValue args.UseReturnValueFromBusinessRule = False args.Cancel = False Select Case args.OperationName '1.Submit 2.Approve 3.Reject Case Is = BREventOperationType.Journals.SubmitJournal If (args.IsBeforeEvent) Then si.WorkflowClusterPk.UniqueStringI Dim wfProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, _ si.WorkflowClusterPk.ProfileKey) Me.AddToLog(si, $"WFProfileName = {wfProfileInfo.NameAndDescription} - Journal Submitted") Dim wfGroupType = 6100 'Process wfGroupType Dim wfGroupUsers = BRAPI.Workflow.General.GetUsersInWorkflowGroup(si, _ si.WorkflowClusterPk, wfGroupType, True, String.Empty)      

Is there a way to get a given Workflows JournalProcess, Approval and Submit Security Groups through a Business Rule.  I need to send an email to these users in the security group as these events happen.      

I have a dashboard that pulls Workflow status information.  It is currently hard coded to look at a top level Workflow parent, as it is for Group use only. I want to make this dashboard available for the regions too, and change the hard coded workflow selection in the dashboard to instead run from the Text 1 property in the user's security setup. How can I do that?  I managed to pull Text 1 using a SQL query, but this only seems to work for me when I set the Parameter as a Bound List. The problem with that is that the Bound list pops up as a selection when the dashboard is run, which I don't want to happen. It should be seamless. 

Can I execute an sql statement against a data table created in a business rule?  I created a table to use in a data set business rule, which returns data to the data adapter.    e.g. if I created dt1 using : Dim dt1 As DataTable = BRApi.Database.ExecuteSql(dbConnApp, strSQL.ToString, True) Can I run a query against dt1, doing something like "select * from dt1" or in other words can I get the tablename for dt1 and then execute a query?

Hi Team, We have 2 similar processes under one member in workflow. I need to lock process A, when B is completed. Could you please advise what method to use?  Many thanks!

When I have a BR, and need to pull in a parameter, I use the following statement or one similar, I create the parameter in a Dashboard. 'Dim scenario As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "Forecast_Scenario") How do you bring a parameter into SQL ?  I tried the above and it doesn't work.  I would like to set a parameter such as date, and then bring the parameter inside SQL, the same way I do as a BR. Thanks for your help in advance. Tom

I tested the following SQL in a data adapter, accessing a external table Select * FROM Table WHERE "Cost Center" = 'CC_11111111' ; Data returned here, data adapter worked fine. I am using a Business Rule, and can’t seem to get the BR to work if there is a space in the field name of the external table. '****WORKS****getExternalDataSql.AppendLine("Select * from TABLE Where PC = 'XXXX' " ) *****Error*****getExternalDataSql.AppendLine("Select * from TABLE Where [Cost Center] = 'CC_XXXXXXXX' " ) I have tried every combination single quote, quotes, brackets on the Cost Center field. Question: Will an external query work in a BR, where the table has spaces in the names ? Any help or advice will be appreciated.  Thanks  Tom

Hi everyone, Thanks in advance for helping. I am new to tree views and trying to get a simple hierarchy (from the account dimension) to show as a tree view. Part of the structure is shown in the screenshot below: I'm using the following Dashboard Data Set Business Rule that I run using a data adapter linked to my tree view: If args.DataSetName.XFEqualsIgnoreCase("TreeView") 'Declare the account dimPK Dim DimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "actAccount") 'Declare the parent ID + member Dim parentMbrId As Integer = BRApi.Finance.Members.GetMemberId(si, dimTypeId.Account, "CF_000000") Dim parentMbr As Member = BRApi.Finance.Members.GetMember(si, dimTypeId.Account, parentMbrId) 'Declare the child list of the parent Dim childList As List(Of Member) = BRApi.Finance.Members.GetChildren(si, DimPk, ParentMbrId) 'Dim the new XFTreeItem List + properties Dim treeChildren As New List(Of XFTreeItem) Dim textColour As String = XFColors.Black.Name Dim imageSource As String = X

Can anyone share brapi command to fetch the Workflow's Hierarchy Level in the Business Rule?    

I have a data adapter that calls a BR that calls one of the IC methods (I thinned it down here) but what I run the data adapter I get the error that the datatable already belongs to another dataset.  Any help on what that means and how to resolve? Public Shared Function IntercompanyReportBudget(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As DataTableDim Threshold As Integer = args.NameValuePairs.XFGetValue("Threshold")Dim dtInterCoBud As New DataTableUsing dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)Dim interCoMethodQuery As String = "{|WFProfile|}{|WFScenario|}{|WFTime|}{}{C#USD}{V#YTD}{}{" & Threshold & "}{}{}{}{}"dtInterCoBud = BRApi.Database.ExecuteMethodCommand(dbConnApp, XFCommandMethodTypeId.ICMatchingForWorkflowUnitMultiPlug, interCoMethodQuery, "ICBud", Nothing).Tables(0) End UsingReturn dtInterCoBudEnd Function

HI All,  I'm running into an issue when running a Finance Business Rule, essentially this rule creates a data buffer and loops through where U8 member is not 0. From here we have a SQL statement where we're inserting these dimensions and the Business Unit which is derived from the Workflow Text field. Whenever a end user presses the button it should trigger this DM job and Business Rule to delete an existing records for this BU in the table and then add all the records again.  The issue is however that in this case that we're expecting to see 112 records but only seeing around 40. I wrote to the error log and see all the row records of 112, however when inserting into the table we only get around 40, sometimes even less. From what I compared looking at all 112 records vs the 40 in the table, I noticed that from the error most of the records that are the looping through last are being added to the table, but only a few from the beginning. It's almost as if these records are be

Dear Community, For an allocation purpose, I would like to load data currently stored in a data table, into a cube. But to speed up the process, I would like to ignore Validation and Transformation steps. Is there a way to do so ? Regards,

Hi all Can I copy a journal using a business rule? Cheers

I want to round the values for the Revenue account for all UD2 members to whole numbers. Do I have to get the values, round them and write the value, or is there a simpler method?

Dear Team, Does anyone know what's the updated version of function SetCultureInfoForUserToThread? I thought that like with other functions I just can see it's new version using intellisense, however nothing appears here. Many thanks!  

Hi there!I have a Workspace called WK1I have a Dashboard called DB1I have a Component called "George" (coz, why not?)How can I programmatically add George to the DB1 dashboard in WK1 workspace?Note that i already have other components assigned to this DB1.Thanks

Hi team! I'm utilizing the "process cube" function within a JournalsEventHandler. However, whenever the event occurs after the journal is unposted, the process cube fails to execute; it gets stuck and never completes. if the event occurs after the journal is posted, it functions correctly. Not sure what is causing it. here is the piece of code: Else If (args.IsBeforeEvent = False) And _ (args.OperationName.Equals(BREventOperationType.Journals.PostJournal) Or _ args.OperationName.Equals(BREventOperationType.Journals.UnpostJournal)) BRApi.DataQuality.Process.ExecuteProcessCube(si, _ BRApi.Workflow.General.GetWorkflowUnitClusterPk(si, _ EntityWFName, WFScenario, WFTime), _ StepClassificationTypes.ProcessCube, _ False) End If Thanks.  

Hi Everyone,I am trying to allocate forecast data to the gl's based on the last year actuals data. The allocation is not happening when the forecast scenario setting "clear calculated data during calc" is set to TRUE. If I set it as FALSE, the allocation is happening.This data is copied using the setdatacell from the allocated value to the target script in which origin member is O#forms. It seems the value is not written to the origin member if I set the scenario setting to true.can someone explain me what is happening? Also, I tried adding the below calculate and clear code. api.data.calculate($"{script} = 0 * {script}")api.data.clearcalculateddata(script)It worked until I did not change any data, once I changed the data, the allocation did not happen again.Thanks,Jeevan

The Function in my rule : Brapi.Utilities.ExecuteDatamanagementSequence run the sequence in Background and allows me to do further operation in BR even before the step is complete.Is this the expected behavior?How do we avoid this. To run further operation until the complete sequence is complete?