x

Any scripts available to quickly generate Report about SSRS reports/permissions/location on server

Hi,

Can some one point me to the right direction of finding a script which generates a report which includes all reporting services reports and the permissions they use along with their location on the server.

Thanks in advance, S

more ▼

asked Jun 08, 2010 at 10:44 AM in Default

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

Report data is stored in the table named dbo.Catalog. Astonishingly, the Contents column which stores the RDL information has image data type (the very data type which Microsoft strongly encourages us to avoid). The workaround is to cast contents as XML friendly varbinary(max) which can then be cast to xml data type. Here is the script which will list all reports and their respective parameters, so if you run it you will get multiple records per report if the latter has multiple parameters:

select 
    [name] ReportName, [path] ReportPath,
    item.value('@Name', 'varchar(100)') as ParameterName,
    item.value('DataType[1]', 'varchar(100)') as DataType,
    isnull(item.value('AllowBlank[1]', 'varchar(5)'), 'false') as AllowBlank,
    isnull(item.value('Prompt[1]', 'varchar(100)'), '') as Prompt,
    isnull(item.value('Hidden[1]', 'varchar(100)'), 'false') as Hidden,
    item.value('data(DefaultValue/Values/Value)[1]', 'varchar(100)') as ParameterValue
    from 
    (
        select
            [Path], [Name], cast(cast([Content] as varbinary(max)) as xml) as ReportXml 
            from dbo.Catalog 
            where [Content] is not null and [Type] = 2
    ) Reports 
    cross apply ReportXml.nodes('/Report/ReportParameters/ReportParameter') items(item)
    order by 1;

All tables in the ReportServer database are very straightforward, i.e. dbo.ReportSchedule relates to dbo.Schedule and dbo.Catalog via ScheduleID - > ScheduleID and ReportID -> ItemID respectively.

Oleg

more ▼

answered Jun 08, 2010 at 02:11 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Nice! I will explore this. I havent yet marked it as the correct answer as of yet.
Jun 09, 2010 at 10:56 AM Slick84
Interesting, the inner sub query works but along with the cross apply i get no results..
Jun 09, 2010 at 11:03 AM Slick84
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x984
x546
x94
x67

asked: Jun 08, 2010 at 10:44 AM

Seen: 2214 times

Last Updated: Jun 08, 2010 at 10:44 AM