Find what queries are reports using on SSRS

Question

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

---

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%'

Answer 1

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 here.