Adding Sum into Details view for Crystal Report

Question

I am trying to update a Crystal Report with a new column. This report ("Employee Roster") looks at one table (PREH) and puts out some details, the important ones being Employee, Company, and Craft. The user specifies parameters (Company, Craft, specific Employee [optional]) and the report spits out all employees for that craft/date range and a few other details for them.

I want to add another column for their "HoursWorked" in a given craft, the details of which are found in a different table, PRTH. In PRTH there is one line for each "hours added" entry, and there may be hundreds or thousands of these for a given employee. The SQL for this would be something like:

SELECT SUM(Hours) 
  FROM dbo.PRTH 
  WHERE PRTH.Employee=%Employee 
  AND PRTH.Craft=%Craft 
  AND PRTH.Company=%Company 
  AND PRTH.EarnCode NOT IN ('5','6','52','60','100','103')

The main problem I'm finding is that I can't just do a simple join, as that causes a lot of row bloat. Right now the report puts out one line for each employee (grouping by craft) - if I join the table I need, then it makes a LOT of lines for each employee. I want to add just the summary of Hours, based off the current employee that's being looked at in the Details section. I can do what I want in SQL but am not sure how to pass that on in Crystal Reports, ultimately trying to use results of the main report as parameters in this second search.

Any help is greatly appreciated, thank you.

SAMPLE OUTPUT FOR CURRENT REPORT (Parameters=Company 1, Craft=46T)

EE#   SortName    FullName     Co Craft
1553  BOBJONES    Jones, Bob    1 46T
1672  RACHELJONES Jones, Rachel 1 46T
2007  TANYAADAMS  Adams, Tanya  1 46T

In the above output, I'd be trying to add a new Column "TotalHours". For the first line, I'd expect it to run the SQL statement using Bob Jones' EE for "%Employee", his Craft for "%Craft", and his Company for "%Company".

Sample data and desired results would really help. For instance, you mention a join, but your query has only one table. — Gordon Linoff
The join was a possible avenue I was trying to explore, not anything done yet - I will update with some sample data. — KaruiKage

Greg Low Greg Low · Accepted Answer · 2019-12-31T23:43:40

It sounds like what you're trying to achieve is just this:

SELECT [EE#], SortName, FullName, Co, Craft,
       (SELECT SUM(Hours) 
       FROM dbo.PRTH 
       WHERE PRTH.Employee=e.Employee 
       AND PRTH.Craft=e.Craft 
       AND PRTH.Company=e.Company 
       AND PRTH.EarnCode NOT IN ('5','6','52','60','100','103')) AS TotalHours
FROM TableWithEmployees AS e
WHERE PRTH.Employee=%Employee 
AND PRTH.Craft=%Craft 
AND PRTH.Company=%Company;

Is that what you mean?

Adding Sum into Details view for Crystal Report

1 Answers