Aggregate Scope Using Another Dataset generate NULL or Zero Value

# Aggregate Scope Using Another Dataset generate NULL or Zero Value

• Sunday, January 20, 2013 9:21 AM

Hi, Before I ask my question in SSRS, I would like to introduce my problem background first.

Problem Background:

I want to show the Aggregate(not default Sum function) of RecoveryRate(attribute in "Order" dataset) value in two different periods: 2011.1-2011.6; 2012.1-2012.12

The "Order" dataset like this:

Year     Month  RecoveryRate

2011    1          0.327

2011    2          0.312

........    ...         .........

2012    1          0.372

2012    2          0.352

........    ...         ..........

2012    12        0.386

My Idea:

I want to aggregate the value of RecoveryRate in both 2011 and 2012 year, but not in all months(just from Jan. to Jun. in 2011 and Jan. to Dec. in 2012)

So I use two datasets instead, Order_2011_Jan_Jun, Order_2012_Jan_Dec. Then use these two datasets as Scope in Aggregate function.

My Problem:

When I use this formula in expression: Aggregate(fields!RecoveryRate.Value,"Order_2011_Jan_Jun"), the value appears to be 0.00. But if I change the Aggregate function to Sum, the value will add RecoverRate's value from Jan to Jun together. I don't know why. The reason of this question or another solution to this problem will be highly helpful to me. Thanks

### All Replies

• Sunday, January 20, 2013 10:43 AM

create table #t (y int, m int, val real)

insert into #t
values (2011,1,10),(2011,2,20),(2011,3,4),(2011,4,50),
(2012,1,10),(2012,2,20),(2012,3,40),(2012,4,55)

select y,sum(case when y=2011 and m between 1 and 3 then val end)
[2011_yan_march],
sum(case when y=2012 and m between 2 and 3 then val end)
[2011_feb_march]
from #t
group by y

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

• Monday, January 21, 2013 5:13 AM
Moderator

Hello,

The Aggregate() function returns a aggregated values that are calculated in the data processing extension and returns as part of the dataset. If the data provider does not support this function or data is not available, the function returns nothing. The Sum function processes the contents of detail rows and calculates the result.

For example, when retrieve product profit margin from Analysis Services data source, the MDX query returns the correctly aggregated value for each product in the results. If we use SUM() function to summary the total profit margin for all products , it will sum the all rows in the report. In order to get the proper results, we should use Aggregate function to instruct Reporting Services to retrieve the server aggregate instead of performing the calculation.

Sum Function (Reporting Services)

Aggregate Function (Reporting Services)

Aggregate (MDX)

Regards,

Fanny Liu

Fanny Liu
TechNet Community Support