SQL SQL Server Visual Studio / SSRS
9 décembre 20139 décembre 2013 ArnaudArnaud 2 de commentaires

[SSRS] Les groupes de hiérarchies récursives…

… ou comment jouer avec l’héritage avec les reporting services.
Très utile pour décrire une arborescence, la récursivité peut être utilisée avec SSRS. Je vous propose donc un tutorial rapide pour expliquer comment faire un tri grâce à une relation existante entre parents et enfants avec SSRS.


Le but du jeu est donc d’obtenir un tableau de cette sorte

SSRS exemple table recursive
Figure 1 : Exemple, d’affichage récursif avec SSRS

A partir d’un tableau tout bête comme celui-là.

table patron recursive ssrs
Figure 2 : Table SSRS d’affichage des données

Pour pouvoir illustrer mon exemple de récursivité avec les reporting services, je me suis appuyé sur une table de la célèbre société ‘Adventure Works Cycles’.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
	EmployeeID SMALLINT NOT NULL,
	FirstName nvarchar(30)  NOT NULL,
	LastName  nvarchar(40) NOT NULL,
	Title nvarchar(50) NOT NULL,
	DeptID SMALLINT NOT NULL,
	ManagerID INT NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

Bien sûr, vous pouvez utiliser n’importe quelle table pour faire vos tests 😉
Par exemple:

USE [test]
GO
 
/****** Object:  Table [dbo].[Parents]    Script Date: 12/9/2013 3:34:00 PM ******/
DROP TABLE [dbo].[Parents]
GO
 
/****** Object:  Table [dbo].[Parents]    Script Date: 12/9/2013 3:34:00 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Parents](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[idParent] [INT] NULL,
	[Nom] [nvarchar](50) NULL
) ON [PRIMARY]
 
GO

Et ci-dessous, la requête CTE pour interroger cette table.

WITH DirectReports (ManagerID, EmployeeID, FirstName, LastName, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT ManagerID, EmployeeID, FirstName, LastName, Title, DeptID, 
        0 AS Level
    FROM [dbo].[MyEmployees] AS e
       WHERE ManagerID IS NULL
 
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.FirstName, e.LastName, e.Title, e.DeptID,
        Level + 1
    FROM [dbo].[MyEmployees] AS e
       INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, FirstName, LastName,Title, DeptID, Level
FROM DirectReports
 
GO

Je suis parti du postulat que vous savez écrire une requête récursive en sql. Si ce n’est pas le cas je vous invite à lire : Requêtes récursives utilisant des expressions de table communes.

Méthode de création de la récursivité

Donc dans ssrs, créez un dataset appelant la requête ci-dessus.
Associez ce dataset à un nouveau tableau pour qu’il ressemble à la figure 2.
Ouvrez les propriétés de la ligne de votre tableau (Clic droit propriété, sur row groups / Details )

Dans l’onglet général, ajoutez le groupe ‘EmployeeID’ dans le champ ‘group Expression’.
Dans l’onglet Advanced, dans le champ ‘Recursive parent’ ajoutez le champ ‘ManagerID’.

group properties recursive parent advanced
Accès à la propriété ‘recursive parent’ dans l’onglet ‘Advanced’

Si vous faites, un preview de votre rapport, vous devriez voir les données triées dans l’ordre de votre arborescence.

Améliorez la lisibilité de votre rapport

Pour une meilleure lisibilité de l’arborescence, je vous propose de mettre un décalage en fonction du niveau dans l’arborescence de chaque élément.
Pour cela, sélectionnez les propriétés de votre ligne.
Recherchez l’option ‘Padding Left’.
Et saisissez, l’expression VB suivante :

= 5 * Level() & " pt "

Cette expression va décaler X points (en fonction du level) vers la droite le texte.

Voilà, j’espère que ce tutorial vous a été utile. Si vous souhaitez lire plus de chose sur le SSRS vous serez peut être intéressé par

SSRS

Publications connexes

2 commentaires sur « [SSRS] Les groupes de hiérarchies récursives… »

  1. Ping : [SSRS] Créer un dégradé de couleur pour un groupe de hiérarchie
  2. Ping : [SSRS] Créer un dégradé de couleur pour un groupe de hiérarchie - Les sources d'UIOP

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.