Not sure what to call this, but I can explain my problem pretty straightforeward. I am using Visual Web Developers reporting GUI but the question should remain the same. I have that consists of a month, year, employee, and Hours. Sample data would look like this:
Month, Year, Employee, Hours
1, 2010, Jim, 20
2, 2010, Jim, 50
1, 2010, Jen, 95
3, 2010, Jen, 65
Note: The report is selected to run for an entire year, and people should only have 1 record per month.
The output has to have a row grouping of months Jan - Dec, and the columns need to have things like:
Col1 = Total Monthly Hours for everyone for that month (Sum(Hours))
Col2 = Number of workers for in that month (CountDistinct(Employee)))
Col3 = Average worker hours ?
Col4 = Max worker hours ?
This is all easy, the problem hits at the Sub Total column. The subtotal for the columns 3 and 4 I can't figure out how to do. For the Max, it can just show the Max monthly hours, it has to some how take all the hours for that individual in the year and display the max yearly hours for the year. So instead of showing the max in any given month which would be 95, it has to show the max taken from all the monthly hours of an employee, so in the example it should show Jens total hours as the max of 160.
View Complete Post