none
Group filters and aggregate functions

    Question

  • Using SSRS 2008 and SSRS 2008 R2. Found a similar post from a couple years ago but with no satisfactory answer...

    It seems to make sense that aggregate functions would be applied only to data that has not been filtered out by a group filter, e.g. if only a portion of the detail or group rows are being displayed because of a filter on that group level, then displaying an aggregate value for the rows such as a count or sum should only include the data that is included on the report. And the following MSDN documentation seems to state just that:

    http://msdn.microsoft.com/en-us/library/ms159673(SQL.100).aspx

    It states that an aggregate function's scope, when it is a row or column group, "specifies the data after the group expressions and group filters are applied for the parent group and child groups. For the purposes of identifying scope containment, every parent group contains its child groups."

    However, that doesn't seem to be the case. The aggregate function is applied over all of the rows within the grouping expression, including those that have been filtered out, which seems to make the group filter fairly useless, considering that one of the most common reasons for grouping in the first place is to provide aggregate totals, sums, averages, etc. for the child groups and detail rows.

    Can someone answer whether or not this is by design, and if I've misinterpreted the documentation, or hopefully that I've missed something that would make this work?

    Also... though I appreciate any help, please don't take the time to reply with suggestions of filtering the data set or applying conditional "IIF" logic in the aggregate expressions - while these could be workarounds for simple filtering scenarios, both of those options become either impossible or unreasonably complex when dealing with complex group filters and/or multiple group levels.

    Thanks,

    Robb

     

    Wednesday, October 27, 2010 9:33 PM

Answers

All replies

  • This seems to be the bit of that page you need to be aware of in regard to scope and aggregates:

    Data region   Specifies data from the data region after data region filter and sort expressions are applied. Group filters are not used when calculating aggregates for data regions.

    So I guess the question is whether you are filtering on groups or data items - it would seem that a data item filter should work with an aggregate but a group filter will not...


    Rgds Geoff
    • Proposed as answer by Jerry NeeModerator Thursday, October 28, 2010 5:23 AM
    • Unproposed as answer by R Eads Thursday, October 28, 2010 2:40 PM
    Thursday, October 28, 2010 12:30 AM
  • Hi Geoff, thanks for your reply. I verified that a filter on the data region, e.g. a Tablix, is correctly applied for aggregate functions. However, I am definitely specifying a group name as the scope for the aggregate functions, and I'm filtering on expressions that include aggregate values and cannot be specified at the data region or data set level. Here is a simple example that demonstrates the behavior.

    I want to create a report from the Northwind database that displays the top 3 grossing products in each territory for a given year, and allows expansion down to the order details. The SQL query for the report data set is:

    SELECT        prod.ProductID, prod.ProductName, ter.TerritoryID, ter.TerritoryDescription, cust.CompanyName AS SoldTo, det.Quantity, 

                             det.Quantity * det.UnitPrice AS ExtendedPrice

    FROM            [Order Details] AS det INNER JOIN

                             Orders AS ord ON ord.OrderID = det.OrderID INNER JOIN

                             EmployeeTerritories AS empter ON empter.EmployeeID = ord.EmployeeID INNER JOIN

                             Territories AS ter ON ter.TerritoryID = empter.TerritoryID INNER JOIN

                             Products AS prod ON prod.ProductID = det.ProductID INNER JOIN

                             Customers AS cust ON cust.CustomerID = ord.CustomerID

    WHERE        (ord.ShippedDate BETWEEN '1/1/1996' AND '12/31/1996')

    I use the report wizard to create a simple report that groups by TerritoryID then by ProductID, and displays detail rows showing the SoldTo, Quantity, and ExtendedPrice, and I specify that group subtotals should be added. Once the report is created, I rename the row groups to Territory, Product, and Order. I also set the sorting on the Product group to sort by [Sum(ExtendedPrice)] descending, so that the products are listed in order of highest to lowest total sales within each territory. I run the report and it looks as I would expect - grouped by territory, then by product ordered descending by total sales, then showing order details.

    Then I go to the Product group properties and specify a filter. The expression is =Sum(Fields!ExtendedPrice.Value, "Product"), the operator is Top N, and the value is 3. And even though it should be implied, I edit each of the aggregate expressions used to display the Quantity and ExtendedPrice totals for each Product and Territory group, specifying the group names as the scope for each. Now when I run the report, it correctly displays only the top 3 products under each territory, but the aggregate totals are the same as before, calculated for all products rather than just the products allowed by the filter. So it seems to me that this clearly fits within the scope description I cited in the original post referring to row and column groups, and that the group filters of the specified group, as well as those of all child groups, should be applied to the aggregate calculation.

    In fact, if this is behaving as it is intended to, then I don't see the value of allowing group filters at all, because in that case, specifying a filter would be no different than specifying an expression for Visibility. The only effect of the filter is that the excluded groups are not displayed.

    Like I said before, in my real-world projects, which have more complex filters, trying to achieve the expected behavior through conditional logic in the aggregate expressions (especially when there are several group levels), or even trying to apply the filtering within the database query, becomes ridiculously complex. So before I start down any of those paths, I'd like to at least resolve that the behavior I'm seeing is by design, or that it is a bug, or that I'm doing something wrong that I can correct.

    Thanks again,

    Robb

     

    Thursday, October 28, 2010 5:50 PM
  • I think my point still stands - aggregate functions do not take group level filters into account - they aggregate at a specified scope - but filters do not affect scope - and they work on the dataset behind the scenes rather than on what you see on screen

    I think you have 2 options:

    1: Rather than using filters, use parameters so that the data you are aggregating on is the full data behind the report.

    2: Have a look at this link that provides a sample of custom code to do the job (originaly posted by Jim Chen)


    Rgds Geoff
    Thursday, October 28, 2010 10:28 PM
    • Marked as answer by R Eads Friday, October 29, 2010 4:23 PM
    Thursday, October 28, 2010 10:29 PM
  • Thanks Geoff. I'll probably take the route of using some custom code. Like I mentioned before, in the actual case where I'm applying this, the filters are more complex than the example I gave - they're pretty straight forward and a good fit at the group level, but would translate into some very ugly SQL queries. I'm already using parameters to affect the query in other ways, but extending that to encompass the group filters wouldn't be practical in this case.

    I know I'm not going to get the resolution I'd like on this point, but I still believe that these aggregate functions are not behaving in the way that the documentation states. As you've pointed out, the docs clearly do say that "group filters are ignored when calculating aggregates for data regions." But data regions are only one of five different types of scope applicable to aggregate functions that are described in the SSRS documentation page I referenced, and they're not the type of scope that I'm specifying in my reports. I'm specifying a scope that is of the Row and column groups type, for which the documentation states that group filters "are applied for the parent group and child groups." I'm not sure I understand what you mean when you say "filters do not affect scope." A big part of the content of the Understanding Containing Scopes portion of the documentation for aggregate functions is dedicated to explaining the effects of filters on scope. And you're right, the filters do in fact work on the dataset behind the scenes, not simply on the visual display of the data, which in my mind is all the more reason it makes sense that aggregate functions would not include data that has been filtered out of the scope at which the function is applied.

    I do appreciate your time and your help pointing me to ways of solving my problem. I'll soon get over my possibly unfounded gripes and continue along my merry way :-)

     

    Friday, October 29, 2010 4:22 PM
  • These kind of discussions are the lifeblood of forums like these tbh as they tend to provide exactly the kind of info that people go looking for!

    You are applying a filter to a "Group" - the Product Group

    Filters are applied for all child groups down from the parent group - this means that child grups will have rows filtered out as per the parent groups. It does not mean that aggregate functions will use this filter criteria also

    The most pertinant line I c find for this from your link is:

    "The data used for aggregation is from the report dataset after dataset filter expressions are applied."

    So it desn't really matter what you filter on at a group level - the aggregate function will only take into account dataset level filters

    The only way I can adequately explain is to think of MS Excel. When you appy an autofilter to data, the SUM function still sums all rows. you need to appy a seperate SUBTOTAL function to get the filtered portion of that...unfortunately, I don;t know of an equivalent subtotal function in SSRS so you have 3 options:

    1: The UDF I linked to before

    2: Return only the data you want to sum on by using Query Parameters

    3: Filter the Dataset rather than the groups


    Rgds Geoff
    Sunday, October 31, 2010 10:37 PM