Get WF Status
Hello, In my BR, I need to incorporate logic that executes code based off the status of a separate WF Channel. E.g. I have 2 workflow channels and the first contains Workspace-->Import-->Validate--Load The second is simply Workspace. This second workspace has a button that kicks off a DM job that executes a BR. In the BR, the code needs to read the status of the first channel, and if it is not completed through process, execute specific code. Any ideas?
Recurring journal with Auto Post
So im trying to create a rule that pulls selected journals forward than posts them automaticaly. I have my test journal pulling forward as expected but when im trying to post the journal in rule below its causing the rule to error. The line in question is, BRApi.Journals.Process.ExecutePost(si, journalObjectHeader.UniqueID) What am i missing it seems pretty straight forward? 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 Imports System.IO.Compression Namespace OneStream.BusinessRule.Extender.CA_JournalRecurring2 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 Select Case args.FunctionType Case Is = ExtenderFunctionType.Unknown CopyJournal(si) Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep Case Is = ExtenderFunctionType.ExecuteExternalDimensionSource 'Add External Members Dim externalMembers As New List(Of NameValuePair) externalMembers.Add(New NameValuePair("YourMember1Name","YourMember1Value")) externalMembers.Add(New NameValuePair("YourMember2Name","YourMember2Value")) Return externalMembers End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Private Sub CopyJournal(ByVal si As SessionInfo) Try 'retrieve the names of the workflow parameters and concatenate them together Dim profilePostfix As String = brapi.Workflow.Metadata.GetProfile(si,si.WorkflowClusterPk.ProfileKey).Name Dim scenarioPostfix As String = brapi.Finance.Members.GetMemberName(si,dimtype.Scenario.Id, si.WorkflowClusterPk.ScenarioKey) Dim timePostfix As String = brapi.Finance.Members.GetMemberName(si, dimtype.Time.Id, si.WorkflowClusterPk.TimeKey) Dim strPostfix As String = $"{profilePostfix}_{scenarioPostfix}_{timePostfix}" 'The name of the journal to copy Dim journalName As String ="Tax Accruals_Houston.journals_Actual_2011M2" Dim oldJournalObject As JournalEx = BRApi.Journals.Metadata.GetJournalOrTemplate(si, journalName) brapi.ErrorLog.LogMessage(si, journalName) 'Remove the postfix from the old journal name to get the name Dim strOldName As String = oldJournalObject.Header.Header.Name Dim oldProfileName As String = brapi.Workflow.Metadata.GetProfile(si,oldJournalObject.Header.Header.WorkflowProfileID).Name strOldName = strOldName.Remove(strOldName.IndexOf(oldProfileName)) brapi.ErrorLog.LogMessage(si, strOldName) 'create a copy of the journal header Dim journalObjectHeader As New JournalHeader(oldJournalObject.Header.Header) 'Update the required parameters journalObjectHeader.Name = $"RF_{strOldName}{strPostfix}" journalObjectHeader.Description = $"Roll Forward from {oldJournalObject.Header.Header.name}: {oldJournalObject.Header.Header.Description}" journalObjectHeader.UniqueID = Guid.NewGuid() journalObjectHeader.MemberIds.Scenario = si.WorkflowClusterPk.ScenarioKey journalObjectHeader.MemberIds.Time = si.WorkflowClusterPk.TimeKey journalObjectHeader.WorkflowProfileID = si.WorkflowClusterPk.ProfileKey ' Log the unique ID Of the journal before posting brapi.ErrorLog.LogMessage(si, $"Journal UniqueID: {journalObjectHeader.UniqueID}") 'Journal status as working journalObjectHeader.JournalStatus = JournalStatus.Working 'Create a copy of the journal line item using linq Dim journalObjectLineItems As list(Of JournalLineItem) = oldJournalObject.LineItems.Select(Function(x) New JournalLineItem(x.LineItem)).tolist 'Create a new journal object Dim journalObject As New Journal(journalObjectHeader, journalObjectLineItems) 'Save it BRApi.Journals.Metadata.SaveJournalOrTemplateUsingIds(si, journalObject, False, True) ' Post the journal using ExecutePost BRApi.Journals.Process.ExecutePost(si, journalObjectHeader.UniqueID) brapi.ErrorLog.LogMessage(si, $"Journal '{journalObjectHeader.Name}' posted successfully.") Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub
Perform a Translate within a Calculate when copying information.
In a calculation, I'm copying data from one scenario to another. I have no issues with the amounts in local currency, but with the USD currency I do, because it retains the same value as the scenario I'm copying to, instead of the value it should actually have. Dim TimeName As String = BRApi.Finance.Time.GetNameFromId(si, si.WorkflowClusterPk.TimeKey) Dim entity As String = api.Pov.Entity.Name Dim index As Integer = TimeName.indexOf("M") Dim monthNumber As String = TimeName.Substring(index + 1) '1 'api.Data.ClearCalculatedData("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE",True,False,False,True) api.Data.ClearCalculatedData("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE",True,True,True,True) 'BRapi.ErrorLog.LogMessage(si,"Time: " & api.Pov.Time.Name & " entity: " & entity & " currency:" & api.Pov.Cons.Name) 'If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then If api.Cons.IsLocalCurrencyForEntity() Then 'api.Data.Calculate("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE = RemoveZeros(FilterMembers(S#[Forecast " & TimeName & "],[A#Root.Base]))") api.Data.Calculate("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE = RemoveZeros(S#[Forecast " & TimeName & "])") api.Data.Translate(sourceDataBufferScript, destDataBufferScript, End If I use this code when consolidating the target scenario, so I'm not sure if I'm missing something in this section, since in theory the translation should happen automatically during consolidation. I appreciate any support you can give me.How to get the sum of multiple entities in a GetDataCell or in a Calculate
Hi Everyone. I want to get the sum of multiple entities, like in the example below, but for now I'm only getting errors or values with 0 'First option Dim strList As String = String.Join(", ", (From l In lstMI Select "" & l.Member.Name & "").ToArray()) Dim lstMI As List(Of MemberInfo) = BRapi.Finance.Members.GetMembersUsingFilter(si, api.Pov.EntityDim.DimPk, "E#Kazakhstan.Base.Where(Text1 = 'M_S_KAZAKHSTAN')", True) api.Data.GetDataCell("A#411000:E#Root.list([" & strList &"]):C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS").CellAmount 'Second Option api.Data.GetDataCell("A#411000:E#Kazakhstan.Base.Where(Text1 = 'M_S_KAZAKHSTAN'):C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS").CellAmount What I ultimately want to do is to get those values so I can change the following, so that the entity includes all those base entities that meet the filter, and I can get the correct value and use it in my Calculate. Dim DBICtry As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#RETBUYBACK:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS + A#423100:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS + A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS)") api.Data.FormulaVariables.SetDataBufferVariable("DBICtry",DBICtry,False) api.Data.Calculate("A#P_EBIPrct:O#Import:U1#D0000:U2#P0000:U3#LOCAL_DATA:U4#PJ00000 = Divide($DBICtry, A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS)") Thanks for your help and I appreciate the help.Using the Trailing3MonthAvg View Member in a Data Buffer Across Years
Hello, I was wondering if anyone has had any success using Trailing3MonthAvg View member within a data buffer when the trailing 3 months cross over years. When I run this for a period like June everything works great, but when I run this in Jan or Feb I get the object reference not set to an instance of an object error. When switching the View member to YTD everything works fine. Below is an example of the code. Thanks!! Dim filter1 As String = "U1#XXXXX.Base" Dim filter2 As String = "U6#XXXXXBase" Dim U2 As String = "XXXXX" Dim test1 As String= "Cb#XXXXXX:T#2023M1:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Trailing3MonthAvg:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" _ & U2 & ":U7#Top:U8#None" Dim test As DataBuffer = api.Data.GetDataBufferUsingFormula( _ "RemoveZeros(FilterMembers(" & test1 & ", " & filter1 & ", " & filter2 & "))",,True)
Syntax to pull entity description where entity NOT the POV entity
Is there a syntax to lookup the description of an entity that is NOT the pov entity? In the example below the pov_entity is XXXXXXX_Canada, but the entity I want to pull a description from is 00250. I am writing a rule in a ud8 member to try to pull this. Thank you,Execute Custom Calculate BusinessRule - Server Timout
Dear community, We are using a Dashboard Extender Rule to trigger a ExecuteCustomCalculateBusinessRule. The business rule that we are running is a finance business rule that contains databuffer formula's. Since we can only run a finance business rule for one entity, we have created a MemberList that is running the Finance Rule each time for one of the 1500 legal entities that we have. This caused a Server Timer Out error. Do you know if the ExecuteCustomCalculateBusinessRule runs for each entity separately or for multiple entities in Parallel that is causing this TimeOut?How to pull a field from Custom table into a Cube view
Hi Everyone, I have a requirement to pull the data in a particular format in my cube view. I have entity dimension members in a row which gets pulled from the cube. I have to pull the respective opening dates of the stores from the custom table in the cube view as a column. I have created the custom table (with store and opening date) and used a business rule to pull the opening date from the custom table. Now I want to display this in the cube view w.r.t the stores we are pulling in rows. For ex if we have a row with Entity (store) tx0001 in the cube view which has an opening date for the store in the custom table. This opening date needs to be displayed in the column w.r.t to the store in the row. How to achieve this? Thanks, Jeevan
Parser rule to compare attribute data between current month load and last year Dec load
HI Experts, I have a parser rule that looks at current attribute 9 and compares it with attribute 9 of last year December load and returns it as attribute 10 which helps me map the differences in Tiering. My issue is - the file is huge around 15k lines and its taking ages for this comparison. How can i make it run a bit faster ? Any help would be appreciated. PARSER RULE BELOW FOR YOUR INFORMATION: Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As ParserDimension, ByVal args As ParserArgs) As Object Try Dim Time As String = BRApi.Finance.Time.GetNameFromId(si,si.WorkflowClusterPk.TimeKey) Dim WfYear As String = Time.Substring(0,4) Dim WfYearPrior As Integer = WfYear - 1 ' 'Get the fields for comparison Dim AttrColPosition As Integer = 97 Dim CurrString As String = api.Parser.DelimitedParsedValues(AttrColPosition) 'Brapi.ErrorLog.LogMessage(si,"My Currr A8 is " &CurrString) Dim EntColPosition As Integer = 2 Dim EntString As String = api.Parser.DelimitedParsedValues(EntColPosition) 'Brapi.ErrorLog.LogMessage(si,"My Currr Entity is " &EntString) Dim ISINColPosition As Integer = 23 Dim ISINString As String = api.Parser.DelimitedParsedValues(ISINColPosition) 'Brapi.ErrorLog.LogMessage(si,"My Currr Entity is " &EntString) 'SQL ---------------------- Dim sql As New Text.StringBuilder Dim dt As DataTable sql.AppendLine("Select TOP 1 A9 ") sql.AppendLine("From vStageSourceAndTargetDataWithAttributes ") sql.AppendLine("Where Si = 'AnalystPackLoad' and TmT = '"& WfYearPrior &"M12' and A18 = '"& ISINString &"' and Et = '"& EntString &"' ") Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) dt = BrApi.Database.ExecuteSql(dbConnApp,sql.ToString,True) End Using If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then Dim Att10 As String = dt.Rows(0)("A9").ToString() Return Att10 End If Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End Namespace
