Literal parameters and multiple users
Hi all, I'm currently developing a dashboard that will likely be used by multiple users at the same time. Users will be able to set some initial parameters within the dashboard, and click on a button that will trigger a BR that will return some output rows within a table in the database. After the process is complete, the user will be able to see the output rows through the following SQL Table Editor: the parameters that you can see in the "Where Clause" are literal parameters that are set from within the BR when it is triggered by the user (using BRApi.Dashboards.Parameters.SetLiteralParameterValue()), and their value depend on the initial parameters that the user selected in the dashboard. The problem is, I saw in some posts within the forum that literal parameters are "common" to all users, so every time one user will click the button and launch the process, the literal parameters will be changed. This is a problem, because we expect users to use the dashboard simultaneously. Is there a workaround that does not use literal parameters? Or is there a way to make literal parameters "unique" to every user? Thanks in advance!
SQL query to retrieve base members of a hierarchy
Hi, All Just wanted to share a SQL query I wrote to retrieve base members and parent of a given hierarchy from within a dashboard grid. (Note that the parameter supplies the member ID of the top member in the chosen hierarchy.) Any thoughts on how to improve are welcome! With TEST AS (SELECT childid, parentID, dimtypeid, 0 AS level FROM relationship WHERE parentid = |!SelectDimension!| UNION ALL SELECT ft.childid, ft.parentID, ft.dimtypeID, level + 1 FROM relationship ft JOIN TEST d ON ft.parentid = d.childid ) SELECT m.name As Child_Name, m.description as Child_Description, P.name AS Parent_Name, Description as Parent_Description FROM TEST d JOIN relationship a ON d.parentid = a.parentid JOIN member AS M ON a.childID = m.memberid JOIN member as P on d.parentid = p.memberid where NOT EXISTS ( SELECT NULL FROM TEST d WHERE d.parentID = m.memberID ) group by m.name, m.description, P.name, p.description
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.SequencePrimary and secondary axis align 0 point
Hi everybody, Below you can see a graph created for a client. A question coming from them is that they want to align the 0 points (highlighted) of both the primary (left) axis and the secondary (right) axis. I've tried (amongst others) to change the visual and whole range, scaling options but it doesn't seem to change anything. Does anybody know if it is possible to align the 0 points? Thanks! Floris
