Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SSRS – How to find all the reports that use one or more given columns?

Jan 21 2011 10:28AM by Jacob Sebastian   

In the past I have published a few queries that allows you to query SQL Server Reporting Services database – to retrieve specific information about various reports deployed in the Reporting Server Instance.

Recently, I got a question in my ASK forum who wanted to identify all the reports that use a specific column. I thought of writing a blog post about it so that it can be added to the SSRS query series and help other people with similar problems.

The RDL file that gets generated when you design a report, is an XML document. When you deploy the report on Reporting Server, the RDL file (which is an XML document) gets inserted into the Report Server database (named ReportServer by default). You can query this database to obtain the XML content of all the reports and then use XQuery to read specific information about each report.

The following query demonstrates how to read all the columns used by all the reports in the given SQL Server Reporting Server instance and search for a specific column. The query given below returns the report name and data source name of all reports that references a specified column name – which is passed as a parameter.

DECLARE @FieldToSearch VARCHAR(100)
SELECT @FieldToSearch = 'PatientNumber'

;WITH XMLNAMESPACES (
	DEFAULT 
	'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
	'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
	name,
	d.value('@Name[1]', 'VARCHAR(50)') AS DataSetName,
	df.value('@Name[1]', 'VARCHAR(50)') AS ReportFieldName,
	df.value('DataField[1]', 'VARCHAR(50)') AS DataFieldName
FROM (
	select name, 
	CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
	from ReportServer.dbo.Catalog
	WHERE Type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(d)
CROSS APPLY d.nodes('Fields/Field') f(df)
WHERE df.exist('DataField[ . = sql:variable("@FieldToSearch")]') = 1
/*
Name          DataSetName   ReportFieldName  DataFieldName
------------  ------------  ---------------  ---------------
Patient List  DSPatients    PatientID        PatientNumber
AR Details    DSPatientsAR  PatientNumber    PatientNumber
*/
The code given above is tested with SSRS 2005 SP2. It is expected to work on other versions, but I have not tested. If you find a problem running this query on other SSRS versions, let me know.

I would recommend you read the following posts as well:

  1. How to find all stored procedures used by Report Server?
  2. How to query report server to find out the data source used by one or more reports?

Tags: XML, XQuery, TSQL, SSRS, BI, SQLSERVER, BRH, #TSQL, #SQL Server, #BI,


Jacob Sebastian
1 · 100% · 32235
3
 
 
0
Refreshed
 
 
0
Incorrect



Submit

10  Comments  

  • Ive tested this on 2008 and changed report definition SCHEMA to http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition but I still do not get all the reports that uses the column Im looking for. Is there anything else I need to modify on the script for 2008 version?

    commented on Jan 21 2011 3:53AM
    Zee
    1029 · 0% · 25
  • If you can find out a report that is 'supposed to show up in the query results' and 'does not show up', please send me the report file definition. I can then figure out why it is not coming up in the query.

    commented on Jan 21 2011 4:41AM
    JacobSebastian
    47 · 4% · 1215
  • I reviewed your file and found that the column you are looking for is not actually used in the report data source. However, it is part of the query text. I posted an example that shows how to search the query text in the original post: http://beyondrelational.com/ask/jacob/questions/11/finding-all-reports-referencing-a-certain-fieldcolumn-on-report-server.aspx

    commented on Jan 23 2011 12:50AM
    Jacob Sebastian
    1 · 100% · 32235
  • In the where clause you have = sql:variable("@FieldToSearch") What is the syntax for a Like statement?

    commented on Feb 3 2011 3:17PM
    Stephen Yale
    408 · 0% · 100
  • For LIKE search, you can use contains(). Here is the modified version of the WHERE clause: WHERE df.exist('DataField[ contains(., sql:variable("@FieldToSearch"))]') = 1

    commented on Feb 3 2011 9:53PM
    JacobSebastian
    47 · 4% · 1215
  • Jacob, Your query works beautifully!

    To enhance the search, how would I search the query used in each report for a wildcard field? So I have 1000 reports and I would like to see how many times field 'widget1' is used in any report. Basically I want to see if field 'widget1' was used anywhere in any query to make a report.

    Thanks, Brady

    P.S. Tip for others, to be sure what version of report definition SCHEMA(http://schemas.microsoft.com) you need to use for your particular report simply open your rdl in notepad and you will see it in the very top.

    commented on Jun 22 2011 11:22AM
    Brady
    3073 · 0% · 2
  • Are you trying to figure out whether the field 'widget1' is used in a query used by a report, or whether that field is actually used in a report?

    commented on Jun 22 2011 9:27PM
    Jacob Sebastian
    1 · 100% · 32235
  • Is there a way to query all reports that have a specific text in the inline query? using (Like) Thanks

    commented on Nov 17 2011 9:49AM
    mnabil78
    3073 · 0% · 2
  • THANK-YOU SO MUCH FOR SHARING YOUR KNOWLEDGE TO THE WORLD!

    This is incredibly advanced code; I don’t know anyone who could develop this code. I joined this web-site just to let you know that I really appreciate your blog!

    Everything works for me but the report column names. I need to extract the report field names in addition to the data field names. When I run this code the data field names and report field names both result to the data field names. I’m using 2008. Any suggestions?

    ;WITH XMLNAMESPACES (
        DEFAULT 
        'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
        'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
    )
    SELECT
        name,
        d.value('@Name[1]', 'VARCHAR(50)') AS DataSetName,
    
        ??--df.value(('DataField[ . = ('@Name[1]', 'VARCHAR(50)') ]'), 'VARCHAR(50)')AS ReportFieldName,
    
    
        df.value('DataField[1]', 'VARCHAR(50)') AS DataFieldName
    FROM (
        select name, 
        CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
        from dbo.Catalog
        WHERE Type = 2
        AND [Path]like '/Policy_Reports%' ) a
    
    CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(d)
    CROSS APPLY d.nodes('Fields/Field') f(df)
    
    commented on Jun 15 2012 1:39PM
    abc123bre
    2382 · 0% · 5
  • @abc,

    What do you mean by report column names? Are you referring to the column headers in the output grid? It is helpful if you can send me an RDL file to jacob at beyondrelational.com and show me what is the output you are expecting.

    commented on Jun 15 2012 11:36PM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

"SSRS – How to find all the reports that use one or more given columns?" rated 5 out of 5 by 3 readers
SSRS – How to find all the reports that use one or more given columns? , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]