cancel
Showing results for 
Search instead for 
Did you mean: 

separate record

0 Kudos
98

I have a report that currently looks like this:

1 employeeA employeeB 2.21

2 employeeC employeeA 3.32

3 employeeB employeeD 2.05

I want to get a list of employees and sum up the values from the last column, ie:

employeeA 5.53

emloyeeB 4.26

employeeC 3.32

employeeD 2.05

Any suggestions on how to accomplish this? I can't wrap my head around grouping or getting a sum because each record has 2 employees and not sure how to separate them out or even get a subreport to somehow get this to work.

Please be kind and detailed because I admit I am very much new to crystal.

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

Assuming the following tables:

Employee - contains an ID field and the employee name.

Scores - contains two ID fields - one for Employee 1 and the other for Employee 2 - the score, and a score date.

You'll probably have to write a "Command" for this instead of just joining tables, because you'll need to UNION the data. A Command is a SQL Select statement. For more information about working with them in Crystal see https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/.

Your query will look something like this:

Select
  Employee_Name,
  Sum(Score) as total_Score
From
(
  Select
    e.Employee_Name,
    s.Score
  From Employee e
    Inner join Scores s
      On e.Employee_ID = s.Employee1_ID
  Where ... <whatever criteria you need to filter the data on>

  UNION ALL

  Select
    e.Employee_Name,
    s.Score
  From Employee e
    Inner join Scores s
      On e.Employee_ID = s.Employee2_ID
  Where ... <whatever criteria you need to filter the data on>
) as Data
Group By Employee_Name
Order By Employee_Name

If the employee names are in the same table as the scores, you would do something similar without the joins. This query will summarize the scores in the database, where it can be done more efficiently than doing the sum in Crystal. So, you'll have total scores already calculated and you'll just have to display the data in the report.

-Dell

0 Kudos

Hmmm...that seems extremely complicated and also, the example I gave above is a report I already built, the names and even the ids are not in the same tables at all and the filters are already setup using the select expert.

I have been able to create 2 reports (one for each employee field) but I wanted to merge it into one report and somehow still group the employees with the same name together.

DellSC
Active Contributor
0 Kudos

Because of the structure of your data, using a comand is the only way you're going to be able to do this. I tried to think of a way to do this by just joining tables, but it just won't work doing that. If you'll post the SQL that Crystal has created for the report you've been working in along with the formula from the Select Expert, I can re-write the SQL so that it will give you what you're looking for.

-Dell