MicroStrategy ONE

View filter effects on dynamic aggregation

Dynamic aggregation occurs when an attribute is moved between the report layout and the Report Objects pane. This type of aggregation dynamically affects the data returned for the report. For more information on dynamic aggregation, refer to Dynamic Aggregation.

A view filter also dynamically affects the data returned for a report, by restricting the report data using various types of qualifications. You can use both view filters and dynamic aggregation to return different analyses of report data, as described in the sections listed below:

Using attribute qualifications on hidden attributes

When report data is restricted using a view filter attribute qualification, this can affect the data displayed on the report even if the attribute it qualifies on is not displayed on the report grid.

For example, consider the View Filter - Dynamic Aggregation report in the MicroStrategy Tutorial project, as shown below.

Notice that dynamic aggregation has been used to remove the Employee attribute from the report grid. However, there is also a view filter attribute qualification on the Employee attribute. Even though Employee is not shown on the report, the data displayed is restricted by the view filter to only display data for the employees Caitlin Bell, Beatrice Conner, Andrew Johnson, Laura Kelly, and Jack Kieferson. This can be verified by using dynamic aggregation to drag and drop Employee on the report grid, as shown below.

Data is dynamically aggregated to be displayed at the region and employee level. A subtotal has been added to the report above to show that the original report was only displaying data for the employees listed in the view filter attribute qualification. For example, notice that the revenue, cost, and profit data for the Northeast region of the original report matches the values for the Northeast subtotal values of the report above.

Using metric qualifications at the report or grid level

When report data is restricted using a view filter metric qualification, this can affect the data displayed on the report in various ways depending on when the view filter metric qualification is evaluated.

For example, consider the View Filter - Dynamic Aggregation report in the MicroStrategy Tutorial project, as shown below.

Notice that dynamic aggregation has been used to remove the Employee attribute from display on the report grid. You then create a view filter metric qualification to restrict the report data for profits greater than $300,000. The view filter qualification and resulting report are shown below.

The view filter metric qualification is evaluated at the grid level by default. This means that the dynamic aggregation of removing Employee from the report layout is evaluated first, and then the view filter metric qualification restricts data based on the remaining data displayed on the report.

With this evaluation order, the view filter metric qualification returns regions with profits greater than $300,000.

However, you can also evaluate view filter metric qualifications at the report level. You can right-click the Profit Greater than $300,000 qualification, and select Apply Condition at the {Employee, Region} level. Evaluating the metric qualification at the report level returns the report results shown below.

Notice that only data for the Northeast region is returned, and all metric values are lower. This is because a view filter metric qualification evaluated at the report level includes all attributes, in the calculation to restrict data from the report.

Data is first restricted to employees with profits greater than $300,000, and then this data is aggregated and displayed at the region level. In the first report the Southwest region included two employees who combined to have more than $300,000 in profit, but neither employee had more than $300,000 alone. These employees are restricted by the view filter and are not included when aggregating the data at the region level.

These two options provide two different types of analysis on report data, summarized below:

  • Evaluating view filter metric qualifications at the grid level: When view filter metric qualifications are evaluated at the grid level and dynamic aggregation is used, only the attributes displayed on the report grid are used to determine the data restricted from the report.

    In the example above, any regions with profits greater than $300,000 for the included employees are displayed on the report.

  • Evaluating view filter metric qualifications at the report level: When view filter metric qualifications are evaluated at the report level and dynamic aggregation is used, all attributes in the Report Objects pane are used to determine the data restricted from the report. This includes attributes that are not on the report grid.

    In the example above, any employees with profits greater than $300,000 for the included employees are displayed on the report. The data for any remaining employees is then aggregated and displayed at the region level.

For information on evaluating view filter qualifications at the report level versus the grid level, see Evaluating qualifications at the report or grid level.