Log anything with JSON
I showed it at Wave and share it with all of you: when you use JSON to log, you can log anything, not just strings, you can log a Dictionary, a list, a MemberInfo, even a Databuffer. First, you need to import JSON, add these 2 rows at the top of your rule: Imports Newtonsoft.Json Imports Newtonsoft.Json.Linq Second, when you want to log, use JSON! Dim stringobj As String = JsonConvert.SerializeObject(lCenterInfo,Formatting.Indented) api.logmessage(lCenterInfo.Member.Name & ", lCenterInfo: " & stringobj) When you use ,Formatting.Indented, your log will be correctly formatted. Thanks to Matt Ha for enlightening me with JSON and RobbSalzmannfor sharing how to have the JSON string correctly formatted!
Rule Rules - ONLY write rules when necessary.
Rules TIP - ONLY write / use Rules when necessary. OneStream projects should not be a contest to see who is better at writing complex rules. If you must write rules please remember: Unless specified, the calculation will run onBase Entities AND each Parent Entity. Use these lines almost always in your rules. ForBaseentities only use: If Not api.entity.HasChildren Then ForParententities only use:If api.entity.HasChildren Then ForALLentities:api.Data.Calculate(“A#Account1 = A#Account2”) There are up to 6calculation operations in the Consolidationprocess per Entity. Conditional statements can be added to formulas to limit which Consolidation calculation processes will do something for this formula. ForLocalcurrency only:If Api.Cons.IsLocalCurrencyforEntity Then ForTranslatedcurrency only:If api.Cons.IsForeignCurrencyForEntity Then For Parent-ChildRelationshiplevels:Api.Cons.IsRelationshipLevelreturns True for Consolidation level being OwnerPreAdj, Share, Elimination or OwnerPostAdj. For best performance, use Remove Functions – Zero and No Data Can be used in a Member Formula or a Business Rule. RemoveZeros Function removes data cells with cell amount of0from data bufferANDremoves data cells with cell status ofNoDatafrom data buffer. RemoveNoData FunctionONLYremoves data cells with cell status ofNoDatafrom data buffer (data cells in aData Unit). When declaring variables in a member formula or business rule, ensure the variables are within the calculation conditions to avoid unnecessary code execution. TIP 2-Don’t Stack api.Data.Calculate Functions. Let's say we need to calculate data for the dimension members below. What shall we do? Use a filter; don’t be afraid to create alternate groupings. Use member filters to perform the same calculation logic for multiple members. Use the hierarchy to drive calculations. Reduces the number of times the Data Unit is called into memory and the number of times data is written back to the Cube. Avoid referencing a user's workflow POV in stored formulas: Finance rules can and often do run outside the workflow. Instead of the above - do the list below. For more information: OneStream Finance Rules and Calculations Handbook. Level 2: Financial Model Rules course.Calculation Sequence of a Single Data Unit:
The items below detail the specific list of tasks that are executed for each data unit during the calculation process. Data Unit Calculation Sequence (DUCS) Clear previously calculated data If Using Hybrid Scenarios - the copy will run here Run Scenario Member Formula Run reverse translations by calculating Flow members from other Alt Cur. Input Flow members Execute Finance Business Rules (1 & 2) Run Formula Passes (1 – 4 ) Account, Flow, UD1, UD2, … UD8 (Member Formula Execution) Execute Finance Business Rules (3 & 4) Run Formula Passes (5 – 😎 Account, Flow, UD1, UD2, … UD8 (Member Formula Execution) Execute Finance Business Rules (5 and 6) Run Formula Passes (9 – 12) Account, Flow, UD1, UD2, … UD8 (Member Formula Execution) Execute Finance Business Rules (7 and 8 ) Run Formula Passes (13 – 16) Account, Flow, UD1, UD2, … UD8 (Member Formula Execution)
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 can I use a Business Rule to sort a Member List in alphabetical order?
Namespace OneStream.BusinessRule.Finance.XFR_MemberListAlphabetical Public Class MainClass '--------------------------------------------------------------------------------------------------- 'Reference Code: XFR_MemberListAlphabetical ' 'Description: Use a business rule to sort a member list in Alphabetical order ' 'Usage: This will put a member list of a dimension in Alphabetical order. ' Use the following on the cube view: ' E#Member.[Name of Business Rule, Name of List in Business Rule] ' e.g. E#Root.[XFR_MemberListAlphabetical, EntityAlphabetical] ' 'Created By: Robert Powers (put in XF Ref by John Von Allmen) ' 'Date Created: 5-24-2013 '--------------------------------------------------------------------------------------------------- Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, _ ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object Try 'This will put a member list of a dimension in Alphabetical order. 'Use the following on the cube view: ' E#Member.[Name of Business Rule, Name of List in Business Rule] ' e.g. E#Root.[XFR_MemberListAlphabetical, EntityAlphabetical] Dim Memberlistname As String = "Ent_Sort" Dim MemberListstart As String = "E#[Total GolfStream].base" Select Case api.FunctionType Case Is = FinanceFunctionType.MemberList If args.MemberListArgs.MemberListName = Memberlistname Then Dim objMemberListHeader = New MemberListHeader( _ args.MemberListArgs.MemberListName) 'Read the members Dim objMemberInfos As List(Of MemberInfo) = api.Members.GetMembersUsingFilter( _ args.MemberListArgs.DimPk, MemberListstart, Nothing) 'Sort the members Dim objMembers As List(Of Member) = Nothing If Not objMemberInfos Is Nothing Then objMembers = (From memberInfo In objMemberInfos _ Order By memberInfo.Member.Name Ascending _ Select memberInfo.Member).ToList() End If 'Return Return New MemberList(objMemberListHeader, objMembers) End If End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End Namespace
UD8 Dynamic Calc - Retrieve Data From Another Application
Hello, I wanted to post this here since this came up at the Wave Conference in Vegas last week. Below is a snippet you can leverage to pull data from another application via a UD8 dynamic calc. This approach uses the CreateSessionInfoForAnotherApp BRApi to create a session in another application with the current user's credentials which ultimately enables you to execute any BRApi function (such as the GetDataCellUsingMemberScript BRApi) against another application in the same environment. This technique can be especially useful when looking to report on data that lives in two applications (in the same environment) in cube views and quick views. 'define the other application name you wish to connect to - this must be an app that lives within the same environment 'the user must also have access to the application to authenticate to it Dim otherAppName As String = "SomeOtherApp" 'create the session info to the other app and store it globally in memory Dim otherAppSeshInfo As SessionInfo = globals.GetObject("OtherAppSeshInfo") If (otherAppSeshInfo Is Nothing) Then otherAppSeshInfo = BRApi.Security.Authorization.CreateSessionInfoForAnotherApp(si, otherAppName, OpenAppResult.Success) globals.SetObject("OtherAppSeshInfo", otherAppSeshInfo) End If 'get the POV intersection from the CV and swap out the UD8 none member with the name of this UD8 reporting member Dim povMemberScript As String = api.Data.CreateMemberScriptBuilder(True, True, True, True).GetMemberScript().Replace("U8#GetDataFromAnotherApp", "U8#None") 'return the POV cell amount from the other application Return BRApi.Finance.Data.GetDataCellUsingMemberScript(otherAppSeshInfo, api.Pov.Cube.Name, povMemberScript).DataCellEx.DataCell.CellAmount Regards, Nick Kroppe Advanced Application Solutions OneStream Software[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:
Consolidation with cascade entity structure
Hi, We have a cascade entity structure, where one of the sub-elements consolidates up to a certain period, and after that period the other begins to consolidate. Entity A1 has an investment in Entity B11. Consolidating 2019M12, the application calculates an elimination of consolidated reserves in both parents Entity B1 & Entity B2, regardless of the fact that Entity B2 does not consolidate until 2022M8. I would like to know if anyone else has had a similar problem and has been able to solve it. Thank you, SaraRules 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.What is a Consolidation Method?
Equity Accounting And Methods A consolidation method is just a way to treat an entity during consolidation based on how much of it you own or control. It makes sense, if you only own 10% of an entity, and another entity has an asset recorded for the value of that investment, you would be overstating your value if you let it add up at 100%. The following are the most common methods you might see. Subsidiary: Entity owned at >50% The Subsidiaries financial statements are consolidated and eliminated. The Equity associated with the subs financial statements are eliminated against the Holding companies Investment in the sub. If the Sub is not 100% owned, the owned portion of Equity is eliminated and the unowned portion is reclassed to Minority Interest. Equity: 50% >Entity owned >20% An Entity held, as Equity Method is NOT consolidated. The Net Income of the Entity is multiplied by the Equity holding and the result is booked to the P&L and Equity Holding (Balance Sheet Equity Investment) of the Holding companies books. Cost: Entity owned at <20% An Entity held as Cost Method is NOT consolidated. The Net Income of the Entity is multiplied by the Cost holding and the result is booked to the Holding companies Balance Sheet in Other Investments. There is no P&L impact. Acquisition and Disposal: Not a traditional method, but can be used as one The entire P&L and Balance Sheet are each net to a single account on each statement. Usually the name of the account is something like "Income (or Loss) from Discontinued Operations" below Income from Continuing Operations and before Net Income. It represents the after tax gain or loss on sale of a segment of business and the after tax effect of the operations of the discontinued segment for the period. This method approach allows you to automate this calculation. Holding Company: A special entity and not really a method Holding companies hold the Investments of other entities. The Holding Companies Equity LIVES and is not eliminated until its parents equity is eliminated.
