Business Geek

Pour les besoins d’une étude d’impact avant une migration, j’ai besoin de récupérer la liste des rapports et la base dans laquelle ils vont piocher leurs données.

Evidemment, quand on a plusieurs centaines de rapports et des dizaines de DataSources accumulés depuis des années, on oublie vite l’idée de faire le référencement à la main.
Surtout quand on sait qu’on a tout dans la base de données de Reporting Services (ReportServer).

J’ai donc écrit une requête qui pioche les informations dans la table dbo.Catalog.
Attention, il y a:

• du type XML à la toque pour creuser dans la définition RDL
• des CTE pour simplifier (ou presque)

Je ne vais pas m’étendre sur une explication de texte. Sachez juste que la table stocke le RDL (format XML de définition du rapport) dans le champ Content (de type binaire).
Avec quelques requêtes XQuery, on récupère le nom de la source de données partagées ou de la chaîne de connexion (pour de l’embedded).
Evidemment, j’exclue les rapports liés.

WITH 
datasources	AS
(
	SELECT 
		[Path], [Name], ItemId,
		CAST(CAST(content as varbinary(max)) as xml).value('/*:DataSourceDefinition[1]/*:Extension[1]', 'varchar(100)') as Provider,
		CAST(CAST(content as varbinary(max)) as xml).value('/*:DataSourceDefinition[1]/*:ConnectString[1]', 'varchar(100)') as ConnectionString
	FROM dbo.Catalog 
	WHERE [Type] = 5
)
,reports AS
(
	SELECT 
		ItemID, 
		Path, Name
		,CAST(CAST(content as varbinary(max)) as xml) as XmlDefinition
	FROM dbo.Catalog
	WHERE [Type] = 2
)
SELECT
	reports.ItemId, reports.[Path], reports.Name, 
	T2.ds.value('(.//*:DataSourceReference)[1]', 'varchar(200)') as DataSourceReference,
	COALESCE(T2.ds.value('(.//*:ConnectString)[1]', 'varchar(200)'),datasources.ConnectionString) as ConnectionString,
	COALESCE(T2.ds.value('(.//*:DataProvider)[1]', 'varchar(200)'),datasources.Provider) as DataProvider
FROM reports
	CROSS APPLY XmlDefinition.nodes('/*:Report/*:DataSources/*:DataSource') as T2(ds)
LEFT JOIN datasources
	ON datasources.Name = T2.ds.value('(.//*:DataSourceReference)[1]', 'varchar(200)') 

PS : après avoir galéré avec les namespaces qui changent entre les version de Reporting Services et de Report Builder, j’ai opté pour la syntaxe wildcard (ex : .//*:DataSourceReference)