How to deal with aggregate data contains duplicates?

[damonkimble]

I am working on a report in Crystal 10 running against MS SQL via ODBC. The following scenario simplifies the situation. Any suggestions on how to accomplish this task?

I have three pizza parlors, two diners and three theaters. My employees can work at any or all of them at the same time or over a period of time. I want to create a report that will give me employee information as of a given date. I am grouping by business type and location. I want to summarize information, such as number of employees and average length of service at both levels as well as grand totals for all. It's not a problem at the location level as each employee only exists once. At the business type and grand levels, there can be multiple rows with the same employee, but having different dates of service. This is causing problems when calculating average length of service.

thanks,
damon

 

[IdoMillet]

Group on Employee at the lowest level.

Create Running Totals and set their accumulation frequency to once per each employee group.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[damonkimble]

How do I account for the duplicates when someone works in more than one location and/or business type?

 

[IdoMillet]

Use CrossTabs at the higher levels of grouping.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Just to clarify, in order to get the count of employees in the CrossTab, you would use "Distinct Count" of emp_id...

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[damonkimble]

Thanks. I've never used cross-tabs before, so I'll do some research into them and post any needed follow up questions.

 

[damonkimble]

After a bit of looking, I don't think this will do what I need. First of all, I'm looking to summarize at the end of each group, not all in one place in a spreadsheet.

Also, I'm using custom functions to calculate and then format length of service. This function does not show up as an available field. Even if it did, I don't want to summarize it, I want to use it to use an average start and end date to calculate the average length of service.

I also still don't see how I will get distinct data using running totals. I can get a distinct count of emp_id, but a distinct count on start_date will elimiate records for those who started on the same day. A distinct count on end_date (I use a function to substitute a valid date for NULL) will eliminate records for anyone who is still employeed at the 'last' date of the report.

I am thinking that a subreport with a query to bring back summary data might work, but not sure how to deal with the multiple start/end dates for individual employees...

damon

 

[damonkimble]

This problem has been revisited and resolved. See

http://tek-tips.com/viewthread.cfm?qid=1167857&page=1

for details.

damon