Member formula to allocate bal sheet by UD
Hi experts, hoping someone can guide me. Our P&L is recorded by Line of Business (UD2), however our balance sheet is recorded to UD2 None. FPA has a requirement that they currently handle manually to "allocate" the balance sheet to the lines of business based on the percent of revenue. Our partner initially achieved getting closer by using a dynamic formula on UD8. This does not ultimately provide the accurate results at the consolidated level (due to nature of dynamic formula). We believe we need to change this to a formula pass but it is proving to be challenging. We are open to suggestions, using UD7 (tracking) or UD6 (reporting). I am also open to creating a mini alt acct hierarchy since it really only needs to apply for a handful of summary level accounts. (Coding examples would be so very appreciated!) Thank you!
Passing parameters from a user defined workspace to a DM process and Business Rule
When creating a dashboard in the default workspace you are able to pass dashboard parameter values to the data management sequence through the parameter section of the execution script: You can also use an Input Value parameter and create an extender process to populate the Input Value and then retrieve that value for a DM sequence or BR. However, when using user defined workspaces, I have only been able to successfully pass parameter values from a dashboard using a literal value (process on combo box executes a rule to populate a literal value with the most recent user selection, literal value is read by DM and BR processes). The issue with this literal value process is that the value is left at the value from the last execution of combo box selection so it requires the user to change the value to some other value, then reset the value to ensure the literal value has a valid stored value. Additionally, this process if being used by multiple users, may cause confusion if two users are changing combo boxes thus resetting literal values without knowing that the other users is changing the value. Is there no other way to reference user defined workspace parameter values like in the argument selections in the Server Task settings? I have seen some instances where the workspace ID can be referenced, however I don't see an examples of using some type of similar format for parameters (e.g. WorkspaceID.|!SomeParameter!|).
executing and reporting on the status of Process Cube via a business rule
For starters, we're on 8.2.1. In reviewing some automation rules that were written by my predecessors, I've been trying to move away from the Harvest folder batch processing. ...mostly, because I don't want to deal with cleaning up the clutter it leaves in the filesystem. In most cases, it's trivial to switch to BRApi.Import.Process functions and I've done this successfully but I find that running the Process Cube from a BR acts a bit weird. BRApi.DataQuality.Process.StartProcessCube and BRApi.DataQuality.Process.ExecuteProcessCube both start the process just fine and the process does exactly what it should. However, both methods only start the process and immediately move on to the next instruction. I can't seem to evaluate them for failure, duration, or anything else as they both instantly and always return that they finished successfully. Question 1: Both functions seems to behave identically and incorrectly (or at least unintuitively and undesirably for me in this case.) Other than the datatype of the object they return, is there actually a difference? Does it make sense to use one over the other or is it just a matter of which one has properties that are most useful in the context of the given rule? Edit. Question 1a: Is there a third way to execute the process that I'm missing? Maybe I shouldn't be using either of these! I tried using a sleep/timer with a loop to repeatedly evaluate process.IsRunning but it seems to evaluate to False instantly. process.Status instantly evaluates to "Completed." Duration and Endtime likewise are static, set almost-instantaneously, and appear to be of no use whatsoever. Question 2: What is the correct way to monitor a process like this? Specifically, I'd like to notify on failure and maybe evaluate the duration in case I want to alert someone that the processing times have gone from 30 seconds to an hour (or something, I'll figure out a way to handle that threshold.) I imagine there must be some way of polling the status. I could conceive of doing this "manually" by querying the TaskActivity table but it seems there must be a better way. I tried using BRApi.TaskActivity.GetTaskActivityItem(si, process.UniqueID) inside of a loop to see if querying it multiple times would help and it still behaves the same way. I know there are new event handlers but those are separate rules and I don't really think they apply in this case as I only want to report on this particular process in the specific context of my lights-out automation.performance issue.
Hello OneStream Community, I am working with a Cube View where I need to display the Income Statement data for the end of every year (December). The data should show up as the beginning balance for the January month in the subsequent year. To achieve this, I used a Row Override1 member filter with the formula T#povprioryearm12. The Cube View includes four dimensions: Entity, Division, Cost Center, and Accounts. However, I am experiencing slow loading times when the view is being accessed. it is not exporting to Excel to validate. I would like to know if there are any best practices or strategies for improving the performance of the Cube View in this scenario. Are there specific optimizations I should consider for the member filter, the dimensions, or the Cube View configuration to reduce loading times? Any advice or recommendations would be greatly appreciated! Thank you in advance!
Retrieve Annotation Data and send to XFBR
Hi All, Let's say we have a table: Fruits (Annotation) Updated Fruit Value (annotation) Price of Fruit (input numerical value) apple banana $1.99 banana kiwi $2.99 strawberry melon $3.99 Let's say the fruits column is a UD dimension member and the Updated Fruit value contains a combo box in the cell where a user can update whatever value that shows in column 1. Meaning if in column 2 it says banana then the selection in that column overrides the apple (by override I mean that the price in the last column will map the numerical value to banana instead of apple for that intersection). Is there a way to retrieve annotation values on the backend and send to an XFBR (Ideally sending to an XFBR so I can assign that member to my pov for my Price of Fruit column)?Help with GetUsersInWorkflowGroup function
I'm trying to lookup the users in a workflow execution group so I can e-mail them when the harvest batch process runs successfully. Below is the code I'm using to try and lookup the users within a workflow execution group. The problem is I don't know what to put in the "WorkflowGroupType" as Integer argument. I know that any number between 0 and 1249 will allow the function to run, but putting 1250 will error the function. I created a loop just to see if I could get any of the valid values to return something and didn't have any luck. Has anyone been able to successfully do this? Thanks!
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
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😂😀😃
