从记录集中添加按ID分组的第一个值；报表制作工具3.0 [英] Adding First Values Grouped by ID from record set; report builder 3.0

问题描述

我返回了一个数据集，该数据集具有不同目标的月度值。目标具有唯一的ID，并且目标的月/日期值将始终相同。区别是，有时一个目标在另一个月都没有另一个目标的价值，因为它可能会在以后的某个日期开始，而我想合并结果并将其基于第一个 startBalance汇总在一起每个目标。示例数据集将是;

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

这是因为目标ID为1的第一值为10，目标ID为2的第一值为13。

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)

并使用表达式；

Sum(First(Fields!startBalance.Value))

我收到错误；

textrun StartingValue3.Paragraphs [0] .TextRuns [0]的值表达式在外部聚合中使用第一，最后或上一个聚合。这些聚合函数不能指定为嵌套聚合。

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.

有人知道我的分组不正确吗，或者是否有其他方法可以获取 First值目标ID正确汇总在一起？

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?

推荐答案

这是您确切想要的代码：
复制

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

这篇关于从记录集中添加按ID分组的第一个值；报表制作工具3.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！