April 1, 2015 at 9:55 am
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/
April 1, 2015 at 8:32 pm
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.
-- Itzik Ben-Gan 2001
April 2, 2015 at 6:15 am
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