How to get a list of SubReports

Question

How to get a list of SubReports

Mel3405

SSCommitted

Points: 1904
More actions
April 13, 2015 at 11:08 am

#316633

I need to simply get a list of what RDL's are used as SubReports.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

as a proof of concept, i searched the xml of the RDL for the tab <subreport, and confirmed that this gets me pretty close to findingf the names of the sub reports;

there could be a one to many, so you probably need to use a pattern splitter to geth all subreports related to teh report in question

WITH MyCTE

AS

(

select convert(varchar(max),convert(varbinary(max),Content)) As TheRDL,* from ReportServer.dbo.[Catalog]

)

SELECT * FROM MyCTE

WHERE CHARINDEX('<subreport',TheRDL) > 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

this worked for me:

/*--results

t Name="DistributionCoderPerHour"><ReportName>Code...lots of XML follows

t Name="DistributionDataEntryPerHour"><ReportName>Da...lots of XML follows

*/

WITH MyCTE

AS

(

select convert(varchar(max),CONVERT(varbinary(max),Content))As StrContent,*

from ReportServer$MSSQLSERVER1.dbo.Catalog

--where name ='CPU - Dynamics UpLoad'

)

SELECT SUBSTRING(StrContent,p1.i + 9,p2.i - p1.i),* FROM MyCTE

CROSS APPLY(SELECT CHARINDEX('<SubReport',StrContent) As i) p1

CROSS APPLY(SELECT CHARINDEX('</SubReport>',StrContent) As i) p2

WHERE CHARINDEX('<SubReport',StrContent) > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!