Sunday, January 20, 2013 9:21 AM
Hi, Before I ask my question in SSRS, I would like to introduce my problem background first.
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
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.
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
Sunday, January 20, 2013 10:43 AMcreate table #t (y int, m int, val real)
insert into #t
select y,sum(case when y=2011 and m between 1 and 3 then val end)
sum(case when y=2012 and m between 2 and 3 then val end)
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 AMModerator
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.
For more information, please see:
TechNet Community Support