Grand total in report

[BobDe1]

I’ve created a monthly report that shows the total hours worked for each person.
In that report I want a grand total of hours worked
Here is the query that the report is based on
SELECT hours.RosterID, memberpersonalinfo.Last_Name, memberpersonalinfo.First_Name, SUM( hours.Hours ), rosterinfo.Position, hours.Dateworked AS Start_Date, hours.Dateworked AS End_Date FROM security.hours AS hours, security.memberpersonalinfo AS memberpersonalinfo, security.rosterinfo AS rosterinfo WHERE hours.RosterID = memberpersonalinfo.RosterID AND hours.RosterID = rosterinfo.RosterID AND ( hours.Dateworked = :Start_Date OR hours.Dateworked = :End_Date ) GROUP BY hours.RosterID
Any Ideas ??
Thanks
Bob

[RobertG]

You could choose a function for the SUM in Report Builder. See Base Guide - Reports - Data properties of field. Set a user defined function in group footer (or report footer).

[BobDe1]

Thanks, but after several attempts I still can’t get it… It’ll give me the first line only.
I’m trying to total all the hours work between two dates…
What am I missing ??
Thanks
Bob
SUM( hours.Hours )

[RobertG]

It you try an aggregate function like SUM in a query, you have to set an aggregate function for all other fields you will show or you have to add these fields in GROUB BY clause.

SELECT hours.RosterID, memberpersonalinfo.Last_Name, memberpersonalinfo.First_Name, 
SUM( hours.Hours ), rosterinfo.Position, :Start_Date, :End_Date 
FROM security.hours AS hours, security.memberpersonalinfo AS memberpersonalinfo, security.rosterinfo AS rosterinfo 
WHERE hours.RosterID = memberpersonalinfo.RosterID AND hours.RosterID = rosterinfo.RosterID 
AND ( hours.Dateworked BETWEEN :Start_Date AND :End_Date ) 
GROUP BY hours.RosterID, memberpersonalinfo.Last_Name, memberpersonalinfo.First_Name, rosterinfo.Position

Might be this runs (without masking).

[BobDe1]

Thanks…I tried it… No luck… Copied the statement and got a nothing on the query.
I can total the hours of each entry but I try to get a grand total of hours worked for all entries (between the two dates). I get nothing.
Any Ideas ??

Thanks
Bob

[RobertG]

If the field is Integer this should work for the total:

SELECT SUM( "hours"."Hours" )
FROM "security"."hours" AS "hours"
WHERE "hours"."Dateworked" BETWEEN :Start_Date AND :End_Date 

Try also without parameters - direct entry formatted like
‘2023-12-01’

1. What database do you use?
2. What is the field type for “Hours”

[Villeroy]

Base is NOT a database. It is a set of tools to work with databases of many kinds. Any Base related question can be answered with information about the actual database software and involved data types.

This is even more important than the OS and the Office version.