SYMPTOM:
A report that has two attributes and percentile metrics will show null values when one attribute is removed from grid and placed into Report Objects.
CAUSE:
Dynamic Aggregation is a function with OLAP Services which dictates the behavior of reports that are aggregated within the Intelligent Cube. Consider in the example above, when Month is removed, the percentile metrics must aggregate at the Quarter level. This action requires new SQL to be generated and results to be retrieved from the data warehouse.
However, when users remove an object from Grid View in Strategy Developer, new SQL is not generated. When reports are dynamically aggregated, values for most metrics are simply summed. However, as in the report above, the Strategy Engine is aware that summing a metric that calculates percentiles will produce erroneous results. Therefore, the Strategy Engine produces temporary null values for the percentile metrics when it is dynamically aggregated, which are shown as "--" on the report, as shown above.
Note:
The content in this Technical Note pertains specifically to percentile metrics causing nulls due to dynamic aggregation. In the case of some metrics, such as Average, Count, Mean and others, there is an additional resolution for the dynamic aggregation issue. This solution is presented in the following Strategy Knowledge Base document:
ACTION:
Instead of removing the attribute from just the grid, remove the attribute from the report. This will cause the report to re-execute. Values will then aggregate at the level of the attribute on the report.
Otherwise, create a user-defined subtotal, then when choosing how the metric will dynamically aggregate, click on 'More' and choose the subtotal previously created. Here are steps on how to create and apply a user-defined subtotal:
