Get Data from SSRS dataSet

Question

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

score 3 · Accepted Answer · answered May 29 '13 at 19:06

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();
        }

I'm not sure I understand this. What do I need to do to get the data from the data set ? cause when I hit the DataSet url from SSRS, it returns me the DS definition, not the data... — Marty, May 30 '13 at 07:53
What djangojazz is saying is that a Reporting Services Dataset contains the *definition* of the data query, it does not contain any actual data. Therefore his suggestion is to parse the Dataset definition to retrieve the query text - you would then have to execute the query against the data source to retrieve the data. — Nathan Griffiths, May 30 '13 at 23:30
Exactly Nathan. The DataSet is a reference in the form of a query in many cases, not the data itself. If you want the data itself you would be far better running the query yourself and populating that data somewhere. — djangojazz, Jun 10 '13 at 17:34

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

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

edited May 23 '17 at 11:43

Community

1
1

answered May 29 '13 at 15:30

kyooryu

1,469
3
23
48

Get Data from SSRS dataSet

2 Answers2

Linked