… 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

Mots clefs liés à cet article:

  • récursivité
  • SSRS MyEmployees