Trucs et astuces pour les développeurs SSRS

Lors de mes diverses expériences, j’ai rencontré à plusieurs reprises certaines difficultés sur SQL Server Reporting Services (qui s’appliquent aussi sur la version On-Premise de Power BI Report Server), avec très peu de documentation française pour m’aider à m’en sortir. Je regroupe dans cet article tous les tips qui m’ont été très utiles et m’ont permis de gagner du temps ou d’identifier un problème :

• Les bases de données ReportServer et ReportServerTemp
• Astuces face à certains problèmes que l’on peut rencontrer
  • Le mode Preview de Visual Studio ne montre pas la dernière version de ma requête
  • Requête très lente sur le serveur de rapport alors qu’elle est très rapide sur SSMS
  • Pages blanches parasites dans l’export pdf d’un rapport

Les bases de données ReportServer et ReportServerTemp

L’installation Reporting Services en mode natif crée pour fonctionner deux bases de données par défaut nommées ReportServer et ReportServerTempDB . La première sert pour le stockage de données persistantes (contenu publié : rapports, fichiers, images, etc. et l’arborescence des dossiers). La seconde permet le stockage temporaire.

Ces 2 bases regorgent d’informations pour nous aider à débuguer certaines situations.

Par exemple, la table ReportServer.dbo.ExecutionLog3 contient toutes les informations d’exécution des rapports. Ainsi nous pouvons savoir qui a exécuté quel rapport, à quelle heure, avec quels paramètres, et si cette requête s’est effectuée en cache ou en live, avec succès ou non, etc…

Autre exemple, nous pouvons dresser la liste des rapports avec leur schedule associé et les datasets utilisés. Cela nous permet de comprendre pourquoi certaines données ne se mettent pas correctement à jour.

SELECT
	 cd.Path
	,cd.Name AS dataset 
	,d.Name AS libelle_dataset
	,cr.Name AS rapport
	,s.Name AS schedule
FROM ReportServer.dbo.DataSets d	--info sur les datasets
INNER JOIN ReportServer.dbo.Catalog cd	--lien entre dataset et rapport
	ON cd.ItemID = d.LinkID
INNER JOIN ReportServer.dbo.Catalog cr  --info sur les rapports
	ON cr.ItemID = d.ItemID
LEFT JOIN ReportServer.dbo.ReportSchedule rs	--lien entre rapport et schedule
	ON rs.ReportID = cd.ItemID
LEFT JOIN ReportServer.dbo.Schedule s	--info sur les schedules
	ON s.ScheduleID = rs.ScheduleID
ORDER BY s.Name DESC

Dans la table ReportServer.dbo.Catalog, la colonne type permet de filtrer sur le type d’élément qui nous intéresse. Pour cela il est important de connaitre sa signification :

1 = ‘Folder’
2 = ‘Report’
3 = ‘File’
4 = ‘Linked Report’
5 = ‘Data Source’
6 = ‘Report Model’
7 = ‘Report Part’
8 = ‘Shared Data Set’
9 = ‘Image’

Autre cas d’usage : chez mon client actuel, nous avons pour habitude de cacher les images que nous utilisons sur le serveur de rapport pour ne pas polluer l’interface des utilisateurs. Pour diminuer au maximum le temps entre le déploiement des images et la manipulation de rendre « invisible » ces images, nous utilisons une requête qui fait tout le travail en un clic et en un rien de temps, plutôt que de le faire manuellement, image par image.

USE ReportServer
GO

--affiche l'inventaire des images du serveur de rapport
SELECT
	 ItemID
	,Path
	,Name
	,MimeType
	,Hidden
	,CreationDate
FROM dbo.Catalog 
WHERE
	type = 3 -- = ressources
AND MimeType LIKE 'image/%'

--met toutes les images en cachées
UPDATE dbo.Catalog
	SET Hidden = 1
	WHERE type = 3 -- = ressources
	AND MimeType LIKE 'image/%'

Astuces face à certains problèmes que l’on peut rencontrer

Le mode Preview de Visual Studio ne montre pas la dernière version de ma requête

Lorsque nous développons un rapport SSRS, si nous sommes amenés à modifier une procédure stockée qui est utilisée par un de nos datasets, il arrive parfois que cette modification ne se répercute pas correctement lors de la prévisualisation du rapport sous Visual Studio. Cela provient de la façon dont Visual Studio met en cache les données pour le rapport. En effet, pour éviter des temps d’exécution trop longs pour la même requête, VS stocke le résultat de cette requête dans un fichier .data. Cela est très pratique lorsque l’on souhaite faire des tests de formatage ou de mise en page et que l’on fait plein d’aller retour entre l’interface de développement et la page de prévisualisation. Mais celle-ci ne se met pas automatiquement à jour lorsque l’on fait des modifications en amont sur la procédure stockée. Pour résoudre ce souci, il suffit donc manuellement, d’aller supprimer ce fichier .data dans l’explorateur Windows (là où est stocké votre code source, juste à côté de votre fichier .rdl). Cela permet de vider le cache et de réexécuter la dernière version de la procédure stockée.

Requête très lente sur le serveur de rapport alors qu’elle est très rapide sur SSMS

Votre procédure stockée est prête et affiche le bon résultat rapidement. Votre rapport affiche le résultat de la procédure stockée grâce à un joli tableau et de beau graphique. Tout fier de vous, vous déployez donc ce dernier sur le serveur de rapport. Mais là, c’est la douche froide, votre rapport met plus d’une minute à s’afficher alors que cela ne prenait que quelques petites seconde à votre procédure stockée sur SSMS. Que faire ?

J’ai la solution pour vous !

SSRS utilise ce qu’on appelle le « parameter sniffing« , qui consiste lors du traitement d’une requête, à créer et mettre en cache un plan d’exécution de requête optimal. Des problèmes de performances peuvent alors survenir lorsque SSRS réutilise un plan d’exécution mis en cache pour de nouveaux paramètres. Alors que le plan était le plus adapté pour les anciens paramètres, il devient inefficace et lent pour les nouveaux paramètres.

S’il existe plusieurs façons de parer le souci (comme par exemple obliger le plan d’exécution à être recompilé à chaque requête avec les fonctions RECOMPILE ou OPTIMIZE FOR) je vais vous présenter un moyen plus simple et très efficace qui consiste à redéfinir les variables de paramètre en variables locales dans la procédure stockée. La déclaration de ces nouvelles variables obligera ainsi l’optimiseur de requête à recompiler pour obtenir un nouveau plan d’exécution optimisé pour les paramètres choisis.

CREATE PROCEDURE rapports.InfoCollaborateurs
( 
	 @IdDate INT	-- mois de présence du collaborateur; peut être null
	,@IdMagasin INT	-- magasin de référence du collaborateur; peut être null
)
AS 

BEGIN
        -- on redéfinit les variables de paramètres en local
	DECLARE @IdDateLocale INT = @IdDate
	DECLARE @IdMagasinLocale INT = @IdMagasin

	SELECT c.* 
	FROM dim.DimCollaborateur c
	INNER JOIN ft.FtPresenceMensuelle pm
		ON pm.IdCollaborateur = c.IdCollaborateur
	WHERE pm.IdDate = @IdDateLocale           -- on utilise la variable locale et non le paramètre
	AND pm.IdMagasin = @IdMagasinLocale       -- on utilise la variable locale et non le paramètre

END

Pages blanches parasites dans l’export pdf d’un rapport

Il est très important lors de la création de rapport SSRS de faire attention à sa mise en page, non seulement sur le serveur de rapport, mais également de l’export pdf, dont les utilisateurs sont très friands.

Il arrive parfois d’avoir, lors l’export pdf, des pages blanches supplémentaires qui n’ont absolument rien à faire là. Il est possible de résoudre le problème grâce à une petite option bien cachée : dans la fenêtre de conception, cliquez en dehors du corps du rapport (la zone grise entourant le rapport) et allez dans l’onglet des propriétés du rapport (F4). Dans la section « Other« , il y a une option « ConsumeContainerWhiteSpace« . Cette propriété indique si les espaces vides dans les conteneurs doivent être conservés ou consommés lorsque les contenus autours augmentent. Seul les espaces blanc à droite et en dessous du contenu sont affectés par cette option.

En passant cette propriété à « True« , fini le souci de pages blanches parasites. Le rapport peut être désormais exporté sans pages vierges.

J’espère que ces petites astuces vous aideront dans vos développements de rapports SSRS.