Create CSV with Parent/Child relationships across Extended Dimensions
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 Routine GetHirerachyWithPropertiesDataTable. Credits to Keith Berry (kberry) for providing me with the main core business rule of the solution as well as his help and guidance on this. Here it is the full Extender Code: Imports System Imports System.Collections.Generic Imports System.Data Imports System.Data.Common Imports System.Globalization Imports System.IO Imports System.Linq Imports Microsoft.VisualBasic Imports OneStream.Finance.Database Imports OneStream.Finance.Engine Imports OneStream.Shared.Common Imports OneStream.Shared.Database Imports OneStream.Shared.Engine Imports OneStream.Shared.Wcf Imports OneStream.Stage.Database Imports OneStream.Stage.Engine Namespace OneStream.BusinessRule.Extender.XF_ExportDimsWithProperties Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object Try 'Returns All dimensions in parent/child format including Member Properties 'Fields include Parent, Child, Child Description, RowId, Generation Number, Text1-Text8 Dim sScenarioType As String = args.NameValuePairs.XFGetValue("ScenarioType", String.Empty) Dim Time As String = args.NameValuePairs.XFGetValue("Time", String.Empty) Dim ScenarioTypeId As Integer = ScenarioType.GetItem(sScenarioType).Id Dim TimeId As Integer = BRApi.Finance.Members.GetMemberId(si,dimtypeId.Time,Time) 'Get parent/child Data Table with Required Properties: Dim dtTotalDim As New DataTable 'Will Extract All Members across All Dimension Types: Dim topMbr As String = "Root" 'Loop through all Dimensions Type: For Each sDimType As DimType In DimType.GetAllDimTypes Dim sDimList As List (Of [Dim]) = BRApi.Finance.Dim.GetDims(si,sDimType.Id) 'Process All Dims By DimType and saves a Data Table with Unique Parent/Child Relationships: Dim ParentChildTable As New DataTable For Each sDim As [Dim] In sDimList Dim sDimTable As DataTable = Me.GetParentChildTable(si,api,sDim.name,sDimType.Id,topMbr) ParentChildTable.Merge(sDimTable) Next sDim 'Creates a Unique Parent/Child Data Table: Dim UniqueParentChildTable As DataTable = ParentChildTable.DefaultView.ToTable(True, {"Parent","Child"}) 'Process Relationships Data Table and re-orders members to get the right dimension Sort Order across extended dims: Me.GetHirerachyWithPropertiesDataTable (si, api, sDimType.Id, topMbr,ScenarioTypeId,TimeId,UniqueParentChildTable,dtTotalDim) Next sDimType 'Exports and saves in the User Temp Folder: Me.ExportToCSV(si,globals,"ExportMetadata_AllDimTypes.csv",dtTotalDim) Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Private Function GetParentChildTable (si As SessionInfo, api As FinanceRulesApi, dimName As String, dimTypeId As Integer, topMbr As String) As DataTable Try 'Initialize table Dim dtDim As New DataTable dtDim.Columns.Add("Parent", GetType(String)) dtDim.Columns.Add("Child", GetType(String)) dtDim.Columns.Add("DimName", GetType(String)) dtDim.Columns.Add("Description", GetType(String)) dtDim.Columns.Add("RowId", GetType(Integer)) dtDim.Columns.Add("Gen", GetType(Integer)) 'Initialize stack variables Dim stk As Stack(Of StkMbr) = New Stack(Of StkMbr) Dim stkItem As StkMbr Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName) Dim rowId As Integer = 1 'Push top member onto stack Dim stkTop As New StkMbr stkTop.Name = topMbr stkTop.Parent = "<root>" stkTop.Gen = 0 stk.push(stkTop) 'Get child members and place on stack until stack is empty Do While stk.Count > 0 stkItem = stk.Pop Dim mbrDesc As String = Brapi.finance.Members.GetMember(si,dimTypeId,stkItem.Name).Description dtDim.Rows.Add(stkItem.Parent, stkItem.Name, dimName,mbrDesc,rowId,stkItem.Gen) rowID= rowId + 1 'Get children of current member Dim mbrId As Integer = BRapi.Finance.Members.GetMemberId(si, dimTypeId, stkItem.Name) Dim mbrChildren As List(Of Member) = BRApi.Finance.Members.GetChildren(si,dimPk, mbrId, Nothing) mbrChildren.Reverse If Not mbrChildren Is Nothing Then For Each child As Member In mbrChildren Dim stkChild As New StkMbr stkChild.Name = child.Name stkChild.Parent = stkItem.Name stkChild.Gen = stkItem.Gen + 1 stk.Push(stkChild) Next End If Loop Return dtDim Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Sub GetHirerachyWithPropertiesDataTable (si As SessionInfo, api As FinanceRulesApi, dimTypeId As Integer, topMbr As String, ScenarioTypeId As Integer, Timeid As Integer, ByVal UniqueParentChildTable As DataTable, ByRef DataTable As DataTable) Try 'Initialize table Dim dtDim As DataTable = DataTable 'Initialise Table and Add Colums if Empty: If DataTable.Rows.Count.Equals(0) Then dtDim.Columns.Add("SORT_ORDER", GetType(Integer)) dtDim.Columns.Add("DIMENSION", GetType(String)) dtDim.Columns.Add("MEMBER", GetType(String)) dtDim.Columns.Add("MEMBER_DESC", GetType(String)) dtDim.Columns.Add("BASE", GetType(Boolean)) dtDim.Columns.Add("PARENT", GetType(String)) dtDim.Columns.Add("CURRENCY", GetType(String)) dtDim.Columns.Add("Text1", GetType(String)) dtDim.Columns.Add("Text2", GetType(String)) dtDim.Columns.Add("Text3", GetType(String)) dtDim.Columns.Add("Text4", GetType(String)) dtDim.Columns.Add("Text5", GetType(String)) dtDim.Columns.Add("Text6", GetType(String)) dtDim.Columns.Add("Text7", GetType(String)) dtDim.Columns.Add("Text8", GetType(String)) End If 'Initialize stack variables Dim stk As Stack(Of StkMbr) = New Stack(Of StkMbr) Dim stkItem As StkMbr Dim rowId As Integer = 1 Dim MembProperties As New Object 'Defines Member Properties Object based on DimType: Select Case dimTypeId Case DimType.Entity.Id MembProperties = BRApi.Finance.Entity Case DimType.Account.Id MembProperties = BRApi.Finance.Account Case DimType.Flow.Id MembProperties = BRApi.Finance.Flow Case DimType.UD1.Id, DimType.UD2.Id, DimType.UD3.Id, DimType.UD4.Id, DimType.UD5.Id, DimType.UD6.Id, DimType.UD7.Id, DimType.UD8.Id MembProperties = BRApi.Finance.UD Case DimType.Scenario.Id MembProperties = BRApi.Finance.Scenario Case Else MembProperties = Nothing End Select Dim MemberDisplay As New memberDisplayOptions Dim DimDisplay As New DimDisplayOptions MemberDisplay.IncludeMemberDim = True 'Push top member onto stack Dim stkTop As New StkMbr stkTop.Name = topMbr stkTop.Parent = "<root>" stkTop.Gen = 0 stk.push(stkTop) 'Get child members and place on stack until stack is empty Do While stk.Count > 0 stkItem = stk.Pop Dim MembDimName As String = $"RootDim" Dim Currency As String = "" Dim mbrDesc As String = "" Dim MembId As Integer = -1 Dim text1 = "" Dim text2 = "" Dim text3 = "" Dim text4 = "" Dim text5 = "" Dim text6 = "" Dim text7 = "" Dim text8 = "" 'Pull member Properties to add into Table: If Not stkItem.Name.XFEqualsIgnoreCase("Root") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("EntityDefault") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("UD1Default") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("None") Then mbrDesc = BRAPI.Finance.Members.GetMember(si, dimTypeId, stkItem.Name).Description MembId = Brapi.Finance.Members.GetMember(si,dimTypeId,stkItem.Name).MemberId MembDimName = Brapi.Finance.Members.GetMemberInfo(si,dimTypeId,stkItem.Name,True,Nothing,MemberDisplay).MemberDim.Name If dimTypeId.equals(DimType.Entity.Id) Then Currency = MembProperties.GetLocalCurrency(si,MembId).Name End If If Not MembProperties Is Nothing If dimType.IsUDDimType(dimTypeId) Then text1 = MembProperties.Text(si,dimTypeId,MembId,1,ScenarioTypeId,TimeId) text2 = MembProperties.Text(si,dimTypeId,MembId,2,ScenarioTypeId,TimeId) text3 = MembProperties.Text(si,dimTypeId,MembId,3,ScenarioTypeId,TimeId) text4 = MembProperties.Text(si,dimTypeId,MembId,4,ScenarioTypeId,TimeId) text5 = MembProperties.Text(si,dimTypeId,MembId,5,ScenarioTypeId,TimeId) text6 = MembProperties.Text(si,dimTypeId,MembId,6,ScenarioTypeId,TimeId) text7 = MembProperties.Text(si,dimTypeId,MembId,7,ScenarioTypeId,TimeId) text8 = MembProperties.Text(si,dimTypeId,MembId,8,ScenarioTypeId,TimeId) Else If dimType.Scenario.Id.Equals(dimTypeId) text1 = MembProperties.Text(si,MembId,1) text2 = MembProperties.Text(si,MembId,2) text3 = MembProperties.Text(si,MembId,3) text4 = MembProperties.Text(si,MembId,4) text5 = MembProperties.Text(si,MembId,5) text6 = MembProperties.Text(si,MembId,6) text7 = MembProperties.Text(si,MembId,7) text8 = MembProperties.Text(si,MembId,8) Else text1 = MembProperties.Text(si,MembId,1,ScenarioTypeId,TimeId) text2 = MembProperties.Text(si,MembId,2,ScenarioTypeId,TimeId) text3 = MembProperties.Text(si,MembId,3,ScenarioTypeId,TimeId) text4 = MembProperties.Text(si,MembId,4,ScenarioTypeId,TimeId) text5 = MembProperties.Text(si,MembId,5,ScenarioTypeId,TimeId) text6 = MembProperties.Text(si,MembId,6,ScenarioTypeId,TimeId) text7 = MembProperties.Text(si,MembId,7,ScenarioTypeId,TimeId) text8 = MembProperties.Text(si,MembId,8,ScenarioTypeId,TimeId) End If End If End If Dim IsBase As Boolean = Not Me.isParentAllDims(si,dimTypeId,stkItem.Name) 'Adds into the Table: dtDim.Rows.Add(rowID,MembDimName,stkItem.Name,mbrDesc,IsBase,stkItem.Parent,Currency,Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8) rowID= rowId + 1 'Looks Up for Children: Dim result() As DataRow = UniqueParentChildTable.Select("[Parent] = '" & stkItem.Name & "'") If result.Count > 0 Then 'Loop through the result backwards to get the stack in the right order: For i As Integer = result.Count - 1 To 0 Step -1 Dim stkChild As New StkMbr stkChild.Name = result(i).Item("Child") stkChild.Parent = result(i).Item("Parent") stkChild.Gen = stkItem.Gen + 1 stk.Push(stkChild) Next End If Loop Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub 'Data structure for stack Private Structure StkMbr Property Name As String Property Parent As String Property Gen As Integer End Structure Function isParentAllDims (ByVal si As SessionInfo, ByVal DimTypeId As Integer, ByVal MembName As String) As Boolean Try Dim MembId As Integer = BRApi.Finance.Members.GetMemberId(si,DimTypeId,MembName) Dim IsParent As Boolean = False For Each sDim In BRApi.Finance.Dim.GetDims(si,DimTypeId).Select(Function(x) x.DimPk).toList() If Not IsParent IsParent = BRApi.Finance.Members.HasChildren(si,sDim,MembId) End If Next sDim Return IsParent Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Private Sub ExportToCSV (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal FileName As String, ByVal DataTable As DataTable) Try '1. Declare target dims and DataTable Dim csvContent As New Text.StringBuilder Dim qualifier As String = StageConstants.ParserDefaults.DefaultQuoteCharacter Dim delimiter As String = StageConstants.ParserDefaults.DefaultDelimiter 'Create DataTable Dim dt As DataTable = DataTable '2. If rows returned, create CSV file content If Not dt Is Nothing Then Dim csv As New Text.StringBuilder 'Declare string builder to store csv text csv.AppendLine("") csv.AppendLine("") 'Create column list from dt Dim colList As New List(Of String) For Each dc As DataColumn In dt.Columns colList.Add(dc.ColumnName) Next 'Write the column Definitions Dim colDescs As New Text.StringBuilder For Each colName As String In colList Dim dc As DataColumn = dt.Columns(colName) 'Check to see if the column data type requires quotes Dim quotesRequired As Boolean = False If dc.ColumnName.Contains(delimiter) Then quotesRequired = True End If If colDescs.Length > 0 Then colDescs.Append(delimiter) If quotesRequired Then colDescs.Append(qualifier) colDescs.Append(dc.ColumnName) If quotesRequired Then colDescs.Append(qualifier) Else If quotesRequired Then colDescs.Append(qualifier) colDescs.Append(dc.ColumnName) If quotesRequired Then colDescs.Append(qualifier) End If Next csvContent.AppendLine(colDescs.ToString) 'Write the Data Rows For Each dr As DataRow In dt.Rows Dim rowVals As New Text.StringBuilder For Each colName As String In colList Dim dc As DataColumn = dt.Columns(colName) Dim rowVal As String = dr(dc.Ordinal).ToString 'Check to see if the row data type requires quotes Dim quotesRequired As Boolean = False If rowVal.Contains(delimiter) Then quotesRequired = True End If If rowVals.Length > 0 Then rowVals.Append(delimiter) If quotesRequired Then rowVals.Append(qualifier) rowVals.Append(rowVal) If quotesRequired Then rowVals.Append(qualifier) Else If quotesRequired Then rowVals.Append(qualifier) rowVals.Append(rowVal) If quotesRequired Then rowVals.Append(qualifier) End If Next csvContent.AppendLine(rowVals.ToString) Next '3. Write File to User Temp Folder (to open from dashboard button) ' This folder is cleared when user session expires Dim fileBytes As Byte() = Encoding.UTF8.GetBytes(csvContent.ToString) BRApi.Utilities.SaveFileBytesToUserTempFolder(si, si.UserName, fileName, fileBytes) 'Add this text To button that calls Function. Navigation Action. Open File 'FileSourceType=Application, UrlOrFullFileName=[Internal/Users/|UserName|/Temp/Test.csv], OpenInXFPageIfPossible=False End If Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub End Class End Namespace
Business Rule Compile Error and Warnings
OneStream Platform releases will periodically include an update to the Business Rules compiler, which is noted in each version’s Release Notes. The enhancements typically make the compiler stricter in detecting syntax or other conditions, which are surfaced through Error or Warning messages. Error messages must be resolved, as the Business Rules will not complete the compile process. Warning messages are exposed to provide guidance to the Administrator. The displayed line items will still function but should be updated to support the latest compiler’s requirements. The method to resolve the Warning will vary. In some cases, a replacement function may be available, or there may be a change to a function’s properties. Example The above error message informs the Administrator of a Warning on the LookupRowFieldValue function having a property change. By reviewing the current rule, and by looking at the current Function Definition, the Administrator can determine that the property for “Criteria as a String” has been modified. The current Definition now defines the field as a dbWhere object. Old Properties LookupRowFieldValue(ByVal si As SessionInfo, ByVal dbLocation As String, ByVal tableName As String, ByVal criteriaExpression As String, ByVal fieldToReturn As String, ByVal defaultValue As String) As String To correct the condition, the Administrator is required to apply the required change. In this example, a dbWhere object must be used to define the criteria against the target database table. New Properties LookupRowFieldValue(ByVal si As SessionInfo, ByVal dbLocation As String, ByVal tableName As String, ByVal dbWheres As List(Of DbWhere), ByVal fieldToReturn As String, ByVal defaultValue As String) As String Other Compile Issues - Namespaces The Vb.Net language in OneStream offers the designer flexibility to implement custom solutions using predefined libraries as well other compatible third-party libraries. During a Business Rules compile, there are NameSpaces in OneStream that will be implicitly compiled: microsoft.visualbasic system.linq system.collections.generic system.collections system.text OneStream also has predefined Namespaces in Business Rules, which if utilized, must not be removed from the rule to compile properly. Imports System Imports System.Data Imports System.Data.Common Imports System.IO Imports System.Collections.Generic Imports System.Globalization Imports System.Linq Imports Microsoft.VisualBasic Imports System.Windows.Forms Imports OneStream.Shared.Common Imports OneStream.Shared.Wcf Imports OneStream.Shared.Engine Imports OneStream.Shared.Database Imports OneStream.Stage.Engine Imports OneStream.Stage.Database Imports OneStream.Finance.Engine Imports OneStream.Finance.Database The solution to resolving a Namespace issue will depend upon whether the rule exists in a Member Formula or as part of a Business Rule file. When an unsupported Namespace is used in a Business Rule file, the Namespace can be added to the Imports to allow the Business Rules to compile. Member Formulas do not allow access to modify the Import section of Business Rules. If the unsupported Namespace is part of a Member Formula, then the full Namespace must be added to the affected expression or variable.
[How to] Log into a file instead of the Error Log
Logging with OneStream is great but when we all use the Error Log at the same time, things can get messy very quickly. Moreover, logging in a file instead of the Error Log can be very convenient when logging kickouts. The initial setup can be a little involving but once you get a knack out of it, logging in a file is as easy as using the Error Log. In this post, I will show you how to do the initial setup and then how to log into a file instead of the Error Log only when there is an exception or every time. This rule and methodology is the result of the genius work of Matt Ha and I am very thankful he shared it with us. Initial Setup In order to log into a file, you need to import a Public Business Rule, it will be called by the logger and it is available in this post (GS_GlobalHelper.xml). Side note, remember that in order to make a BR Public, you need to change the setting for ‘Contains Global Functions for Formulas’ to True. Setup of the BR to log into a file In order to call a Public function, you need to Reference it in your Business Rule You also need to add it to your Imports: Imports OneStream.BusinessRule.Finance.GS_GlobalHelper.MainClass The Logger function needs 2 variables to be declared and set, I like to do it at the very top of my rule #Region "Logging Variables" Dim bVerboseLogging As String = True Dim logger As New Text.StringBuilder #End Region Then, you need to add 2 Private Functions to your rule, this is where you set your naming convention (if you want to add the data and time or anything else) and the folder name where you want your files to be stored. Private Sub StoreLoggerOnSession(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByRef globals As BRGlobals) ' Multithread-safe way to store logger to session Dim globalLogger As Text.StringBuilder = globals.GetObject("globalLogger") If globalLogger Is Nothing Then globals.SetObject("globalLogger", logger) Else globalLogger.AppendLine(logger.ToString) End If End Sub Private Sub WriteSessionLogToFileShare(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByRef globals As BRGlobals, ByVal logName As String) ' Write logger stored on session to application database Dim globalLogger As Text.StringBuilder = globals.GetObject("globalLogger") If globalLogger IsNot Nothing Then WriteLogger(si, api, globalLogger, logName, "TestLogs", DateTime.Now.ToString("yyyy-MM-dd-hh-mm")) End If End Sub In the end, your rule should look like this: Logging into a file when there is an Exception You can write to a file instead of the Error log when an Exception happens, in this case, update the Exception Catcher at the end of your Main Function. Catch ex As Exception logger.AppendLine(ex.Message) If bVerboseLogging Then StoreLoggerOnSession(si, api, globals) WriteSessionLogToFileShare(si, api, globals, "MyRule") Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try Logging anything into a file Of course, you can also log anything you would usually log to the Error Log in a file instead. Use the “logger” instead of the Error Log. 'Log to the Error Log api.LogMessage("Logging to the Error Log with the api") brapi.ErrorLog.LogMessage(si, "Logging to the Error Log with the brapi") 'Log to a file logger.AppendLine("Logging to a file").AppendLine #Region "Push logs to the File Explorer" If bVerboseLogging Then StoreLoggerOnSession(si, api, globals) WriteSessionLogToFileShare(si, api, globals, "MyRule") #End Region Results This is what you get in the Error Log And this is what you get in the File Explorer:Rules Formatting - Please Format Your Rules!!
General Formatting One of the most important things you can do, to make you rules readable and helpful, is making sure they are properly formatted. There are some simple rules you should probably follow all the time: Proper Case – Function names are not case-sensitive; for example, api.data.calculate, Api.Data.Calculate, or API.DATA.CALCULATE, are treated as the same. Still, it is a best practice of VB style to be consistent and to capitalize judiciously. Proper case throughout the rules file makes it much easier to read. Always Comment – Add comments to most lines of code explaining what you are doing and why. Use the apostrophe at the beginning of the comment to make sure it is not interpreted as part of the rules. If this is done in a VB editor, it should turn green by default. Indentation is critical for readability – This is especially true when using any nested statements or conditionals. Those are explained below. Indentation should be done for any scripting, even scripting objects in Business Rules Editor. Variable and Constants properly named – All variables should be given useful names. Line Continuations Properly formatting rules will typically also mean using the underscore ‘_’ and colon ‘:’ symbols. When using space + underscore, you are telling the script that the command continues on the next line. For example: api.data.Calculate("F#[Bad Debt]:A#[EBITDAVar] = " & _ "(A#54100:S#Budget:F#None-A#54100:S#Actual:F#None)") The colon allows you to combine two lines. For example: strSalesAccount = “A#7999.UD1#Sales” : strMktAccount = “A#7999.UD1#Marketing” Comments I think it is important to emphasize the importance of comments. You will not remember in a year or two why something is quite the way it is, so comments will help you from making the same mistake again. It can help provide a new administrator detailed information, such as what needs updating or regular maintenance, if you add a new cash flow account for example. Finally it can help remind you what needs to be considered for an upgrade, or rebuild. Variables & Constants Variables and constants are used to hold values or expressions. Think back to your 9th grade algebra class. In 2+y = x, y is the variable. (See, your teacher was right, this may prove useful yet...) Variables can have any name; but while ‘y’ and ‘x’ are valid names, they don't tell you anything. Names should be something that makes sense. Consider which of the following is easier to follow: 2 + y = x or 2 + strVariablePercent = strPercentMarkUp. I would say you can understand more form the second line of rules than the first, even without knowing the context. Add a line of comments, and note the proper case, and you are on your way to well formatted descriptive rules. A Variable is a value that changes depending on parameters and when it is used; whereas a Constant will not change, regardless of when it is used or changes in the application. You will want to declare constants at the beginning of rules files. They can be available to all procedures at all times. Apart from that, they are used just like variables. You should have some guidelines when writing rules; one of the simplest things to do, to keep yourself organized, is to have a naming convention. I like to use a prefix. The prefix is something that helps me remember what is in the variable. I might use ‘str’ or ‘s’ for a string, or ‘bln’ or ‘b’ for a Boolean (true or false), and ‘nbr’ or ‘n’ for number. Then using proper case I use a descriptive label for my variable. So, for a number from Net Income, my variable might be called ‘nbrNetIncome’. I can see that variable name anywhere in my file and know what the variable is for and what it is. Compare that with ‘x’; if I just see x, who knows what it is for. It also helps to know what you are going to use the variable for. We have two names for variables; Replacement Variables and Execution Variables. Replacement Variables are typically used for constants like static strings (for example topUD1=“.UD1#TopUserDefined1”). This variable might change, but it is replacing some part of a string. Execution Variables are typically used for situations in which variable is populated or reset during some condition or rule (for example sPOVEntity = api.POV.Entity.Name). The point of view changes constantly and what would be written in the variable would be updated accordingly. There are some rules for variable names that you just must follow, to write valid VB.Net. They must always begin with a letter. They cannot contain a period. You should avoid keywords such as “OneStream”, “Entity”, “Account”, when naming variables; they tend to be reserved by OneStream and could cause problems if shadowed. VB.Net requires you to declare variables before using them. Since variables will require what type they can hold, you need to make sure you avoid letting the variable use a type that Rules Engine is not expecting for that member. For example, if you write a rule checking if the year is 2010, OneStream could see that as something different than “2010”. By using the quotes and declaring it As String, the number 2010 becomes a String of text, “2010”. Otherwise you might get a Type Mismatch error in some situations; if you do get this error, double check that the variable you are testing is correctly declared.[howto] Use "Insert Code" when posting on Forum boards
Hey guys! Just a reminder: if you're going to post code in this forum, please make it easier to read by using the "Insert/Edit Code Sample" button on the expanded formatting toolbar. Here's a short video showing how it works. If you need to edit code after you created the block, just double-click on it. Ideally you'd also indent it first, either in an editor or with something like DotNetFiddle. Remember: the easier it is for others to read your code, the more likely that they'll be able to solve your problem! Cheers! Your friendly neighborhood Spider-ModVB.NET Rules - For Better Performance
Api.Data.Calculate(“A#28100=A#69000”) What is wrong with writing a calculation like this? Tips. A#28100 might contain thousands of cells. Calculation MAY work but will damage performance. Defining more dimensions reduces the number of cells that need to be processed. A#28100 = 10 Flow x 1 IC(None) x 3 Origin x 10 UD1 x 20 UD2 x UD3 x UD4 etc Defining more Dimensions reduces the number of cells OneStream has to process. Write the rule as follows: To learn more see GES - Level 2: Financial Model Rules Course Level2-Rules.pdf (hubspotusercontent30.net)
Destination Data Units
I am passing 2 Entities in the Data unit for Calculation as Entity is different in both the destinations So what i was expecting is that it will run as a for each loop with both the entities so it will pick enitity1 and then run the Calc and then enitity2 and then run the Calc what i was expecting was that the order will remain same every time it will run that is Enitity1 then Enitity2 but what is happening is that it is first taking enitity1 and then entity2 but when i run it again it is taking enitity2 and then enitity1 in that for each loop to solve this i used 2 different DM steps but why it is running like this i want to know Can anyone explain why it is running like this ?
