group total based on last() summed as report total?

[Alan0568]

I have a report with a grouping, typically each group has 5 - 6 rows and a value. I want the report to have an overall sum() total based on the last() value of the group.

like ..

order,seq,value
123,1,245
123,2,300
123,3,100
group total = 100

order,seq,value
456,1,246
456,2,400
456,3,900
group total = 900

report total = 1000

I can add the group total using last() but then can use it to have a sum() of it for the whole report. I'm using SSRS 2005 sp2.

Thanks

A

 

[Alan0568]

typo

I can add the group total using last() but then **can't** use it to have a sum()

 

[xlbo]

This might be worth a try:

IIF(Fields!Sequence.Value = Last(Fields!Sequence.Value),Fields!Sequence.Value,0)

Then sum that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Alan0568]

Many thanks for the reply.

I failed to mention that the seq's and values can be the same and the sql behind the group is sorted on other criteria. like ..

123,1,500
123,2,600
123,3,900
123,3,900

I could possibly get around this by adding a row number to my sql using row_number()over etc but having done a quick test based on your IIF suggestion I still can't sum as it complains about the nested last() aggregation when I wrap a sum() around it.