Need help to calculate a total 1

[techwriterAR]

I have a Crystal 8.5 report that calculates the number of days an employee has worked in a department. The MS-SQL table has fields for employee number, employee status, date in, date out, department. I can use either the formula or the SQL expression listed below and arrive at the correct number of days for each record.

The problem is that some employees have gone back and forth between departments and have more than one set of date in and date out records in a department. The report needs to show a total for all days worked in a particular department for each employee, not separate records by employee. I tried grouping by employee and adding a summary total. However, I do not want the total to display below the employee information. I tried to add Sum to the front of the SQL expression but get an error message and Crystal Reports locks up.

(The error messages are: ODBC error:[Microsoft][ODBC SQL Server Driver][SQL server]Column ‘Dpt_Mbrs.dpt’ is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.” “Error detected by database DLL.”)

I understand what the error message is telling me, I just do not know how to fix it, as I am not using an SQL statement as the basis for the report.

I need to know if there is a way to get a total for all the days worked in a department by employee.

-------------------------------------------------
Either of these will calculate number of days in department for each record:
Formula:
datetimevar start:={DPT_MBRS.DATE_IN};
datetimevar end;
if isnull({DPT_MBRS.DATE_OUT}) then
end:= CurrentDate
else
end:={DPT_MBRS.DATE_OUT};
datediff('d',start,end);

SQL expression:
datediff(d, DPT_MBRS."DATE_IN", ISNULL(DPT_MBRS."DATE_OUT", getdate()))
---------------------------------------------
BTW: the formula listed above came from noxum and the SQL expression was supplied by synapsevampire from a previous posting (May 20, 2003.)

Thanks in advance for any and all help,
gjeffcoat

 

[CrysUser]

Group by department first and then be employee. Hope this solves the issue, if I understood you right.

Good-luck

 

[techwriterAR]

Sorry, I should have mentioned that this report has a parameter for selecting a department. So in a sense the report is "grouping" by department already. I just need a way to total each employee's records for the selected department. I should further explain the situation: One employee has 4 records for days worked in one department (4 separate date in and date out records that span several years). I need to show only the total days for all 4 times in the department.

Thanks,
gjeffcoat

 

[CrysUser]

If it is for a single department then group it by employee and add the subtotal for the datediff column on the employee group footer and supress the detail section.

Good-luck

 

[lbass]

Since this is a simple summary, not a running total,after inserting the sum, you can drag the summary field from the report footer to the report header, and then suppress the detail. If this is not the solution you are looking for, please provide an example of the report layout you are trying to achieve.

-LB

 

[techwriterAR]

Okay, here is more detail on what I am trying to achieve. This is my report layout:

--------------------------------------------------------
Employee Status HireDate Dept.StartDate Days
J. Claypool H 02/26/73 06/24/85 6559
J. Bell H 09/07/79 09/07/79 702
J. Bell H 09/07/79 03/21/83 601
J. Bell H 09/07/79 04/07/86 930
J. Bell H 09/07/79 01/09/89 5264
E. Myers H 02/12/79 02/12/79 8724
-------------------------------------------------------

“Days” is a calculation based on the dept start date through the dept end date or if the employee is still in the dept, the current date. I need one record for each employee, not a record for each dept start date. In other words, I need to know how to get just one total for J. Bell (for 7497 days) not four separate records. I get the start date by using the formula: “Minimum([{DPT_MBRS.DATE_IN}])”. Also I do not need a grand total for the department either.

I can create an SQL select statement in MSSQL 8.0 that gives me a sum for the “days”; however, I do not know how to write the SQL expression inside Crystal to do the same thing or change the formula (listed above in previous post) to give a sum.

I hope this sheds a little more light on the situation. Again, thanks in advance for any help you can give.

gjeffcoat

 

[lbass]

In your initial post, you said you tried grouping by employee and adding a summary total, but that you didn't want this to display below the employee information. Have you tried grouping on employee and inserting a sum on {@days} by right-clicking the field and selecting insert summary? Then you can drag the summary field to the group header for the employee. If you insert a minimum on the startdate field, you can also drag that to the group header, and then go to format section->details->suppress, and you will have only one row displayed per employee. From what you've described, I can't see the need for a formula to calculate the summary.

-LB

 

[techwriterAR]

lbass:
Thank you so much for your help. I grouped by employee and pulled all the fields I wanted in the report into the group header, created a summary total and pulled it into the group header also. My report looks like I want it (one line for each employee with the total number of days in the department). I did not realize I could do this. I have moved group headers around and used them in different ways but I have not set up the group header as the "detail" of a report before. I learned something today and I appreciate your help so much.

gjeffcoat