Sum Distinct avec SSRS

Reporting Services (SSRS) 2005 propose de nombreuses fonctions d'agrégation dont Sum, Min, Max, Avg, Count, Count distinct etc.

Malheureusement il arrive souvent qu'on ait besoin d'une autre fonction qui n'est pas fournie par défaut: Sum Distinct. Ce billet vous montre donc comment l'implémenter dans votre rapport.

Imaginons qu'on ait une requête qui ramène une liste combinant des employés, leurs spécialités, leur département et leur salaire. On aura donc une spécialité d'employé par ligne et donc potentiellement plusieurs lignes pour un même employé.

Si on désire réaliser un rapport avec un regroupement par département affichant le nombre d'employés à ce niveau agrégé, alors on peut utiliser la fonction CountDistinct(Fields!EmployeeID.value) dans la case correspondante, ce qui ramène le bon résultat.

Le problème apparaît lorsqu'on désire par exemple calculer la somme des salaires d'un département. On pourrait rajouter un champ dans la requête qui serait le résultat d'une sous requête et ramènerait le TotalSalaireDepartement pour chaque département. C'est une solution qui est peu envisageable si le nombre de champs à rajouter est élevé. D'où l'idée de cette fonction SumDistinct qui permet de récupérer le résultat grâce à un petit bout de code Vb.

Pour ajouter la nouvelle fonction, aller dans l'onglet "code" des propriétés du rapport:

Il ne reste plus qu'à appeler la fonction dans les cases du rapport.

SumDistinct(Mesure, ID, Valeur)

Ce qui donne pour cette exemple au niveau d'une ligne département:

=Sum(Code.SumDistinct(01, Fields!EmployeeId.Value, Fields!Salaire.Value))

Remarquez dans le code la clé de la HashTable qui est composée d'une Mesure (01 pour Salaire) et d'un ID (EmployeeID). Cette astuce permet de pouvoir réutiliser la même fonction pour des mesures différentes, par exemple 01 pour le salaire, 02 pour le poids de l'employé ou encore 03 pour l'âge de l'employé, sous réserve que les valeurs agrégées au niveau département de ces dernières mesures aient un intérêt quelconque:)