To which integration/function area is this feature request related? ConnectWise Manage
Summary: Dataset
Description: I need a new dataset created.
We have a custom field on agreements for estimated usage (hours) on the agreement and have a widget (gauge) in bright gauge and calculates actual hours vs estimated usage (custom field).
Here is the BG query:
SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,t.First_Name AS first_name
,t.Last_Name AS last_name
,t.Location AS location
,t.BusGroup AS billing_unit
,t.Company_Name AS company
,t.project_name AS project
,t.Agreement AS agreement
,t.work_role AS work_role
,t.work_type AS work_type
,t.Charge_Code AS charge_code
,CAST(t.Time_Start_UTC AS DATETIME) AS start
,CAST(t.Time_END_UTC AS DATETIME) AS [end]
,CAST(t.date_start as date) as date
,t.time_status AS time_status
,t.Hours_Actual AS hours_actual
,CASE
WHEN t.Billable_Flag = 1 AND t.Invoice_Flag = 1 THEN ‘B’
WHEN t.Billable_Flag = 0 AND t.Invoice_Flag = 0 THEN ‘NB’
WHEN t.Billable_Flag = 0 AND t.Invoice_Flag = 1 THEN ‘NC’
END AS billing_status
,COALESCE(CASE WHEN t.Invoice_Flag = 1 THEN t.Billable_Hrs END,0) AS hours_b
,COALESCE(CASE WHEN t.Invoice_Flag = 0 THEN t.NonBillable_Hrs END,0) AS hours_nb
,COALESCE(CASE WHEN t.Invoice_Flag = 1 THEN t.NonBillable_Hrs END,0) AS hours_nc
,CASE
WHEN t.Utilization_Flag = 1 THEN ‘Y’
ELSE ‘N’
END AS utilized
,COALESCE(t.Actual_Utilized_Hrs,0) AS hours_utilized
,COALESCE(CASE WHEN t.Utilization_Flag = 0 THEN t.hours_actual END,0) AS hours_non_utilized
,COALESCE(t.AgrHrsCovered,0) AS hours_agreement
,t.Hourly_Rate AS hourly_rate
,CONVERT(DECIMAL(9,2),t.Billable_Amt) AS billable_amount
,t.InvAdj_Total AS adjustment
,t.AgrAmtCovered AS agreement_amount
,t.Invoice_Number AS invoice_number
,t.SR_Service_RecID AS ticket_number
,COALESCE(sr.Summary,pr.Summary) AS ticket_summary
,COALESCE(sr.board_name,pr.board_name) AS ticket_board
,COALESCE(sr.ServiceType,pr.ServiceType) AS ticket_type
,CAST (t.Billable_Hrs * t.Hourly_Cost_Decimal AS NUMERIC (18, 2)) AS time_cost
,CAST (t.Hourly_Cost AS NUMERIC (18, 2)) AS hourly_cost
,CAST(t.pm_project_recid AS VARCHAR) + ': ’ + t.project_name AS project_number_and_name
,agr.pp_amount as ‘Application_Limit’
,CAST(vagr.“Estimated Usage” AS INT) AS estimated_usage
–Diverged–
,sr.servicesubtype AS ‘Ticket_SubType’
,sr.servicesubtypeitem AS ‘Service_Item’
FROM v_rpt_time AS t
LEFT JOIN v_rpt_service AS sr ON sr.SR_Service_RecID = t.SR_Service_RecID
LEFT JOIN v_rpt_project AS pr ON pr.SR_Service_RecID = t.SR_Service_RecID
LEFT JOIN dbo.AGR_Header AS agr ON agr.AGR_Header_RecID = t.Agr_Header_RecID
LEFT JOIN v_AGR_Header_Custom_Fields AS vagr ON vagr.AGR_Header_RecID = t.Agr_Header_RecID
WHERE DATEADD (Day, -365, CURRENT_TIMESTAMP) <= t.Date_Start
Why: We used this regularly.
Priority: High