How to achieve aggregate "Distinct Count" in Pivot Grid Widget
Pivot Grid widgets do not support the use of the aggregate function "Distinct Count" for dimension fields due to the impact on the performance of the widget and the technical complexity involved in calculating the value for the aggregation. However, it is possible to achieve a distinct count aggregation on dimension fields in Pivot Grid widgets using expressions.
Using Expressions for Distinct Count
To achieve a distinct count aggregation on dimension fields in pivot grid widgets, you can create an expression that calculates the distinct count of the dimension field values and bind it to the pivot grid widget.
Example
Consider a scenario where you want to calculate the distinct count of the `CustomerID` field. You can create an expression using the DISTINCT COUNT of the `CustomerID` dimension fields and bind it to the pivot grid widgets. The pivot grid widget will then display the result of the expression, showing the DISTINCT COUNT of the `CustomerID` dimension field.
After creating the expression, bind it to the pivot grid widget. The widget will display the result of the expression, showing the DISTINCT COUNT of the `CustomerID` dimension field.
Conclusion
While the Pivot Grid widget does not support the "Distinct Count" aggregate function for dimension fields directly, you can achieve this functionality using expressions. By creating an expression that calculates the distinct count of the dimension field values and binding it to the pivot grid, you can display the distinct count aggregation in your reports.
