Using that little checkbox in the Report Builder filter dialog

  • Article

The Filter dialog in Report Builder has a little checkbox near the bottom that many users have asked about. The checkbox label says, "When adding a new condition, apply to all data in my report."

The effect of selecting this checkbox is that when subsequent fields are added to your filter, and those fields have a to-many relationship from the primary entity, an attempt will be made to adjust the query so those conditions filter not only the rows in the report, but the totals as well. This is the default behavior, and mimics the familiar "slice-and-dice" approach to filtering.

If the checkbox is not selected, no attempt will be made to ensure the conditions you add affect your totals. They will only affect the set of rows displayed in your report. (Technically, they will only affect the set of rows from the primary entity that contribute to the rows in your report. If you are not displaying individual rows from the primary entity, e.g. you are grouping on a field instead, a new filter condition may still affect your totals even though the checkbox is not selected.)

For example, if I add Customer Name and Total Sales to a new report, then open the Filter dialog and add a filter condition specifying Customer.Orders.Order Year = 2006, two things could happen:

If the checkbox is selected, the primary entity will change from Customer to Order, and the filter will now be an Order filter. This will affect the set of rows displayed (only Customers with Orders in 2006 will be displayed), and the Total Sales field (only sales from Orders in 2006 will be included).

If the checkbox is not selected, the primary entity will remain Customer, and an aggregate filter group ("Any Orders with...") will be created instead. This will affect the set of rows displayed (only Customers with any Orders in 2006 will be displayed), but it will not affect the Total Sales field (it will continue to show their all-time sales). This allows you to isolate the row filter from the filter used for totals.