An overview of relationship and empty filters is provided below. Additional information is provided about when to use them and how the filters behave depending on other characteristics of a view. See these sections:
Use the Relationship and Empty Filters to filter out members of a result set where no related data exists. Related data is based on the visible measure items of the view. Filtering out those members where no data exists related to the measure items in the view produces a more focused result set.
By default, the Relationship and Empty Filter properties are enabled (Yes) for rows and columns. If you wanted to see all members on an axis regardless of related data, you would set the Relationship and Empty Filter properties to No. These properties are controlled through the Properties windows for rows and columns.
For example, in a view displaying the current year YTD sales by product and where the Relationship and Empty Filters are enabled, the result set would include only product members that have sales for the current year (related data). When the Relationship and Empty Filter are disabled, the result set would include all products -- those that have sales as well as those that don't have sales.
Here is the example view with both properties set to No, returning all products. Results show products with and without YTD sales.
Here is the view when just the Relationship is set to Yes. The row count has decreased because results now show only the products that have YTD sales.
Here is the view when Empty Filter also is set to Yes. The row count is only 250 after this change because the view only returns products that have YTD sales given the level and member(s) on columns. In this case, returning only products with YTD sales for the Fresh Vegetables member of Product Category.
For period based views that have levels from time hierarchies visible on rows or columns, it is recommended that you set the Relationship Filter and Empty Filter properties to No for the axis where the time levels are used. This will ensure that all periods will display in the view, even those for which no data exists.
Levels from the Weeks time hierarchy are on rows in this view. Both Relationship and Empty Filter are No, which means all weeks display -- even those where there is no related data. Rows for all 52 weeks display in calendar order.
Here is the same view with Relationship and Empty Filter set to Yes. Only the weeks that have related data display.
Levels from the Year Months time hierarchy are on rows in this view. Here is the view where both Relationship and Empty Filter are No, which means the rows for December through October of 2014 still display even though there is no related data yet for those months (the current month of the year is September, so no data exists yet for the months after that current month).
Here is the same view with Relationship and Empty Filter set to Yes.
The Empty Filter setting has no impact on All Others rows and columns. If an All Others row or column has no data and Empty Filter is set to Yes, that empty row or column will remain in the view.
The Empty Filter only considers cells to be empty when they have null or no data in them. Cells with zero (0) or text are not considered empty and therefore will not be excluded from the result set.
The Relationship Filter considers all visible measure items (regular and calculated) when determining which members to include in the result set. This means that each calculated measure item expression is performed for all members. In cases where the expression contains a constant or an attribute relationship, this can cause an unexpected result set. In cases where the expression is time consuming, view performance can be impacted.
When an expression contains a constant or an attribute relationship, the calculated measure item produces a non-null value for every member, this results in the Relationship Filter not excluding any members from the result set since every member has "related" data. For example, if a calculated measure item expression includes an attribute relationship, then every member will have related data. You can set up conditions in the expression for the calculated measure item to be executed only under certain conditions. An example follows. See also Creating Expressions for Calculated Measure Items.
The Extended List Price measure item in the next view is a calculated measure item which has the expression:
[Product].[Product].Properties("Prod Current List Price") * [Measures].[Data13 (Actual Sales Sales Units Jan 2014 to Sep 2014)]
The Prod Current List Price in the expression is an attribute relationship. Every product has a list price, which means every product will have an Extended List Price result and no rows will be removed from the view by the relationship filter. If the calculation is made conditional to only execute when YTD sales units exists, then the view will contain only those products that have YTD sales units.
Here is the expression, modified to check for YTD sales units. This optimized expression will not proceed with the expression calculation in cases where no YTD Sales Units exists.
IIF([Measures].[Data13 (Actual Sales Sales Units Jan 2014 to Sep 2014)] <> Null, [Product].[Product].Properties("Prod Current List Price") * [Measures].[Data13 (Actual Sales Sales Units Jan 2014 to Sep 2014)], null)
Here is the updated view, which now has fewer rows because the relationship filter removed rows without YTD Sales units.
The next view contains YTD calculated measure items for sales amount and units. The expressions for both measure items use named sets and therefore were optimized by making them conditional based on the existence of related data. The YTD calculations will execute only when Actual Sales Sales Amount and Actual Sales Sales Units respectively are not null. Here is the expression for the YTD Sales Amount measure item. A similar expression was used for the YTD Sales Units measure item.
IIF([Measures].[Data1 (Actual Sales Sales Amount)] = null, null, Sum(CrossJoin({[Time].[Year Based Months Based].[Year Based].[Current Year]},{[Act Sales YTD Months]}),[Measures].[Data1 (Actual Sales Sales Amount)]))
When a view has levels and measure items on the same axis, only members that have null data for all measure items will be removed by a Relationship and Empty Filter.
Relationship Filters on one axis will ignore level filters on the opposite axis when determining which members have related data. For example, this view has a filter on the Customer Class level in columns that returns class 91. Relationship Filter is enabled for rows and returns all Distribution Channels with YTD Sales Units. Once the Customer Class filter is applied, the result set may contain empty rows. In this case two distribution channels do not have any YTD Sales Units for Customer Class 91, but do have YTD Sales for another Customer Class. These empty rows could be removed by enabling the Empty Filter.
Here is the view after Empty Filter has been enabled on rows.
The sequence in which filters are executed in relation to View Filters is View Filter first, then Relationship Filter, and then Empty Filter. Only data that meets the View Filter criteria will be considered by the Relationship Filter.