Rules

Hi OneStreamers,  is it somehow possible to reference a System Extender rule from an application level business rule (e.g. Dashboard XFBR String)? We would need this to centralize some functions, that should be available and standardized across multiple OneStream applications.   Thanks Markus

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)      

Hello OneStream Community, I'm seeking guidance on a functionality related to POV selection within the OneStream application and its transmission to Excel. Specifically, I'm curious if it's feasible to automatically capture the user's selected POV from OneStream and populate it into Excel cells using table views once the Excel application is initiated from OneStream. In my current setup, I've created a custom cube view that allows users to select certain POV members. There is also a custom button that exports the cube view in Excel using spreadsheet rules. The goal now is to have the user-selected POV members auto-populate within Excel cells. Has anyone implemented this functionality or does anyone know if this is possible? Any guidance or insights would be greatly appreciated. Thanks in advance for your help!

Hi - I have a dashboard XFBR business rule that calculates totals for members with a specific text value in a cube view. The rule is working as designed, and I have a couple GetDataCell functions in separate columns that reference these totals. However, I also have a UD8 member for variance analysis purposes that need the totals to determine whether or not a value exceeded a threshold - how do I pass what my business rule calculates into my UD8 formula? Here's the business rule for reference: If args.FunctionName.XFEqualsIgnoreCase("CVMath") Then 'Math function used in CV Dim MathFunction As String = "GetDataCell(" 'Suffix for GetDataCell function Dim Suffix As String = "):Name(Total)" 'Get ColNames to use in Math Function Dim ColNametoSum As String = args.NameValuePairs.XFGetValue("ColName") 'Prefix Dim ColMath As String = "CVC(" & ColNametoSum & "," 'Get cube view member filter Dim cvMemberFilter As String = arg

Hello All, Could be a very simple question but need help. I have a calculation F#Flowmember= T#TimeMember1+T#TimeMember2 +T#TimeMember3 in Local which works fine.I want a similar calc in USD as well, so i run the same custom calc through DM Step just changing the Consol Member to USD. Now the problem is, when i run consolidation. the USD Calculated values are overwritten by translation numbers which i dont want to and retain the custom cal which i did in USD. Any idea on this one..THanks in advance..

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. 

Dear Community,   I'm currently working with event handlers and need to retrieve workflow-related information within the FinalizeValidateIntersect operation. I'm using si.WorkflowClusterPk to access this data, but I'm exploring alternative approaches to avoid relying solely on this method. Thank You.

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

Where can we find snippets for OneStream C# Business rule ? I have searched on marketplace but did not find.

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