How do I total a sub group like in Crystal

I'm converting some crystal reports and can't seem to find a function in SRS
that does totals by group. Here is the crystal function:

Sum ({rdo.TimeWorked},{rdo.JobID} )

It takes the sum of time worked for each job id. I tried using RunningValue
with a scope but that didn't work because I'm not grouping by Job ID in my
report.

If it helps here is what I'm trying to do.

Snapshot of data:

Weekday JobID Estimate Name TimeWorked
Monday 452536 90 HARDWI 16
Monday 458924 240 VONIER 65
Monday 458946 90 THOMSO 92
Monday 458956 90 NASH 53
Monday 458958 120 ROLLS 91
Monday 458958 120 ROLLS 48
Monday 458964 90 SANDS 78

I'm grouping by weekday. I want to get the amount of time worked over the
Estimate. I don't care about the time worked UNDER the Estimate. So the
formula in crystal reads:

--
numberVar TimeExceeded := Sum({rdo.TimeWorked},{rdo.JobID}) - {rdo.Estimate}
;

if TimeExceeded > 0 then
ToText(TimeExceeded)
else
"00:00"
--

So the data with result looks like this:

Weekday JobID Estimate Name TimeWorked Result
Monday 452536 90 HARDWI 16 0
Monday 458924 240 VONIER 65 0
Monday 458946 90 THOMSO 92 2
Monday 458956 90 NASH 53 0
Monday 458958 120 ROLLS 139 19
Monday 458964 90 SANDS 78 0

Total = 21

Notice the formula grouped the ROLLS row.

So my final result in the report looks like this:

Weekday Total TimeWorked Exceeded Estimate
Monday 443 21

Answer this question

How do I total a sub group like in Crystal

Wade Heisen
Since you have duplicated data in your rowset which needs to be removed before aggregating, this is a classic problem requiring aggregates of aggregates, which is not yet supported in SQL 2005. For future reference, you'd want something like this for the total estimate: =Sum(First(Fields!Estimate.Value,"JobGroup"))

Until we have a chance to implement this feature, you'll need to modify your query to remove the duplicates.
Instead of this:
Job      Estimate      Worked
1         100            50
1         100            60
2         50              20
2         50              10
2         50              30

Alter your query to return this:
Job      Estimate      Worked
1         100            50
1         NULL           60
2         50              20
2         NULL           10
2         NULL           30

Then you'll be able to sum the estimates directly.
Dan Heidel
This is exactly the same situation I am experiencing....

When will aggregates of aggregates be available

Just a note that not all report writers also have the authorization to alter stored procedures etc. And without the ability to also group seperate datasets with related fields being supported as well, my tool box just became a bit smaller, So 'just change the query' is not as simple as it might appear :)
lemec

Thanks for your response,

I am grouping by weekday but the problem is that I have a many to one relationship in my results and one of the fields that is calculated is in "one" side. Here is what I mean.

If you add up the Estimate times you get 840, but it should be 720. The estimate for the ROLLS job (jobID 458958) is 120 but because there are 2 occurrences of time worked it appears twice (because 2 people worked on it). How do I account for this

Any help is appreciated,

Patrick
fishfree

All aggregates take an optional scope parameter. However, it looks like weekday is you lowest level of grouping so the scope should be optional. You should be able to simply group by weekday and use:

=Sum(IIf(Fields!TimeWorked.Value>Fields!Estimate.Value,0,Fields!TimeWorked.Value-Fields!Estimate.Value))
Gaston

You can use the SQL 2005 ROW_NUMBER() aggreagate function as follows:

SELECT Job
, Estimate = CASE ROW_NUMBER() OVER (PARTITION BY Job ORDER BY Job) WHEN 1 THEN Estimate ELSE NULL END
, Worked
from WorkTable

Animesh Misra

Chris Hays wrote:
Since you have duplicated data in your rowset which needs to be removed before aggregating, this is a classic problem requiring aggregates of aggregates, which is not yet supported in SQL 2005. For future reference, you'd want something like this for the total estimate: =Sum(First(Fields!Estimate.Value,"JobGroup"))

Until we have a chance to implement this feature, you'll need to modify your query to remove the duplicates.
Instead of this:
Job Estimate Worked
1 100 50
1 100 60
2 50 20
2 50 10
2 50 30

Alter your query to return this:
Job Estimate Worked
1 100 50
1 NULL 60
2 50 20
2 NULL 10
2 NULL 30

Then you'll be able to sum the estimates directly.

how do you get the query to selectively put in NULL

How do I total a sub group like in Crystal

Answer this question