SYMPTOM:
When using a level metric on a grid and the report filter contains an attribute qualification, the filter does not seem to be applied in Strategy Developer 10.x. Extra elements will be displayed on the grid. (Technically, the filter is applied. However, it's not applied in the final SQL pass.)
In this example, a level metric with absolute filtering and standard grouping is used with a target of Region. This can be reproduced with any level metric with any filtering or grouping settings, and with or without report level present in the dimensionality, as shown below:
Notice that in the example report below, Miami is the only attribute element in the report filter, yet Atlanta (which belongs to the same Region as Miami) also displays on the report grid:
CAUSE:
This is expected behavior with the default VLDB settings. By default, the filtering condition will only be applied to passes of SQL that touch the warehouse tables, as shown below:
This means that the level metric will be calculated based upon the behavior specified, but when choosing what to display on the report grid, the filtering conditions will not be applied.
ACTION:
Changing the VLDB settings at the report level will change the SQL generated by the report such that the filtering condition is applied in all passes, and thus the filtering condition will apply to the report grid display as well as the calculation of the level metric.
In this example, notice that Miami is now the only attribute element displayed on the grid, but the value calculated by the level metric is still the same total value for the Region, as desired:
