Avec Visual Studio 2017 est arrivé l’éditeur DAX tant attendu, finie l’astuce consistant à utiliser l’éditeur DMX afin de créer nos rapports SSRS en DAX.
Il est maintenant directement possible de sélectionner l’éditeur DAX lors de la création de nos Datasets et donc pouvoir glisser / déposer des éléments en ayant une auto génération du DAX. Mais aussi de gérer nos paramètres et tester nos requêtes. (Il n’aura finalement fallu que 6 ans avant de voir cette fonctionnalité arriver…)
Mais voilà, le changement de langage du MDX au DAX n’est pas sans conséquence, en particulier sur la gestion des totaux.
En effet, vous vous souvenez surement, SSRS est capable d’utiliser les membres ALL de vos requêtes MDX afin d’obtenir les niveaux d’agrégation souhaités dans vos totaux / sous totaux lors de l’utilisation de la fonction « =Aggregate() ». Or en DAX cela n’est pas possible.
Prenons l’exemple suivant :
Qui génère le DAX :
EVALUATE SUMMARIZECOLUMNS ( 'Product'[Color], 'Date'[Calendar Year], "Panier moyen", [Panier moyen], "Internet Total Sales", [Internet Total Sales] )
La Matrice générée est la suivante :
Comme vous pouvez le constater, le panier moyen total est faux, en effet la fonction par défaut est la somme et ce n’est pas ce que j’attends sur une moyenne.
En MDX j’aurais changé la fonction SUM par un AGGREGATE, mais cela n’est pas possible en DAX, car notre jeu de données ne contient pas les différents ALL des niveaux intermédiaires.
Retourne :
Deux solutions s’offrent donc à nous :
- Exposer les éléments additifs de notre calcul et effectuer la partie non additive dans SSRS.Dans notre exemple, il faudrait donc exposer la mesure Nombre de Commandes et la mesure Ventes totales et effectuer la division Ventes totales / Nombre de commandes dans SSRS.En DAX :
EVALUATE SUMMARIZECOLUMNS ( 'Product'[Color], 'Date'[Calendar Year], "Panier moyen", [Panier moyen], "Internet Total Sales", [Internet Total Sales], "Nb Orders",[Internet Distinct Count Sales Order] )
Dans mon rapport :
=Sum(Fields!Internet_Total_Sales.Value)/SUM(Fields!Nb_Orders.Value)
Certains d’entre vous aurons peut-être noté que dans ce cas précis, je ne peux pas me permettre cette souplesse, car le nombre de commandes est un Distinct Count, il faudra donc appliquer la solution suivante.
- Effectuer les différents sous-totaux en DAX dans la requête source.En DAX :
EVALUATE SUMMARIZECOLUMNS ( 'Product'[Color], 'Date'[Calendar Year], "Panier moyen", [Panier moyen], "Internet Total Sales", [Internet Total Sales], "Panier moyen total", CALCULATE([Panier moyen],ALL('Product'[Color])) )Dans SSRS :
En conclusion, les deux méthodes ont leurs avantages et inconvénients.
Méthode 1 :
- En cas de changement de la règle de calcul il est nécessaire de repasser sur tous les rapports pour répercuter le changement
- Le calcul est portée par SSRS à la place de SSAS, et c’est rarement une bonne chose
- En cas de DistinctCount… il peut devenir très compliqué et coûteux d’exposer la base des calculs
Méthode 2 :
- En cas de nombreux sous totaux (en ligne, en colonne) cela fait vite beaucoup de colonnes à créer
- La datasource devient intimement liée à ce qui est affiché (Dans mon cas, le résultat ne sera plus bon si j’ajoute un axe d’analyse)
Une nouvelle raison pour pleurer notre modèle Multidim et son MDX qui vont nous manquer 🙁
