Filtering on totals in Report Builder
Since releasing Report Builder almost two years ago, we've found that many users run into difficulty at some point with filtering on totals. A classic example of this is, "show me a list of customers with more than $5000 in Accessory sales." "Accessory sales" is a typical slice-and-dice filter, because it just eliminates detail or measure values (sales). It means, "include only the sales for products in the Accessory category." "More than $5000", on the other hand, is not a typical slice-and-dice filter. It means, "include only the customers for which the sales total is more than $5000." In other words, the filter does not eliminate detail or measure values (sales), it eliminates groups instances (customers) based on a detail or measure total.
Filtering on totals can be very handy at times. It is possible in an Excel PivotTable using the "Value Filters" context menu on the row or column labels (try it -- it's fun). It eliminates items from the row or column axis based on the value totals at the outermost level. Filtering on totals is also possible at any level in Report Designer by adding group filters, although this is not always recommended, since it may mean requesting much more data in your query than your report really needs to display.
Filtering on totals is also possible in Report Builder, but (a) there is only limited support for it, (b) it takes some training to ensure you build the right filter, and (c) in the case of RB-over-AS, the performance of the resulting queries will probably be unacceptable. Why is filtering on totals so hard right now? Because in this release RB was primarily designed and optimized for relational/operational reporting. It has a number of innovative and powerful features that are especially valuable in this space (and unavailable in typical OLAP reporting, e.g. Excel). The reverse is also true: some features typical in OLAP reporting have only limited or no support in this release of RB. Filtering on totals is one of those features. There is still a large area of overlap, though (i.e., the extremely common "slice-and-dice" scenario), which is why we offer the option of RB-over-AS. However, users should realize that in this release there will be some challenges/limitations. Specifically, filtering on totals when using RB over AS will rarely result in acceptable performance.
That said, let me go through a couple of examples to help explain what is possible, and what to watch out for.
Example 1: A botched report
The report we want is, "show me a list of customers with more than $5000 in Accessory sales" (same as above). Conceptually this is fairly easy to think about, so many users will assume it must be easy to build. So they'll just follow their nose and build a report that has all the parts they think it needs, then hit "Run" and expect the right answer, like this:
1. Create a new table report using the AdventureWorks sample report model
2. Add Customer->Customer Name to the report
3. Add Customer->Sales Orders->Sales->Sum Line Total to the report
4. Open the Filter dialog
5. Add Customer->Sales Orders->Sales->Product->Product Category to the filter, and set it to "Accessories"
6. Add Sale->Line Total to the filter, and set it to "greater than 5000".
7. Close the filter dialog and run the report
Looks like it should do the job, right? Nope. This report actually returns no data. What went wrong?
Well, there are two problems. The first is that step 5 caused RB to change the primary entity of the report from Customer to Sale, based on the fact that the little checkbox at the bottom of the filter dialog was checked. Changing the primary entity ensures the filter condition will filter all the values added to our report, not just toss out some of the rows. This would be perfect if all we were doing was slice-and-dice filtering. However, we are trying to filter on a total at the same time. This change in the primary entity means that the context of the report filter is now Sale instead of Customer, so the condition added in step 6 means "include only individual sales where Line Total is greater than 5000" (notice that this change is reflected in the text at the top of the filter dialog, and later in the filter description on the surface of the report). As it turns out, there are no individual sales of a product in the Accessory category where the line total is greater than 5000, so we get no results.
The second problem is that in step 6, we selected Line Total instead of Sum Line Total. This actually doesn't make a difference in this case because of the effect of the first problem, but it was nevertheless an issue because we weren't thinking about the fact that we wanted to filter on an aggregate of Line Total in some other context (Customer), not the individual Line Total value attached to a single Sale. We shouldn't have expected to specify both filter conditions in the same place.
Example 2: Getting it right
This report is possible in RB using the approach described below, and has reasonable performance over a relational database (on AdventureWorks on my laptop, this report returns in <2 seconds). Be warned, however, that for a number of reasons, running a report that uses this approach over AS will have performance ranging from disappointing to tragic.
To do the same report as Example 1 correctly, we need to avoid making Sales the primary entity, because that would keep us from being able to specify a filter at the Customer level. We can accomplish this by applying the slice-and-dice part of our filter directly to the displayed field instead of to the overall report. We can then reuse the filtered field to create a report-level filter condition in the context we want (Customer).
1. Create a new table report based on the AdventureWorks sample report model.
2. Add Customer->Name to the report
3. Add Customer->Sales Orders->Sales->Sum Line Total to the report
4. Right-click on the Sum Line Total field in the table, and choose Edit Formula (the Formula dialog opens)
5. Double-click on the field reference in the formula to expand it
6. Click on the link to add a filter on Sales
7. Add Sale->Product->Product Category to the filter, and set it to "Accessories"
8. Click OK to close the Filter dialog.
9. Check the box at the bottom of the Formula dialog to create a custom field using this formula, click OK, and name the field "Accessory Sales".
11. Open the main Filter dialog
12. Add the new "Accessory Sales" field to the filter, and set it to "greater than 5000"
13. Close the Filter dialog and run the report
Result: A quick 1-page report showing customers with more than $5000 in accessory sales.
So, that's great! Does that mean RB supports any kind of filtering on totals? No. You can filter on totals as long as (a) all the totals are in a single context, and (b) that context is the primary entity of the report.
If you wanted to filter on totals in more than one context, such as "show me customers grouped by territory, but only show customers with > $10000 in sales and territories with > $1M in sales acoss all customers", it wouldn't work, because you need a filter on the sales total for each customer and the sales total for each territory. Note that this isn't possible in Excel either, since it only allows filtering on totals for the outermost group on an axis.
If you wanted to filter on a total in a context other than the primary entity, such as "show me customer sales grouped by territory, but only show territories with > $1M in sales", that wouldn't work either, because you need to show values for each customer (which means Customer needs to be the primary entity), but filter on totals for each territory (which would not be the primary entity). This is possible in Excel, but note that if you add an outer group like Territory in Excel, you actually can't do the report I described above, because it won't let you filter on a total for an inner group.
Another, more common example is when you want to filter on a total at the lowest level of detail in the report, but that doesn't happen to be an entity context, such as "show me monthly sales, but only months with > $1M in sales." This would work if Month were an entity, but chances are it isn't in your report model, and if not, you can't make it the primary entity, so you won't be able to filter on a total in that context (in this case, the primary entity would probably be Sales Order, where Order Date->Month is defined). This is possible in Excel, because it doesn't distinguish between entity groups and value groups.
So, what kind of filters can you do in RB that you often can't do in OLAP reporting? Actually, a lot. RB supports an arbitrary number of filter conditions, "OR" groups, aggregate filter groups (e.g. "customers with any orders where Year=2004"), row filters that don't filter the displayed values (e.g. "show customers and their total sales, but only those with > $1000 sales in the last month), and independently filtered fields (e.g. the "Accessory Sales" custom field we created in Example 2), including those with filters on multiple levels (e.g. "total sales with Product->Category = "Accessories" and Sales Order->Order Year = 2004"). All of these features help deliver a rich and powerful relational/operational reporting experience, which is what this release of RB was primarily designed to do.
Rest assured, however, that improving support for filtering on totals is still a high priority for the product team.