Find what queries are reports using on SSRS



  • EDIT: It seems they're using "semanticQuery" and this is why I cant see usefull information?

    enter image description here


    We will be decommissioning one of our functions, and we noticed that some reports are still using it.

    Is there a way to grab the "query" of a report, and then filter it?

    for example:

    SELECT REPORT_NAME, QUERY from Magic_DMV_report_queries where QUERY like '%dbo.badFunction%'
    

    I tried to find some dmvs and etc but with no success even trying to get some XML information.

    With this query, the "command" is not so "understandable" and I thought it would be:

        DECLARE @details AS TABLE
        (
            id INT IDENTITY(1,1) NOT NULL,
            Report              NVARCHAR(100), 
            ReportPath          NVARCHAR(500),
            DataSetName         NVARCHAR(50), 
            DataSourceName      NVARCHAR(100), 
            ComandType          NVARCHAR(50), 
            CommandText         NVARCHAR(MAX)
        )
    

    DECLARE @report xml
    DECLARE @reportname NVARCHAR(100), @reportpath NVARCHAR(500)
    DECLARE @query NVARCHAR(MAX)
    DECLARE db_cursor CURSOR FOR
    SELECT Name, Path ,CAST(cast(Content AS varbinary(max)) as XML)
    FROM Catalog
    WHERE Type = 2
    ORDER BY Name
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @reportname, @reportpath, @report
    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO @details
    SELECT
    @reportname AS Report,
    @reportpath,
    t.c.value('@Name[1]', 'nvarchar(50)') AS DataSetName,
    t.c.value('(./:Query/:DataSourceName)[1]', 'nvarchar(50)') AS DataSourceName,
    t.c.value('(./:Query/:CommandType)[1]', 'nvarchar(100)') AS CommandType,
    ISNULL(t.c.value('(./:Query/:CommandText)[1]', 'nvarchar(MAX)'), 'Shared - ' + t.c.value('(./:SharedDataSet/:SharedDataSetReference)[1]', 'nvarchar(MAX)')) AS CommandText
    FROM
    @report.nodes('/:Report/:DataSets/*:DataSet') AS t(c)
    FETCH NEXT FROM db_cursor INTO @reportname, @reportpath, @report
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor

    SELECT * FROM @details ORDER BY Report, DataSetName

    EIDT2:

    I think I did it:

    with CTE as
    (SELECT Name,
    cast(CAST(CAST(Content AS VARBINARY(MAX)) AS XML)as nvarchar(max)) AS ReportXML
    FROM Catalog
    where Type = 2)select * from cte 
    where ReportXML like '%My_Cool_Procedure_or_function%'
    


  • As J.D. says, you could search your SSRS solution or source control. Alternatively, if you have multiple SSRS solutions or projects, you could use the "Find in Files" Search option in Notepad++ (or a similar tool) to search the contents of all .rdl files in a folder or set of folders for the name of the function.

    If you do not have SSRS project (i.e. if your SSRS reports only live on the report server), then you can find some information on how to download them all https://stackoverflow.com/questions/46783093/download-all-ssrs-reports .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2