Introduction:
Consider a report which has attributes Day, Region and Customer. Add a metric which calculates the distinct count of customer. The metric is defined with the expression:
Count<Distinct=True, FactID=>(Customer) {~}.
When the report is executed, the data is as shown below:
The requirement is to have the value for the metric: Count_Customer to be calculated dynamically at any desired attribute level. In other words, depending on the attributes present on the grid, the count metric should be calculated at that level. For example, if the attribute 'customer' is removed from the grid, the requirement is to get a count of customers at the level of Day, Region. The above metric cannot fulfill this requirement even after adding the required dynamic aggregation function. This is because the data returned for the metric does contain the necessary information to perform an aggregation inside of Strategy to a higher level. To obtain the desired behavior, perform the steps provided below:
1. Create a custom subtotal object which is defined with the expression: Count<Distinct=True>(x) {@}, as shown below
2. Create a metric object which is defined as: Max(Customer@ID){~+}, as shown below:
3. Go to the subtotals/aggregation tab of the above created metric and make the subtotal created in Step 1 as the dynamic aggregation function as shown below.
4. Add the metric from Step 2 to the report shown above. When the 'Customer' attribute is removed from the grid, the newly created metric dynamically shows the distinct count of customers at the Day, Region level as shown below. If the Region attribute is removed from the grid, the metric value is dynamically aggregated to the Day level.
