Unrelated Filter Options for Nested Metrics

Unrelated Filter Options for Nested Metrics is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Unrelated Filter Options property determines whether to remove or keep unrelated filter qualifications when using nested metrics. Nested metrics, or nested aggregation metrics, are a type of simple metric, where one aggregation function is enclosed inside another. For additional information on nested metrics, see the Advanced Reporting Help.

To explain how this VLDB property determines whether to keep or remove unrelated filter qualifications when using nested metrics, consider the following example:

The following example was created in the MicroStrategy Tutorial project, with its data stored in a Microsoft Access database.
A report is created that includes the following:

The Category attribute on the rows of the report.
A metric on the columns of the report. THe metric is defined as Sum(ApplySimple("IIf(#0 = 1, #1, 0)", Region@ID, Sum(Revenue) {~+})) {~}. This metric returns revenue data for the Northeast region (Region@ID =1) or a zero value.
A report filter that is defined as Category In List (Books). This report filter returns data only for the Books category.

For the example explained above, the metric includes the Region attribute (through the use of Region@ID) and the report filter includes the Category attribute. Since the Category attribute is unrelated to the Region attribute, it is considered unrelated to the nested metric's inclusion of the Region attribute.

This VLDB property has the following options:

Use the 8.1.x behavior (default): Select this option to use the behavior in MicroStrategy 8.1.x. In the example described above, this returns the following SQL statement, which has been abbreviated for clarity:
```
 insert into ZZTTM6REM4ZMD000
```
```
 select a11.[CATEGORY_ID] AS CATEGORY_ID,
```
```
 sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
```
```
 from [YR_CATEGORY_SLS] a11
```
```
 where a11.[CATEGORY_ID] in (1)
```
```
 group by a11.[CATEGORY_ID]
```
```
 select pa11.[CATEGORY_ID] AS CATEGORY_ID,
```
```
 max(a13.[CATEGORY_DESC]) AS CATEGORY_DESC0,
```
```
 sum(IIf(a12.[REGION_ID] = 1, pa11.[WJXBFS1], 0)) 
```
```
 AS WJXBFS1
```
```
 from [ZZTTM6REM4ZMD000] pa11, 
```
```
 [LU_REGION] a12, 
```
```
 [LU_CATEGORY] a13
```
```
 where pa11.[CATEGORY_ID] = a13.[CATEGORY_ID]
```
```
 group by pa11.[CATEGORY_ID]
```
While the unrelated filter qualification is kept in the first pass of SQL, it is removed from the second pass of SQL. This means that the filtering on Category is applied to the inner aggregation that returns a summation of revenue for the Northeast region only. However, the filtering on category is not used in the final summation.
This option can be beneficial for the processing of security filters, which can create additional unrelated filter qualifications on a report based on a user's security filter constraints. Selecting this option can remove some of these unrelated filter qualifications caused by a user's security filter.

Use the 9.0.x behavior: Select this option to use the behavior in MicroStrategy 9.0.x. In the example described above, this returns the following SQL statement, which has been abbreviated for clarity:

 insert into ZZTTM6REM4ZMD000

 select a11.[CATEGORY_ID] AS CATEGORY_ID,

 sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1

 from [YR_CATEGORY_SLS] a11

 where a11.[CATEGORY_ID] in (1)

 group by a11.[CATEGORY_ID]

 select pa11.[CATEGORY_ID] AS CATEGORY_ID,

 max(a13.[CATEGORY_DESC]) AS CATEGORY_DESC0,

 sum(IIf(a12.[REGION_ID] = 1, pa11.[WJXBFS1], 0))

 AS WJXBFS1

 from [ZZTTM6REM4ZMD000] pa11,

 [LU_REGION] a12,

 [LU_CATEGORY] a13

 where pa11.[CATEGORY_ID] = a13.[CATEGORY_ID]

and pa11.[CATEGORY_ID] in (1)

 group by pa11.[CATEGORY_ID]

By using the 9.0.x behavior, the unrelated filter qualification is kept in both SQL passes. This means that the filtering on category is applied to the inner aggregation that returns a summation of revenue for the Northeast region only. The filtering on category is also used in the final summation.

Levels at Which You Can Set This

Database instance, report, and template