Recent Content
Dashboard: XFBR String
Business rules written and used as Parameters that return a specific value based on the defined inputs. This Business Rule can be applied to any Dashboard or Cube View property where a Parameter is used. 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 Namespace OneStream.BusinessRule.DashboardStringFunction.XFBRStringExamples Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object '------------------------------------------------------------------------------------------------------------ 'Reference Code: GetCalcStatus ' 'Usage: This shows different examples of BRString usage in Cube Views and Dashboards ' 'GetCalcStatus ' Parameter Example: ' BRString(XFBRStringExamples, GetCalcStatus, ParamCube = GolfStream, ' ParamEntity = [Houston Heights], ParamParent = Houston, ' ParamCons = USD, ParamScenario = Actual, ParamPeriod = 2011M1) ' 'GetUserIsAdmin ' Parameter Example: BRString(XFBRStringExamples, GetUserIsAdmin) ' 'EntityDesc ' Parameter Example: BRString(XFBRStringExamples, EntityDesc, ParamEntity=[|!MyEntity!|]) ' 'Created By: OneStream 'Date Created: 12-18-2017 '------------------------------------------------------------------------------------------------------------ Try Select Case args.FunctionName Case Is = "GetCalcStatus" 'Get the Passed in parameters Dim paramCubeValue As String = args.NameValuePairs("ParamCube") Dim paramEntityValue As String = args.NameValuePairs("ParamEntity") Dim paramParentValue As String = args.NameValuePairs("ParamParent") Dim paramConsValue As String = args.NameValuePairs("ParamCons") Dim paramScenarioValue As String = args.NameValuePairs("ParamScenario") Dim paramPeriodValue As String = args.NameValuePairs("ParamPeriod") Return BRApi.Finance.Data.GetCalcStatus(si, _ paramCubeValue, paramEntityValue, paramParentValue, _ paramConsValue, paramScenarioValue, paramPeriodValue) Case Is = "GetUserIsAdmin" 'Check to see if the user is an administrator ' (Can be used to hide objects that are administrator only) Return BRApi.Security.Authorization.IsUserInAdminGroup(si) Case Is = "EntityDesc" 'Get the Passed in parameter Dim myEntity As String = args.NameValuePairs("ParamEntity") Dim myEntityDesc As String = BRApi.Finance.Metadata.GetMember( _ si, 0, myEntity).Member.Description Return myEntityDesc End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End Namespace2likes0CommentsDashboard: Get or Set Literal Parameter
Literal Parameters are, effectively, application-wide variables that can be used to drive Dashboards and Cube Views. Their values can be set or retrieved in code as shown below. From version 7.3 onwards, they are contained in a Workspace. That means that the relevant calls now require the ID of the Workspace where the Parameter is located. Note that the ID of Default Workspace is an empty Guid. Note: Literal Parameters are effectively shared between all users; which means that, if a Dashboard Action triggered by "User1" modifies a Literal Parameter value, "User2" will also receive the changed value in any Dashboards they are using (at the first refresh). OneStream Version 7.3+ Dim parameterName As String = "MyParameterName" ' if Parameter lives in Dashboards under "System" tab, set this to True Dim isSystemLevel as Boolean = False ' retrieve workspace ID. If it's Default Workspace, you can just use Guid.Empty instead Dim workspaceID As Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName( _ si, isSystemLevel, workspaceName) ' --- Set literal parameter value Dim newValue As String = "New Literal Value" BRApi.Dashboards.Parameters.SetLiteralParameterValue( _ si, isSystemLevel, workspaceID, parameterName, newValue) ' --- Retrieve literal parameter value Dim pValue as String = BRApi.Dashboards.Parameters.GetLiteralParameterValue( _ si, isSystemLevel, workspaceID, parameterName) OneStream Version 7.2 and below Dim parameterName As String = "MyParameterName" ' if Parameter lives in Dashboards under "System" tab, set this to True Dim isSystemLevel as Boolean = False ' --- Set literal parameter value Dim newValue As String = "New Literal Value" BRApi.Dashboards.Parameters.SetLiteralParameterValue( _ si, isSystemLevel, parameterName, newValue) ' --- Retrieve literal parameter value Dim pValue as String = BRApi.Dashboards.Parameters.GetLiteralParameterValue( _ si, isSystemLevel, parameterName)1like0CommentsExtender: Auto Create Member
This snippet will create a new Account member, including setting some properties that can vary by Scenario Type and/or Time. Note: SaveMemberInfo does not create entries in Audit tables, which means the Audit Metadata report will not contain anything related to this operation. For this reason, we do not recommend to use this snippet outside of implementation activities or in production environments. 'Create a new MemberInfo object with its child objects. Dim objMemberPk As New MemberPk(DimType.Account.Id, DimConstants.Unknown) 'Update Dim Name accordingly Dim objDim As OneStream.Shared.Wcf.Dim = BRApi.Finance.Dim.GetDim(si, "<Dimension Name>") 'Create New Member Dim objMember As New Member(objMemberPk, _ "<New Member Name>", "<Member Description>", objDim.DimPk.DimId) 'Create VaryingMemberProperties object Dim objProperties As New VaryingMemberProperties( _ objMemberPk.DimTypeId, objMemberPk.MemberId, DimConstants.Unknown) 'Create new member info object for new member Dim objMemberInfo As New MemberInfo( _ objMember, objProperties, Nothing, objDim, DimConstants.Unknown) 'Modify some member properties. Account dimension, in this example. Dim accountProperties As AccountVMProperties = objMemberInfo.GetAccountProperties() accountProperties.AccountType.SetStoredValue(AccountType.Revenue.Id) accountProperties.Text1.SetStoredValue( _ ScenarioType.Unknown.Id, DimConstants.Unknown, "MyNewText1Value") 'Save the member and its properties. Dim isNew As TriStateBool = TriStateBool.TrueValue BRApi.Finance.MemberAdmin.SaveMemberInfo(si, objMemberInfo, True, True, False, isNew)Extender: User Inactivity Email
This Extender can be executed in a Data Management step to automate emailing details of an auto-expiring account to the related user. 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.Threading 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 '** IMPORTANT! Update "InactiveEmailNotification" in line below ' with actual name of the Extender business rule you created Namespace OneStream.BusinessRule.Extender.InactiveEmailNotification Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object '--------------------------------------------------------------------------------------- 'Description: User Expiration Warning ' 'Usage: Sends an email to users prior their account auto expire date. ' This Snippet should replace an entire rule as it includes non-standard Imports [Lines 1-18] ' 'Notes: Administrator must set the following values prior to use: ' BusinessRuleName [Line 20] ' EmailConnectionName [Line 58] ' UserWarningThreshold [Line 61] ' EmailTitle [Line 64] ' EmailMessage [Line 68-75] ' EmailList [Line 78] ' 'Created By: OneStream Software 'Date Created: 09-15-2020 '--------------------------------------------------------------------------------------- Try Select Case args.FunctionType Case Is = ExtenderFunctionType.Unknown Me.EmailNotification(si) Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep Me.EmailNotification(si) End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function #Region "General Helpers" Public Sub EmailNotification(ByVal si As SessionInfo) Try 'Specify the email connection (Defined in Application Server setup) Dim emailConnectionName As String = "OneStreamEmail" 'Enter number of days prior to user expiration that warning will be sent. ' Email will only be sent if "Remaining Allowed Inactivity" is less than or equal to threshold days. Dim userWarningThreshold As Double = 20 'Define the the email title to be sent. The value [days] will be updated during processing. Dim emailTitle As String = "OneStream User ID Expiration - [days] day warning" 'Define the the email body to be sent. ' [user] & [days] will automatically be replaced, during processing, ' with the OneSteam Username & number of days till expiration. Dim emailMessage As New Text.StringBuilder emailMessage.AppendLine("Attention: [user]") emailMessage.AppendLine("") emailMessage.AppendLine("Your login for OneStream will expire in [days] due to inactivity." emailMessage.AppendLine("Please login as soon as possible.") emailMessage.AppendLine("") ' replace xxx in text below with relevant email address emailMessage.AppendLine("If you require assistance, please contact xxxx .") emailMessage.AppendLine("") emailMessage.AppendLine("Thank you.") emailMessage.AppendLine("The Support Team") 'Define any additional email addresses to include other than the user that is expiring. ' All emails will be listed in the "To: field" of the email. Dim emailList As New List(Of String) ' Uncomment following lines if necessary and update with relevant addresses. 'emailList.Add("emailaddress1@customer.com") 'emailList.Add("emailaddress2@customer.com") ' If you need more addresses, just add more lines like the ones above 'Account Expiration Warning Me.ValidateUserExpiration(si, emailConnectionName, _ userWarningThreshold, emailTitle, emailMessage.ToString, emailList) Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub Public Sub ValidateUserExpiration(ByVal si As SessionInfo, ByVal emailConnectionName As String, _ ByVal userWarningThreshold As Double, ByVal emailTitle As String, ByVal emailMessage As String, ByVal emailList As List(Of String)) Try Dim dtResults As New DataTable Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si) Dim ds As DataSet = BRApi.Database.ExecuteMethodCommand( _ dbConnApp, XFCommandMethodTypeID.Users, "{}", "Users", Nothing) If Not ds Is Nothing Then Dim dt As DataTable = ds.Tables(0).Copy Dim userID As Guid = Guid.Empty Dim userName As String = String.Empty Dim remainingDays As Double = 0 Dim updatedEmailList As New List(Of String) Dim updatedEmailTitle As String = String.Empty Dim updatedEmailMessage As String = String.Empty Dim objUserInfoAndStatus As UserInfoAndStatus = BRApi.Security.Admin.GetUserAndStatus( _ si, si.UserName) For Each dr As DataRow In dt.Rows 'Filter out inactive users and users without a defined email address If(dr(4).ToString().XFContainsIgnoreCase("TRUE")) And (Not String.IsNullOrEmpty(dr(7).ToString)) Then 'Get UserName and UserInfoAndStatus userName = dr(1) objUserInfoAndStatus = BRApi.Security.Admin.GetUserAndStatus(si, userName) If objUserInfoAndStatus.LogonStatus.GetNumDaysOfRemainingAllowedInactivity <= userWarningThreshold Then remainingDays = objUserInfoAndStatus.LogonStatus.GetNumDaysOfRemainingAllowedInactivity 'Reset email list for next user updatedemailList.Clear updatedemailList.AddRange(emailList) 'Add user to email list updatedemailList.Add(dr(6).ToString) 'Replace [days] & [user] values in email EmailTitle and EmailMessage updatedEmailTitle = emailTitle.Replace( _ "[days]", ConvertHelper.ToInt32(remainingDays)) updatedEmailMessage = emailMessage.Replace( _ "[days]", ConvertHelper.ToInt32(remainingDays)) updatedEmailMessage = updatedEmailMessage.Replace( _ "[user]", userName) 'Send the email using a worker background thread Dim mailThread As New SendMailThread(si, _ emailConnectionName, updatedemailList, _ updatedEmailTitle, updatedEmailMessage, Nothing) mailThread.Start End If End If Next End If End Using Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub #End Region #Region "Constants and Enumerations" 'String Messages Public m_MsgNoEmailConnection As String = "Cannot Send Notifications: Email Connection must be specified." #End Region Public Class SendMailThread #Region "Module Level Variables" Private Const m_ThreadNamePrefix As String = "XF Send Mail Thread" Private m_SI As SessionInfo = Nothing Private m_MailConnectionName As String = String.Empty Private m_ToEmailAddresses As New List(Of String) Private m_Subject As String = String.Empty Private m_Body As String = String.Empty Private m_AttachmentFilePaths As New List(Of String) Private m_WorkerThread As Thread #End Region #Region "Constructor" Public Sub New(ByVal si As SessionInfo, ByVal mailConnectionName As String, _ ByVal toEmailAddresses As List(Of String), ByVal subject As String, _ ByVal body As String, ByVal attachmentFilePaths As List(Of String)) 'Copy the input parameters so the background thread can access them. m_SI = si m_MailConnectionName = mailConnectionName m_ToEmailAddresses = toEmailAddresses m_Subject = subject m_Body = body m_AttachmentFilePaths = attachmentFilePaths End Sub #End Region #Region "Public Methods" Public Sub Start() Try 'Create the Background Thread m_WorkerThread = New Thread(AddressOf Me.WorkerThreadMethod) 'We don't want this worker thread to keep the process from being shut down. m_WorkerThread.IsBackground = True 'Naming thread to provide a unique recognizable marker when debugging. m_WorkerThread.Name = m_ThreadNamePrefix & " " & Guid.NewGuid().ToString("N") XFWcfOperationInvoker.SetCultureInfoForUserToThread(m_SI, m_WorkerThread) m_WorkerThread.Start() Catch ex As Exception Throw ErrorHandler.LogWrite(m_SI, New XFException(m_SI, ex)) End Try End Sub #End Region #Region "Private Methods" Private Sub WorkerThreadMethod() Try 'Send the email BRApi.Utilities.SendMail(m_SI, m_MailConnectionName, m_ToEmailAddresses, m_Subject, m_Body, m_AttachmentFilePaths) Catch ex As Exception 'Important: do not re-throw the exception from this worker thread since it will be processed by .NET as an unhandled exception. 'Even if an exception could be processed normally, it couldn't be sent back to the client via WCF because the client isn't 'waiting for a WCF method to complete and the client might not not even be logged on anymore. Try BRApi.ErrorLog.LogError(m_SI, ex) Catch End Try End Try End Sub #End Region End Class End Class End Namespace1like0CommentsFinance Dynamic Calc: Annotations Consolidations
This DynamicCalc formula consolidates Annotations for entities in a hierarchy. 'Cube View definition 'Row Definition: E#[Total GolfStream].tree 'Col1 Definition: V#Annotation:Name("Comment") 'Col2 Definition: V#Annotation:UD8#DynamicTextCons:Name("Consolidated") 'UD8 Member setup 'Name: DynamicTextCons <-- If this is changed it must be updated in the CV Col2 definition above. 'Formula Type: DynamicCalc 'Allow Input: True 'Is Consolidated: False 'In Use: True 'Assign the formula below to the UD8 member to show the consolidated text Dim iEntityID As Integer = api.pov.Entity.MemberId Dim iETestID As Integer Dim strETestName As String = string.empty Dim eTest As Member Dim sSave As String = string.empty Dim sSource As String = string.empty If api.View.IsAnnotationType Then If api.Entity.HasChildren() Then For Each etest In api.Members.GetDescendents( _ api.Dimensions.GetBaseDim(dimtypeid.Entity).DimPk, iEntityID) iETestID = etest.MemberId sSource = api.Data.GetDataCellEx("U8#None:E#[" & etest.Name & "]:C#" & _ api.Entity.GetLocalCurrency(iETestID).Name).DataCellAnnotation If Not sSource.Equals(String.Empty) Then sSave = sSave.Trim & "; " & eTest.Name & ": " & sSource End If Next Else sSource = api.Data.GetDataCellEx($"U8#None:E#[{api.pov.entity.Name}]").DataCellAnnotation If Not sSource.Equals(String.Empty) Then sSave = ";" & sSource End If End If End If If Not sSave.Equals(String.Empty) Then Return right(sSave, len(sSave)-1).Trim Else Return Nothing End If Source: Snippet Editor: Finance - Common Account - DynamicCalc5likes0CommentsExtender: Auto Update Member Property
This snippet will modify a Member property that can vary by Scenario Type and/or Time. Just pass the relevant ScenarioType ID or Time member ID to set it in a more specific way; it will then appear as a "Stored Item" in the interface. Note: SaveMemberInfo does not create entries in Audit tables, which means the Audit Metadata report will not contain anything related to this operation. For this reason, we do not recommend to use this snippet outside of implementation activities or in production environments. 'Get the MemberInfo object for the member you want to update, in this example an Account. Dim objMemberInfo As MemberInfo = BRApi.Finance.Members.GetMemberInfo( _ si, DimType.Account.Id, "<Member Name>", True) ' Retrieve member properties so we can modify them. Dim accountProperties As AccountVMProperties = objMemberInfo.GetAccountProperties() ' change the Account Type accountProperties.AccountType.SetStoredValue(AccountType.Revenue.Id) ' change default Text1 value ' if you want to set it for a specific ScenarioType and/or time, ' use the relevant values in the first 2 parameters accountProperties.Text1.SetStoredValue( _ ScenarioType.Unknown.Id, DimConstants.Unknown, "<UpdatedValue>") 'Save the member and its properties. Dim isNew As TriStateBool = TriStateBool.TrueValue BRApi.Finance.MemberAdmin.SaveMemberInfo(si, objMemberInfo, False, True, False, isNew)Dashboard: Data Sets
DashboardDataSet Rules are used to create programmatic query results. This rule type enables the rule writer to combine multiple types of data into a single result set using the full syntax capability of VB.Net. '------------------------------------------------------------------------------------------------------------ 'Reference Code: SampleDashboardDataSet ' 'Description: Creates an AOD.Net DataTable that selectively returns help desk tickets based on user ' level, ticket status and ticket priority. 'Usage: Used as custom method query in a Dashboard Data Adapter with the following parameter structure. 'Parameter ProtoType: {SampleDashboardDataSet}{DataSetFunctionName}{Param1="Paramater Value to be used in Data Set"} 'Parameter Example: {SampleDashboardDataSet}{DataSetFunctionName}{Param1="Value1"} ' 'Created By: OneStream Software 'Date Created: 06-1-2016 '------------------------------------------------------------------------------------------------------------ Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object Try Select Case args.FunctionType Case Is = DashboardDataSetFunctionType.GetDataSet If (args.DataSetName.XFEqualsIgnoreCase("TicketAnalysis")) Then Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) 'Get passed in parameters Dim paramValue1 As String = args.NameValuePairs.XFGetValue("Param1", Guid.Empty.ToString) 'Execute a SQL query that filters tickets by user name for Non-Administrators Dim sql As New Text.StringBuilder Dim whereClause As New Text.StringBuilder sql.AppendLine("Select * From TableName ") '<- Update SQL Query 'Check Param1 If Not paramValue1.Equals("XXXX",StringComparison.InvariantCultureIgnoreCase) Then '<- Update tested value 'Update where clause based on parameter value whereClause.AppendLine("And Field1 = 'XXXX' ") '<- Update SQL Else 'Update where clause based on parameter value whereClause.AppendLine("And TicketStatus = 'XXXX' ") '<- Update SQL End If sql.AppendLine(whereClause.ToString) 'Return results of DataSet SQL Return BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString, True) End Using End If End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class1like0CommentsExtender: automate Import, Validate, Load
This example will perform Import, Validate, Load as a batch operation. The approach is typically seen in Extenders or Event Handlers. 'Set Processing Switches Dim valTransform As Boolean = True Dim valIntersect As Boolean = True Dim loadCube As Boolean = True Dim processCube As Boolean = False Dim confirm As Boolean = False Dim autoCertify As Boolean = False Dim scenario As String = "Actual" Dim timeperiod As String = "2017M3" 'Execute Batch Dim batchInfo As WorkflowBatchFileCollection = BRAPi.Utilities.ExecuteFileHarvestBatch( _ si, scenario, timeperiod, valTransform, valIntersect, _ loadCube, processCube, confirm, autoCertify, False)1like0CommentsHow can I bypass importing lines of data?
'Check the parsed value for the criteria string If StringHelper.FindIgnoreCase(args.Value, "[String To Find]") Then ' Set the bypass switch on the globals object ' in order to tell the parser engine to skip this line. globals.Bypass = True End If Return args.Value4likes0CommentsAggregating a tagged set of accounts (e.g. Balance Sheet only)
This is an example to obtain values for a Cash Flow formula for all accounts tagged with a certain Text1 field. For every Account tagged CF_AR (Cash Flow - Accounts Receivable) and evaluating the Flow member Activity Calc, if the Activity Calc and the Account Tag in UD1 match, the number is added to the data buffer. Values are then sign-flipped by multiplying them by -1. There are two main approaches, depending on the version you're working with. For releases 6.0 and later, we can leverage GetDataBufferUsingFormula to produce performant code that is easy to read. For older releases, we have to use a more complex approach via the Eval function. GetDataBufferUsingFormula - v6.0 and above ' retrieve the account ID once, outside the loop, to avoid multiple lookups Dim cfArId as Integer = api.Members.GetMemberId(DimType.Account.Id, "CF_AR") ' create the result buffer Dim targetBuf As New DataBuffer() ' retrieve source data, limited to tagged accounts with a complex filter Dim sourceBuf As DataBuffer = api.Data.GetDataBufferUsingFormula( _ "RemoveNoData(FilterMembers(F#ActivityCalc, [A#BalanceSheet.Base.Where(Text1 = CF_AR)]))") ' loop through each cell For Each cell As DataBufferCell In sourceBuf.DataBufferCells.Values ' copy cell into new one, so we can safely tweak it Dim newCell As New DataBufferCell(cell) ' change the account newCell.DataBufferCellPk.AccountId = cfArId ' Stuff cell into target buffer. ' The last parameter ensures multiple values are summed up, ' rather than replacing each other targetBuf.SetCell(si, newCell, True) Next ' assign target buffer to a variable we can reference in Calculate api.Data.FormulaVariables.SetDataBufferVariable("myBuf", targetBuf, False) ' actually save the buffer into the database, against the target destination, while flipping signs. api.Data.Calculate("A#CF_AR:F#CF_Activity = $myBuf * -1") Note: this sample aims to strike a balance between code complexity, flexibility, and performance. More performant approaches are possible: you could modify the Flow ID also, and flip sign, inside the loop; then just saving results with api.Data.SetDataBuffer, rather than going through a Calculate call. That would be less flexible to integrate with other requirements though (e.g. performing other operations before saving, which can be easily done in Calculate but not with buffer objects). Eval - v.5.x and below Formula The Calculate call is straightforward. Notice the second parameter; that must be the name of our Helper Function. api.Data.Calculate("A#CF_AR:F#CF_Activity = EVAL(F#ActivityCalc) * -1", AddressOf onEvalDataBuffer) Helper Function This can be reused over and over, so it might be better placed in a shared Business Rule for ease of maintenance. Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs) Dim cfAccountId As Integer = api.Members.GetMemberId(DimType.Account.Id, "CF_AR") Dim cfFlowId As Integer = api.Members.GetMemberId(DimType.Flow.Id, "CF_Activity") 'Retrieve the target accounts, and keep their IDs in a Dictionary Dim ms As String = "A#BalanceSheet.Base.Where(Text1 = CF_AR)" Dim members As List(Of MemberInfo) = _ api.Members.GetMembersUsingFilter( _ api.Pov.AccountDim.DimPk, ms, Nothing) Dim memberlookup As New Dictionary(Of Integer, Object) If Not members Is Nothing Then For Each memInfo As MemberInfo In members memberlookup.Add(memInfo.Member.MemberId, Nothing) Next End If 'Loop over cells that match our "TEXT1" account filter Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell) For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values ' Ignore NoData cells and where the account is not tagged If(Not sourceCell.CellStatus.IsNoData) And _ memberLookup.ContainsKey(sourceCell.DataBufferCellPk.AccountId) Then ' We don't assign the original cell object to output buffer, ' but an edited copy Dim cashflowCell As New DataBufferCell(sourceCell) cashflowCell.DataBufferCellPk.AccountId = cfAccountId cashflowCell.DataBufferCellPk.FlowId = cfFlowId Dim existingCell As DataBufferCell = Nothing If resultCells.TryGetValue(cashflowCell.DataBufferCellPk, existingCell) Then 'Since there is already a cell in the dictionary, ' replace it with the aggregated amount. existingCell.CellAmount = existingCell.CellAmount + sourceCell.CellAmount Else 'Add this dataCell to the new dictionary. resultCells.Add(cashflowCell.DataBufferCellPk, cashflowCell) End If End If Next 'Assign the new list of DataCells to the result. eventArgs.DataBufferResult.DataBufferCells = resultCells End Sub2likes0Comments