Le nombre de rapports SSRS ne cesse d’augmenter dans les différentes sociétés que je rencontre, cependant le nombre de projets documentés reste très limité.
L’objet de cet article est de vous fournir un certain nombre de requêtes vous permettant de générer une documentation sur vos projets Reporting Services.
L’idée de cet article m’est venue en analysant les N rapports d’un projet. Voici les quelques problématiques auxquelles j'ai été confrontées : Retrouver les liens morts entre les différents rapports, documenter la solution et simplifier les déploiements ainsi que la configuration des rapports.
A l’installation de Reporting Services, deux bases de données sont installées ReportServer et ReportServerTempDB.
La base de données ReportServer stocke le contenu suivant :
La base de données ReportServerTempDB quant à elle gère les données temporaires liées aux sessions, au cache des données, les tables de travail temporaires. Un processus automatique s’occupe de supprimer les anciens éléments inutilisés de cette base.
Afin de documenter l’environnement SSRS, sa configuration, les différents rapports publiés ainsi que les souscriptions nous effectuerons des requiêtes sur les différentes tables de la base ReportServer.
SELECT
MachineName,
InstanceName, Client FROM dbo.Keys WHERE MachineName IS NOT NULL |
Remarque : La table Keys contient aussi les informations sur les clés de chiffrement et sur l’installation de l’instance.
SELECT Name, Value FROM dbo.ConfigurationInfo |
Résultat :
Voici la liste des différents paramètres :
https://msdn.microsoft.com/en-us/library/bb934303.aspx
SELECT CAT.name AS ReportName, USR.username AS UserName, USR.authtype AS AuthType, ROL.rolename AS RoleName, ROL.Description AS Description FROM dbo.users USR INNER JOIN dbo.policyuserrole PLU ON USR.userid = PLU.userid INNER JOIN dbo.policies POL ON POL.policyid = PLU.policyid INNER JOIN dbo.roles ROL ON ROL.roleid = PLU.roleid INNER JOIN dbo.catalog CAT ON CAT.policyid = POL.policyid WHERE CAT.TYPE = 2 |
Résultat :
SELECT CAT.name AS ReportName, CAC.cacheexpiration AS CacheExpiration, CAC.expirationflags AS ExpirationFlags, FROM dbo.cachepolicy CAC INNER JOIN dbo.catalog CAT ON CAT.itemid = CAC.reportid |
SELECT CAT.Name AS ReportName, SUB.Description AS SubscriptionName, SUB.EventType AS EventType, SUB.LastStatus AS LastStatus, SUB.LastRunTime AS LastRunTime, SUB.Parameters AS Parameters, SCH.Name AS ScheduleName, SCH.Type AS ScheduleType, SCH.EventType AS ScheduleEventType FROM dbo.Subscriptions SUB INNER JOIN Catalog CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN ReportSchedule RSC ON RSC.ReportID = CAT.ItemID AND RSC.SubscriptionID = SUB.SubscriptionID INNER JOIN Schedule SCH ON RSC.ScheduleID = SCH.ScheduleID |
SELECT Name AS ReportName, Path AS ReportPath, CreationDate AS CreationDate, ModifiedDate AS ModifiedDate, Property AS Property, Hidden AS Hidden, Parameter AS Parameter, Convert(XML,(Convert(VARBINARY(MAX),Content))) AS XMLRdl FROM dbo.Catalog WHERE Type = 2 |
Résultat :
Remarques :
En effet il est possible de cacher à l’utilisateur des objets dans Report manager :
Une problématique récurrente à SSRS : « J’ai une centaine de rapports dans mon projet, certains rapports ont changés de noms, … Comment retrouver les liens morts entre les différents rapports ? »
Voici la solution :
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ), ReportInfo AS ( SELECT CAT.Path AS ReportPath, CAT.name AS ReportName, TAB.value('data(ReportName)[1]', 'varchar(255)') AS Link, REPLACE(CAT.Path, CAT.name, '') + TAB.value('data(ReportName)[1]', 'varchar(255)') AS PathLink FROM dbo.catalog CAT CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(MAX), content))) AS REP (reportxml) CROSS APPLY REP.reportxml.nodes('//*:Textbox/ActionInfo/Actions/Action/Drillthrough') NOD (TAB) WHERE CAT.Type = 2 ) SELECT ReportPath, ReportName, Link, PathLink, COUNT(1) AS NbLinks FROM ReportInfo WHERE PathLink NOT IN (SELECT path FROM dbo.catalog) GROUP BY reportpath, reportname, Link, PathLink ORDER BY reportpath |
Résultat :
Remarques : Ici le rapport « Product Line Sales » contient un lien (NbLinks) vers le rapport « Employee Sales Summary SQL2008R2 » situé dans le dossier « /AdventureWorks Sample Reports/ » qui n’existe pas ou plus.
L’Addin Excel NodeXL (Excel 2007 à 2016) n’est plus à présenter, il permet d’analyser sous forme de graph tout type de données. (Mon article de présentation de NodeXL : http://www.pulsweb.fr/nodexl/ ).
Voici le résultat d’une analyse sur une partie des rapports d’un projet SSRS :
Analyse :
Combiner le résultat de la précédente requête avec NodeXL permet d’avoir une vision complète des relations entre les différents rapports d’un projet, de vérifier qu’il n’y a pas de rapport isolé (sans aucun lien allant vers celui-ci), d’analyser les liens morts, …
WITH TMP AS ( SELECT CAT.Name, CONVERT(XML,CAT.Parameter) AS XMLParameter FROM dbo.Catalog CAT WHERE CAT.Type = 2 AND CAT.Name = 'Product Line Sales' ) SELECT VAL.value('Name[1]', 'VARCHAR(250)') AS ParameterName, VAL.value('Type[1]', 'VARCHAR(250)') AS ParameterType, VAL.value('Nullable[1]', 'VARCHAR(250)') AS ParameterNull, VAL.value('AllowBlank[1]', 'VARCHAR(250)') AS ParameterBlank, VAL.value('MultiValue[1]', 'VARCHAR(250)') AS ParameterMultiValue, VAL.value('UsedInQuery[1]', 'VARCHAR(250)') AS ParameterInQuery, VAL.value('Prompt[1]', 'VARCHAR(250)') AS ParameterPrompt, VAL.value('DynamicPrompt[1]', 'VARCHAR(250)') AS ParameterDynamic, VAL.value('PromptUser[1]', 'VARCHAR(250)') AS ParameterPromptUser, VAL.value('State[1]', 'VARCHAR(250)') AS ParameterState FROM TMP CROSS APPLY XMLParameter.nodes('//Parameters/Parameter') PARAMXML (VAL) |
Résultat :
SELECT DSR.Name AS DataSourceName, CASE WHEN DSR.Name IS NOT NULL THEN CAT.Name ELSE 'Shared Data Source' END AS DataSourceType, CLink.Name AS DataSource, CAT.Name AS ReportName FROM dbo.DataSource DSR INNER JOIN dbo.Catalog CAT ON DSR.ItemID = CAT.ItemID LEFT JOIN dbo.Catalog CLink ON Clink.ItemID = DSR.Link |
Résultat :
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as rdl), TMP AS ( SELECT CAT.Path, CONVERT(XML, CONVERT(VARBINARY(MAX), CAT.Content)) AS XMLColumn FROM dbo.Catalog CAT WHERE CAT.Type = 2 ) SELECT Path as ReportPath, n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') AS ReferencedReport FROM TMP CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x) |
A partir de SQL Server 2008 R2, la vue ExecutionLog3 permet d’avoir des informations concernant l’utilisation des rapports SSRS.
SELECT ItemPath, UserName, Format, ItemAction, TimeStart, TimeEnd, Status, SUM(TimeDataRetrieval) AS TimeDataRetrieval, SUM(TimeProcessing) AS TimeProcessing, SUM(TimeRendering) AS TimeRendering, SUM(ByteCount) AS ByteCount, SUM([RowCount]) AS [RowCount] FROM dbo.ExecutionLog3 GROUP BY ItemPath, UserName, Format, ItemAction, TimeStart, TimeEnd, Status ORDER BY TimeStart DESC |
Résultat :
Plus d’informations sur la vue ExecutionLog3 : https://msdn.microsoft.com/en-us/library/ms159110(v=sql.105).aspx
Il existe plusieurs solutions pour déployer des rapports SSRS : SSDT (BIDS auparavant), Report Manager ou encore l’utilitaire RS.exe.
Lorsque plusieurs rapports sont à déployer, SSDT est bien pratique, mais celui-ci n’est pas toujours installé sur l’environnement cible ou un environnement ayant accès à l’environnement cible. Report Manager quant à lui ne permet pas l’Upload de plusieurs rapports en une fois ! La solution est alors d’utiliser RS.exe.
Le script RSS suivant permet d’uploader les rapports d’un dossier local : OneDrive
Après avoir téléchargé le .RSS, il faut créer et modifier le .Bat :
rem Source : http://www.sqlblogspot.com/2014/03/ssrs-deploymentcomplete-automation2012.html rem 1 - Create a folder named “ProjectName” under this new folder rem 2 - copy all the reports, datasource and dataset into the “ProjectName” folder rem 3 - Modify 'ProjectName' Folder (Destination) rem 4 - Modify 'C:\ProjectName' Reports path set varServerPath=http://localhost/reportserver set varReportFolder=ProjectName set varDatasetFolder=ProjectName set varDataSourceFolder=ProjectName set varDataSourcePath=ProjectName set varReportName= set varReportFilePath=C:\ProjectName
rs.exe -i
|
Le script RSS a été réalisé par Nishar , plus d’informations sur celui-ci à l’adresse suivante : SSRS Deployment–Complete Automation–2012 & 2008 .
Dans cet article nous avons vu quelques requêtes T-SQL permettant de documenter une instance Reporting Services, ses rapports et sa configuration.
Nous avons aussi vu une requête basique permettant d’auditer l’utilisation des rapports, leurs temps d’exécution …
Enfin le script de déploiement présenté permet de gagner du temps sur l’Upload d’une solution.
Sans oublier le fait qu’avec quelques requêtes T-SQL et NodeXL, il est possible d’avoir une vision complète sur les différents liens entre les rapports SSRS !
Romain Casteres
Premier Field Engineer – SQL & BI & Big Data
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.