Book Viewer / Book Publishing Best Practices
Good afternoon, I'm curious to see how other companies handle this situation. We've recently launched a Dashboard allowing the vast majority of our user base, ~800 users, the ability to interact with and drilldown on their financials. One of the growing pains we're experiencing with this change is that the users have the ability to create a PDF Book for their selected Locations + Children on whatever Cube View they're currently on. So it could be one PDF or a lot (50+ locations). Opening the Book Viewer creates a background server task which on its own doesn't seem to be an issue. However, we have a couple of "triggering" events that cause an influx of our user base to all access the system for financial review and book publishing at the same time. Prelim financials are ready to review, financials are closed, etc. The problem we're experiencing seems to be when we have a lot of users all trying to create books at the same time it appears that their tasks are stepping on each other. So either the process is taking longer than normal or the user is receiving a "Cannot access the file filepathandfilename.pdf because it is being used by another process." And maybe the last error is something the user did but I don't know what that could have been honestly. Just trying to get a feel if any other companies / users experience this kind of influx on Book Viewer all at once and how they handle. Thanks, Brandon
Enforce Constraints on Data Table. Failed to enable constraints. One or more rows contain values.
I am trying to return a SQL query in a Data table inside the business rule. But I am Getting the issue: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. I tried to remove the constraint. Maybe I am doing it incorrectly or it is not possible. Dim sql As New Text.StringBuilder sql.AppendLine("Select COALESCE(S.RegisterID,O.RegisterID, W.RegisterID) RegisterID, ") sql.AppendLine("COALESCE(S.RegisterIDInstance,O.RegisterIDInstance,W.RegisterIDInstance) RegisterIDInstance, ") sql.AppendLine("COALESCE(S.WFProfileName,O.WFProfileName,W.WFProfileName) WFProfileName, ") sql.AppendLine("COALESCE(S.WFScenarioName,O.WFScenarioName,W.WFScenarioName) WFScenarioName, ") '''''''''QUERY Continues as below SQL CODE. Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) Dim ds As New DataSet ds.Tables.Clear() ds.EnforceConstraints = False Dim dt As DataTable = ds.Tables.Add() ' error comes when executing next line dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False) Dim Count As Integer = dt.Constraints.Count BRApi.ErrorLog.LogMessage(si, "Count: here2") Return dt End Using The SQL query generates a table in a Data Adapter dashboard component. But the rule fails as the Data table does not enforce constraints through data coming from SQL. The SQL query: Select COALESCE(S.RegisterID,O.RegisterID, W.RegisterID) RegisterID, COALESCE(S.RegisterIDInstance,O.RegisterIDInstance,W.RegisterIDInstance) RegisterIDInstance, COALESCE(S.WFProfileName,O.WFProfileName,W.WFProfileName) WFProfileName, COALESCE(S.WFScenarioName,O.WFScenarioName,W.WFScenarioName) WFScenarioName, COALESCE(S.WFTimeName,O.WFTimeName,W.WFTimeName) WFTimeName, COALESCE(NULLIF(O.JobTitle,''),W.JobTitle) JobTitle, COALESCE(NULLIF(O.LastName,''), W.LastName) LastName, COALESCE(NULLIF(O.FirstName,''), W.FirstName) FirstName, COALESCE(O.Wage,W.Wage) Wage, COALESCE(O.Bonus,W.Bonus) Bonus, COALESCE(O.FTE,W.FTE) FTE, COALESCE(O.GradeLevel,W.GradeLevel) GradeLevel, COALESCE(S.Status,O.Status,W.Status) Status, COALESCE(O.HireDate ,W.HireDate ) HireDate, COALESCE(O.HirePeriod,W.HirePeriod) HirePeriod, COALESCE(O.TermDate,W.TermDate) TermDate, COALESCE(O.TermPeriod,W.TermPeriod) TermPeriod, COALESCE(O.BirthDate,W.BirthDate) BirthDate, DATEDIFF(month, '1/1/2024', IIF(CONVERT(varchar,COALESCE(NULLIF(O.TermDate,'1/1/1900 12:00:00 AM'), W.TermDate),101) = '1/1/1900','1/1/3999',CONVERT(varchar,COALESCE(NULLIF(O.TermDate,'1/1/1900 12:00:00 AM'), W.TermDate),101))) +1 As OutPeriod, COALESCE(S.OutCode,O.OutCode,W.OutCode) OutCode, DATEDIFF(month, '1/1/2024',IIF(CONVERT(varchar,COALESCE(NULLIF(O.DCode1,'1/1/1900 12:00:00 AM'), W.DCode1),101) = '1/1/1900','1/1/3999',CONVERT(varchar,COALESCE(NULLIF(O.DCode1,'1/1/1900 12:00:00 AM'), W.DCode1),101))) +1 As InPeriod, COALESCE(NULLIF(NULLIF(S.Entity,'None'),''),NULLIF(NULLIF(O.Entity,'None'),''),W.Entity) Entity, COALESCE(NULLIF(NULLIF(S.Code1,'None'),''),NULLIF(NULLIF(O.Code1,'None'),''), W.Code1) Code1, COALESCE(NULLIF(O.Code2,'None'), W.Code2) Code2, COALESCE(NULLIF(O.Code3,''), W.Code3) Code3, COALESCE(NULLIF(O.Code4,''), W.Code4) Code4, COALESCE(NULLIF(O.Code5,''), W.Code5) Code5, COALESCE(NULLIF(O.Code6,''), W.Code6) Code6, COALESCE(NULLIF(O.Code7,''), W.Code6) Code7, COALESCE(NULLIF(O.Code6,''), W.Code6) Code8, COALESCE(NULLIF(O.Code6,''), W.Code6) Code9, COALESCE(NULLIF(O.Code6,''), W.Code6) Code10, COALESCE(O.NCode1, W.NCode1) NCode1, COALESCE(O.NCode2, W.NCode2) NCode2, COALESCE(O.NCode3, W.NCode3) NCode3, COALESCE(O.NCode4, W.NCode4) NCode4, COALESCE(O.NCode5, W.NCode5) NCode5, COALESCE(O.NCode6, W.NCode6) NCode6, COALESCE(O.NCode7, W.NCode7) NCode7, COALESCE(O.NCode8, W.NCode8) NCode8, COALESCE(O.DCode1, W.DCode1) DCode1, COALESCE(O.DCode2, W.DCode2) DCode2, COALESCE(O.DCode3, W.DCode3) DCode3, COALESCE(O.DCode4, W.DCode4) DCode4, COALESCE(NULLIF(O.Annot1,''),W.Annot1) Annot1, IIF(COALESCE(S.Status,O.Status,W.Status) = 'Workday','Workday', 'People Register') As Annot2, XFW_PLP_CalcPlanDetail.CalcPlanDetailID As DetailID, XFW_PLP_CalcPlanDetail.FKClassID, XFW_PLP_CalcPlanDetail.Description, XFW_PLP_CalcPlanDetail.WeightOrCount, XFW_PLP_CalcPlanDetail.PeriodDivisor, XFW_PLP_CalcPlanDetail.PeriodFilter, XFW_PLP_CalcPlanDetail.Condition, XFW_PLP_CalcPlanDetail.EntityOverride, XFW_PLP_CalcPlanDetail.FlowOverride, XFW_PLP_CalcPlanDetail.ICOverride, XFW_PLP_CalcPlanDetail.UD1Override, XFW_PLP_CalcPlanDetail.UD2Override, XFW_PLP_CalcPlanDetail.UD3Override, XFW_PLP_CalcPlanDetail.UD4Override, XFW_PLP_CalcPlanDetail.UD5Override, XFW_PLP_CalcPlanDetail.UD6Override, XFW_PLP_CalcPlanDetail.UD7Override, XFW_PLP_CalcPlanDetail.UD8Override, XFW_PLP_CalcPlanDetail.Sequence From (Select * from XFW_PLP_Register where status='Workday' and WFProfileName = 'Workforce UK.01 Employee Register' And WFScenarioName = 'BudV1' And WFTimeName = '2024' ) W FULL OUTER JOIN (Select * from XFW_PLP_Register where Status = 'OneStream' and WFProfileName = 'Workforce UK.01 Employee Register' And WFScenarioName = 'BudV1' And WFTimeName = '2024') O On O.RegisterID = W.RegisterID LEFT OUTER JOIN (Select * from XFW_PLP_Register where Status = 'Split' and WFProfileName = 'Workforce UK.01 Employee Register' And WFScenarioName = 'BudV1' And WFTimeName = '2024') S On S.RegisterID = W.RegisterID CROSS Join XFW_PLP_CalcPlanDetail Where ( XFW_PLP_CalcPlanDetail.FKCalcPlanID = '01_ACTIVE_UK' And XFW_PLP_CalcPlanDetail.FKWFProfileName = 'PlanTemplate' And XFW_PLP_CalcPlanDetail.FKWFScenarioName = 'PlanTemplate' And XFW_PLP_CalcPlanDetail.FKWFTimeName = 'PlanTemplate' ) Order By RegisterID,RegisterIDInstance,XFW_PLP_CalcPlanDetail.CalcPlanDetailID, XFW_PLP_CalcPlanDetail.Sequence
Workflow Status Details including user names
I'm looking to add user names to the workflow status report we've already created but I can't seem to find where in the database tables that info is tracked. We've manipulated the WorkflowStatus method query to pull in the WF steps and the status of each. For example: I reached out to OS support and they pointed me to these two posts, one of which mentions CertifySignOffInstances. Workflow Confirm and Certify Users Info | OneStream Community Displaying user names on the workflow status report | OneStream Community I can also see WorkflowLockHistory as a table but any thoughts on which other tables store workflow status by user info? Appreciate any direction you're able to provide! Thanks!Excel Extract from SQL Table Editor
Hi Team, Is there any way to extract the SQL Table Editor to an Excel File? Currently I have a SQL table editor with multiple pages and the default extract method by right clicking on the column/rows is just extracting one page. Is there a way to extract the full table into an excel file? Thanks.Losing Dynamic Parameters on Dashboard Refresh
Hello, I would be very grateful if someone could offer some direction on fixing the issue of dynamically generated parameters disappearing on a refresh action. When the dashboard initially loads, all of the dynamic parameters render as expect. I have an extender service setting the count of parameters to 2 for each param type (Debits,Credits). These work off two stored member list parameters, par_DebitList and par_CreditList. When the dashboard is refreshed, I lose the _dynamic_2 even though the parameter count is still 2. Public Function GetDynamicParametersForDynamicComponent( _ ByVal si As SessionInfo, ByVal api As IWsasDynamicDashboardsApiV800, ByVal workspace As DashboardWorkspace, ByVal maintUnit As DashboardMaintUnit, ByVal dynamicComponentEx As WsDynamicComponentEx, ByVal customSubstVarsAlreadyResolved As Dictionary(Of String, String)) _ As WsDynamicParameterCollection Implements IWsasDynamicDashboardsV800.GetDynamicParametersForDynamicComponent Try If api Is Nothing Then Return Nothing Dim CompName As String = dynamicComponentEx.DynamicComponent.Component.Name If CompName.Contains("CBX_Credits") Or CompName.Contains("CBX_Debits") Then Dim params As WsDynamicParameterCollection = api.GetDynamicParametersForDynamicComponent( _ si,workspace,dynamicComponentEx,String.empty,Nothing,tristatebool.TrueValue,WsDynamicItemStateType.MinimalWithTemplateParameters) Dim paramname As String = Nothing If CompName.Contains("CBX_Credits") Then paramname = "par_CreditList" Else paramname = "par_DebitList" End If Dim ParamComp As WsDynamicCompParamMemberEx = params.GetParameterUsingBasedOnName(paramname) Return params Else Return api.GetDynamicParametersForDynamicComponent(si, workspace, dynamicComponentEx, String.Empty, Nothing, TriStateBool.TrueValue, WsDynamicItemStateType.MinimalWithTemplateParameters) End If Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function This is my setting on the combo box: When refreshing the dashboard, all the components render, but just the parameters are lost. When loading the dashboard, GetDynamicParametersForDynamicComponent executes 8 times, while on a refresh it is only executing 6 times. Would appreciate any guidance on this. Thanks!
Actuals Adjustment
How to Add Adjustments with Add Back Category and Free-Form Add Back Name to Actuals in OneStream? Hi Everyone, I’m working with Actuals data structured like the attached screenshot and want to add adjustments linked to this data. Key points: Adjustments must tie to Actuals by Company, Account, and Month Each adjustment includes: An Add Back Category An Add Back Name, which is free-form text and can vary monthly I want to maintain monthly detail and enable reporting and drill-down by both Add Back Category and Add Back Name My questions: What’s the best approach to model and load adjustments with both a Add Back Category and a free-form Add Back Name in OneStream? Should Add Back Name be a UD member with auto-create, a text attribute, or something else? How to avoid member explosion or reporting complexity given the free-form nature of Add Back Name? How can I ensure adjustments roll up correctly and maintain data integrity? Any recommendations for validation, governance, and reporting for this kind of adjustment detail? Thanks in advance for your insights!Pass-thru Formatting on Cube Views
Good afternoon, I'm trying to pass through formatting from one cube view to another. The 2nd cube view is a dialog pop-up in a dashboard triggered by a selection changed event. So far I've been able to determine destination formatting based on Cell POV dimension members of the cell "clicked" but I'm hitting a snag on dynamically calculated cells (column is Actual versus Budget % for example). The Cell POV dimension members are the same with the only difference being a "GetDataCell" at the end. I haven't figured out a way to retrieve what, if anything, is in the "calculation script" for a cell intersection. Or if there's a way to retrieve the row and column names for a cell intersection at the time of a click? Looking for any guidance possible. Thanks, BrandonExecuting QuickViews using BRs
Hi everyone, Does anyone know if there's a specific method to be able to run Quickviews using a Business Rule? Trying to understand if OS is capable of doing something like that. Presumably, what I would like is to execute a dashboard that would allow me to see one, just seeking for some ideas on this topic. Thank you!Task Manager Dashboard Configuration
I have to create a task manager build that does not include weekends in the duration. We have tried adding the weekends to holiday profiles and excluding them from the explicit day map, but the duration will always include the weekends. Right now, I have due dates on weekends an holidays even when calling them out in the Holiday Profile and setting each one with an offset of 1. Is there a work around for this or any other solution to have the durations skip weekends/ due dates not be due on weekends or holidays?
