Rules

Hi everyone,  I'm trying to create a formula for an account member that includes different accounts and UDs. I'm trying to do something like this: (A#COMMODITY_GM = A#GROSS_MARGIN - A#NET_INCOME:U2#WC - A#NET_INCOME:U2#US + A#503099:U2#EC - A#FINANCE_EXPENSE:U3#OTHERGRN) I found in the book Finance Rules and Calculations a possible solution using a Data Buffer, so I came up with the following formula: Dim grossmarginDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#GROSS_MARGIN:U8#None") api.Data.FormulaVariables.SetDataBufferVariable("GROSS_MARGIN",grossmarginDataBuffer, False) Dim netincomeWCDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#NET_INCOME:U2#WC:U8#None") api.Data.FormulaVariables.SetDataBufferVariable("NET_INCOME_WC" , netincomeWCDataBuffer , False) Dim netincomeUSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#NET_INCOME:U2#US:U8#NONE") api.Data.FormulaVariables.SetDataBufferVariable("NET_INCOME_US" , netincomeUSData

Hi Guys,  I'm at a project where we use Data Cell Detail, we have created classifications and added 2 lines of test data. I can find the data via a data adapter Method query: But now i want to add it via a BRApi, i've found a post here but what ever I try i cannot get it to work. I have a cube view with 2 columns and the only difference is the UD8 member, so i first want to get the current data cell POV and set UD8 to None: Dim objMbrScrBldr = New MemberScriptBuilder() 'Apply current POV to memberscript and override UD8 api.Data.ApplyDataCellPkToMemberScriptBuilder(objMbrScrBldr, api.pov.GetDataCellPk(), True, True, True, True) objMbrScrBldr.SetUD8("None") Then I call the datacell containing the amount:  'Return the sum of the DataCells that contain the selected Classification Dim objDataCell As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, api.Pov.cube.name, objMbrScrBldr.GetMemberScript()) and check if the data has DataCellDetail: I

api.Data.Calculate("A#FCTR:F#EndBalanceLoad:I#None:O#Import:V#YTD:U1#00000:U2#00:U3#GL:U4#0000:U5#00:U6#00:U7#None:U8#None = A#Assets:F#EndBalance:I#Top:O#Top:V#YTD:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#Top:U7#None:U8#None:C#Share - A#Liabilities:F#EndBalance:I#Top:O#Top:V#YTD:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#Top:U7#None:U8#None:C#Share - A#Equity:F#EndBalance:I#Top:O#Top:V#YTD:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#Top:U7#None:U8#None:C#Share") Error in formula: Error processing source script 'A#Assets:F#EndBalanceLoad:V#YTD:O#Top:I#Top:U1#Top:U2#Top:U3#Top:U4#Top:U5#Top:U6#Top:C#Share' or destination script 'A#FCTR:F#EndBalanceLoad:V#YTD:O#Import:I#None:U1#00000:U2#00:U3#GL:U4#0000:U5#00:U6#00'.Error processing data. The item was not found. Entity, Parent, -1.

I have the existing BR rule to export the region hierarchy which is working for last one year without any issues and I am trying to reuse the BR rule for country hierarchy export purpose. I copied & pasted the BR rules, and I am getting the error below while compling. Can you suggest what are all the possibilities to check these issues?   1) Error at line 313: Type 'SessionOptions' is not defined. 2) Error at line 315: 'Protocol' is not declared. It may be inaccessible due to its protection level. 3) Error at line 324: Type 'Session' is not defined. 4) Error at line 329: Type 'PGPLib' is not defined. 5) Error at line 338: Type 'TransferOptions' is not defined. 6) Error at line 339: Type 'TransferOperationResult' is not defined. 7) Error at line 340: 'TransferMode' is not declared. It may be inaccessible due to its protection level. 😎 Warning at line 18: Namespace or type specified in the Imports 'WinScp' doesn't contain any public member or cannot be found. Make sure the name

Hello, Is there a way to create a mapping rule for an account that looks at all entities but one?For example, A#123:E#*but E#456Thanks!

HI Am looking to lock the accounts (Rev first and then COGS ), such that if the Rev load is done i should be locking the Rev account for a particular scenario, then lock COGS account based on the Input deadlines. Can anyone help me on this Regards Narayana (BOYD Corporation)

Hello every expert: I am not able to reference a different scenario member in custom calculation business rule. I tried the following api.Data.Calculate("S#Forecast=S#Actual","A#10100" ...........) in the finance business rule I created. When I run the rule through Data Management Step, I have to specify Scenario and I select Forecast, I got error S#Actual is not valid dimension member when I run the business rule through custom calculate step. I really appreciate any helps from you. Thanks in advance.

Hi all, I have a member formula with simple calculation (e.g., Account A / Account B), and there is a data access security set up upon account A. I am wondering if a user without account A access, can the user be able to see the calculated result? My hunch is that it will show access on the end results, may I know how OneStream will handle this? Thank you.

Dear Team, while opening the cube view, I am experiencing issue " Value was either too large or too small for a Decimal."  I can see that in BR for EndBal, one of the divided formula causing the issue I am thinking. I am trying to add the divide function, but it is not working. any suggestions on the new formula?  0 Kudos   Accept as Solution Existing furmula: Return api.Data.GetDataCell("VariancePercent(A#AVG_Daily_Sales:T#POV:U8#None,A#AVG_Daily_Sales:T#POVPrior12:S#Actual:U8#None)/100")   New furmula: Return api.Data.GetDataCell("(VariancePercent(Divide(A#AVG_Daily_Sales:T#POV:U8#None,A#AVG_Daily_Sales:T#POVPrior12:S#Actual:U8#None)),100)")    

I would like to give my users the ability to launch the DM sequence from the workflow step. I have the dashboard with the button that passes the parameters to the DM package and launches the package. The last step in the DM sequence runs the Extensibility BR that completes the workflow step. Everything seems to work except that the workflow step window does not refresh after the sequence completion. The workflow step appears incomplete even though it is. The click on the "refresh Application" icon is required. Is there a way to address it? My Extensible BR is below:  

This is a doubt with the rest of the questions I have, and is that in the new workflow I created, when I hit complete worklfow there are two data management that are not executed (COPY_PLP_TO_PLN, PROCESS_PLP), which are below, but I try it in other workflows and it works correctly. Then I wanted to know how I can solve this in the new workflow, so that when I complete the workflow it also runs those data management as in the others.    

Is there any way I Can enter a +ve or -ve value based on the text property if a Flow member in a form. I think a Save Data Event Handler can, but I will throw an error if the only after the form is saved. What if the cell color will change (Just like in Excel) if the value enter on the form  should  +ve or -ve value based on the text property if a Flow member. is this achievable.   TIA, Nik

Hi Everyone. I wanted to know if there is a way to send emails that are made with parcel service automatically, for example, to send a certain number of days at a certain time, I would appreciate if you could help me to do something like that.  

I want to run a DM job from a dashboard (with the task popup showing progress, so no background running) and then when finished have the originating dashboard refresh after updating a parameter used in the dashboard.   If I call the DM job directly from the dashboard I am not sure how to return a task result that will update the parameter value in the dashboard.  I tried using a BR that called a DM job and then updated the parameter and refreshed the screen. However, when running it this way the popup did not appear and it just froze the screen, sort of negating the value of using the DM job.  Has anyone successfuly tried to do something like this before?

Hi all I like to check a manual data entry during save. Is this possible?  

Hello, I have a list of segments in WFText2 that I use as UD2 dropdown selection for my cube view.    So whenever I open my Form/Cube View I can see all segments excluding GBA_021, GBA031, GBA_032 and GBDP_001,  in UD2 Dropdown selection. My dilemma is, I am creating a confirmation rule that will loop for each UD2 that were not stated to be removed. I use this to get Text2: Dim WFText2 As String = brapi.Workflow.Metadata.GetProfile(si,si.WorkflowClusterPk.ProfileKey).GetAttributeValue(ScenarioTypeID.ScenarioType1, SharedConstants.WorkflowProfileAttributeIndexes.Text2) And the entire UD2: Dim GBA As List(Of Member) = api.Members.GetBaseMembers(api.Pov.ud2Dim.DimPk, api.Members.GetMemberId(dimtype.ud2.Id,"gba")) I initially used this GBA in my confirmation rule but it also includes those that were removed in WFText2 in the loop.   Any advise would be helpful.

I am looking to create a FX Rate finance rule or calculate to go get the planning scenario's last actual month's closing rate from the ClosingRate FX table. I am using the consolidation member C#Aggregated in the planning scenarios.  I know that C#Aggregated executes business rules at the base level only. Is there a workaround here or if I use C#Aggregated am I stuck creating FX rate tables for each planning scenario and copying last actual month rates forward to the planning periods? Appreciate any help or guidance.  Thanks 

I am trying out a complex expression in Time Data Source. The issue is the next: I have a Forecast scenario type with input frequency monthly and on the other hand I have a Long Term scenario type with input frequency yearly. Therefore, the colum time in the file is yearly, so I developed a complex expression where I can load depending on neither scenario type year 2024 or WFTime (forecast for this case) or scenario type year 2024 + M12 or WFTime + M12 (Long Term for this case). This is my complex expression (that is not working): Dim vYear As String = args.Value Dim scenId As Integer=api.CurrentDataClusterKey.ScenarioIDDim scenType As ScenarioType = BRApi.Finance.Scenario.GetScenarioType(si,scenId) If scenType=ScenarioType.Forecast ThenReturn vYear + "M12"ElseReturn vYearEnd If I appreciate all your comments, Thank you.

Hi, This formula is working, but it's returning 100% for each Entity. Is there something I'm missing regarding Entity? Will this work in a member formula or only in a business rule? Dim entity As String = api.pov.entity.NameDim entityID As String = api.pov.entity.MemberIDDim OwnValue As Decimal = api.entity.percentOwnership(entityID) If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Thenapi.Data.Calculate("A#NCI_IS:V#Periodic:F#EndBalLoad:O#None:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None= "& _"RemoveZeros(A#Income_Loss_before_Noncontrolling_Interests:V#Periodic:C#Local:F#EndBalLoad:O#Top:I#Top:U1#Top:U2#Top:U3#None:U4#None:U5#None:U6#None:U7#Top:U8#None * ( ("& OwnValue &")))")End If

Hi, has any one used DocumentFormat.OpenXml.dll coming v8 to create excel docs? It looks like it is working from standard extender BR once you add reference to DocumentFormat.OpenXml.dll and System.IO.Packaging.dll but it does not work when you do in a file within Workspace assembly as it complains about not finding the referenced System.IO.Packaging.dll even if the dependency is added   Thanks  

I am trying to get the journal post group in the workflow profile properties using JournalsEventHandler Business rule.  Does anyone have done this before? Is it possible to get the journal post group value from the Workflow Profile?

I am trying to call a database stored procedure from an extensibility rule.  The stored procedure returns an OUTPUT parameter, but I'm unsure how to reference that OUTPUT parameter, using the DBParamInfo object and ParameterDirection setting.  Can someone give guidance on the proper usage for OUTPUT. Stored-Procedure CREATE OR ALTER PROCEDURE [dbo].[TEST_OUTPUT_PARAM_PROC] ( @OUT_PARAM_VALUE VARCHAR(100) OUTPUT ) AS BEGIN --Do stuff that needs to be done here SELECT @OUT_PARAM_VALUE = 'Param from Procedure'; END GO Extensibility Rule 'definition DB Call To stored procedure Dim OutParamValue As String = "" Dim tsSQL_CallProcwithOutputParamter As String = "DECLARE RC INT EXECUTE RC = TEST_OUTPUT_PARAM_PROC @OUT_PARAM_VALUE OUTPUT" Dim parameters As New List(Of DbParamInfo) parameters.Add(New DbParamInfo("@OUT_PARAM_VALUE", OutParamValue,ParameterDirection.Output)) 'paramDirection: Using dbC

Hello Everyone, I need a Literal parameter which will dynamically calculate something like the prior forecast based on another parameter so i ended up writing an XFBR for that Literal parameter, something like below XFBR(HelperRule,GetPriorForecast). And now i need this Prior Forecast value for some Finance calculations. So I tried to retrieve the value of Parameter in the finance rule by using  BRApi.Dashboards.Parameters.GetLiteralParameterValueBut it returns the same "XFBR (Helper Rule, Get Prior Forecast)" rather than the Actual String Value. Strangely enough, if i use the same XFBR for Scenario Filter in a DM step, it picks up the proper string value. Curious Why would it not work when I try to retrieve its value in a finance BR?

Hello OneStream Experts: I created an account called MyAcct to store calculation. I run the business rule in Data Management step and put Net Income to MyAcct. api.Data.Calculate("A#MyAcct = A#NETINC") After I run the business rule, the Calc Status in View dimension show CA for both NETINC and MyAcct. However, after I run right click the data cell to run consolidation or Calculation, the value in MyAcct got lost, the calc Status for NETINC became OK. I wonder why my calculated account lost the data value after I run consolidation. Thank you advance for any answers.

Hello OneStream experts: I created and account=MyAccount and put member formula. api.Data.Calculate("A#MyAccount = A#NETINC") I set AccountType=Dynamic Calc Formula Type=Formula Pass 1 I am able to compile the syntax and run the calculation successfully. But MyAccount account member showing no data from my quick view excel sheet. My NETINC account has data. I wonder why the calculation is not working. Thank you for any feedbacks.