Misc

Markdown file related

To display functions in the markdown file use ${showFilteredWorkHours(totalWorkHours, {width})}

To display Observable Inputs tables use ${resize((width) => Inputs.table(pivotTest, {width}))}

Javascript in Markdown conventions

A Test to display JS code (not executed)

function sum(a, b) {
  return a + b;
}

Targets calc

// Employee Performance vs Targets Clearview Dashboard Table Columns
const colList = ['Employee Name', 'Office', 'Mod Utilization', 'TAFW', 'Billable Hrs', 'Billable Hrs Target', '% to Target - Bill Hrs', 'Billable Revenue', 'Billable Revenue Target', '% to Target - Billable Rev', 'Avg Bill Rate', 'Avg Bill Rate Target', '% to Target - Bill Rate']

'Employee Name' = employee table
'Office' = HomeOrg
'Modified Utilization' = Modified Utilization = [Billable Hours Less NB-WO]/([Billable Hours Less NB-WO]+[NonBillable Hours Actual])
// Note below that this method can include billable projects.  Also, this is different that the list I maintain.
'TAFW' = [Indirect_WorkHrs_TAFW] = case when pudf.custUtilizationExclude = 1 then it.WorkHrs else 0 end
'Billable Hrs' = Billable Hours Less NB-WO = Sum(Iif([Project_Charge_Type_Code] IN('B','H'),[Work_Hours],0))
'Billable Hrs Target' = [Scheduled_Hours] = ls.SchedHrs



% Dollars Achieved = Iif([Planning Target Dollars]=0,0,[Billable Revenue Less NB-WO]/[Planning Target Dollars])
% hours achieved = iif([Planning Target Hours]=0,0,[Billable Hours Less NB-WO]/[Planning Target Hours])
% Rate Achieved = iif([Planning Target Hours]=0,0,[Billable Hours Less NB-WO]/[Planning Target Hours])
Achieved Target Dollars = iif([Planning Target Dollars]=0,0,iif([% Dollars Achieved]>1,1,0))
Achieved Target Hrs = iif([% Hours Achieved]>1,1,0)
Available Work Hours = Sum([Work_Hours]-[Indirect_Work_Hours_TAFW]-Iif([Project_Charge_Type_Code]='O',[Work_Hours],0))

Charge Type Split = Iif([Project_Charge_Type_Code]='B', 'Billable', 'Indirect')
Avg Bill Rate = ([Billable Dollars Actual]-Sum([Direct_Labor_Effort_NB_WO]))/([Billable Hours Actual]   -Sum([Direct_Work_Hours_NB_WO]))
Avg Bill Rate Target = ([Billable Dollars Actual]-Sum([Direct_Labor_Effort_NB_WO]))/([Billable Hours Actual]   -Sum([Direct_Work_Hours_NB_WO]))
Billable Dollars Actual = ([Billable Dollars Actual]-Sum([Direct_Labor_Effort_NB_WO]))/([Billable Hours Actual]   -Sum([Direct_Work_Hours_NB_WO]))
Billable Hours Actual = Sum(Iif([Project_Charge_Type_Code] IN('B','H'),[Work_Hours],0))
Billable Hours Less NB-WO = Sum(Iif([Project_Charge_Type_Code] IN('B','H'),[Work_Hours],0))
Billable Revenue Less NB-WO = ([Billable Dollars Actual])-Sum([Direct_Labor_Effort_NB_WO])
Count Exceeded Target = iif(([Billable Hours Less NB-WO])>Sum([Scheduled_Hours]),1,0)
Employee Count = CountDistinct([Employee_Code])

NonBillable Hours Actual = Sum(Iif([Project_Charge_Type_Code]='I',[Work_Hours],0)-[Indirect_Work_Hours_TAFW]+[Direct_Work_Hours_NB_WO])
NonBillable Hours Less TAFW = [NonBillable Hours Actual]-SUM([Indirect_Work_Hours_TAFW])
Percentage Direct = ([Billable Hours Actual]) / Sum([Work_Hours])
// Data from the LaborSchedules table
Planning Target Dollars = Sum(Iif([Project_Path_Level_1]='PJ60015',[Scheduled_Effort] ,0 ))
Planning Target Hours = Sum(Iif([Project_Path_Level_1]='PJ60015',[Scheduled_Hours] ,0 ))

// Query Result Fields related to above calculated fields



,[Direct_Labor_Effort_NB_WO] = Summary.Direct_ExtBillAmountRnd_NB_WO
,[Indirect_WorkHrs_TAFW] = case when pudf.custUtilizationExclude = 1 then it.WorkHrs else 0 end--MD
,[Direct_WorkHrs_NB_WO] = case when pf.ChargeTypeID = 1 AND it.billstatusid in(2,4) then it.WorkHrs else 0 end
,[Labor_Effort] = it.ExtBillAmountRnd
,[Direct_ExtBillAmountRnd_NB_WO] = case when pf.ChargeTypeID = 1 AND it.billstatusid in(2,4) then it.ExtBillAmountRnd else 0 end
,[Employee_Target_Utilization_Percent] = e.TargetPCT
,[Labor_Budget] = prj.LaborBudget
,[Hours_Budget] = prj.HoursBudget
,[Project_Charge_Type_Code] = prj.ChargeTypeCode
,[Project_Charge_Type] = prj.ChargeTypeName
,[Project_Budget_Rate_Type] = Coalesce(prj.BudgetRateType,@EmptyName)
,[Project_Budget_Rate_Type_Code] = Coalesce(prj.BudgetRateTypeCode,'')
,[Amount_Metric]
,FactID = Summary.FactID
,[Work_Date] = Coalesce(Summary.WorkDate,@EmptyName)
,[PM_Comments] = Summary.PMComments
,[Period_Code] = Summary.PeriodCode
,[Work_Hours] = Summary.Work_Hours



,[Labor_Cost] = Summary.Labor_Cost
,[Labor_Pay_Cost] = Summary.Labor_Pay_Cost
,[Labor_Effort] = Summary.Labor_Effort

,[Org_Budget_Amount] = Summary.[Accrual_Budget_Amount]
,[Scheduled_Hours] = Summary.Scheduled_Hours
,[Scheduled_Cost] = Summary.Scheduled_Cost
,[Scheduled_Pay_Cost] = Summary.[Scheduled_Pay_Cost]
,[Scheduled_Job_Cost] = Summary.[Scheduled_Job_Cost]
,[Scheduled_Effort] = Summary.[Scheduled_Effort]
,[Scheduled_Amount] = Summary.[Scheduled_Amount]
,[Metric_Hours] = case when [Amount_Metric] = 'Actual' then Summary.Work_Hours else Summary.Scheduled_Hours end
,[Metric_Cost] = case when [Amount_Metric] = 'Actual' then Summary.Labor_Cost else Summary.Scheduled_Cost end
,[Metric_Effort] = case when [Amount_Metric] = 'Actual' then SUmmary.Labor_Effort else SUmmary.Scheduled_Effort end

TAFW Projects

List use in facs time DuckDB query

FROM
(
    SELECT
     EmpID
    ,WorkDate
	,PMComments
	,PeriodCode
    ,JTID
    ,FactID
    ,Amount_Metric
    ,Work_Hours = SUM(WorkHrs)
	,Indirect_WorkHrs_TAFW = Sum([Indirect_Workhrs_TAFW])
	,Direct_WorkHrs_NB_WO = Sum([Direct_WorkHrs_NB_WO])
    ,Labor_Cost = SUM(Labor_Cost)
    ,Labor_Pay_Cost = SUM(Labor_Pay_Cost)
    ,Labor_Effort = SUM(Labor_Effort)
    ,Direct_ExtBillAmountRnd_NB_WO = SUM(Direct_ExtBillAmountRnd_NB_WO)
	,Accrual_Budget_Amount = MAX(Accrual_Budget_Amount)
    ,[Scheduled_Hours] = Sum(Scheduled_Hours)
    ,[Scheduled_Cost] = SUM(Scheduled_Cost)
    ,[Scheduled_Pay_Cost] = SUM([Scheduled_Pay_Cost])
    ,[Scheduled_Job_Cost] = SUM([Scheduled_Job_Cost])
    ,[Scheduled_Effort] = SUM([Scheduled_Effort])
    ,[Scheduled_Amount] = SUM([Scheduled_Amount])

    FROM
    (
    SELECT
     h.EmpID
	,it.WorkDate
	,it.PMComments
	,glp.periodcode
	,it.WorkHrs
	,[Indirect_WorkHrs_TAFW] = case when pudf.custUtilizationExclude = 1 then it.WorkHrs else 0 end--MD
	,[Direct_WorkHrs_NB_WO] = case when pf.ChargeTypeID = 1 AND it.billstatusid in(2,4) then it.WorkHrs else 0 end
	,it.JTID
	,p.FactID
	,[Labor_Cost] = CASE WHEN @UseJCRate = 1 AND @CanViewJCRate = 1 THEN ExtJCAmountRnd
		                WHEN @UseJCRate = 0 AND @CanViewPayRate = 1 THEN ExtPayAmountRnd
		            ELSE Convert(money,0)
            		END
    ,[Labor_Pay_Cost] = case when @CanViewPayRate = 1 THEN ExtPayAmountRnd else Convert(money,0) end
    ,[Labor_Job_Cost] = case when @CanViewJCRate = 1 and it.IsPremium = 1 then ExtJCAmountRnd else Convert(money,0) end
    ,[Labor_Effort] = it.ExtBillAmountRnd
    ,[Direct_ExtBillAmountRnd_NB_WO] = case when pf.ChargeTypeID = 1 AND it.billstatusid in(2,4) then it.ExtBillAmountRnd else 0 end
	,[Accrual_Budget_Amount] = Convert(money,0)
    ,[Amount_Metric] = 'Actual'
    ,[Scheduled_Hours] = Convert(money,0)
    ,[Scheduled_Cost] = Convert(money,0)
    ,[Scheduled_Pay_Cost] = Convert(money,0)
    ,[Scheduled_Job_Cost] = Convert(money,0)
    ,[Scheduled_Effort] = Convert(money,0)
    ,[Scheduled_AMount] = Convert(money, 0)
    FROM TimesheetItems [it]
    INNER JOIN Timesheets [h] ON it.TimeID = h.TimeID
    INNER JOIN Projects [p] ON it.ProjectID = p.ProjectID
	INNER JOIN Projectfacts [pf] ON p.factid = pf.factid
	LEFT JOIN ProjectUDFS [pudf] ON pf.factid = pudf.factid
	LEFT JOIN GLPeriods [glp] ON it.periodid = glp.periodid
    WHERE it.WorkDate BETWEEN @From_Date AND @Thru_Date
	AND it.homeorgid = IIF(@AllBranchBit = 0, @homeorgid, it.HomeOrgID)
    UNION ALL
    SELECT
     ls.EmpID
    ,ls.WorkDate
	,PMComments=''
	,periodcode = (SELECT periodcode from glperiods glp where
					(ls.workdate BETWEEN glp.startdate AND glp.enddate))
	,[WorkHrs] = Convert(money,0)
	,[Indirect_WorkHrs_TAFW] = Convert(money,0)
	,[Direct_WorkHrs_NB_WO] = Convert(money,0)
    ,la.JTID
    ,p.FactID
    ,Labor_Cost = Convert(money,0)
    ,Labor_Pay_Cost = Convert(money,0)
    ,Labor_Job_Cost = Convert(money,0)
    ,Labor_Effort = Convert(money,0)
    ,Direct_ExtBillAmountRnd_NB_WO = Convert(money,0)
	,[Accrual_Budget_Amount] = Convert(money,0)
    ,[Amount_Metric] = 'Scheduled'
    ,[Scheduled_Hours] = ls.SchedHrs
    ,[Scheduled_Cost] = case when lrt.RateTypeCode = 'P' and @CanViewPayRate = 1 then ls.SchedAmount
                            when lrt.RateTypeCode = 'J' and @CanViewJCRate = 1 then ls.SchedAmount
                            else 0 end
    ,[Scheduled_Pay_Cost] = case when lrt.RateTypeCode = 'P' and @CanViewPayRate = 1 then ls.SchedAmount else Convert(money,0) end
    ,[Scheduled_Job_Cost] = case when lrt.RateTypeCode = 'J' and @CanViewJCRate = 1 then ls.SchedAmount else Convert(money,0) end
    ,[Scheduled_Effort] = case when lrt.RateTypeCode = 'B' then ls.SchedAmount else Convert(money,0) end
    ,[Scheduled_Amount] = ls.SchedAmount
    FROM LaborSchedules [ls]
    INNER JOIN LaborAllocations [la] on ls.AllocLaborID = la.AllocLaborID
	INNER JOIN Employees [e] on ls.empid = e.empid
    INNER JOIN Projects [p] on la.ProjectID = p.ProjectID
    INNER JOIN ProjectFacts [pf] on p.FactID = pf.FactID
    LEFT JOIN LaborRateTypes [lrt] on pf.BgtRateTypeID = lrt.RateTypeID
    WHERE ls.WorkDate BETWEEN @From_Date AND @Thru_Date
	AND e.orgid = CASE WHEN @AllBranchBit = 0 THEN @homeorgid ELSE e.OrgID END
	UNION ALL
SELECT DISTINCT
     EmpID = (Select TOP 1 emp.empid FROM employees emp where gla.orgid = emp.orgid AND emp.isactive = 1 order by emp.orgid ASC)
	,WorkDate = glp.startdate
	,PMComments=''
	,glp.periodcode
	,WorkHrs = Convert(money,0)
	,[Indirect_WorkHrs_TAFW] = Convert(money,0)
	,[Direct_WorkHrs_NB_WO] = Convert(money,0)
	,JTID = 1
	,FactID = 16982
	,[Labor_Cost] = Convert(money,0)
    ,[Labor_Pay_Cost] = Convert(money,0)
    ,[Labor_Job_Cost] = Convert(money,0)
    ,[Labor_Effort] = Convert(money,0)
    ,[Direct_ExtBillAmountRnd_NB_WO] = Convert(money,0)
	,[Accrual_Budget_Amount] = (glb.accrualbudgetamount)
    ,[Amount_Metric] = 'Target'
    ,[Scheduled_Hours] = Convert(money,0)
    ,[Scheduled_Cost] = Convert(money,0)
    ,[Scheduled_Pay_Cost] = Convert(money,0)
    ,[Scheduled_Job_Cost] = Convert(money,0)
    ,[Scheduled_Effort] = Convert(money,0)
    ,[Scheduled_AMount] = Convert(money, 0)
    FROM employees [e]
    LEFT JOIN glaccounts [gla] ON e.orgid = gla.orgid AND gla.baseid IN(204,205,206,207,208,209,210,211)
	LEFT JOIN glbudgets [glb] ON gla.glid = glb.glid
	LEFT JOIN GLPeriods [glp] ON glb.periodid = glp.periodid
    WHERE glp.startdate BETWEEN @From_Date AND @Thru_Date
    ) as [Detail]
    GROUP BY
     EmpID
    ,WorkDate
	,PMComments
	,PeriodCode
    ,JTID
    ,FactID
    ,Amount_Metric
) as [Summary]
LEFT JOIN Employees [e] on Summary.EmpID = e.EmpID
LEFT JOIN Employees [eA] on e.TimeExpApproverEmpID = ea.EmpID
LEFT JOIN JobTitles [jt] on Summary.JTID = jt.JTID
LEFT JOIN #OrgDenormal [oxEmployee] on e.OrgID = oxEmployee.OrgID
INNER JOIN
(
 SELECT p.FactID
    ,ProjectPath
    ,ProjectLongName
    ,pct.ChargeTypeCode
    ,pct.ChargeTypeName
    ,BudgetRateType = lrt.RateTypeName
    ,BudgetRateTypeCode = lrt.RateTypeCode
    ,OrgID = pf.OrgID
    ,p.LaborBudget
    ,p.HoursBudget
 FROM Projects [p]
 INNER JOIN ProjectFacts [pf] on p.FactID = pf.FactID
 INNER JOIN ProjectChargeTypes [pct] on pf.ChargeTypeID = pct.ChargeTypeID
 LEFT JOIN LaborRateTypes [lrt] on pf.BgtRateTypeID = lrt.RateTypeID
 WHERE p.IsBillTermsNode = 1
) as [prj] on Summary.FactID = prj.FactID
LEFT JOIN #OrgDenormal [oxProject] on prj.OrgID = oxProject.OrgID