Where do I find the Report Path?

  • Reporting services is stored on one Server and the Database is on another Server.

    How do I find the Report path?

    ReportPath

    /USFR/LMS/EOYStatus

    /USFR/LMS/EOYStatus

    /USFR/LMS/EOYStatus

    /USFR/LMS/LS_ActivityOverlap

    /USFR/LMS/LS_ActivityOverlap

    /USFR/LMS/Timbersale_And_RSS_Status_Differences

    /USFR/LMS/TS-Activity

    /USFR/LMS/TS-Activity

    /USFR/LMS/TS-Activity

    /USFR/LMS/TS-Activity

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I recently rolled off a project where we needed to extract some information from the report server catalog (e.g. the reportserver db). This function will includes the report path to the report.

    IF EXISTS

    (

    SELECT 1

    FROM information_schema.routines

    WHERE routine_type = 'FUNCTION'

    AND routine_schema = 'dbo' AND routine_name = 'ssrs_report_info'

    )

    DROP FUNCTION dbo.ssrs_report_info

    GO

    CREATE FUNCTION dbo.ssrs_report_info(@path varchar(200))

    -- for the path variable you must include the /'s in front and back (e.g. /quest_2/)

    RETURNS TABLE

    RETURN

    WITH cte AS

    (

    SELECT -- Do All aliases

    [path],

    [name] AS Report_Name,

    creationDate,

    ModifiedDate,

    CONVERT(XML, CONVERT(VARBINARY(MAX), content)) AS rdl

    FROM reportserver.dbo.[catalog]

    WHERE Type=2 AND ([Path] LIKE @path+'%' OR (@path IS NULL OR @path = '/'))

    )

    SELECT TOP 2000000000 -- required for the order by clause, helps get higher cardinality est.

    ReportInfo_id = row_number() over (order by (select null)),

    ReportRDL = rdl,

    Path AS Report_Path,

    LEFT([Path], LEN([path]) - CHARINDEX('/',REVERSE([Path])) + 1) AS Report_Folder,

    Report_Name,

    T2.N.value('(*:DataSourceName/text())[1]', 'nvarchar(128)') AS DataSourceName,

    T1.N.value('@Name', 'nvarchar(128)') AS DataSetName,

    ISNULL(T2.N.value('(*:CommandType/text())[1]', 'nvarchar(128)'), 'T-SQL') AS CommandType,

    T2.N.value('(*:CommandText/text())[1]', 'nvarchar(max)') AS CommandText,

    creationDate,

    ModifiedDate

    --, property, parameter

    FROM cte AS T

    CROSS APPLY T.rdl.nodes('/*:Report/*:DataSets/*:DataSet') AS T1 (N)

    CROSS APPLY T1.N.nodes('*:Query') AS T2 (N)

    --ORDER BY Report_Path, Report_Name, DataSetName, DataSourceName, CommandType, CommandText

    -- use the new query/flwor technique to eliminate the cross-apply (double-check paralellism), or just query method

    -- use a copy of the ssrs db with xml unpacked and indexes applied

    GO

    I can't test this at the moment because I don't have a populated reportserver DB handy but I think this will get you what you need.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks Alan!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply