Si vous développez des rapports SSRS pour le CRM, et
que parfois la structure complexe des données relationnelles vous empêche de
présenter les données comme vous le souhaitez, l'astuce que je présente dans
cet article est pour vous.
Grâce à des méthodes standard de SQL Server, il est
possible de fabriquer du XML à partir de données relationnelles, ce qui veut
dire que vous avez la possibilité de transformer le résultat d'une requête
SELECT en éléments XML, que vous pouvez parser par la suite de manière très
simple et transformer dans la structure que vous souhaitez.
Une de ces méthodes SQL Server est la clause For XML
qui permet de transformer chaque ligne d'une requête SELECT en un élément XML ;
par exemple pour la requête SQL ci-dessous :
SELECT contactId,
lastName,
FirstName
FROM FilteredContact
FOR XML RAW ('Contact')
On obtient le résultat suivant :
<Contact contactId="1"
lastName="Clélia" FirstName="Berguig"/>
<Contact contactId="1"
lastName="Julien" FirstName="Miquel"/>
<Contact contactId="1"
lastName="William" FirstName="Keo"/>
Ensuite, ce flux XML peut être parsé à l'aide des
fonctions suivantes :
§ Value () : Cette méthode permet d'extraire une valeur de nœud
d'un document XML et de la retourner dans un type SQL passé en paramètre
§ Nodes () : Cette méthode permet de fragmenter le document XML
d'origine en plusieurs lignes.
Afin de vous expliquer ces méthodes, rien de mieux
qu'un exemple concret.
J'avais utilisé ces fonctions pour réaliser un rapport
SSRS qui permet d'auditer les différences entre deux versions d'un produit. Le
rapport devait afficher sous forme de tableau la liste des attributs de l'entité
produit ainsi que les valeurs des deux versions de produit.
Produit est une entité personnalisée que nous avons
créée, deux instances / version d'un produit sont liées entre elles via une
relation 1-N.
Pour ce faire, j'ai commencé par écrire la requête SQL
qui permet de récupérer les données des deux versions de produit :
J'obtiens le résultat suivant :
Comme vous le remarquez dans le tableau ci-dessous, la
réponse de ma requête SQL est deux lignes de produit avec en colonnes la liste
des attributs récupérés.
Maintenant, je veux transformer ces deux lignes + l'en-tête
en trois colonnes : une colonne qui contiendra les noms des attributs, une
deuxième colonne qui contiendra les valeurs du produit version N et une
dernière colonne qui contiendra les valeurs du produit version N+1.
Pour cela, je vais utiliser la clause For XML pour
transformer le résultat de ma requête SQL en éléments XML:
J’obtiens donc les deux éléments XML suivants qui
correspondent aux deux enregistrements produit :
Ensuite, je vais parser ce flux XML en utilisant les
méthodes nodes (), value () et des expressions XPATH :
Je vais donc créer un tableau temporaire à partir de
chaque ligne XML, avec comme colonnes Nom d'attribut et Valeur d'attribut.
Ce qui nous donne les deux tableaux suivants :
Comme vous le remarquez, j'ai pu transformer les deux
lignes de produit obtenues initialement dans le format qui me permet de
réaliser mon rapport de manière simple en passant par une transformation SQL
vers XML.
La dernière étape qu’il reste est de fusionner ces
deux tableaux temporaires en utilisant une jointure SQL à l'aide de la colonne
ColumnName :
Et voilà, le résultat de cette dernière requête m'a
permis d'alimenter mon rapport SSRS qui affichera ces informations sous la
forme suivante :
Aucun commentaire:
Enregistrer un commentaire