reporting services - Adding First Values Grouped by ID from record set; report builder 3.0 -

reporting services - Adding First Values Grouped by ID from record set; report builder 3.0 -

i have dataset beingness returned has monthly values different 'goals.' goals has unique id's , month/date values same goals. difference 1 goal doesn't have values same months other goal because might start @ later date, , want 'consolidate' results , sum them based on 'first' startbalance each goal. illustration dataset be;

goalid monthdate startbalance 1 1/1/2014 10 1 2/1/2014 15 1 3/1/2014 22 1 4/1/2014 30 2 4/1/2014 13 2 5/1/2014 29

what want display these consolidated (summed) values in table based on 'first' (earliest month/year) value each goal. result like;

year startbalance 2014 23

this because 'first' value goalid of 1 10 , 'first' value goalid of 2 '13' when seek grouping

year(fields!monthdate.value)

and utilize expression;

sum(first(fields!startbalance.value))

i receive error;

the value look textrun ‘startingvalue3.paragraphs[0].textruns[0]’ uses first, lastly or previous aggregate in outer aggregate. these aggregate functions cannot specified nested aggregates.

does know if grouping incorrect, or if there's different way can 'first' value goalids summed correctly?

you have change

sum(first(fields!startbalance.value))

to

sum(fields!startbalance.value)

reporting-services sum aggregate-functions reportbuilder3.0