Rules

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 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?

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.

Unposting

Hello everyone,I am trying to create a table in OS with the fx rates by calling an APIwhile calling the API, i can get the data needed but i have this error <Type 'SqlBulkCopy' is not defined.> that's doesn't allow me to copy the data in a table in OS.this is a part of the code, the lines in orange are the ones not working. 'create table ' #Region "Create Table Currencies_Mantu" Dim sSQL As String = $" IF OBJECT_ID('Table_rate_mantu') IS NOT NULL BEGIN     DROP TABLE Table_rate_mantu END   CREATE TABLE Table_rate_mantu (            fromCurrency varchar(255),     exchangeRate numeric(18, 5),     date varchar(255)   ); " Dim strPeriod As String = "" Dim strCurrency As String = "" Dim strPivot As String = "" Dim strclo As Double = 0 Dim vavg As Double = 0 For Each row As DataRow In dt.Rows strCurrency = row("fromCurrency") ' strclo = row("Value1") vavg = row("exchangeRate") strPeriod = row("Date")

hi Experts, i've a member formula (stored calc) to calculate %. My scenario type is periodic. i'm calculating % for various P&L accounts. M1,M2 and M3 %s is correct but for quarter, system is adding M1%, m2% and m3% which is not right. i want to write specific logic for qtr..for example: take quarterly expense and divide by revenue to calculate Qtrly %. is there a way i can override Qtr%? i may use dynamic calc to override V#QTD in report. but just wanted to know if there is more elegant way of doing this. pls guide

I'm trying to pull a Text field from a workflow in a transformation event handler rule but ran into an issue.  I originally tried to use api.Workflow.GetWFText1() but that does not work in a transformation event handler rule.  Is there a way to get it using a BRAPI

Is the any documentation available on all of the listed functions in OneStream for namespaces/libraries? other than API Overview. 

Hi All, I want to ask about my issue on FDXExecuteCubeView. It cannot export annotation columns or columns with text even I have put the parameter includeCellTextCols to True. Here's the annotation column member filter :  Fyi, the annotation appears in cube view but not in data table result from FDXExecuteCubeView.   Thanks for your help.  

Can anyone confirm if a GetDataBuffer function only pulls back real data?  In other words it will not return dynamically calculated data (say from a dynamic account) or derived data?  Trying to understand the limiitation of what data the function will and will not return.  Thanks.   

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