Member Avatar for awmantonio
awmantonio 0 Newbie Poster

Hi! I have a problem in Crystal Reports on VB.NET 2008. I have two GroupHeaderSection, one is grouping per Department and the other is per Section. Under the Section grouping, I have series of Summaries, i.e. EmpA, EmpB, EmpC. These summaries are the Sum of all the workOutputHrs of employees per section (workOutputHrs is from an SQL table, saved per date). The Department & Section are from TableA while the workOutputHr is from TableB. These two tables are linked by the EmployeeID (auto). What I want to do is to get the Maximum values of these summaries and put it in the GroupFooter. I tried this code but I get "This field cannot be summarized":

Maximum (Sum ({TableB.EmpA}, {TableA.Section}))

I hope someone can help. Thanks!

  • 1 Contributor
  • 0 Replies
  • 105 Views

Dani AI

Generated

Crystal will not let you nest aggregate functions (so you cannot take a Maximum of a Sum directly). A reliable pattern is to compute the section-level Sum and then use formula variables to track the maximum as each section prints. The reset formula goes in the Department header, an update formula runs in the Section footer (placed after the section summary), and a display formula goes in the Department footer.

Reset (put in the Department header):

WhilePrintingRecords;
NumberVar MaxEmpA := 0;

Update (put in the Section footer below the Sum summary; suppress this formula field so it doesn't print):

WhilePrintingRecords;
NumberVar MaxEmpA;
NumberVar Curr := Sum({TableB.workOutputHrs}, {TableA.Section});
If Curr > MaxEmpA then MaxEmpA := Curr;
"";

Display (put in the Department footer):

WhilePrintingRecords;
NumberVar MaxEmpA;
MaxEmpA;

For multiple employee summary columns (EmpA, EmpB, EmpC) repeat with separate variables (MaxEmpA, MaxEmpB, etc.), or use arrays if the list is dynamic. If you prefer to remove Crystal-level logic, aggregate in SQL and let the database return per-section sums, then select the MAX per department. Example (SQL Server):

SELECT a.Department,
       MAX(t.SectionSum) AS MaxSectionSum
FROM (
  SELECT a.Department, a.Section, SUM(b.workOutputHrs) AS SectionSum
  FROM TableA a
  JOIN TableB b ON a.EmployeeID = b.EmployeeID
  GROUP BY a.Department, a.Section
) t
GROUP BY a.Department;

Notes: place the update formula in a subsection that comes after the summary so the Sum is available; reset in the correct header; handle NULLs if necessary; use CurrencyVar instead of NumberVar if you need currency precision. This approach avoids nested summaries and gives you the max per department reliably.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.