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
How do I clean a scenario before copying data from another scenario? in the member formula.
Hi Everyone. I want to copy the information from one scenario to another. Currently, I’m using the member formula of the target scenario to transfer the data. However, I first apply a calculate to set the values to zero, followed by a clecarcalculateddata to properly remove the existing information. The issue is that after this step, the copy process doesn’t execute, and the scenario remains with zero values. My code is as follows: api.Data.calculate("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE = 0*(S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE)",True) api.Data.ClearCalculatedData("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE",True,True,True,True) Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE") '' Create new Databuffer for the results Dim resultDataBuffer As New DataBuffer 'Base Entity at Local If Not api.Entity.HasChildren And api.Cons.IsLocalCurrencyForEntity() Then BRapi.ErrorLog.LogMessage(si,"Validate 1") ' Get Databuffer from the account and scenario Dim sourceDataBuffer As DataBuffer = api.data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(S#[Forecast " & TimeName & "],[A#Root.Base]))", , False) ' Verificar si hay celdas con datos If sourceDataBuffer.DataBufferCells.Count > 0 Then BRapi.ErrorLog.LogMessage(si,"Validate 2") For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values If (Not sourceCell.CellStatus.IsNoData) And (sourceCell.CellAmount <> 0.0) Then BRapi.ErrorLog.LogMessage(si,"Validate 3") Dim resultCell As New DataBufferCell(sourceCell) resultCell.DataBufferCellPk.OriginId = DimConstants.Import resultDataBuffer.SetCell(si, resultCell, True) End If Next api.Data.SetDataBuffer(resultDataBuffer, destinationInfo,,,,,,,,,,,,,False) End If End If I would appreciate your help in understanding why the values are not being copied. The process is executed when I consolidate the information.
What does api.Data.GetRelationshipChanges(dimensionName, startDate, endDate) return?
Hello, I already have a data file for cost centers and their Default values, but now I need to include the before and after changed values to the cost center member. I found this code: Function GetRelationshipChangesForTimeSpan(ByVal api As Api, ByVal dimensionName As String, ByVal startDate As DateTime, ByVal endDate As DateTime) As List(Of RelationshipChange) ' Initialize a list to store relationship changes Dim relationshipChanges As New List(Of RelationshipChange) ' Use the API to retrieve relationship changes relationshipChanges = api.Data.GetRelationshipChanges(dimensionName, startDate, endDate) ' Return the list of changes Return relationshipChanges End Function How can I tell what data it's actually returning, what columns? Is there a better way to do this? Any help is greatly appreciated! Thanks!ExecuteCustomCalculateBusinessRule ... but in workspaces
Hi, I came across an update function in the business rules for ExecuteCustomCalculateBusinessRule Previous function (still in use) : BRApi.Finance.Calculate.ExecuteCustomCalculateBusinessRule(si, brName, functionName, nameValuePairs, timeType) New function for workspaces : BRApi.Finance.Calculate.ExecuteCustomCalculateBusinessRule(si, workspaceID, brName, functionName, nameValuePairs, timeType) We can indeed at least in version 8.5 create Finance Custom Calculate Services : However, these don't use any business rule name, hence my confusion ? How do we execute a custom calculate service which is currently within my workspace assembly services ? Or should it be used in a different way ? Regards,
