Announcement
ABC
Recent Activity
Monthly Data from D365 into OneStream
Is there an interface from Dynamics 365 into OneStream? I have seen some comments mention direct connection with D365 using oData API. We have several business units across the world that will need to push month end data from D365. Is there a way they can just push the data from their end or would it have to come from OneStream to get the data?
Need help on Clear Specific member data
Hi Team, I need your support regarding an issue I am facing with the dashboard-driven data clearing logic. Requirement: When specific Dimension/Members are selected in the dashboard, the attached code is expected to execute in Preview and Clear modes to remove the corresponding data. Issue Observed: The code runs successfully during execution. I can see the log message indicating that the data is “cleared.” However, the actual data is not getting cleared from the system. This suggests that while the process is triggering correctly, the clearing operation is not impacting the underlying data as expected. Could you please review and provide your suggestions on what might be causing this issue or any potential gaps in the approach? Let me know if you need additional details or logs from my end. Thanks in advance for your help. api.Data.SetDataCell(memberScript, amount, isNoData, isDurableCalculatedData) Imports System Imports System.Collections.Generic Imports System.Data Imports System.Data.Common Imports System.Globalization Imports System.IO Imports System.Linq Imports Microsoft.VisualBasic Imports OneStream.Finance.Database Imports OneStream.Finance.Engine Imports OneStream.Shared.Common Imports OneStream.Shared.Database Imports OneStream.Shared.Engine Imports OneStream.Shared.Wcf Imports OneStream.Stage.Database Imports OneStream.Stage.Engine Namespace OneStream.BusinessRule.Finance.EPM_Clear_Specific_Scenarios Public Class MainClass Public Function Main(si As SessionInfo, globals As BRGlobals, api As FinanceRulesApi, args As FinanceRulesArgs) As Object Try '======================================== ' VALID FUNCTION TYPE '======================================== If api.FunctionType <> FinanceFunctionType.CustomCalculate Then Return Nothing End If '======================================== ' PARAMETERS '======================================== Dim entityName As String = GetParam(args, "Entity") Dim scenarioName As String = GetParam(args, "ParamSScenario") Dim timeName As String = GetParam(args, "Time") Dim con as String="Local" Dim accountName As String = GetParam(args, "Account") Dim flowName As String = GetParam(args, "Flow") Dim originName As String = GetParam(args, "Origin") Dim icName As String = GetParam(args, "IC") Dim ud1 As String = GetParam(args, "UD1") Dim ud2 As String = GetParam(args, "UD2") Dim ud3 As String = GetParam(args, "UD3") Dim ud4 As String = GetParam(args, "UD4") Dim ud5 As String = GetParam(args, "UD5") Dim ud6 As String = GetParam(args, "UD6") Dim ud7 As String = GetParam(args, "UD7") Dim ud8 As String = GetParam(args, "UD8") Dim mode As String = GetParam(args, "Mode", "EXECUTE").ToUpper() Dim confirmDelete As String = GetParam(args, "ConfirmDelete", "NO").ToUpper() api.LogMessage("=== DATABUFFER CLEAR START ===") api.LogMessage("Mode=" & mode & " | ConfirmDelete=" & confirmDelete) '======================================== ' VALIDATION '======================================== If String.IsNullOrWhiteSpace(accountName) Then Throw New Exception("Account parameter is required.") End If '======================================== ' BUILD FILTER '======================================== Dim filter As String = "E#" & entityName & ":S#" & scenarioName & ":T#" & timeName & ":A#" & accountName If Not String.IsNullOrWhiteSpace(flowName) Then filter &= ":F#" & flowName If Not String.IsNullOrWhiteSpace(originName) Then filter &= ":O#" & originName If Not String.IsNullOrWhiteSpace(icName) Then filter &= ":I#" & icName If Not String.IsNullOrWhiteSpace(Con) Then filter &= ":C#" & "Local" If Not String.IsNullOrWhiteSpace(ud1) Then filter &= ":U1#" & ud1 If Not String.IsNullOrWhiteSpace(ud2) Then filter &= ":U2#" & ud2 If Not String.IsNullOrWhiteSpace(ud3) Then filter &= ":U3#" & ud3 If Not String.IsNullOrWhiteSpace(ud4) Then filter &= ":U4#" & ud4 If Not String.IsNullOrWhiteSpace(ud5) Then filter &= ":U5#" & ud5 If Not String.IsNullOrWhiteSpace(ud6) Then filter &= ":U6#" & ud6 If Not String.IsNullOrWhiteSpace(ud7) Then filter &= ":U7#" & ud7 If Not String.IsNullOrWhiteSpace(ud8) Then filter &= ":U8#" & ud8 api.LogMessage("Filter used: " & filter) '======================================== ' GET DATABUFFER '======================================== Dim dataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(filter) If dataBuffer Is Nothing _ OrElse dataBuffer.DataBufferCells Is Nothing _ OrElse dataBuffer.DataBufferCells.Count = 0 Then api.LogMessage("No data found for given filter.") Return Nothing End If Dim totalCells As Integer = dataBuffer.DataBufferCells.Count api.LogMessage("Cells found: " & totalCells) '======================================== ' SAFETY LIMIT '======================================== If totalCells > 500000 Then Throw New Exception("Too many records selected (" & totalCells & "). Reduce filter.") End If '======================================== ' PREVIEW MODE '======================================== If mode = "PREVIEW" OrElse confirmDelete <> "YES" Then Dim previewCount As Integer = 0 For Each kvp As KeyValuePair(Of DataBufferCellPk, DataBufferCell) In dataBuffer.DataBufferCells api.LogMessage("PREVIEW: " & kvp.Value.CellAmount) previewCount += 1 Next Dim preview As New StringBuilder() preview.AppendLine("DATA SELECTED FOR CLEAR") preview.AppendLine("====================================") preview.AppendLine("Filter :") preview.AppendLine(filter.ToString()) preview.AppendLine("") preview.AppendLine("Total Cells : " & totalCells.ToString("N0")) 'preview.AppendLine("Total Amount: " & Kvp.value.cellamount.ToString("F2")) api.LogMessage("Total PREVIEW cells: " & previewCount) BRApi.Dashboards.Parameters.SetLiteralParameterValue( si, True, "ETPreviewData", preview.ToString()) Return Nothing End If '======================================== ' CLEAR MODE '======================================== Dim clearedCount As Integer = 0 Dim skippedCount As Integer = 0 For Each kvp As KeyValuePair(Of DataBufferCellPk, DataBufferCell) In dataBuffer.DataBufferCells Try Dim cellPk As DataBufferCellPk = kvp.Key Dim memberScriptBuilder As New MemberScriptBuilder() api.Data.ApplyDataBufferCellPkToMemberScriptBuilder(memberScriptBuilder, cellPk) Dim writeScript As String = memberScriptBuilder.GetMemberScript.ToString api.LogMessage("WriteScript" & writescript.ToString()) api.Data.SetDataCell(writeScript, 0D, True,True) clearedCount += 1 Catch exInner As Exception skippedCount += 1 api.LogMessage("SKIPPED: " & kvp.Value.CellAmount.ToString("F2") & " | Error: " & exInner.Message) End Try Next '======================================== ' SUMMARY '======================================== api.LogMessage("=== CLEAR SUMMARY ===") api.LogMessage("Script :" & filter ) api.LogMessage("Total Cells : " & totalCells) api.LogMessage("Cleared : " & clearedCount) api.LogMessage("Skipped : " & skippedCount) api.LogMessage("=== DATABUFFER CLEAR END ===") Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, ex) End Try End Function '======================================== ' PARAM HELPER '======================================== Private Function GetParam(args As FinanceRulesArgs, name As String, Optional def As String = "") As String Try Dim v As String = args.CustomCalculateArgs.NameValuePairs.XFGetValue(name) If String.IsNullOrWhiteSpace(v) Then Return def Return v.Trim() Catch Return def End Try End Function End Class End Namespace
LIM: Example of Register Field Population from a Table View
DISCLAIMER: It should be noted that the focus of this technical guide is to provide general information, considerations, and guidelines for an identified topic. It is NOT to be interpreted as the ONLY approach nor a guarantee that there will not be any issues encountered by using this approach as a customer’s requirements or application configuration may render this guidance as not applicable. In addition, statements that “we believe” and similar statements reflect our beliefs and opinions on the relevant subject. These statements are based upon information available to us as of the date of this article, and while we believe such information forms a reasonable basis for such statements, such information may be limited or incomplete, and our statements should not be read to indicate that we have conducted an exhaustive inquiry into, or review of, all potentially available relevant information. OneStream does not warrant as to the accuracy of this guidance, which is provided on an as-is basis. Any forward-looking statements contained herein are based on information available at the time those statements are made and/or good faith beliefs and assumptions as of that time with respect to future events and are subject to risks and uncertainties that could cause actual performance or results to differ materially from those expressed in or suggested by the forward-looking statements. Considering these risks and uncertainties, the forward-looking events and circumstances discussed in this guide may not occur and actual results could differ materially from those anticipated or implied in the forward-looking statements. VERSION: PV900 SV200 PLATFORM: 9.2.0 USE CASE: your customer wants a standard repository to store supplementary information to be used to populate certain Register Fields. In this example, a list of job codes and its descriptions is handled as a Table View. The user is able to make updates to it as a Spreadsheet component within an OneStream dashboard. Every time a user selects a job code or inputs a job code in the Register, the expectation is that the Job Code Description register field is populated with the description that is from this table view. PURPOSE: to provide you with an example that you can leverage as part of your build ADDITIONAL INFORMATION: You have a custom table to be the underlying source Your customer has made it very clear that the user does not want to manually click to add new Job Codes. Rather, the user wants to interact with the table like an Excel. Therefore… You want the dashboard to display like it did for the .csv (Excel-like) but the information entered are captured in the XFC table ASSUMPTIONS AND GENERAL STEPS: The relevant Register Fields have already been created. In my example, I have JobCode and JobCodeDescription register fields. The relevant custom XFC table is created. For my example, the XFC table comprises of two columns, one is JobCode and the second column is JobCodeDesc. Adding a Spreadsheet Business Rule file and its logic to your custom Assembly component A simple dashboard that allows the user to update the table view, which the underlying table is the XFC table. This section covers the creation of a custom Spreadsheet Business Rule file to allow for this table view. NOTE: there is a dependency prior to this Spreadsheet Business Rule file creation, which is the custom XFC table and its logic. If you are looking for example logic that can populate a Register Field based on the XFC table, refer to LIM: Example of Register Field Population from a Custom XFC Table. Spreadsheet Business Rule Type Components GetCustomSubtVarsInUse: used to define the interaction with OneStream Dashboard parameters GetTableView: used to define the source(s) for the Table View SaveTableView: defines the table or cell intersections that should be written to a target database table My sample code: RetrieveJobCodes private TableView RetrieveJobCodes(SessionInfo si, Dictionary<string, string> custSubstVarsAlreadyResolved) { try { var sql = new System.Text.StringBuilder("SELECT * FROM dbo.XFC_JobCodes"); if (custSubstVarsAlreadyResolved.TryGetValue("MyTableViewParameterName", out var param) && !string.IsNullOrWhiteSpace(param)) { sql.AppendLine($"WHERE MyFilterColumn = '{param}'"); } DataTable dt; using (var dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si)) { dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), false); if (dt != null) dt.TableName = "NoData"; } var tableView = new TableView { CanModifyData = true }; // Header row var headerRow = new TableViewRow(); foreach (DataColumn col in dt.Columns) { var column = new TableViewColumn { Name = col.ColumnName, Value = col.ColumnName, IsHeader = true, }; tableView.Columns.Add(column); headerRow.Items.Add(column.Name, column); } tableView.Rows.Add(headerRow); // Actual data rows foreach (DataRow row in dt.Rows) { var tableViewRow = new TableViewRow(); foreach (var tableViewColumn in tableView.Columns) { var column = new TableViewColumn { Name = tableViewColumn.Name, IsHeader = false }; var value = Convert.ToString(row[tableViewColumn.Name]); if (column.Name.Equals("MySensitiveData") && !BRApi.Security.Authorization.IsUserInAdminGroup(si)) { value = "Not Available"; } column.Value = value; tableViewRow.Items.Add(column.Name, column); } tableView.Rows.Add(tableViewRow); } // Add 5 placeholder rows even if they don’t exist in dbo.XFC_JobCodes for (int i = 1; i <= 5; i++) { var placeholderRow = new TableViewRow(); foreach (var tableViewColumn in tableView.Columns) { var column = new TableViewColumn { Name = tableViewColumn.Name, IsHeader = false, Value = $"Placeholder {i} for {tableViewColumn.Name}" }; placeholderRow.Items.Add(column.Name, column); } tableView.Rows.Add(placeholderRow); } return tableView; } catch (Exception ex) { throw ErrorHandler.LogWrite(si, new XFException(si, ex)); } } UpdateJobCodes private bool UpdateJobCodes(SessionInfo si, TableView tableView) { if (tableView == null) return true; using var dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si); foreach (var row in tableView.Rows.Where(r => !r.IsHeader)) { var jobCode = row.Items.ContainsKey("JobCode") ? row.Items["JobCode"] : null; var jobCodeDesc = row.Items.ContainsKey("JobCodeDesc") ? row.Items["JobCodeDesc"] : null; if (jobCode == null) continue; // Only update if at least one of the fields has changed if ((jobCode?.IsDirty() ?? false) || (jobCodeDesc?.IsDirty() ?? false)) { // First check if the JobCode already exists var checkSql = $"SELECT COUNT(*) FROM dbo.XFC_JobCodes WHERE JobCode = '{jobCode.Value}'"; var existsDt = BRApi.Database.ExecuteSql(dbConnApp, checkSql, false); var exists = existsDt.Rows.Count > 0 && Convert.ToInt32(existsDt.Rows[0][0]) > 0; if (exists) { // Update existing record var setClauses = new List<string>(); if (jobCode?.IsDirty() == true) { setClauses.Add($"JobCode = '{jobCode.Value}'"); } if (jobCodeDesc?.IsDirty() == true) { setClauses.Add($"JobCodeDesc = '{jobCodeDesc.Value}'"); } if (setClauses.Count > 0) { var sql = $"UPDATE dbo.XFC_JobCodes SET {string.Join(", ", setClauses)} WHERE JobCode = '{jobCode.Value}'"; BRApi.Database.ExecuteSql(dbConnApp, sql, false); } } else { // Insert new record var sql = $"INSERT INTO dbo.XFC_JobCodes (JobCode, JobCodeDesc) VALUES ('{jobCode.Value}', '{jobCodeDesc?.Value ?? ""}')"; BRApi.Database.ExecuteSql(dbConnApp, sql, false); } } } return true; } Then create the Spreadsheet component and create the link:LIM: Example of Setting up my Customizations Outside of LIM’s Workspace(s) for Future Upgrades
DISCLAIMER: It should be noted that the focus of this technical guide is to provide general information, considerations, and guidelines for an identified topic. It is NOT to be interpreted as the ONLY approach nor a guarantee that there will not be any issues encountered by using this approach as a customer’s requirements or application configuration may render this guidance as not applicable. In addition, statements that “we believe” and similar statements reflect our beliefs and opinions on the relevant subject. These statements are based upon information available to us as of the date of this article, and while we believe such information forms a reasonable basis for such statements, such information may be limited or incomplete, and our statements should not be read to indicate that we have conducted an exhaustive inquiry into, or review of, all potentially available relevant information. OneStream does not warrant as to the accuracy of this guidance, which is provided on an as-is basis. Any forward-looking statements contained herein are based on information available at the time those statements are made and/or good faith beliefs and assumptions as of that time with respect to future events and are subject to risks and uncertainties that could cause actual performance or results to differ materially from those expressed in or suggested by the forward-looking statements. Considering these risks and uncertainties, the forward-looking events and circumstances discussed in this guide may not occur and actual results could differ materially from those anticipated or implied in the forward-looking statements. VERSION: PV900 SV120 PLATFORM: 9.2.0 WHY DOES THIS MATTER? To handle future upgrades! By having customizations sit outside of LIM-related Workspaces, the risk of rework or override is minimized. I’ll cover the initial customizations setup and the step-by-step examples of how upgrades are handled. There are three main parts CustomEventHandler initial setup: download and upload the CustomEventHandler file, then update the Workspace Assembly Services on the CodeOnly (XXX) and Workspace Create a custom workspace and copy the Assembly from your LIM-related workspace to your custom workspace. Update assembly code accordingly. Create the dependency within the LIM-related Workspace CustomEventHandler Initial Setup Go to Files under your Workforce Planning # workspace (or LIM-related workspace). You should see a File with a name along the lines of CustomServiceFactory_XXX.zip. NOTE: XXX represents the prefix/suffix for your LIM-selected solution (e.g., Workforce, Duration, Term) Download the .zip and then Load this via Application > Load/Extract Update the Maintenance Unit > CodeOnly (XXX) “Workspace Assembly Service” to CustomEventHandler_XXX.WsAssemblyFactory. NOTE: XXX represents the prefix/suffix for your LIM-selected solution. In my example, the file is CustomServiceFactory_PLN so notice that my Workspace Assembly Service is updated accordingly. Before After Update the Workspace’s Workspace Assembly Service as well from XXX.WsAssemblyFactory to CustomEventHandler_XXX.WsAssemblyFactory NOTE: XXX represents the prefix/suffix for your LIM-selected solution. In my example, the file is CustomServiceFactory_PLN so notice that my Workspace Assembly Service is updated accordingly. Before After Upon refresh, you will see CustomEventHandler_XXX assembly component under your CodeOnly (XXX) Maintenance Unit. Custom Workspace Setup Create new workspace (e.g. Workforce Planning Custom) Set Is Shareable Workspace = True Give it a Namespace Prefix. THIS IS IMPORTANT. REMEMBER TO DO SO. After creating the new workspace (e.g. Workforce Planning Custom), within that workspace create a DMU (e.g. custom as shown below) Copy the CustomEventHandler assembly from your LIM-related workspace) Paste it into your newly created Workspace. In my example, I am pasting it to my Workforce Planning Custom’s Custom Maintenance Unit Rename the Assembly. This can be done by clicking on the Assembly Properties tab and then clicking on the Rename button. In my example, because I have copied the CustomEventHandler assembly from Workforce Planning 2 into my Workforce Planning Custom, I am going to use PLNB2 as the suffix; remember PLNB2 is Workforce Planning 2’s Namespace Prefix. Click into the Assembly component under your Custom Maintenance Unit. Click on the WsAssemblyFactory file. Update ALL the lines to reference the source Workspace (in my example, I updated these lines to reference that of Workforce Planning 2 [prefix of PLNB2]). Another example (I am in the WsAssemblyFactory file). The 4 highlighted rows are going to correspond to your specific customizations that you will then write into the corresponding Services files. Click into each of the Services files and update to your source Workforce Planning instance. Create the Dependency in your LIM-Related Workspace Go back to your source Workforce Planning instance’s CustomEventHandler_PLN assembly and Add Dependency. Fill in the details. Example below. Import your separate workspace in the WsAssemblyFactory file (Workspace.NAMESPACEPREFIX.ASSEMBLYNAME) Update your WsAssemblyFactor lines to reference your custom Assembly. Walk-though of an Upgrade after Implementing this Custom Workspace Load the latest LIM version into the application Go to specifically Line Item Modeling Installer workspace > click on Uninstall or upgrade existing instance of Planning > select relevant workspace > Upgrade REFRESH application then you will see your dashboard under the Workspace appear. CustomEventHandler will be removed, so you will need to go to Files, under CodeOnly (PLN) and download the CustomServiceFactory_PLN.zip. Upload this zip to the application. Update Maintenance Unit > CodeOnly (PLN) "Workspace Assembly Service" to CustomEventHandler_PLN.WsAssemblyFactory. Update Workspaces > Workforce Planning 2 "Workspace Assembly Service" to CustomEventHandler_PLN.WsAssemblyFactory. In CustomEventHandler_PLN, add Dependency and reference the Assembly from your dedicated custom Workforce-related workspace. Add in the import to the WsAssemblyFactory file in CustomEventHandler_PLN Update the relevant ServiceType(s) Your custom code is good to go!
Modifying SQL Editor component in Dynamic Dashboards not working
Hi there, I'm working on a client using the version 8.5.2 and I'm trying to use the Dynamic Dashboards functionallity to modify a property from a SQL Editor Component. The purpose of this logic is to set a dashboard in "ReadOnly" mode when the parameter is set, and in this case, the read only mode I want to achieve is by setting "ShowDataManipulationButtons = False". The BR logic used is the following: If Component.DashboardComponentType = DashboardComponentType.SqlTableEditor Then Dim sqlDefinition = XFSqlTableEditorDefinition.ReadXmlString(si,Component.XmlData) sqlDefinition.ShowDataManipulationButtons = False Component.XmlData = sqlDefinition.WriteXmlString(si) End If I have checked that after this point the component XmlData has changed, however, despite returning the modified component, the changes as not being displayed in the dashboard. If instead of modifying an SqlEditor component, I change any property on a Button component, the changes are being displayed correctly. Is this a known issue for the version 8.5.2 or is there any specific thing I might be missing for the SqlEditor component? PD: I know I can achieve creating a copy of the component, but it doesn't makes sense to do something twice (or ten times) when there is a way to solve it dynamically once for all. Thank you in advance!Course Announcement: Creating Dashboard Smart Links for the Modern Browser Experience (MBE)
1 MIN READ This course is intended for administrators or consultants who want to distribute dashboards to MBE users by generating Smart Link URLs. It introduces Smart Links, reviews the required code, and explores three use case examples. Delivery Types: On-Demand (OD) Duration: 1 Hour Availability: This Course is Free Course Link: Creating Dashboard Smart Links for the MBEAccounting for Fiscal Year Calendars in LIM other than January to December.
DISCLAIMER: It should be noted that the focus of this technical guide is to provide general information, considerations, and guidelines for an identified topic. It is NOT to be interpreted as the ONLY approach nor a guarantee that there will not be any issues encountered by using this approach as a customer’s requirements or application configuration may render this guidance as not applicable.In addition, statements that “we believe” and similar statements reflect our beliefs and opinions on the relevant subject. These statements are based upon information available to us as of the date of this article, and while we believe such information forms a reasonable basis for such statements, such information may be limited or incomplete, and our statements should not be read to indicate that we have conducted an exhaustive inquiry into, or review of, all potentially available relevant information. OneStream does not warrant as to the accuracy of this guidance, which is provided on an as-is basis. Any forward-looking statements contained herein are based on information available at the time those statements are made and/or good faith beliefs and assumptions as of that time with respect to future events and are subject to risks and uncertainties that could cause actual performance or results to differ materially from those expressed in or suggested by the forward-looking statements. Considering these risks and uncertainties, the forward-looking events and circumstances discussed in this guide may not occur and actual results could differ materially from those anticipated or implied in the forward-looking statements. VERSION: PV900 SV200 PLATFORM: 9.X PURPOSE: to provide an example that you can leverage as part of your LIM build. RESOLUTION: First, create the following set of 4 date fields in your register: StartDate = required field and will be the start date that comes in from your source data and visible to users EndDate = required field and will be the start date that comes in from your source data and visible to users StartDateOffset = Used by LIM as the “Start Date” field for calculations but will be hidden from users and populated through custom date offset code. EndDateOffset = Used by LIM as the “End Date” field for calculations but will be hidden from users and populated through custom date offset code There are 3 places in which register data can be added: WsComponentService = File import or Direct Connect WsDynamicGridService = Add rows directly in dynamic grid WsTableViewService = Adding rows in Spreadsheet So, you will need to add custom code to all 3 places to handle populating the date offset fields. Below walks through the code to be added to all 3 places. In the custom workspace you set up for your custom code (e.g. Workforce Planning Custom 1) go into the custom event handler you created (see other OC post for how to set this up: LIM: Example of Setting up my Customizations Outside of LIM’s Workspace(s) for Future Upgrades | One...), e.g. your Workforce Planning Custom 1 > Code Only (LIM) > CustomEventHandler_LIM > WsComponentService.cs file, add the following code: private static void AfterEvent(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardExtenderArgs args, XFSelectionChangedTaskResult taskResult) { // GET FUNCTION NAME FROM COMPONENT SELECTION CHANGED SERVER TASK ARGUMENTS if (args.FunctionName.XFEqualsIgnoreCase("ImportRegisterData")) { ParseDateFields(si, brGlobals, workspace, args, taskResult); } } private static void ParseDateFields(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardExtenderArgs args, XFSelectionChangedTaskResult taskResult) { var sql = new StringBuilder(); sql.Append(@$" UPDATE [limb1].XFW_LIM_StageRegister SET StartDateOffset = DATEADD(MONTH, -6, StartDate), EndDateOffset = DATEADD(MONTH, -6, EndDate); "); BRApi.Database.ExecuteSql(BRApi.Database.CreateApplicationDbConnInfo(si), sql.ToString(), false); } In your Workforce Planning Custom 1 > Code Only (LIM) > CustomEventHandler_LIM > WsDynamicGridService.cs file, add the following code: private static void BeforeEvent(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDynamicGridArgs args) { // GET FUNCTION NAME FROM COMPONENT SELECTION CHANGED SERVER TASK ARGUMENTS if (args.Component.Name.XFEqualsIgnoreCase("dg_Register_Insert_Delete_LIM")) { ParseDateFields(si, args); } } private static void ParseDateFields(SessionInfo si, DashboardDynamicGridArgs args) //Had to add in DashboardDynamicGridArgs args { if (args.SaveDataArgs != null) { foreach (XFEditedDataRow row in args.SaveDataArgs.EditedDataRows) { //if (row.InsertUpdateOrDelete == DbInsUpdateDelType.Update) if (row.InsertUpdateOrDelete != DbInsUpdateDelType.Insert && row.InsertUpdateOrDelete != DbInsUpdateDelType.Update) continue; { bool StartDateParsed = DateTime.TryParse( row.ModifiedDataRow.Items.ContainsKey("Start Date") ? row.ModifiedDataRow.Items["Start Date"]?.ToString() : null, out DateTime StartDate); bool EndDateParsed = DateTime.TryParse( row.ModifiedDataRow.Items.ContainsKey("End Date") ? row.ModifiedDataRow.Items["End Date"]?.ToString() : null, out DateTime EndDate); int StartDayInt = 0; int StartMonthInt = 0; int StartYearInt = 0; int EndDayInt = 0; int EndMonthInt = 0; int EndYearInt = 0; row.ModifiedDataRow.Items["Start Date Offset"] = StartDate.AddMonths(-6); row.ModifiedDataRow.Items["End Date Offset"] = EndDate.AddMonths(-6); // row.ModifiedDataRow.Items["End Date Offset"] = EndDate.ToString("MM-dd-yyyy"); //if parsing succeeded, extract month and year directly from DateTime instead of manually trying to convert if (StartDateParsed) { StartDayInt = StartDate.Day; // day StartMonthInt = StartDate.Month; // month StartYearInt = StartDate.Year; // year row.ModifiedDataRow.Items["Start Day"] = StartDayInt; row.ModifiedDataRow.Items["Start Month"] = StartMonthInt; row.ModifiedDataRow.Items["Start Year"] = StartYearInt; } if (EndDateParsed) { EndDayInt = EndDate.Day; // day EndMonthInt = EndDate.Month; // month EndYearInt = EndDate.Year; // year row.ModifiedDataRow.Items["End Day"] = EndDayInt; row.ModifiedDataRow.Items["End Month"] = EndMonthInt; row.ModifiedDataRow.Items["End Year"] = EndYearInt; } } } } } In your Workforce Planning Custom 1 > Code Only (LIM) > CustomEventHandler_LIM > WsTableViewService.cs file, add the following code: private static void BeforeEvent(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, string tableViewName, TableView tableView) { if (tableViewName == "RegisterEditor") { ParseDateFields(si, brGlobals, workspace, tableView); } } private static void ParseDateFields(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, TableView tableView) { foreach (TableViewRow tableViewRow in tableView.Rows) { var RegisterId = tableViewRow.Items["Register ID"]?.OriginalValue?.ToString(); if (tableViewRow.IsHeader) continue; // Skip header row if (string.IsNullOrWhiteSpace(RegisterId)) continue; // Skip blank or whitespace-only rows bool StartDateParsed = DateTime.TryParse( tableViewRow.Items.ContainsKey("Start Date") ? tableViewRow.Items["Start Date"]?.Value?.ToString() : null, out DateTime StartDate); bool EndDateParsed = DateTime.TryParse( tableViewRow.Items.ContainsKey("End Date") ? tableViewRow.Items["End Date"]?.Value?.ToString() : null, out DateTime EndDate); int StartDayInt = 0; int StartMonthInt = 0; int StartYearInt = 0; int EndDayInt = 0; int EndMonthInt = 0; int EndYearInt = 0; bool StartDateUpdate = tableViewRow.Items["Start Date"].IsDirty(); bool EndDateUpdate = tableViewRow.Items["End Date"].IsDirty(); tableViewRow.Items["Start Date Offset"].Value = StartDate.AddMonths(-6).ToString(); tableViewRow.Items["End Date Offset"].Value = EndDate.AddMonths(-6).ToString(); //if parsing succeeded, extract month and year directly from DateTime instead of manually trying to convert if (StartDateUpdate) tableViewRow.Items["Start Date Offset"].Value = StartDate.AddMonths(-6).ToString(); if (StartDateUpdate && StartDateParsed) { StartDayInt = StartDate.Day; // day StartMonthInt = StartDate.Month; // month StartYearInt = StartDate.Year; // year tableViewRow.Items["Start Day"].Value = StartDayInt.ToString(); tableViewRow.Items["Start Month"].Value = StartMonthInt.ToString(); tableViewRow.Items["Start Year"].Value = StartYearInt.ToString(); } if (EndDateUpdate) tableViewRow.Items["End Date Offset"].Value = EndDate.AddMonths(-6).ToString(); if (EndDateUpdate && EndDateParsed) { EndDayInt = EndDate.Day; // day EndMonthInt = EndDate.Month; // month EndYearInt = EndDate.Year; // year tableViewRow.Items["End Day"].Value = EndDayInt.ToString(); tableViewRow.Items["End Month"].Value = EndMonthInt.ToString(); tableViewRow.Items["End Year"].Value = EndYearInt.ToString(); } } } In your Settings > Security > Register Columns page be sure to restrict the date offset column visibility to just administrators. This is because these fields are used to drive calculations but will confuse users if they are visible. Ensure that any formulas that refer to the Start or End Date fields, do so to the StartDateOffset and EndDateOffset fields since those fields are the ones that drive the calculations behind the scenes. By setting StartDateOffset and EndDateOffset in the register as the true start and end date fields, calculations will already take this into account but if you have used any of those register fields further in formulas, you will need to revisit those to ensure they are referring to the correct date field to account for calendar differences. When you import register data, the following should happen by example: Focusing on employee ID #1, we can see the following: Start Date = 7/1/2025 in MM/dd/yyyy format End Date = 6/1/2026 in MM/dd/yyyy format If your company is a July to June Fiscal calendar then those should appear as follows: Start Date = 7/1/2025 = 2025M1 in LIM End Date = 6/1/2026 = 2025M12 in LIM Without the date offset logic, they will appear incorrectly as follows: Start Date = 7/1/2025 = 2025M7 in LIM End Date = 6/1/2026 = 2026M6 in LIM To correct these dates in calculations, the following will correctly happen: Start Date Offset = 1/1/2025 in MM/dd/yyyy format so this will appear as 2025M1 in LIM calculated data End Date Offset = 12/1/2026 in MM/dd/yyyy format so this will appear as 2025M12 in LIM calculated data When mapping data to the cube the data source should appear as follows: This ensures that the data appears in the correct periods within LIM (2026M1 = July 2026….2026M12 = June 2026) and when loaded to the cube.
