Sum over nested groups

[Cnnx]

I have a report where I have a group which has a sum of a sum in the group header (group 1). I now want to add a second group (group2)and display a further sum in the footer of group2. I'm struggling a little to explain this but the following is what I do in Crystal 9. Can someone explain how to replicate this in reporting services? I've got as far as the sum of a sum in group1 but cannot get the sum over group2.I've seen from other posts how to add the sum into the page header but there must be a way to display at group2 level??

Crystal 9
Group 2 Header Organisation contains formula3

whileprintingrecords;numbervar A := 0

Group 1 Header contains formula2

If Sum ({@formula1}, {Group1})>=1 then 1 else 0

Details contain formula1 evaluated for each record

Group 1 Footer contains formula4

whileprintingrecords;numbervar A := A+ {@formula2}

Group 2 Footer contains formula5

whileprintingrecords;numbervar A;

 

[jymm]

I had to do something similar just today... but I made SQL do the work and not RS

I did things a bit bass-ackwards, but it works. I needed a summary of the ordered quantities for the purchase orders

first I modified the SQL source to have another join - like this

Left Join (select PONumber, sum(qty) as OnOrder
from PODatabase..POdet
group by PONumber) as Tot on Tot.PONumber = Hdr.PONumber

so now my source has the total on order on EACH line - so then in the RS Table I popped in the field where I wanted it - RS wanted it to be a SUM for the POs - I changed that to be FIRST - (ex First(Fields!OnOrder.Value))

in other cases I have been able to get away with using =First(Fields!QtyOrdered.Value, "POLine") - which is using the scope - but this time I wanted a summary... and RS does not like that...

that seemed to take care of my issue - that help or hinder?

 

[Cnnx]

Many thanks jymm. Haven't had a chance to try this yet but it looks like a promising approach. I hadn't thought of modifying the SQL source.

 

[jymm]

I came from the Crystal end of things as well - and there are MANY things that I have had to think differently about in RS - but I LOVE it MUCH more than Crystal now... good luck!