Is there anyway to sum an expression in SSRS? I have the total hours an employee worked for a week. The fist field checks to see if its over 40 hours, and assigns 40 if its over 40 other wise is keeps the total. The second field checks and calculates the over time if its over 40.
I then need these totals for each employee to total for the department and then at the end of the report for all employees.
I can not get the expressions to total for the department group and then for the report total.
Can you provide the expression you are trying to sum? What you have so far. Or at least a screenshot
You can either likely moving the calculation into the SQL statement to allow yourself to use the normal Sum function in SSRS.
Or
You could go further down the chain and create a custom code function to add the ReportItem to a running total every detail line.
But id like to see your code first
=Iif(Sum(Fields!LaborHrs.Value) > 40, 40, Sum(Fields!LaborHrs.Value))
=Iif(Sum(Fields!LaborHrs.Value) > 40, Sum(Fields!LaborHrs.Value) - 40, 0)
Easiest most performant solution would be to move this expression to your SQL statement inside your dataset query.
SELECT
(case when Sum(T1.LaborHrs) > 40 then 40 else Sum(T1.LaborHrs) end) as RegularHrs,
(case when Sum(T1.LaborHrs) > 40 then Sum(T1.LaborHrs) - 40 else 0 end) as OvertimeHrs
1 Like
Here is the Query with the statement you suggested.
SELECT Erp.LaborHed.ActualClockinDate, Erp.LaborHed.DspClockInTime, Erp.LaborHed.DspClockOutTime, Erp.LaborHed.EmployeeNum, Erp.LaborHed.LaborHedSeq, Erp.LaborHed.PayHours, Erp.LaborDtl.ClockinTime,
Erp.LaborDtl.ClockOutTime, Erp.LaborDtl.IndirectCode, Erp.LaborDtl.LaborHrs, Erp.EmpBasic.JCDept, (CASE WHEN SUM(Erp.LaborDtl.LaborHrs) > 40 THEN 40 ELSE SUM(Erp.LaborDtl.LaborHrs) END) AS RegularHrs,
(CASE WHEN SUM(Erp.LaborDtl.LaborHrs) > 40 THEN SUM(Erp.LaborDtl.LaborHrs) - 40 ELSE 0 END) AS OvertimeHrs
FROM Erp.LaborHed LEFT OUTER JOIN
Erp.EmpBasic ON Erp.LaborHed.Company = Erp.EmpBasic.Company AND Erp.LaborHed.EmployeeNum = Erp.EmpBasic.EmpID LEFT OUTER JOIN
Erp.LaborDtl ON Erp.LaborHed.PayrollDate = Erp.LaborDtl.PayrollDate AND Erp.LaborHed.LaborHedSeq = Erp.LaborDtl.LaborHedSeq AND Erp.LaborHed.Company = Erp.LaborDtl.Company AND
Erp.LaborHed.EmployeeNum = Erp.LaborDtl.EmployeeNum
WHERE (Erp.LaborHed.ActualClockinDate > @StartDate) AND (Erp.LaborHed.ActualClockinDate < @EndDate)
When I try to save it I get this message.
I still get the âDefine Query Parameterâ message box and then this.
Third times a charm and actually after I tested it? This method uses inline subqueries
SELECT T1.ActualClockinDate, T1.DspClockInTime, T1.DspClockOutTime, T1.EmployeeNum, T1.LaborHedSeq, T1.PayHours, T2.ClockinTime, T2.ClockOutTime, T2.IndirectCode, T2.LaborHrs, T3.JCDept,
(SELECT (case when Sum(S2.LaborHrs) > 40 then 40 else Sum(S2.LaborHrs) end) FROM Erp.LaborHed S1 LEFT OUTER JOIN Erp.LaborDtl S2 ON S1.Company = S2.Company AND S1.LaborHedSeq = S2.LaborHedSeq WHERE S1.ActualClockinDate > @StartDate AND S1.ActualClockinDate < @EndDate AND T1.Company = S2.Company AND T1.EmployeeNum = S2.EmployeeNum) AS RegularHrs,
(SELECT (case when Sum(S2.LaborHrs) > 40 then Sum(S2.LaborHrs) - 40 else 0 end) FROM Erp.LaborHed S1 LEFT OUTER JOIN Erp.LaborDtl S2 ON S1.Company = S2.Company AND S1.LaborHedSeq = S2.LaborHedSeq WHERE S1.ActualClockinDate > @StartDate AND S1.ActualClockinDate < @EndDate AND T1.Company = S2.Company AND T1.EmployeeNum = S2.EmployeeNum) AS OvertimeHrs
FROM Erp.LaborHed T1
LEFT OUTER JOIN Erp.LaborDtl T2
ON T1.PayrollDate = T2.PayrollDate AND T1.LaborHedSeq = T2.LaborHedSeq AND T1.Company = T2.Company AND T1.EmployeeNum = T2.EmployeeNum
LEFT OUTER JOIN Erp.EmpBasic T3
ON T1.Company = T3.Company AND T1.EmployeeNum = T3.EmpID
WHERE (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate)
What data type are your parameters?
Date/Time
Are you able to run the query in SSMS?
Never seen this error. Could it be a visual studio issue?
It does run in from SQL Server Reporting Services but Regular and Overtime total are not correct. Oddly the Total in the right column looks correct.
Maybe it would be easier to me to and create a custom code function to add the ReportItem to a running total every detail line. Can you point me to a site that explains how to do that?
I donât think its your code, I believe its how my report is grouping the IndirectCodes. There are 4 codes for PTO and 4 more for Holidays. All the rest make up the regular and holiday hours. If I understand your code the sub-query doesnât take that into account.
I would reccomend playing around with the SQL method first. This will be the most performant method.
You can add more WHERE criteria into your subqueries to not include Indirect Codes into the sum.
WHERE S2.IndirectCode <> 'Hol' AND S2.IndirectCode <> 'Sic'
Here is a link for custom code documentation. The custom code section uses VB. There are also some other topics floating around here about using custom code
So after working on this I am able to run the query in Visual Studios but my PTO total is pulling from the sub-query as the grand total for the report. Each line is showing 29.5 but that is the over all total.
Here is my query.
SELECT T1.EmployeeNum, (CASE WHEN SUM(T3.LaborHrs) > 40 THEN 40 ELSE SUM(T3.LaborHrs) END) AS RegularHrs, (CASE WHEN SUM(T3.LaborHrs) > 40 THEN SUM(T3.LaborHrs) - 40 ELSE 0 END) AS OvertimeHrs, T2.JCDept,
(SELECT SUM(S3.LaborHrs) AS Expr1
FROM Erp.LaborHed AS S1 LEFT OUTER JOIN
Erp.EmpBasic AS S2 ON S1.Company = S2.Company AND S1.EmployeeNum = S2.EmpID LEFT OUTER JOIN
Erp.LaborDtl AS S3 ON S1.LaborHedSeq = S3.LaborHedSeq AND S1.PayrollDate = S3.PayrollDate AND S1.Company = S3.Company AND S1.EmployeeNum = S3.EmployeeNum
WHERE (S1.ActualClockinDate > @StartDate) AND (S1.ActualClockinDate < @EndDate) AND (S3.IndirectCode = âPTENâ) OR
(S1.ActualClockinDate > @StartDate) AND (S1.ActualClockinDate < @EndDate) AND (S3.IndirectCode = âPTPOâ) OR
(S1.ActualClockinDate > @StartDate) AND (S1.ActualClockinDate < @EndDate) AND (S3.IndirectCode = âPTPDâ) OR
(S1.ActualClockinDate > @StartDate) AND (S1.ActualClockinDate < @EndDate) AND (S3.IndirectCode = âPTSRâ)) AS PTO
FROM Erp.LaborHed AS T1 LEFT OUTER JOIN
Erp.EmpBasic AS T2 ON T1.Company = T2.Company AND T1.EmployeeNum = T2.EmpID LEFT OUTER JOIN
Erp.LaborDtl AS T3 ON T1.LaborHedSeq = T3.LaborHedSeq AND T1.PayrollDate = T3.PayrollDate AND T1.Company = T3.Company AND T1.EmployeeNum = T3.EmployeeNum
WHERE (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate) AND (T3.IndirectCode <> âHOENâ) AND (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate) AND (T3.IndirectCode <> âHOLPâ)
AND (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate) AND (T3.IndirectCode <> âHOSRâ) AND (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate) AND
(T3.IndirectCode <> âHOLOâ) AND (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate) AND (T3.IndirectCode <> âPTENâ) AND (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate)
AND (T3.IndirectCode <> âPTPOâ) AND (T1.ActualClockinDate > @StartDate) AND (T1.ActualClockinDate < @EndDate) AND (T3.IndirectCode <> âPTPDâ) AND (T1.ActualClockinDate > @StartDate) AND
(T1.ActualClockinDate < @EndDate) AND (T3.IndirectCode <> âPTSRâ)
GROUP BY T2.JCDept, T1.EmployeeNum
At no point in your inline subquery did you link it back to the main. You linked the tables inside of the subquery to the tables in there, but not back to the main dataset.
In your subquery where statement add the below. Assuming employees arenât working in multiple companies.
WHERE T1.Company = S1.Company AND T1.EmployeeNum = S1.EmployeeNum
Also side note, your current subquery WHERE clause could be simplified to:
WHERE S1.ActualClockinDate > @StartDate AND S1.ActualClockinDate < @EndDate AND (S3.IndirectCode IN ('PTEN', 'PTPO', 'PTPD', 'PTSR')I tied the IN statement and couldnât get it to work.
Adding either of these statements causes an error when I close the Query Designer.
The fist statement causes this error.
Invalid column name âEmployeeNumâ.
Invalid column name âEmployeeNumâ.
Invalid column name âEmployeeNumâ.
Invalid column name âEmployeeNumâ.
Column âErp.LaborHed.Companyâ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column âErp.LaborHed.Companyâ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column âErp.LaborHed.Companyâ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column âErp.LaborHed.Companyâ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Got the first statement to work.
Deleted and retyped the code and got the IN statement to work. Thank you very much for all your assistance.
1 Like