CODEWITHSUNDEEP
reporting-services
Marty
Marty

Reputation: 3555

Get Data from SSRS dataSet

Is it possible (if Yes - how) to retrieve data from SSRS dataset that has been published to the ReportServer ?

I have is Azure reporting services setup, and published a DataSource, DataSet, and a report that work perfectly fine.

What I want - is to be able to access that "published DataSet" - like some sort of a XML API ? Say hit some URL with params from a browser, and get a XML result with Data from that DataSet

Upvotes: 1

Views: 5482

Answers (2)

djangojazz
djangojazz

Reputation: 13270

The problem you will run into is the rsd (datasetFile) is not much itself but a query and for that reason publishing the proxy services of ReportService2010 will get you the data but you still have to then handle the XML shredding. It is much, much easier to get the data from the dataset through querying the 'ReportServer' IMHO in SQL directly. Rather than making the models from the proxy classes, getting the data from invoking those classes, then you have xml you still have to query to get your data.

Example with word of warning: (This may only work with my example of datasets not other catalog items):

use ReportServer
GO

Select  
    Path
,   Name
,   Content
,   cast( cast(Content as varbinary(max)) as xml) as ContentAsXML
,   cast(
            cast(Content as varbinary(max)) 
    as xml)
        .query('declare namespace a="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition"; 
    /a:SharedDataSet/a:DataSet/a:Query/a:CommandText')
        .value('.', 'varchar(max)') as QueryStatement
from dbo.Catalog
where type = 8

Based on this guy's writing:

http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/

If you decide you just must use the proxy classes you will have to remember to invoke the namespace and I did an Xdocument method with C# to get the data. Don't have the dataset but I did the Datasource which will be a similar method. You mainly do this method in a foreach loop for each dataset you want. Keep in mind YOUR NAMESPACE for 2008 or 2005 will differ:

private List<string> GetDataSourceRefs(string aSourceLocation)
        {
            var xdoc = XDocument.Load(aSourceLocation);

            // Need a namespace or else the xml elements will not be properly identified.  Default below is for 2012 ONLY.
            XNamespace ns = XNamespace.Get("http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition");

            return xdoc.Descendants(ns + "DataSource")
                .Elements(ns + "DataSourceReference")
                .Select(x => x.Value)
                .ToList();
        }

Upvotes: 3

kyooryu
kyooryu

Reputation: 1509

Use ReportExecution2005 proxy class - you can execute the report and get it in the XML format. I'm not sure but I think you should be able to execute datasets too. You can read more about it here:

Generate reports programmatically using the TFS API and SSRS

Upvotes: 0

Related Questions