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

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

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

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

Might help if I provided the link eh! http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/7dc94cbe-6673-4586-9817-b119d55d1472Rgds Geoff

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 :-)

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 groupsRgds Geoff