Recent Discussions
Entity Relationship Properties update via business rules
Hi Team, I would like to fetch and update the Entity Relationship Properties for the Entity Dimension members via Business Rules. The below peace of code works fine for only when I pass the Actual and Budget but when i pass the other scenario Type like Scenariotype 1 to 8 I get the error Object is not set to an instance of an object. Dim Scenario As String = args.CustSubstVarsAlreadyResolved("Param_SecnarioType_EO_24") 'The above value i get it from the combo box as delimited list Dim Scenarioid As Integer = BRApi.Finance.Members.GetMemberId(si,dimtype.Scenario.Id,Scenario) Dim MyScenarioTypeid As ScenarioType = BRApi.Finance.Scenario.GetScenarioType(si,Scenarioid) Dim Timefilter As String = args.CustSubstVarsAlreadyResolved("Param_Time_24") Dim Timeid As Integer = BRApi.Finance.Members.GetMemberId(si,dimtype.Time.Id,Timefilter) Can you please help me with the correct way to get the scenario Type.
SQL Manipulation with a VB Net Variable
Hello, Does anyone know an effective way to alter the amount column of a table using a SQL script? Use case: the client is bringing in what they call "Production Downtime" to the system, which shows hours of downtime by plant based on a reason (attribute). Using a Dashboard Data Set rule, I created a SQL query to return specific results from the stage table based on Entity and Plant selections. This rule is linked to a data adapter and fed into the BI Viewer. In their BI Viewer report, it shows current Month vs PY Month. Since this data is coming in daily, when the user is looking at the current month, (today is Nov 21) the current month shows 21 days' worth of downtime data. They would like the PY Month to also represent 21 days of data. So, I would like to create a "ratio" variable that reads the current day of month and divides by the total days in month, and multiply the PY data by it. I created something similar in a UD8 calc: (SQL Is not my strong suit) how can I apply this "ratio" to the amount column of a SQL query? Amount * Ratio
Fetch Metadata values from Text file in Transformation Event Handler
Hi Experts, I am working on TransformationEventHandler rule in which we need to read the first 4 columns of a text file that is being uploaded to Workflow Import so I can do some sanity checks and error out if they are not correct. Does anyone have sample code that reads the values of file? I checked everywhere but not much documentation on this. Also, I placed this code in "ParseSourceData" BREventOperationType. Not sure if this is correct or need to put under "ParseTransformationRules"? Thanks in advance, PM
How to pass parameters through ExecuteCustomCalculateBusinessRule
Hi Everyone. I am trying to run a CustomCalculateBR in another BR, and I have the following for the variable namevaluepairs, but I would also like to pass parameters in this BR. I have been investigating but did not find a solution. Similarly, I would like to ask if passing with .base will work the same way as if it were in a step or not. I would appreciate it if you could help me. Dim mvp As New Dictionary(Of String,String) mvp.Add("Cube","HLFPLN") mvp.Add("Entity",strEntTx1) mvp.Add("Consolidation","Local") mvp.Add("Scenario","|WFScenario|") mvp.Add("Time","T#|!PLN_ParamFcstMths!|") mvp.Add("View","None") mvp.Add("Flow","None") mvp.Add("IC","None") mvp.Add("UD5","None") mvp.Add("UD6","None") mvp.Add("UD7","None") mvp.Add("UD8","None") mvp.Add("Parameters","test=ValueTest") BRApi.Finance.Calculate.ExecuteCustomCalculateBusinessRule(si,"PLN_ThreeMonths2","Test",mvp,CustomCalculateTimeType.MemberFilter)
Dimension members loading process using SIC
Hi all, I'm quite new to OS integration pieces and being struglled with a request from my client to load dimension members using their data lake, so the memebers would be updated automatically or by a scheduled event. The database is already configured at SIC and working fantastically well, however, I can't figure how to use it's features to load the metadata members to one of our UD dimensions. I read a lot of topics here in the forum, but couldn't find anything that matches my case. I already tried to create a Extender BR, but I'm getting a "Object variable or With block variable not set" error message when running the "api.Parser.ProcessSQLQuery" function. Not sure if I'm doing something extremely wrong, so I"m assuming that this part of the api must be used only within Connector BR's. I also trie to create a connector rule to do the job, but when setting it up at the dimension property, nothing happened (not sure if I missed something here). Any help on this will be really appreciated! RegardsHow can I assign a new value to an entity that is not the POV of my Data Management?
Hi Everyone. I would like to know how I can perform a setdatacell on an entity that is not in my Data Management configuration. I obtain the value of the entity from Text 1 of my entity assigned to the POV, but it does not allow me to apply a setdatacell or a calculate to assign a new value to that entity. Therefore, I would like to know how I can achieve this. My Data management configuration: The example code I am using: Dim strEntity As String = api.Pov.Entity.Name Dim strScenario As String = api.Pov.Scenario.Name Dim strTime As String = api.Pov.Time.Name Dim povEntityId As Integer = api.Pov.Entity.MemberId Dim strEntTx1 As String = api.Entity.Text(povEntityId, 1) 'setdatacell api.Data.SetDataCell("S#[" & strScenario & "]:T#" & strTime & ":E#" & strEntTx1 & ":A#S_ConvFactorInner:O#BeforeAdj:U1#D0000:U2#P0000:U3#LOCAL_DATA:U4#PJ00000:U5#None:U6#None:U7#None:U8#None",totAll,False,True) 'Data.Calculate api.Data.Calculate("Cb#HLFPLN:S#[" & strScenario & "]:T#" & strTime & ":E#" & strEntTx1 & ":A#S_ConvFactorInner:V#Periodic:C#Local:F#None:O#BeforeAdj:I#None:U1#D0000:U2#P0000:U3#LOCAL_DATA:U4#PJ00000:U5#None:U6#None:U7#None:U8#None = (" & totAll.ToString & ")",True)
Where does Stored Calculations store Data?
Today, a customer asked me:Where do stored calculations store data? api.Data.Calculate If Statements In stored calculations, If Statements are helpful to specify that a formula is only required to execute on specific Data Units, as shown in the image below. If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity()))contains two logical operators (AndandNot). Due to theANDoperator, the statement api.Data.Calculate("S#Forecast = S#Actual") betweenThenandEnd Ifwill only run if both of the following are true: The Entity is a Base-level Entity Member, i.e.: doesNOThave Children (Not api.Entity.HasChildren()) The Consolidation Member is Local indicating the Entity Member's local currency (api.Cons.IsLocalCurrencyForEntity()) The statementapi.Data.Calculate("S#Forecast = S#Actual")will copy the S#Actual data buffer to the S#Forecast data buffer. TheIf...End Ifis limiting the calculation statement to execute only for specific Data Unit Dimension Members. Other consideration to take into account when creating store calculation rules.💥 api.Data.Calculate("A#CashCalc=RemoveZeros(A#10000)")uses the RemoveZeros function to remove cells with No Data or 0.00 cells in the A#10000 data buffer. The results are stored in the A#CashCalc data buffer. The RemoveZeros function is to help with performance if there are large amounts of No Data or 0.00 cells. END IF😂😀😃How to sum three times in a single getdatacell
Hi Everyone. What I want to achieve is to obtain the amount of three times in a single getdatacell. What I have is the following: Dim first As Decimal = api.Data.GetDataCell("S#[" & strScenario & "]:" & timesTest(0) & ":E#30801_30800:A#FINVOLUME:F#None:O#BeforeAdj:U1#D0000:U2#P0100:U3#LOCAL_DATA:U4#PJ00000").CellAmount The issue is that I need to use three different times, but I don’t want to create three variables to sum those values, and I also don’t want to do something like the following: 'Example Dim tot as Decimal = api.Data.GetDataCell("#Time1" + "#Time2" + "#Time3") So, I wanted to see if there is a way to put all the times together in one so that the sum is done internally, or if it can only be done as shown above. I hope you can explain the best method to achieve this.
Filter Expression in DataUnit ExecuteMethodCommand
What is the correct syntax for the filter expression in the DataUnit method command used inBRAPi.Database.ExecuteMethodCommand()? We are trying to filter on Flow member EndBal. The following syntax does not work: MethodQuery: {FinRpt}{CorpEntity}{}{}{ACTUAL}{2024M1}{YTD}{True}{F#EndBal} results in the follwoing error: The expression contains invalid date constant '#End_Inp'. MethodQuery: {FinRpt}{CorpEntity}{}{}{ACTUAL}{2024M1}{YTD}{True}{WHERE Flow LIKE '%EndBal%'} results in the follwoing error: Syntax error: Missing operand after 'Flow' operator. {WHERE Flow = 'EndBal'},{WHERE Flow IS 'EndBal'}, and other variations all result in the same syntax error.