Adding First Values Grouped by ID from record set;

Question

I have a dataset being returned that has monthly values for different 'Goals.' The goals has unique ID's and the month/date values will always be the same for the goals. The difference is sometimes one goal doesn't have values for all the same months as the other goal because it might start at a later date, and i want to 'consolidate' the results and sum them together based on the 'First' startBalance for each goal. Example dataset would 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 i want to do is display these consolidated (summed) values in a table based on the 'First' (earliest Month/Year) value for each goal. The result would look like;

Year        startBalance
2014        23

This is because the 'First' value for goalID of 1 is 10 and the 'First' value for goalID of 2 is '13' but when I try to group by the

Year(Fields!MonthDate.Value)

and use the expression;

Sum(First(Fields!startBalance.Value))

I receive the error;

The Value expression for the textrun ‘StartingValue3.Paragraphs[0].TextRuns[0]’ uses a First, Last or Previous aggregate in an outer aggregate. These aggregate functions cannot be specified as nested aggregates.

Does anyone know if my grouping is incorrect, or if there's a different way i can get the 'First' value for the goalIDs summed together correctly?

Answer 1

This is Code that you exactly want: Copy

create table #temp
(id int,
monthDate date,
value int)

insert into #temp values(1,'1/1/2014',10)
insert into #temp values(1,'1/2/2014',15)
insert into #temp values(1,'1/3/2014',20)
insert into #temp values(2,'1/4/2014',25)
insert into #temp values(2,'1/5/2014',19)

declare @min int,@max int
select @min=MIN(ID) from #temp
select @max=MAX(ID) from #temp

select * from #temp --This is your main table

select top 0 * into  #res 
from #temp

while(@min<=@max)
begin

    declare @minDT date
    set @minDT=(select MIN(MonthDate) from #temp where id=@min)

    insert into #res
    select *
    from #temp
    where ID=@min
    and Convert(Date,monthDate,103)=Convert(Date,@minDT,103)

    set @min=@min+1
end

select * from #res --This is Result

drop table #res
drop table #temp

Answer 2

You have to change

Sum(First(Fields!startBalance.Value))

to

Sum(Fields!startBalance.Value)