How to create a report that will dynamically show the distinct count of an attribute at different levels.

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 distinct customer to be calculated dynamically. 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 cutomers at the level of Day, Region. The above metric cannot fullfil this requirement even after adding the required dynamic aggregation function.To get this data, 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.

Comments