Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Aggregate Scope Using Another Dataset generate NULL or Zero Value

Answered Aggregate Scope Using Another Dataset generate NULL or Zero Value

  • Sunday, January 20, 2013 9:21 AM
     
     
    Sign In to Vote
    0

    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
     
     Proposed Answer
    Sign In to Vote
    0
    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
     
     Answered
    Sign In to Vote
    0

    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.

    For more information, please see:

    Sum Function (Reporting Services)

    Aggregate Function (Reporting Services)

    Aggregate (MDX)


    Regards,

    Fanny Liu

    Fanny Liu
    TechNet Community Support