Rétro documentation SSRS et analyse des liens morts entre les rapports

Published Jan 15 2019 04:46 PM 454 Views
Microsoft
First published on MSDN on Sep 15, 2015

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.



1 - Introduction


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 :



  • Les objets gérés par le serveur comme les rapports, les rapports liés, les sources de données, les modèles de rapports, les dossiers … ainsi que toutes les propriétés et les paramètres de sécurité associés à ces éléments.

  • Les définitions des souscriptions et leurs planifications.

  • Les Snapchots et l’historique des rapports

  • Les propriétés du système et les paramètres de sécurité

  • Les logs d’exécution

  • Les clés symétriques et connexions cryptées ainsi que les références des sources de données


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.




2 - Informations sur l’instance SSRS


2.1 - Informations sur l’instance



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.


2.2 - Informations sur la configuration 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


2.3 - Informations sur les utilisateurs et leurs rôles



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 :



2.4 – Informations sur l’utilisation du cache



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




2.5 – Informations sur les souscriptions et leurs planifications



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




3 - Informations sur les rapports


3.1 - Liste des rapports et leurs paramètres



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 :



  • Le champ XMLRdl affiche l’ensemble du .RDL au format XML

  • Le champ Path détermine l’emplacement du rapport par rapport au dossier racine

  • Le champ Hidden détermine si le rapport sera caché ou non dans la liste des objets dans Report manager


En effet il est possible de cacher à l’utilisateur des objets dans Report manager :



3.2 – Liens morts entre les rapports


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 :



  • Vertex 1 correspond au rapport contenant le lien vers le rapport Vertex 2

  • La grosseur du lien correspond au nombre de liens présents dans le rapport. Exemple le rapport « CM-Main » contient 8 liens vers le rapport « CM-Dashboard Hierarchy all Levels – Summary ».

  • La direction de la flèche indique le lien entre les rapports. Exemple le rapport « CM-main » contient un lien vers le rapport « CM-Dashboard SQL » et vice versa.

  • Le format de la flèche en continue ou en pointillé dépend de l’existence du rapport. Exemple le rapport « CM-Main » contient un lien vers le rapport « CM-Bad » or celui-ci n’existe pas.




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, …



3.3 – Informations sur les paramètres d’un rapport



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 :




3.4 - Liste des Data Sources et leurs rapports



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 :



3.5 - Liste des sous rapports



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)




4 – Analyse des Logs


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



5 – Déploiement


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
Commonscript.rss -s %varServerPath% -v
ReportFolder="%varReportFolder%" -v DataSetFolder="%varDatasetFolder%"
-v DataSourceFolder="%varDataSourceFolder%" -v
DataSourcePath="%varDataSourcePath%" -v
ReportName="%varReportName%"
-v filePath="%varReportFilePath%" -e Mgmt2010



Le script RSS a été réalisé par Nishar , plus d’informations sur celui-ci à l’adresse suivante : SSRS Deployment–Complete Automation–2012 & 2008 .



6 – Conclusion


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


%3CLINGO-SUB%20id%3D%22lingo-sub-318654%22%20slang%3D%22en-US%22%3ER%C3%A9tro%20documentation%20SSRS%20et%20analyse%20des%20liens%20morts%20entre%20les%20rapports%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318654%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Sep%2015%2C%202015%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3ELe%20nombre%20de%20rapports%20SSRS%20ne%20cesse%20d%E2%80%99augmenter%20dans%20les%20diff%C3%A9rentes%20soci%C3%A9t%C3%A9s%20que%20je%20rencontre%2C%20cependant%20le%20nombre%20de%20projets%20document%C3%A9s%20reste%20tr%C3%A8s%20limit%C3%A9.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EL%E2%80%99objet%20de%20cet%20article%20est%20de%20vous%20fournir%20un%20certain%20nombre%20de%20requ%C3%AAtes%20vous%20permettant%20de%20g%C3%A9n%C3%A9rer%20une%20documentation%20sur%20vos%20projets%20Reporting%20Services.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EL%E2%80%99id%C3%A9e%20de%20cet%20article%20m%E2%80%99est%20venue%20en%20analysant%20les%20N%20rapports%20d%E2%80%99un%20projet.%20Voici%20les%20quelques%20probl%C3%A9matiques%26nbsp%3Bauxquelles%20j'ai%20%C3%A9t%C3%A9%20confront%C3%A9es%26nbsp%3B%3A%20Retrouver%20les%20liens%20morts%20entre%20les%20diff%C3%A9rents%20rapports%2C%20documenter%20la%20solution%20et%20simplifier%20les%20d%C3%A9ploiements%20ainsi%20que%20la%20configuration%20des%20rapports.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1674403152%22%20id%3D%22toc-hId-1732440576%22%3E1%20-%20Introduction%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EA%20l%E2%80%99installation%20de%20Reporting%20Services%2C%20deux%20bases%20de%20donn%C3%A9es%20sont%20install%C3%A9es%26nbsp%3BReportServer%26nbsp%3Bet%20ReportServerTempDB.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELa%20base%20de%20donn%C3%A9es%20ReportServer%26nbsp%3Bstocke%20le%20contenu%20suivant%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ELes%20objets%20g%C3%A9r%C3%A9s%20par%20le%20serveur%20comme%20les%20rapports%2C%20les%20rapports%20li%C3%A9s%2C%20les%20sources%20de%20donn%C3%A9es%2C%20les%20mod%C3%A8les%20de%20rapports%2C%20les%20dossiers%20%E2%80%A6%20ainsi%20que%20toutes%20les%20propri%C3%A9t%C3%A9s%20et%20les%20param%C3%A8tres%20de%20s%C3%A9curit%C3%A9%20associ%C3%A9s%20%C3%A0%20ces%20%C3%A9l%C3%A9ments.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELes%20d%C3%A9finitions%20des%20souscriptions%20et%20leurs%20planifications.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELes%20Snapchots%20et%20l%E2%80%99historique%20des%20rapports%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELes%20propri%C3%A9t%C3%A9s%20du%20syst%C3%A8me%20et%20les%20param%C3%A8tres%20de%20s%C3%A9curit%C3%A9%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELes%20logs%20d%E2%80%99ex%C3%A9cution%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELes%20cl%C3%A9s%20sym%C3%A9triques%20et%20connexions%20crypt%C3%A9es%20ainsi%20que%20les%20r%C3%A9f%C3%A9rences%20des%20sources%20de%20donn%C3%A9es%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ELa%20base%20de%20donn%C3%A9es%20ReportServerTempDB%20quant%20%C3%A0%20elle%20g%C3%A8re%20les%20donn%C3%A9es%20temporaires%20li%C3%A9es%20aux%20sessions%2C%20au%20cache%20des%20donn%C3%A9es%2C%20les%20tables%20de%20travail%20temporaires.%20Un%20processus%20automatique%20s%E2%80%99occupe%20de%20supprimer%20les%20anciens%20%C3%A9l%C3%A9ments%20inutilis%C3%A9s%20de%20cette%20base.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAfin%20de%20documenter%20l%E2%80%99environnement%20SSRS%2C%20sa%20configuration%2C%20les%20diff%C3%A9rents%20rapports%20publi%C3%A9s%20ainsi%20que%20les%20souscriptions%20nous%20effectuerons%20des%20requi%C3%AAtes%26nbsp%3Bsur%20les%20diff%C3%A9rentes%20tables%20de%20la%20base%20ReportServer.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68358i9B2EFE75CB46DDE5%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--877753809%22%20id%3D%22toc-hId--819716385%22%3E2%20-%20Informations%20sur%20l%E2%80%99instance%20SSRS%3C%2FH2%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-668543021%22%20id%3D%22toc-hId-726580445%22%3E%3CSTRONG%3E%202.1%20-%20Informations%20sur%20l%E2%80%99instance%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EMachineName%2C%20%3CBR%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EInstanceName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EClient%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20dbo.Keys%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20MachineName%20IS%20NOT%20NULL%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ERemarque%26nbsp%3B%3A%20La%20table%20Keys%20contient%20aussi%20les%20informations%20sur%20les%20cl%C3%A9s%20de%20chiffrement%20et%20sur%20l%E2%80%99installation%20de%20l%E2%80%99instance.%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1883613940%22%20id%3D%22toc-hId--1825576516%22%3E%3CSTRONG%3E%202.2%20-%20Informations%20sur%20la%20configuration%20de%20l%E2%80%99instance%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EValue%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20dbo.ConfigurationInfo%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68359i9E734EC0431AC925%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CBR%20%2F%3E%20Voici%20la%20liste%20des%20diff%C3%A9rents%20param%C3%A8tres%26nbsp%3B%3A%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb934303.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fbb934303.aspx%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--140803605%22%20id%3D%22toc-hId--82766181%22%3E%3CSTRONG%3E%202.3%20-%20Informations%20sur%20les%20utilisateurs%20et%20leurs%20r%C3%B4les%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.name%26nbsp%3B%20AS%20ReportName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUSR.username%26nbsp%3B%20AS%20UserName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUSR.authtype%26nbsp%3B%20AS%20AuthType%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EROL.rolename%26nbsp%3B%20AS%20RoleName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EROL.Description%20AS%20Description%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20dbo.users%20USR%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20dbo.policyuserrole%26nbsp%3BPLU%20ON%20USR.userid%20%3D%20PLU.userid%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20dbo.policies%20POL%20ON%20POL.policyid%20%3D%20PLU.policyid%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20dbo.roles%20ROL%20ON%20ROL.roleid%20%3D%20PLU.roleid%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20dbo.catalog%20CAT%20ON%20CAT.policyid%20%3D%20POL.policyid%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20CAT.TYPE%20%3D%202%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68360iF98070332F8AFD11%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-1602006730%22%20id%3D%22toc-hId-1660044154%22%3E%3CSTRONG%3E%202.4%20%E2%80%93%20Informations%20sur%20l%E2%80%99utilisation%20du%20cache%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.name%20AS%20ReportName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAC.cacheexpiration%20AS%20CacheExpiration%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAC.expirationflags%20AS%20ExpirationFlags%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edbo.cachepolicy%20CAC%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20dbo.catalog%20CAT%20ON%20CAT.itemid%20%3D%20CAC.reportid%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--950150231%22%20id%3D%22toc-hId--892112807%22%3E%3CSTRONG%3E%20%3CSTRONG%3E%202.5%20%E2%80%93%20Informations%20sur%20les%20souscriptions%20et%20leurs%20planifications%20%3C%2FSTRONG%3E%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.Name%20AS%20ReportName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUB.Description%20AS%20SubscriptionName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUB.EventType%20AS%20EventType%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUB.LastStatus%20AS%20LastStatus%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUB.LastRunTime%20AS%20LastRunTime%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUB.Parameters%20AS%20Parameters%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESCH.Name%20AS%20ScheduleName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESCH.Type%20AS%20ScheduleType%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESCH.EventType%20AS%20ScheduleEventType%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edbo.Subscriptions%20SUB%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20Catalog%20CAT%20ON%20SUB.Report_OID%20%3D%20CAT.ItemID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20ReportSchedule%20RSC%20ON%20RSC.ReportID%20%3D%20CAT.ItemID%20AND%20RSC.SubscriptionID%20%3D%20SUB.SubscriptionID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20Schedule%20SCH%20ON%20RSC.ScheduleID%20%3D%20SCH.ScheduleID%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-989173609%22%20id%3D%22toc-hId-1047211033%22%3E3%20-%20Informations%20sur%20les%20rapports%3C%2FH2%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1759496857%22%20id%3D%22toc-hId--1701459433%22%3E%3CSTRONG%3E%203.1%20-%20Liste%20des%20rapports%26nbsp%3Bet%20leurs%20param%C3%A8tres%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EName%20AS%20ReportName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPath%20AS%20ReportPath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECreationDate%20AS%20CreationDate%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EModifiedDate%20AS%20ModifiedDate%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EProperty%20AS%20Property%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHidden%20AS%20Hidden%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EParameter%20AS%20Parameter%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EConvert(XML%2C(Convert(VARBINARY(MAX)%2CContent)))%20AS%20XMLRdl%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20dbo.Catalog%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20Type%20%3D%202%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68361i4F66310D8F92DB70%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERemarques%26nbsp%3B%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ELe%20champ%20XMLRdl%20affiche%20l%E2%80%99ensemble%20du%20.RDL%20au%20format%20XML%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELe%20champ%20Path%20d%C3%A9termine%20l%E2%80%99emplacement%20du%20rapport%20par%20rapport%20au%20dossier%20racine%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELe%20champ%20Hidden%20d%C3%A9termine%20si%20le%20rapport%20sera%20cach%C3%A9%20ou%20non%20dans%20la%20liste%20des%20objets%20dans%20Report%20manager%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EEn%20effet%20il%20est%20possible%20de%20cacher%20%C3%A0%20l%E2%80%99utilisateur%20des%20objets%20dans%20Report%20manager%26nbsp%3B%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68362iB84EC0E5CD1C390F%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--16686522%22%20id%3D%22toc-hId-41350902%22%3E%3CSTRONG%3E%203.2%20%E2%80%93%20Liens%20morts%20entre%20les%20rapports%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3EUne%20probl%C3%A9matique%20r%C3%A9currente%20%C3%A0%20SSRS%26nbsp%3B%3A%20%C2%AB%26nbsp%3BJ%E2%80%99ai%20une%20centaine%20de%20rapports%20dans%20mon%20projet%2C%20certains%20rapports%20ont%20chang%C3%A9s%20de%20noms%2C%20%E2%80%A6%20Comment%20retrouver%20les%20liens%20morts%20entre%20les%20diff%C3%A9rents%20rapports%20%3F%26nbsp%3B%C2%BB%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVoici%20la%20solution%26nbsp%3B%3A%3C%2FP%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3BWITH%20XMLNAMESPACES%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDEFAULT%20'%3CA%20href%3D%22http%3A%2F%2Fschemas.microsoft.com%2Fsqlserver%2Freporting%2F2008%2F01%2Freportdefinition%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Fschemas.microsoft.com%2Fsqlserver%2Freporting%2F2008%2F01%2Freportdefinition%3C%2FA%3E'%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E'%3CA%20href%3D%22http%3A%2F%2Fschemas.microsoft.com%2FSQLServer%2Freporting%2Freportdesigner'%26nbsp%3BAS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Fschemas.microsoft.com%2FSQLServer%2Freporting%2Freportdesigner'%26nbsp%3BAS%3C%2FA%3E%20rd%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%2C%20ReportInfo%20AS%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.Path%20AS%20ReportPath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.name%20AS%20ReportName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETAB.value('data(ReportName)%5B1%5D'%2C%20'varchar(255)')%20AS%20Link%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EREPLACE(CAT.Path%2C%20CAT.name%2C%20'')%20%2B%20TAB.value('data(ReportName)%5B1%5D'%2C%20'varchar(255)')%20AS%20PathLink%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edbo.catalog%20CAT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECROSS%20APPLY%20(SELECT%20CONVERT(XML%2C%20CONVERT(VARBINARY(MAX)%2C%20content)))%20AS%20REP%20(reportxml)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECROSS%20APPLY%26nbsp%3BREP.reportxml.nodes('%2F%2F*%3ATextbox%2FActionInfo%2FActions%2FAction%2FDrillthrough')%20NOD%20(TAB)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%26nbsp%3BCAT.Type%20%3D%202%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EReportPath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EReportName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELink%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPathLink%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECOUNT(1)%20AS%20NbLinks%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20ReportInfo%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20PathLink%20NOT%20IN%20(SELECT%20path%20FROM%20dbo.catalog)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGROUP%20BY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereportpath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereportname%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELink%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPathLink%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EORDER%20BY%20reportpath%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68363iB06754DB02797DCC%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERemarques%26nbsp%3B%3A%20Ici%20le%20rapport%20%C2%AB%26nbsp%3BProduct%20Line%20Sales%26nbsp%3B%C2%BB%20contient%20un%20lien%20(NbLinks)%20vers%20le%20rapport%20%C2%AB%26nbsp%3BEmployee%20Sales%20Summary%20SQL2008R2%26nbsp%3B%C2%BB%20situ%C3%A9%20dans%20le%20dossier%20%C2%AB%26nbsp%3B%2FAdventureWorks%20Sample%20Reports%2F%26nbsp%3B%C2%BB%20qui%20n%E2%80%99existe%20pas%20ou%20plus.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EL%E2%80%99Addin%20Excel%20%3CA%20href%3D%22http%3A%2F%2Fnodexl.codeplex.com%2F%22%20title%3D%22nodexl%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20NodeXL%20%3C%2FA%3E%20(Excel%202007%20%C3%A0%202016)%20n%E2%80%99est%20plus%26nbsp%3B%C3%A0%20pr%C3%A9senter%2C%20il%20permet%20d%E2%80%99analyser%20sous%20forme%20de%20graph%20tout%20type%20de%20donn%C3%A9es.%20(Mon%20article%20de%20pr%C3%A9sentation%20de%20NodeXL%26nbsp%3B%3A%20%3CA%20href%3D%22http%3A%2F%2Fwww.pulsweb.fr%2Fnodexl%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fwww.pulsweb.fr%2Fnodexl%2F%20%3C%2FA%3E%20).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVoici%20le%20r%C3%A9sultat%20d%E2%80%99une%20analyse%20sur%20une%20partie%20des%20rapports%20d%E2%80%99un%20projet%20SSRS%26nbsp%3B%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68364iA0409D61E62D8256%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CBR%20%2F%3E%20Analyse%26nbsp%3B%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EVertex%201%20correspond%20au%20rapport%20contenant%20le%20lien%20vers%20le%20rapport%20Vertex%202%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELa%20grosseur%20du%20lien%20correspond%20au%20nombre%20de%20liens%20pr%C3%A9sents%20dans%20le%20rapport.%20Exemple%20le%20rapport%20%C2%AB%26nbsp%3BCM-Main%26nbsp%3B%C2%BB%20contient%208%20liens%20vers%20le%20rapport%20%C2%AB%26nbsp%3BCM-Dashboard%20Hierarchy%20all%20Levels%20%E2%80%93%20Summary%26nbsp%3B%C2%BB.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELa%20direction%20de%20la%20fl%C3%A8che%20indique%20le%20lien%20entre%20les%20rapports.%20Exemple%20le%20rapport%20%C2%AB%26nbsp%3BCM-main%26nbsp%3B%C2%BB%20contient%20un%20lien%20vers%20le%20rapport%20%C2%AB%26nbsp%3BCM-Dashboard%20SQL%26nbsp%3B%C2%BB%20et%20vice%20versa.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELe%20format%20de%20la%20fl%C3%A8che%20en%26nbsp%3Bcontinue%20ou%20en%20pointill%C3%A9%20d%C3%A9pend%20de%20l%E2%80%99existence%20du%20rapport.%20Exemple%20le%20rapport%20%C2%AB%26nbsp%3BCM-Main%26nbsp%3B%C2%BB%20contient%20un%20lien%20vers%20le%20rapport%20%C2%AB%26nbsp%3BCM-Bad%26nbsp%3B%C2%BB%26nbsp%3Bor%20celui-ci%20n%E2%80%99existe%20pas.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68365i71CE2879597DD86C%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECombiner%20le%20r%C3%A9sultat%20de%20la%20pr%C3%A9c%C3%A9dente%20requ%C3%AAte%20avec%20NodeXL%20permet%20d%E2%80%99avoir%20une%20vision%20compl%C3%A8te%20des%20relations%20entre%20les%20diff%C3%A9rents%20rapports%20d%E2%80%99un%20projet%2C%20de%20v%C3%A9rifier%20qu%E2%80%99il%20n%E2%80%99y%20a%20pas%20de%20rapport%20isol%C3%A9%20(sans%20aucun%20lien%20allant%20vers%20celui-ci)%2C%20d%E2%80%99analyser%20les%20liens%20morts%2C%20%E2%80%A6%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-1010962524%22%20id%3D%22toc-hId-1068999948%22%3E%3CSTRONG%3E%203.3%20%E2%80%93%20Informations%20sur%20les%20param%C3%A8tres%20d%E2%80%99un%20rapport%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EWITH%20TMP%20AS%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.Name%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECONVERT(XML%2CCAT.Parameter)%20AS%20XMLParameter%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%26nbsp%3Bdbo.Catalog%26nbsp%3BCAT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%26nbsp%3BCAT.Type%26nbsp%3B%20%3D%202%20AND%20CAT.Name%20%3D%20'Product%20Line%20Sales'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('Name%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('Type%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterType%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('Nullable%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterNull%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('AllowBlank%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterBlank%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('MultiValue%5B1%5D'%2C%20'VARCHAR(250)')%20AS%26nbsp%3BParameterMultiValue%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('UsedInQuery%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterInQuery%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('Prompt%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterPrompt%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('DynamicPrompt%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterDynamic%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('PromptUser%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterPromptUser%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVAL.value('State%5B1%5D'%2C%20'VARCHAR(250)')%20AS%20ParameterState%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20TMP%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECROSS%20APPLY%20XMLParameter.nodes('%2F%2FParameters%2FParameter')%20PARAMXML%20(VAL)%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68366iB08FE87918F1784D%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1541194437%22%20id%3D%22toc-hId--1483157013%22%3E%3CSTRONG%3E%203.4%20-%20Liste%20des%20Data%20Sources%20et%20leurs%20rapports%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDSR.Name%20AS%20DataSourceName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECASE%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHEN%20DSR.Name%20IS%20NOT%20NULL%20THEN%20CAT.Name%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EELSE%20'Shared%20Data%20Source'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEND%20AS%20DataSourceType%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLink.Name%20AS%20DataSource%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.Name%20AS%20ReportName%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edbo.DataSource%20DSR%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EINNER%20JOIN%20dbo.Catalog%20CAT%20ON%20DSR.ItemID%20%3D%20CAT.ItemID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELEFT%20JOIN%20dbo.Catalog%20CLink%20ON%20Clink.ItemID%20%3D%20DSR.Link%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68367iC3DAFB8F49E5E090%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-201615898%22%20id%3D%22toc-hId-259653322%22%3E%3CSTRONG%3E%203.5%20-%20Liste%20des%20sous%20rapports%20%3C%2FSTRONG%3E%3C%2FH3%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EWITH%20XMLNAMESPACES%20('%3CA%20href%3D%22http%3A%2F%2Fschemas.microsoft.com%2Fsqlserver%2Freporting%2F2008%2F01%2Freportdefinition'%26nbsp%3Bas%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Fschemas.microsoft.com%2Fsqlserver%2Freporting%2F2008%2F01%2Freportdefinition'%26nbsp%3Bas%3C%2FA%3E%20rdl)%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETMP%20AS%20(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECAT.Path%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECONVERT(XML%2C%20CONVERT(VARBINARY(MAX)%2C%20CAT.Content))%20AS%20XMLColumn%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20dbo.Catalog%20CAT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20CAT.Type%26nbsp%3B%20%3D%202%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPath%20as%20ReportPath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3En.x.value('(%2F%2Frdl%3AReportName)%5B1%5D'%2C%20'nvarchar(256)')%26nbsp%3BAS%20ReferencedReport%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20TMP%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECROSS%20APPLY%20xmlColumn.nodes('%2F%2Frdl%3ASubreport')%20n(x)%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--553589704%22%20id%3D%22toc-hId--495552280%22%3E4%20%E2%80%93%20Analyse%20des%20Logs%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EA%20partir%20de%20SQL%20Server%202008%20R2%2C%20la%20vue%20ExecutionLog3%20permet%20d%E2%80%99avoir%20des%20informations%20concernant%20l%E2%80%99utilisation%20des%20rapports%20SSRS.%3C%2FP%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESELECT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EItemPath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUserName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFormat%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EItemAction%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETimeStart%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETimeEnd%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EStatus%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUM(TimeDataRetrieval)%20AS%20TimeDataRetrieval%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUM(TimeProcessing)%20AS%20TimeProcessing%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUM(TimeRendering)%20AS%20TimeRendering%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUM(ByteCount)%20AS%20ByteCount%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESUM(%5BRowCount%5D)%20AS%20%5BRowCount%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFROM%20dbo.ExecutionLog3%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGROUP%20BY%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EItemPath%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EUserName%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFormat%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EItemAction%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETimeStart%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETimeEnd%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EStatus%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EORDER%20BY%20TimeStart%20DESC%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ER%C3%A9sultat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68368i98EEA727BBC04A63%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPlus%20d%E2%80%99informations%20sur%20la%20vue%20ExecutionLog3%26nbsp%3B%3A%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms159110(v%3Dsql.105).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms159110(v%3Dsql.105).aspx%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1189220631%22%20id%3D%22toc-hId-1247258055%22%3E5%26nbsp%3B%E2%80%93%20D%C3%A9ploiement%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EIl%20existe%20plusieurs%20solutions%20pour%20d%C3%A9ployer%20des%20rapports%20SSRS%26nbsp%3B%3A%20SSDT%20(BIDS%20auparavant)%2C%20Report%20Manager%20ou%20encore%20l%E2%80%99utilitaire%20RS.exe.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELorsque%20plusieurs%20rapports%20sont%20%C3%A0%20d%C3%A9ployer%2C%20SSDT%20est%20bien%20pratique%2C%20mais%20celui-ci%20n%E2%80%99est%20pas%20toujours%20install%C3%A9%20sur%20l%E2%80%99environnement%20cible%20ou%20un%20environnement%20ayant%20acc%C3%A8s%20%C3%A0%20l%E2%80%99environnement%20cible.%20Report%20Manager%20quant%20%C3%A0%20lui%20ne%20permet%20pas%20l%E2%80%99Upload%20de%20plusieurs%20rapports%20en%20une%20fois%26nbsp%3B!%20La%20solution%20est%20alors%20d%E2%80%99utiliser%20RS.exe.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELe%20script%20RSS%20suivant%26nbsp%3Bpermet%20d%E2%80%99uploader%20les%20rapports%20d%E2%80%99un%20dossier%20local%20%3A%20%3CA%20href%3D%22https%3A%2F%2Fonedrive.live.com%2F%3Fcid%3D0363B73C0DA07C62%26amp%3Bid%3D363B73C0DA07C62%2521159%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20OneDrive%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EApr%C3%A8s%20avoir%20t%C3%A9l%C3%A9charg%C3%A9%20le%20.RSS%2C%20il%20faut%20cr%C3%A9er%20et%20modifier%20le%20.Bat%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3Erem%20Source%20%3A%20%3CA%20href%3D%22http%3A%2F%2Fwww.sqlblogspot.com%2F2014%2F03%2Fssrs-deploymentcomplete-automation2012.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.sqlblogspot.com%2F2014%2F03%2Fssrs-deploymentcomplete-automation2012.html%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erem%201%20-%20Create%20a%20folder%20named%20%E2%80%9CProjectName%E2%80%9D%20under%20this%20new%20folder%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erem%202%20-%20copy%20all%20the%20reports%2C%20datasource%20and%20dataset%20into%20the%20%E2%80%9CProjectName%E2%80%9D%20folder%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erem%203%20-%20Modify%20'ProjectName'%20Folder%20(Destination)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Erem%204%20-%20Modify%20'C%3A%5CProjectName'%20Reports%20path%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varServerPath%3Dhttp%3A%2F%2Flocalhost%2Freportserver%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varReportFolder%3DProjectName%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varDatasetFolder%3DProjectName%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varDataSourceFolder%3DProjectName%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varDataSourcePath%3DProjectName%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varReportName%3D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20varReportFilePath%3DC%3A%5CProjectName%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ers.exe%20-i%20%3CBR%20%2F%3E%20Commonscript.rss%20-s%20%25varServerPath%25%20-v%20%3CBR%20%2F%3E%20ReportFolder%3D%22%25varReportFolder%25%22%20-v%20DataSetFolder%3D%22%25varDatasetFolder%25%22%20%3CBR%20%2F%3E%20-v%20DataSourceFolder%3D%22%25varDataSourceFolder%25%22%20-v%20%3CBR%20%2F%3E%20DataSourcePath%3D%22%25varDataSourcePath%25%22%20-v%20%3CBR%20%2F%3E%20ReportName%3D%22%25varReportName%25%22%20%3CBR%20%2F%3E%20-v%20filePath%3D%22%25varReportFilePath%25%22%20-e%20Mgmt2010%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CP%3ELe%20script%20RSS%20a%20%C3%A9t%C3%A9%20r%C3%A9alis%C3%A9%20par%20%3CA%20href%3D%22https%3A%2F%2Fwww.blogger.com%2Fprofile%2F03151449590672654447%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Nishar%20%3C%2FA%3E%20%2C%26nbsp%3Bplus%20d%E2%80%99informations%20sur%20celui-ci%26nbsp%3B%C3%A0%20l%E2%80%99adresse%20suivante%26nbsp%3B%3A%20%3CA%20href%3D%22http%3A%2F%2Fwww.sqlblogspot.com%2F2014%2F03%2Fssrs-deploymentcomplete-automation2012.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20SSRS%20Deployment%E2%80%93Complete%20Automation%E2%80%932012%20%26amp%3B%202008%20%3C%2FA%3E%20.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--1362936330%22%20id%3D%22toc-hId--1304898906%22%3E6%26nbsp%3B%E2%80%93%20Conclusion%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EDans%20cet%20article%20nous%20avons%20vu%20quelques%20requ%C3%AAtes%20T-SQL%20permettant%20de%20documenter%20une%20instance%20Reporting%20Services%2C%20ses%20rapports%20et%20sa%20configuration.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENous%20avons%20aussi%20vu%20une%20requ%C3%AAte%20basique%20permettant%20d%E2%80%99auditer%20l%E2%80%99utilisation%20des%20rapports%2C%20leurs%20temps%20d%E2%80%99ex%C3%A9cution%20%E2%80%A6%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEnfin%20le%20script%20de%20d%C3%A9ploiement%20pr%C3%A9sent%C3%A9%20permet%20de%20gagner%20du%20temps%20sur%20l%E2%80%99Upload%20d%E2%80%99une%20solution.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESans%20oublier%20le%20fait%20qu%E2%80%99avec%20quelques%20requ%C3%AAtes%20T-SQL%20et%20NodeXL%2C%20il%20est%20possible%20d%E2%80%99avoir%20une%20vision%20compl%C3%A8te%20sur%20les%20diff%C3%A9rents%20liens%20entre%20les%20rapports%20SSRS%26nbsp%3B!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68369i1128888F31B48B60%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Romain%20Casteres%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPremier%20Field%20Engineer%20%E2%80%93%20SQL%20%26amp%3B%20BI%20%26amp%3B%20Big%20Data%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318654%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Sep%2015%2C%202015%20Le%20nombre%20de%20rapports%20SSRS%20ne%20cesse%20d%E2%80%99augmenter%20dans%20les%20diff%C3%A9rentes%20soci%C3%A9t%C3%A9s%20que%20je%20rencontre%2C%20cependant%20le%20nombre%20de%20projets%20document%C3%A9s%20reste%20tr%C3%A8s%20limit%C3%A9.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EReporting%20Services(SSRS)%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 04:46 PM
Updated by: