Ho to extract a hierarchy for any dimension in Level format in OneStream?
Originally posted by Vignesh Mohan Hi, How can we extract the hierarchy of any dimension in an excel in a Level based format starting from top most level to the bottom most level for every base member ? Basically I do not want a Parent-Child format extract. Please find the attachments for the sample screen shot.SQL Table Editor Unable to Save Data
OS Version 7.4.2 I have a SQL Table Editor in a dashboard. Three columns: Date, Amount, Descr. The user can edit Amount and Descr. No Inserts, no deletes. The table loads with a bunch of dates. The user fills in amounts in records where needed. The descr and amount columns both accept nulls. With the "Save Data Server Task" set to "No Task", when an amount is added to one record (or several), and save is clicked, the following error occurs: With the "Save Data Server Task" set to "Execute Dashboard Extender Business Rule", I specify a Rule and get the same "Wrong Number of database rows" error. If I use the SQL Table Editor code supplied by OneStream in the Rule, the error in the log is: I'm trying another version of this which omits the use of the Using block. Same "Wrong Number of database rows" error and the log just shows "Unknown Error" Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardExtenderArgs) As Object Dim saveDataTaskResult = Nothing Try Select Case args.FunctionType Case Is = DashboardExtenderFunctionType.SqlTableEditorSaveData If args.FunctionName.XFEqualsIgnoreCase("SaveInvoiceStagedAmounts") Then SaveRecords(si, args) saveDataTaskResult = New XFSqlTableEditorSaveDataTaskResult() saveDataTaskResult.IsOK = True saveDataTaskResult.ShowMessageBox = False saveDataTaskResult.Message = "" saveDataTaskResult.CancelDefaultSave = True End If End Select Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try Return saveDataTaskResult End Function Private Sub SaveRecords(si As SessionInfo, args As DashboardExtenderArgs) Dim dbConn As DbConnInfo = Nothing Try dbConn = BRApi.Database.CreateDbConnInfo(si, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.DbLocation, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.ExternalDBConnName) dbConn.BeginTrans() BRApi.Database.SaveDataTableRows(dbConn, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.TableName, args.SqlTableEditorSaveDataTaskInfo.Columns, args.SqlTableEditorSaveDataTaskInfo.HasPrimaryKeyColumns, args.SqlTableEditorSaveDataTaskInfo.EditedDataRows, True, False, True) dbConn.CommitTrans() 'prevent the UI from trying to save what we already saved, while allowing the ui to refresh to show the changes by 'leaving cancelDefaultSave as false args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Insert) args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Update) Catch ex As Exception dbConn.RollbackTrans() Throw New XFException($"{Environment.NewLine}{Me.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}", ex) Finally dbConn.Close() End Try End Sub End ClassUsing Variance Explanations and UD8 (Dynamic calc periods) together
Originally posted by Felipe Alcorta Hello: We'd like to incorporate variance explanations into our cube views. If the cube view shows one periodic comparison (e.g. Jun YTD CY vs June YTD PY), we have a corresponding dynamic calc that calculates the variance and we can use the (out of the box) variance explanation from the View dimension in order to have comment/explanation functionality. However, if we have multiple comparison period in the same column template/cube view, we are unable to. If we take the same approach as one periodic comparison, the same comment would appear in both comparison columns since there's nothing to distinguish which periodic comparisons are being explained against. We tried using a placeholder UD8 elements (no formulas) in addition to the variance explanation from View dimension but OneStream says it's an invalid cell. Advice?
Cube Properties Best Practices!
Given a situation, determine the correct properties for the Cube. Certification Exam. Workflow The most common setting Is Top Level Cube for Workflow (Main Cube) = True Project requirements dictate Suffixes for varying Workflow by Scenario Type Assigning Suffix by Scenario Type allows the same Entity to be assigned to different Workflows in different Workflow Cube Root by Scenario Type The best practice is to configure a suffix early on for common Scenario Types regardless of whether they get used. NOTE: These settings cannot be updated once data is loaded to a specific Scenario Type. Leaving blank is defining the suffix as well. Calculation Consolidation and Translation Algorithm Type Most common setting = Standard Settings based on design decisions on how consolidation and translation to be customized. Recommendation – Only change from Standard to Custom on sporadic cases. Elimination and Translation logic should be done either on the Member using O#Elimination or C#Elimination or using Translate as part of the Finance Business Rule and assigning it to Cube. Use Standard Using Business Rules for FX Rates for Translation Algorithm Type in the above case. Calculation Properties: Business Rules Cube Properties: FX Rates Cube Properties: Set Default Currency FX Rates for the Cube. The most common settings for the Cube are shown in the screenshot. Default Currency is dependent on the currency of the application. NOTE: Triangulation is dependent on default currency Best Practice – Control the FX Rates by Scenario. Deep dive of FX Rates discussed during setting Scenario settings. For more - OneStream Certified Professional (OCP) – Lead Architect Certification Prep Webinar in Navigator.
BRApi.Finance.Metadata.GetMembersUsingFilter returns null in Cube View Extender
OS Version 7.4.1. GolfStream v44 When calling BRApi.Finance.Metadata.GetMembersUsingFilter in a Cube View Extender rule I'm getting a null return. e.g.: List<MemberInfo> members = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "Account", "a#16999.children", false); I'm following the method signature: Is there a better/alternate way to get the output of a memberFilter in a CV Extender Rule?
