CODEWITHSUNDEEP
sqlreporting-servicesssrs-2012
Henrov
Henrov

Reputation: 1610

Building a 'universe' in SSRS

I am trying to build a 'universe' where my colleagues and I can see the following:

REPORTNAME - DATASET(S) - DATASOURCE

For each I want to know details: 

REPORTNAME - which parameters? what kind? caching enabled?
DATASET - underlying Query, resulting Columns
DATASOURCE - COnnectionstring

Getting a list of reports is done (through queries on Reportserver database)

Getting the details for the datasources too (script executed against webservice with rs.exe)

But where do I find the information regarding the datasets? I cannot find anything in the tables/views in the reportserver database, nor can I find any infor regarding the datasets in the documentation with the webservice. Who can point me in the right direction?

Thanks, henro

Upvotes: 1

Views: 291

Answers (1)

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

The fact that you can't find any information regarding the data sets in the report server database makes me think that you're looking for information on in-report (local) data sets. If you were dealing with shared data sets, you would have found them when you looked in the Catalog (type = 8) and DataSets tables.

In-report (local) data set details are contained in the report's definition (RDL XML) under /Report/DataSets/DataSet/Query.

You can query the local data set information out of the report server database, using the XML data type's functions to pivot each data set's details into its own result set row:

SELECT ReportItemID = ItemID,
        DataSetName = QueryXml.value('@Name', 'NVARCHAR(256)'),
        DataSourceName = QueryXml.value('(*:Query/*:DataSourceName)[1]', 'NVARCHAR(260)'),
        CommandType = QueryXml.value('(*:Query/*:CommandType)[1]', 'NVARCHAR(15)'),
        CommandText = QueryXml.value('(*:Query/*:CommandText)[1]', 'NVARCHAR(MAX)')
    FROM (
        SELECT *,
             ContentXml = (CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))
        FROM Catalog
        WHERE Type = 2
    ) AS Data
        CROSS APPLY ContentXml.nodes('/*:Report/*:DataSets/*:DataSet') QueryData(QueryXml)

While you're at it, you could query all the details needed for your report out of the report server database, eliminating the need to use the Report Server web service/rs.exe (disclosure: link is to an article on my blog).

Upvotes: 3

Related Questions