Rules

I have created an extender BR to load from a  xlsx file with xft named range into a custom table. The file is in the OS cloud fileshare in a specific folder. I am 100% sure the file is set up correctly and the path is not an issue, however I still keep getting an error when running the BR from data management that file is invalid or missing. Can someone please help.  Below is the BR . What am i missing   Dim fileName As String = "XFD_Plan1.xlsx" '<-- Enter name of Excel fileDim filePath As String = "Applications\OneStreamDevelopment\Batch\Harvest\" '<-- Enter path to Excel file'Note: dbLocation, TableName, LoadMethod & Field names defined in header of each excel "xft" range.BRApi.Utilities.LoadCustomTableUsingExcel(si, SourceDataOriginTypes.FromFileShare, filePath & fileName, Nothing)

Hello,   I have a use case where I need to get data from siblings (or parents) for a dynamic calc. Entities A,B are part of the same parent EntityC. Cost1 / Entity A : 1000 Cost1 / Entity B : 500 Cost2 / Entity A : 2000 Cost2 / Entity B : 1000 The calculation of the KPI should be as follow for each entity : (0.5*Cost1 + Cost2) / ( 0.5*Cost1:EntityC + Cost1:EntityC). or : (0.5*Cost1 + Cost2) / ( 0.5*Cost1:EntityA+EntityB + Cost1:EntityA+EntityB). (it's the same really) Just to give more fun to it, this formula should be dynamic, as I have multiple entities and multiple parent...   I'm looking for a formula that would help me achieve this ?   Regards,

Does anyone has a NotePad++ language definition file for OneStream rules? Thanks in advance.

Hello Everyone,  Looking for a quick help Please. Here I am trying to write a Dynamic Calculation code which will follow the exact logic of PVA translation however will use rates from previous year. So kind of a Constant currency solution. However when I am in Time period 2, I am unable to pull data from different periods, This calculation works If I make my T#Hardcoded e.g. 2020M1/2020M2. Current outcome is all zeros 😞 Please help with any solution anyone has or can point me to right direction - since unable to understand what is missing.   Thanks Satish    

Hi all, I have a dynamic calc that displays zeros instead of null when the source cell is null. In the image below, MTD_CF is dynamic and it shall display None data under certain conditions. When None is null, MTD_CF is showing 0. Is there a way to avoid this so the dynamic calc shows nulls as well? I've tried with RemoveZeros, like this: Return api.Data.GetDataCell("RemoveZeros(V#" & view & ":U1#None)").CellAmount The code returns the result, but does not remove the zeros. Any ideas? Thanks in advance. GSF

Hi all Onestream stores a lot of information using an integer ID instead of a name. And most of the time we also deliver a simple way, to get objects (or names of objects) using an api or brapi call. Somehow getting the name of a dimension from its ID is an exception. Is there a simple way to do it? Cheers  

Hello Experts, I am trying to export Cell Details to a CSV using a business rule function as shown below:     If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Export_CellDetails_CSV") Then Dim wfUnitInfo As WorkflowUnitInfo = api.Workflow.GetWorkflowUnitInfo() Dim SRCTimeName As String = wfUnitInfo.TimeName Dim configSettings As AppServerConfigSettings = AppServerConfig.GetSettings(si) Dim timeStamp As DateTime = DateTime.Now '.UtcNow Dim AESTNow = System.TimeZoneInfo.ConvertTime(timeStamp, TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time")) Dim timeString As String = AESTNow.ToString("yyyy_MM_dd_HHmm") Dim xfolderPath As String = FileShareFolderHelper.GetDataManagementExportUsernameFolderForApp(si, True, configSettings.fileShareRootFolder, si.AppToken.AppName) Dim csvFilePath As String = xfolderPath & "\" & "Cell_Details.csv" If File.Exists(csvFilePath) Then File.Delete(csvFilePath) End If

Hello, I am working on a CTA plug account and also historical overrides. Right now for the plug and overrides to run I have to force calculate and translate. Is there a way the user would be able to run the consoldiation and the formulas run correctly without having to go through a whole sequence of steps? Basically, is there an easier way to run the translation logic within consolidation? These are the current formulas I have: CTA Plug with formula pass 8 being used: If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) ThenDim entityDimPk As DimPk = api.Pov.EntityDim.DimPk Dim entityId As Integer = api.Pov.Entity.MemberPk.MemberId If Not api.Members.HasChildren(entityDimPk, entityId, Nothing) Then api.data.calculate("A#313001:F#TB_ENDBAL:I#None = A#TotalAssets:F#TOT_USGAAP:I#Top - A#TOTALLIABEQUITY:F#TOT_USGAAP:I#Top") End If End If and for overrides I only have one formula being used for “Historical Override Prior” on Formula pass 1. I think this may be part

Source:   Migrated from Champions Hello all, I’m creating a Data Adapter that is using SQL to poll an external database. I can get it to work when I’m just supplying the strings for dates. However, I wanted to get fancy and be able to parse the effective dates using variables. When I go to an online SQL tester (https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_left), I can get the below code to return ‘121181’, which is the Julian date I need in querying JDE. But the OS SQL Query returns an error saying ‘int’ is an invalid token. does anyone know if there’s a way to use variables in this way? Declare @YearRun intDeclare @YearPart intDeclare @MonthRun intDeclare @Global varchar(100)Declare @GetTime datetimeDeclare @RunTime varchar(100)Set @Global = ‘2021M6’set @YearRun = LEFT(@Global, 4)Set @YearPart = Right(@YearRun,2)set @MonthRun = Right(@Global, Len(@Global)-CHARINDEX(‘M’,@Global))Set @GetTime = EOMONTH(CONCAT(@MonthRun, ‘/’, 1, ‘/’, @YearRun))S

Hi all How can I avoid the ampersand in string operations? I need to concatenate a pov for a calculation, but with all the ampersands, it looks complicated: "A#" & accountName & ":F#" & flowName & ":UD2#" & ud2Name & ":UD4#" & ud4Name  

HelloI've tried to load my excel file through the data source fixed files with a complex expression on the time dimension, and i think it doesn't go through the complex expression at all so i'm wondering if i can use a complex expression in fixed file data source ?    The check i'm trying to do is to verify the workflow period and the time that's in my load file. if it's the same it can continue loading but if it's different i'm asking OS to pop up a error message.If anyone has an idea please share it with meThanks 🙂

Hi there,  I want to add data to a dictionary with a key that contains 3 fields. I've been advised to use a ValueTuple but have tried all the syntax options that i could find in Google without success.   Ideally it should be like the setup of a databuffer where the key is the databuffercellPk and the value the actual stored number. My key should contain 3 strings and the value a decimal). I've tried something like: Dim dctDict as New Dictionary<(String sn, String Tm, String Ac)>, int(); and various other options.  Does anybody had a suggestion how to create the best performing Dictionary Key that exists of 3 strings?

Hi - We are try to create logic that will determine whether a scheduled job would run. We have been able to create the logic to only run if current date is <= to the day of the month (Calendar Day) but what we really want is to be able to know if it is before for a specific working day so excluding weekends (for example working day 10 in September 2021 would be the 14th) . Has anyone created this logic and would be willing to share?

I've been given a task to debug an error that happens when we confirm a month, and I'm not sure what some of the messages are telling me.   When I run Confirmation, I get an error in a dialog box, and the title of the Dialog Box is "Extensible Finance." Should I be looking for a confirmation or a business rule with that name? The error text is "A#Facilities_Allocation:F#None:O#Forms:I#None:U1#None:U2#1:U3#None:u4#None:U5#None:U6#None:U7#None:U8#None" which I recognize as a ... I forget the term but I think of it as the filter parameters. But I'm not sure where to look for this. If I scroll down further in the task activity and open the error message to see the details, I see a line that says      Source code: ConfirmationEngineWcf.cs, line 133, method ExecuteConfirmation Is there a rule called ConfirmationEngineWcf that I should be looking for? Chuck

Coming from HFM, I'm used to having a single rules file that I can easily search through. In OneStream,  the equivalent rules appear to be defined for each individual account as a metadata property. I.e.  the Formula. Is there a way to extract all of the Account Formulas into a single file so that  I can search through it? Instead of having to click through each account in turn and opening up the individual formulas?

Hey - Wondering how you learned to code VBNet. I’ve been trying to improve my skill to write tailored Business Rules. Any resources/guide to share? Thanks,Mustafa

Source:   Migrated from Champions I’m trying to use the below member filter to remove only the intercompany accounts. This works, but I lose the indents from using Tree Descendants. A#410000.TreeDescendants.Where(IsIC = False) Is there a workaround to remove just the IC accounts while still retaining the indents from tree descendants?

Dear community, I have a nasty error message when I execute a data management task : "Error processing Data Management Step 'STEP1'. Data validation error. The Data Cell is read-only because the Parent Workflow Profile 'Activity MyEntity' has no active Input Profiles for the Workflow Channels that are assigned to the Data Cell's Account and/or UD member. Entity=MyEntity, Account=GL11410, Origin=Import." The funny thing is, I have no clue what is the issue. I try to execute a "Copy Data" data management that copies periodic data on the same cube, same entity, but from 2021 Actual to 2022 budget (only imported data).   --> What am I missing ?   Regards,

There are certain calculations are specific to some entities in the hierarchy so want to add if condition to check entity names and then execute the code. Couldn't find any api which returns Entity name. Regards, Yashwant

Hello,   I'm new to Business Rule, and I'm struggling with the execution of a data management step with type "custom calculate". Currently, the execution of this script is giving me the message below : Error processing Data Management Step 'CC_FY2022_BUD'. Unable to execute Business Rule 'CopyData'. Invalid destination data unit in script 'A#Account1:C#Local:T#2022M1:S#Budget:U1#None:U3#T:U8#None:O#Import'.   In the Data Management step, I have defined elements in the POV and Data Units, and "Data Units" elements seem to be taken into account correctly, but elements in the "POV" such as U2, U4, U5, U6, U7 elements that have been defined in the POV of data element task (hierarchy nodes) seem like is not considered in the calculation.   So here are my questions: - What is the error message mentionned above actually saying ? - Are POC elements from the Data Element task taken into account in the Finance Business Rules ? - How do you efficiently debug a Business Rule ? &nbsp

Is there a way to validate the Total Debit or Total Credit amount of a journal and send back a warning/fail message if the total is over a certain threshold when we try to post/save the journal? Thanks

Hi, I would like to retrieve the value for a text attribute of a member - could be in any any dimension - in order to display it in a label on a dashboard. Can I do so with a generic XFBR?

Hi all How can I run a calculation for a subset of dimension members. Something like all base members of member Top, but not the none member, or just the members with text1 attribute = "Calculate", or just a comma separated list of members. Do I need a for loop? Cheers

Originally posted by Rahul Joshi I am trying refer a intersection in member formula and apply condition based on the referenced cell.     Dim nvASP As DataCell = api.Data.GetDataCell("A#[ASP (£)]:F#No_Curr:I#None:U1#[Adjustment (Override)]:U2#None:U4#None:U5#None:U6#None:U8#None") If nvASP.CellStatus.CellAmount = 0.0 Then api.Data.Calculate("F#No_Curr:O#Forms:U1#Total:U2#None:U4#None:U5#None:U6#None:U8#None:I#None:A#[ASP (£)] = F#No_Curr:O#Forms:U1#Calculated:U2#None:U4#None:U5#None:U6#None:U8#None:I#None:A#[ASP (£)]") Else api.Data.Calculate("F#No_Curr:O#Forms:U1#Total:U2#None:U4#None:U5#None:U6#None:U8#None:I#None:A#[ASP (£)] = F#No_Curr:O#Forms:U1#[Adjustment (Override)]:U2#None:U4#None:U5#None::U6#None:U8#None:I#None:A#[ASP (£)]",,,True)     It does not consider the if-else condition and just process the first condition .Something which i am doing wrong in here ?

How can I use the api.LogMessage or BRApi.ErrorLog.LogMessage functions to print out the member names of a list of objects  like member, or memberinfo?