Rules

Hi, In the GetTableView function of a Spreadsheet BR, I extract data from a database table (XFW_TLP_Register). For a numeric value (e.g. NCode1) negative numbers are by default extracted to Excel as varchar (with “,” as decimal separator) and positive numbers as numeric values (with “.” as decimal separator). Hence, the value cannot be submitted back since it’s trying to submit a text value into a numeric database column. If I cast the column (in my SQL query in the GetTableView function) to numeric it works but then it will not display digits in the Excel. Any idea how to solve this?    

Hi, I am trying to achieve variance and change sign flip using UD8 member and based on account type however it is working for some accounts and not for all types of accounts. If someone handles the situation thru UD8 member, please share your experience or thoughts here. Thank you.  Here is the quick code snipped what I am using.  Dim acctType As String = api.Account.GetAccountType(api.Pov.Account.MemberId).NameIf api.cons.IsLocalCurrencyForEntity And acctType.XFEqualsIgnoreCase("Asset") Then  api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1))")Else If api.cons.IsLocalCurrencyForEntity And acctType.XFEqualsIgnoreCase("Liability") Then  api.data.Calculate("UD8#MonthlyVariance=RemoveZeros((U8#None:T#Pov-U8#None:T#PovPrior1)*(-1))")End If 

I am using a BR to extract the Business Rules as part of an update process. I am using the code from here: https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Import-XML-to-create-objects/m-p/32087 I can extract to a zip file which contains the BRs wrapped up in their application area XMLs eg ApplicationDashboards.xml contains all the dashboard type BRs. I want to extract the BRs as individual files i.e. the format you get when you extract the xfproj file without checking 'Extract to zip'. Anyone know how to do that? Thanks

I am developing a member formula which uses Percent Of Ownership in calculation. I am using the following to get Percent of Ownership: Dim pOwn As Decimal = api.Entity.PercentOwnership(entityID, parentID) In the context of this calculation I have parentID but I only have a name for the entity. How can I get the Member ID from the Member Name? Additional question: what will the code above return if there is NO parent/child relationship between parentID and entityID? "0"? Thanks in advance. Yan  

Hi all,  I'm after a bit of advice. We have a STAT cube which has BS and PL data and there is a MGMT cube which has PL data but goes to the level of Regions (UD1) and Departments (UD2).  There is a Stat entity structure and a Mgmt entity structure, with the only difference being S_ and M_ prefixes. So Group is S_GRP and M_GRP respectively.  I have been requested to build a report to calculate creditor outstanding days by taking the Creditors balance in S_GRP and dividing by COS in M_GRP split by Regions (UD1) and multiplying by days in the month.  I started to create some new reporting accounts to calculate these, but then I realised there's a mix of dimensions between the cubes so it's not as straightforward as using api.data.calculate etc.  Does this seem like something that is possible? Currently the users utilise the XFGetCell function in the add-in, but want to have something more automated.  Any advice is appreciated.  Thanks

Hi all, I am baffled as to why I am unable to retrieve the Text2 ( or 3 or 4 ) attribute value in a workflow profile. I use the following code to get the Text1 value : Dim wfClusterPK As WorkflowUnitClusterPk = si.WorkflowClusterPk Dim wfScenarioTypeID As Integer = BRApi.Workflow.General.GetScenarioTypeId(si, wfClusterPK) Dim sVarStep2 As String =BRApi.Workflow.Metadata.GetParent(si,args.SubstVarSourceInfo.WorkflowUnitInfo.ProfileName).GetAttributeValue(wfScenarioTypeID, 18000) However, if I substitute 20000, 22000 or 24000 as the attribute it will not apply this in the business rule. I have checked the database tables and the values are there with those attribute values and the scenarioTypeId is set to 0 for Actual. Here is the database filter, so the value are there, but why even I hard code the scenariotype as 0 will it not retrieve these values for me. At present, am only just trying to get them to show in the error log. I am running the cubeview from the workflow profile, previou

Hi All Does anyone know if you can get the current member name in a Member Formula? As an example, I have the UD8 member UD8 AverageRate_Budget - I want that information without manually adding a variable. I have a lot of members whose member names contain all the information for their value. It would save me a lot of time if I had one script that I could copy to all the members without editing them to update the member name. The members are DynamicCalc if that makes a difference. I have tried args.DataCellArgs.FunctionName and args.CustomCalculateArgs.FunctionName but they both raise an error. Thanks Marcus

Hi, anyone has worked with a similar member script or rule that has a conditions - eg assessed loss utilised in the current year is based on higher of $1 million and 80% of Current Taxable Income account. This is limited to the assessed loss carried forward account (Assessed loss carried forward is assessed loss bough forward, less assessed loss utilised in the current year). 

Hi all,  I recently was going through a V8 upgrade and found that the documentation on ERPConnect with SIC was quite sparse.  I hope this post helps anyone who might be struggling as I was a few days ago.   Smart Integration Functions in Version 8 + now must make all calls using ERPConnect and referencing Dlls stored on the SIC Server.  Make sure you add your 'ERPConnectStandard20.dll' to the referenced assemblies property of the Smart Integration Function, this will allow ERPConnect and ERPConnect.Utils to be imported into your SIF.  You will likely also notice there is no IntelliSense for 3rd Party Dlls for SIF, which makes things a little tougher. Use this link for specifics related to the ERP Connect API.  ERPConnect.Utils.ReadTable - ERPConnect HelpCenter (theobald-software.com)   Smart Integration Function Snippet using ERPConnect  Imports System Imports System.Collections.Generic Imports System.Data Imports System.Data.Common Imp

We have a job that leverages the Global Time and Scenario to copy Actuals over into our Fcst Scenario.  My question is around the ability of a BR to change data in a certified scenario.  From what we are finding, even if we certify the Fcst Scenario, when the rule runs and the Global period has not changed, the BR copies/updates the Fcst period with Actuals.  Part of the problem is that sometimes we have to move the period forward for our operational folks while we are still working on Fcst so the Global Fcst scenario stays the same but the period get moved.  This then creates a situation where our DM jobs update the current period (Import Origin) with $$ which throws off our Fcst. My question is:  Is there a systematic way to prevent this or do I need to add logic in my BR to check the status of something (note: this copy is not tied to any WF's, just a general copy this to that). Goal is that once we certify our Fcst WF's (Entities are tied to these WF's), no

I am writing to the community to see if anyone knows of a way to programmatically update a Parameter from a Business Rule to update the Filter Property (for a Member List or really any type of list). I can see the way to set the "Default Value" of a property but not a way to update the List which the property gets its values from. The member Filter is set to just reference Net Sales, but what I'd like to really do here is build the Member Filter from a list that I create in a business rule (Dashboard Extender, call to an Extender Rule, etc). I only see these options: This will set the value to 2024, but it has to be already in the List... This throws an error after the 1st entry, but still won't render in a combo box because I'm not really adding it to the List of Member Filters... This will set the 1st value, but doesn't work for a list ... only 1 value... I don't see in the api an ability to modify the Filter property or really inspect a parameter and change its other properties.

Hi there!I am using OS v8.1 and SIC with latest ERPConnectStandard20.dll and the other dlls.I can connect to SAP and retrieve data using READTABLE. Now I am trying to use CreateFunction but I am struggling.In my Smart Integration Function I have this :  Imports System Imports System.Collections.Generic Imports System.Data Imports System.Data.Common Imports System.Globalization Imports System.IO Imports System.Linq 'Added for SAP ERP Connect Imports Microsoft.VisualBasic Imports ERPConnect Imports ERPConnect.Utils Namespace OneStream.BusinessRule.SmartIntegrationFunction.SAPTest8 Public Class MainClass Public Shared Function RunOperation() As DataTable Dim r3Conn = New R3Connection("1.1.1.1", "00", "user", "pass", "EN", "224") r3Conn.Protocol = ClientProtocol.NWRFC r3Conn.Open() Dim r3Func As RFCFunction = r3Conn.CreateFunction("BAPI_MATERIAL_GET_DETAIL") r3Func.Exports("MATERIAL").ParamValue = "1001" r3Func.Execute() Return r3Func.Imports("MATERIA

I have an interesting error appear when attempting to run an XFBR that simply returns "10"; in a C# XFBR rule.  The stack trace is below.  But this does appear to be an issue with the OneStream Libraries themselves rather than anything that has been developed - is that right?   Summary: Error in DbConnInfo.~DbConnInfo(). DbConnInfo should be used in a 'using' statement or Close() should be called explicitly. ----------------------------------------Description: Error in DbConnInfo.~DbConnInfo(). DbConnInfo should be used in a 'using' statement or Close() should be called explicitly.Error Time: 8/9/2024 10:42:39 AMError Level: ErrorTier: AppServerUser: david.fraserApplication: App_NameApp Server: NLCFT6APP1App Server Version: 8.2.1.16005App Server OS Version: Microsoft Windows NT 10.0.17763.0Session ID: 1650096c-0df5-4e1d-aac3-8ad9ad2561a3Error Log ID: 9b3d53fe-a285-4dbb-ba17-e9178258e139 Total Memory: 25,769,267,200 (24.00 GB)Memory In Use: 6,983,364,608 (6.50 GB)Private

Hello community,  Has anyone used a cube view extender rule to change between the portrait/landscape paper orientation on a PDF report? In our example, we are trying to use a parameter that allows the user to select the column template set needed, and although the idea of calling out the CV column template name sounds logical, I don't think OS has the capability to do this, similar to a column name as shown in the below example. Putting it out there just in case, as I promised our user we will explore different avenues.  

hi we are using stored calc to apply tax rate on pre tax income and eventually calculating net income. Tax rate is pretty static throughout the year but it may change in middle of the year. we've given users to enter tax rate as applicable throughout the year so this tax computation remains dynamic. Actuals scenario is YTD.  the issue we're facing is if we change the % (lets say 20% up until Jan to May and starting April, it goes up to 25%). In other words, we want April YTD tax to be 25%, it is not footing to 25% at lower level. any ideas how to achieve this?  

Hi All,  Is there a way to the use cb#cube in the data buffer FilterMembers?  I'm trying the following, but it does not work.    Dim sourceBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(Cb#Cube:V#Periodic, [A#123.Base], [U7#Top.Base]))",)   Thanks.

Hello I would like to change the 'can modify data' parameter of the two columns via a business rules : I want to put a conditionnal rule that says if the column "is store" = yes the corresponding cells in the columns "Opening Date" and "Closing Date" seizable if not the cells won't seizable. if anyone has already done this , your help will be much appreciated. thank's. Yanis.

  Hi All In the below example value is displayed on every single combination of UDs.  This should only be calculated and written to each UD7 separately but calculated on Tops and displayed only on “NotDefined” for all other UDs. For any other POV combination- the result returned should be zero. Can we do something like this?   Dim Tops_NoUD7 As String = ":O#Top:I#Top:F#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#Top:U8#Top"Dim dEbit As Decimal = api.Data.GetDataCell("A#EBIT" & Tops_NoUD7).CellAmountDim dSalesProd As Decimal = api.Data.GetDataCell("A#SALESPROD" & Tops_NoUD7).CellAmountDim sEbitProd As Decimal = (dEbit/dSalesProd)*100Return sEbitProd

I have an XFBR on the time filter on a Data Management step that pulls a list of time. When I run it on the Data Management tab it runs just fine and works. Now when i try to put that on a button to run on a dashboard, I get an error. I have tried using the Execute Data Management Sequence Server Task as well as running it through an extender rule. I am passing the required parameters to the either situation but im still getting an error? Why would it work on the Data Management Tab and not work while running it through a button?      

I have recently had the request of creating a Dimensions CSV Export File with Parent/Child Relationships including some member properties. I realised this wasn’t straightforward when Extensibility is used across the dimensionality. Specially with the requirement of presenting the hierarchy with the correct Sort Order. See Example Below: With the following set up: The CSV Out Put should be: I am sharing the solution we have implemented in case to help anyone with the same type of request/requirement. The attached Extender Business rule could be used in a Data Management Step as follows: It will create CSV file within the User Temp Folder with all the Parent Child Relationships including some properties: Currency (Only For Entity Type Dim), Text1-Text8. The solution will extract All Dimensions Type, including system dimensions. The code could be easily adapted to work of a Dimension Type List instead. Also, additional properties could be extracted as needed by modifying the Sub Routin

Hi all,   I came across a very helpful post that shows how to automate extracting metadata for backup. (original code at the bottom of post) https://community.onestreamsoftware.com/t5/Accepted-Code-Samples/Extender-Automate-Application-Metadata-Backup/ta-p/6914    I tried this business rule and it works perfectly, however I am looking for something a little more specific since this returns a massive dump of data. Basically, I am looking to automate a metadata xml extract of specific dimensions and their members / relationships separately into different files and combine them into a zip package. Is there any documentation that shows how to edit this xmlOptions syntax below to include specified return xml values instead of just extracting all items?  'Set the extract options Dim xmlOptions As New XmlExtractOptions xmlOptions.ExtractAllItems = True   Ideally in the package, each dimension would have two files. I would have two files as such: - TopU4 Members - TopU

I have an issue I would be very interested in getting community's take on and I would greatly appreciate any direction: I am trying to deal with the case of a nested ownership: one  of our 100% owned subs owns a minority stake in the other sub, At the top consolidated level there should be no Minority Interest , but at intermediary consolidated level there should be. I created an intercompany account that is calculated at that intermediary consolidated level. Calculation seem to work fine. However, when consolidation reaches the first common parent between the 2 subs, elimination of this new account is not being triggered, even though correct partner is assigned (IC Matching report matches the relevant amounts) Here is the calculation: If (api.Cons.IsRelationshipLevel And api.Entity.OwnershipType().ToString = "Custom1") Dim entityID As Integer = api.POV.Entity.Memberpk.Memberid Dim parentID As Integer = api.POV.Parent.Memberpk.Memberid Dim pOwn As Decimal = api.Entity.Percent

I was wondering if it is even possible to create a transformation rule which involves different dimensions like the following composite A#[5000-0002]:UD4#[2010]:UD2#[2412] but adding ranges to it. so instead of having 10 different TR that have the UD4 going from 2010, 2011, 2012 to 2020, have only one composite including a range for the UD4 mentioning 2010~2020. Is that possible? what would be the TR be?Thanks    

Does anyone know how to get access to the "OneStreamGatewayService" Import through a Smart Integration Function in OneStream WinApp.  I am trying to run remote business rules/ remote queries through SIC but cannot seem to find any documentation related to SIC Server assemblies needed, or BR Assemblies needed to access this Import.   See line 10 below  

Hello Community, I have a puzzler for you.  My confirmation rule is set to evaluate a data intersection.  If the intersection is greater than zero, then an annotation is required in another intersection. I have tested both of the intersections in a quick view to ensure I have identified the correct data intersection members.   When tested, the rule identifies the numeric intersection (first condition), delivers the error message,  but does not identify the annotation intersection as being present.  Below is the rule: 'Check an Account value and if it is greater than zero, request a comment be made to a Cube View under the Analysis section of this pageargs.ConfirmationRuleArgs.DisplayValue = api.Data.GetDataCell("Cb#Consol:E#" & api.Pov.Entity.Name &":T#WFPrior3:C#Local:S#Actual:V#YTD:A#9610:F#EndBalInput:O#BeforeAdj:I#None:U1#Admin:U2#Top2:U3#TLOB:U4#None:U5#None:U6#None:U7#USGAAP:U8#None").cellAmountIf args.ConfirmationRuleArgs.DisplayValue = 0 Th