Report Builder: Custom Aggregate

Hi All,
I have used Report Builder to enable user to do Ad-Hoc Report that has
been great so far, but I have unique requirement for below table/view:
Month Headcount
================== January-06 45
February-06 41
March-06 43
April-06 42
May-06 38
June-06 30
July-06 50
(etc...)
Dec-06 42
Above table represents how many employees, one company/department has
during Month column. In January-06, there is 45 employees,
February-06, there is 44 employees, and so on.
If user choose to show Headcount attribute, Report Builder should
display each month headcount. The current problem is on the aggregate.
for example, report should show Headcount for Quarter 1 as 43, which
is headcount for March-06 (how many employees I have at the end of
Quarter 1), Quarter 2 should display 30, which is headcount for
June-06, and for Year of 2006 should be 42 (at Dec-06). There will be
more complexity: if now is still in the month of November 06 and no
data for December yet, report should show headcount of November 06 as
headcount for Year of 2006.
I don't think default available aggregates which are: Total, Average,
Max, Min, able to do this.
Q1, correct value should be 40 (March-06)
If I use Total Aggregate, it will be 99.
If I use Average, it will be 24.75
If I use Max, it will be 45.
If I use Min, it will be 41.
I need an aggregate that show number from last record in the dataset.
Is there any possible way how to do this? Any input is appreciated.
Thank you.On Jun 19, 4:59 pm, Gunady <Gun...@.gmail.com> wrote:
> Hi All,
> I have used Report Builder to enable user to do Ad-Hoc Report that has
> been great so far, but I have unique requirement for below table/view:
> Month Headcount
> ==================> January-06 45
> February-06 41
> March-06 43
> April-06 42
> May-06 38
> June-06 30
> July-06 50
> (etc...)
> Dec-06 42
> Above table represents how many employees, one company/department has
> during Month column. In January-06, there is 45 employees,
> February-06, there is 44 employees, and so on.
> If user choose to show Headcount attribute, Report Builder should
> display each month headcount. The current problem is on the aggregate.
> for example, report should show Headcount for Quarter 1 as 43, which
> is headcount for March-06 (how many employees I have at the end of
> Quarter 1), Quarter 2 should display 30, which is headcount for
> June-06, and for Year of 2006 should be 42 (at Dec-06). There will be
> more complexity: if now is still in the month of November 06 and no
> data for December yet, report should show headcount of November 06 as
> headcount for Year of 2006.
> I don't think default available aggregates which are: Total, Average,
> Max, Min, able to do this.
> Q1, correct value should be 40 (March-06)
> If I use Total Aggregate, it will be 99.
> If I use Average, it will be 24.75
> If I use Max, it will be 45.
> If I use Min, it will be 41.
> I need an aggregate that show number from last record in the dataset.
> Is there any possible way how to do this? Any input is appreciated.
> Thank you.
Reporting Services actually supports "Last" Aggregate (using regular
RDL file, e.g. developed using Visual Studio), which I think can solve
the problem. I notice that we can use
Count,CountDistinct,StDev,StDevP,Var, or VarP, in the Report Builder
editor itself, but there is no First or Last available. Does anyone
knows how to use this "Last" aggregate in report builder?