September 30, 2015 at 3:28 am
Hi,
I wondered if anyone knew if it was possible to determine reports built by Report Builder from those built by SSDT/BIDS in the Catalog table of the ReportServer database or to be honest by any means.
Any help would be appreciated.
Thanks,
Nic
September 30, 2015 at 2:57 pm
the report Server database in the Catalog table has a Type field.
Here is what that means:
1 = folder
2 = report (both rdl and report builder)
3 = file (Word, Excel, PDF, etc.)
4 = linked report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)
Here is the query I came up with to show the Models and their reports:
SELECT
C2.Name AS Data_Source_Name,
C.Name AS Dependent_Item_Name,
C.Path AS Dependent_Item_Path
FROM
ReportServer.dbo.DataSource AS DS
INNER JOIN
ReportServer.dbo.Catalog AS C
ON
DS.ItemID = C.ItemID
AND
DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
WHERE Type = 6) --Type 5 identifies report data sources, 6 = Models
FULL OUTER JOIN
ReportServer.dbo.Catalog C2
ON
DS.Link = C2.ItemID
WHERE
C2.Type = 6 --5= report data sources, 6 = models
ORDER BY
C2.Name ASC,
C.Name ASC;
I used the following website as a reference:
http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/
October 1, 2015 at 1:43 am
Hi Sql Sarah,
Thanks for the reply. The type is very useful but what I'm actually trying to do is for all the type 2's determine which are Report Builder built and which are SSDT etc, in effect creating a kind of sub type.
I thought there may be something in the content XML but nothing stands out.
Any ideas?
Thanks,
Nic
October 1, 2015 at 9:12 am
Reports created with report builder have a model as a data source. The query I posted lists all the models and their associated reports.
You can use that query to list all the reports and if the datasource is Type 5, then it is SSRS, and if it is Type 6, then it is Report Builder.
October 20, 2015 at 5:51 am
Hi SQL Sarah,
Thanks for this, my concern was someone may have used BIDS/SSDT and had a report model data source, it seems this isn't the case so as you rightly pointed out I can pick up all the Report Builder reports from their data sources.
Thanks again,
Nic
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply