SSRS ReportViewer problems with XML embedded data source

Question

I have C# (WPF) application where I want to display a SSRS report in the ReportViewer control. The local report file has XML datasource embedded in it. The report is displayed correctly when running from SQL Server Business Intelligence Development Studio. But when I run with my app I get the following error:

A data source instance has not been supplied for the data source '...'.

So here is what I'm doing:

I have defined embedded XML data, as explained in this tutorial Defining a Report Dataset from Embedded XML Data. I have a data source called XmlDataSource_TopCustomers and a data set called XmlDataSet_TopCustomers, using that data source. I have referred the data set in a table and a chart. Overall, the RDL looks like this (just the essential, of course):

    <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <DataSetName>XmlDataSet_TopCustomers</DataSetName>
          </Tablix>
          <Chart Name="Chart1">
            <DataSetName>XmlDataSet_TopCustomers</DataSetName>
          </Chart>
        </ReportItems>
      </Body>
      <DataSources>
        <DataSource Name="XmlDataSource_TopCustomers">
          <ConnectionProperties>
            <DataProvider>XML</DataProvider>
            <ConnectString />
          </ConnectionProperties>
          <rd:SecurityType>None</rd:SecurityType>
          <rd:DataSourceID>47833b52-231f-4634-8af4-3c63272b02a7</rd:DataSourceID>
        </DataSource>
      </DataSources>
      <DataSets>
        <DataSet Name="XmlDataSet_TopCustomers">
          <Query>
            <DataSourceName>XmlDataSource_TopCustomers</DataSourceName>
            <CommandText><Query>
     <ElementPath>Root /CustomerOrder {@CustomerNo, @CustomerName, @OrdersCount (Integer), @Total(Float), @AveragePerOrder(Float)}</ElementPath>
     <XmlData>
      <Root>
    <CustomerOrder CustomerNo="10001" CustomerName="Name 1" OrdersCount="2" Total="5.446740000000000e+003" AveragePerOrder="2.723370000000000e+003" />
    <CustomerOrder CustomerNo="10894" CustomerName="Name 2" OrdersCount="5" Total="3.334750000000000e+003" AveragePerOrder="6.669500000000001e+002" />
    <CustomerOrder CustomerNo="12980" CustomerName="Name 3" OrdersCount="2" Total="2.003290000000000e+003" AveragePerOrder="1.001645000000000e+003" />
      </Root>
     </XmlData>
    </Query></CommandText>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
          </Query>
          <Fields>...
        
      </DataSets>
      <rd:ReportUnitType>Inch</rd:ReportUnitType>
      <rd:ReportID>02172db8-2a1d-4c35-9555-b37ee6193544</rd:ReportID>
    </Report>

At this point everything works fine from the IDE.

In my C# application, I have a ReportViewer and the following code:

Viewer.LocalReport.ReportPath = @"<actualpath>\TopCustomers.rdl"; // actual path is OK
Viewer.RefreshReport();

And then I get that

A data source instance has not been supplied for the data source 'XmlDataSet_TopCustomers'.

I've seen others having the same problem, but in most of the cases the problem is multiple datasources, which is not the case here, as you can see from the RDL snippet above.

Any suggestions?

score 1 · Answer 1 · edited May 23 '17 at 11:49

The answer to my question can also be found here When to use RDLC over RDL reports? and here http://www.gotreportviewer.com/. It's basically this:

Unlike the Report Server the ReportViewer control does not connect to databases or execute queries. Also, in local mode the only export formats available are Excel, Word and PDF. (In remote mode all formats supported by the Report Server are available.) The ReportViewer control cannot be extended by adding custom renderers or custom report items.

More information can be found here http://msdn.microsoft.com/en-us/library/ms252109(v=vs.80).aspx.

The ReportViewer control, which processes .rdlc files, ignores the element of RDL. If a report definition contains a query, the control will not process it.

and

When converting a .rdl file to .rdlc format, you must manually replace the data source and query information in the report definition with data constructs provided in your application

So you have to fetch the data explicitly and provided for the ReportViewer as a ReportDataSource having the exact same name as the dataset in the RDL file.

Kevin Dahl · Answer 2 · 2013-01-23T15:30:28.343

I have a small command line app that does something similar, but between defining the report path and doing anything with the report viewer I'm setting a data source for the report to be run against:

report.DataSources.Add(new ReportDataSource("DataSet_for_Distribution", table));

...table is a DataTable.

After that I have no problems programmatically calling the report Render method.

Can you set a break before the render and see what data sources the report actually has?

Another thing to try, and it may just be that you formatted (or stack formatted ) it to post it here, but when I embed an XML data set in a report it is all using a format like this:

<CommandText>&lt;Query&gt;
    &lt;ElementPath&gt;Root /S  {@OrderDate (Date), @TotalDue (Decimal)} /C {@LastName} &lt;/ElementPath&gt;
    &lt;XmlData&gt;
    &lt;Root&gt;
    &lt;S OrderDate="2003-07-01T00:00:00" SalesOrderNumber="SO51131" TotalDue="247913.9138"&gt;
      &lt;C FirstName="Shu" LastName="Ito" /&gt;
    &lt;/S&gt;
    &lt;S OrderDate="2003-10-01T00:00:00" SalesOrderNumber="SO55282" TotalDue="227737.7215"&gt;
      &lt;C FirstName="Shu" LastName="Ito" /&gt;
    &lt;/S&gt;
    &lt;S OrderDate="2002-07-01T00:00:00" SalesOrderNumber="SO46616" TotalDue="207058.3754"&gt;
      &lt;C FirstName="Jae" LastName="Pak" /&gt;
    &lt;/S&gt;
    &lt;S OrderDate="2002-08-01T00:00:00" SalesOrderNumber="SO46981" TotalDue="201490.4144"&gt;
      &lt;C FirstName="Ranjit" LastName="Varkey Chudukatil" /&gt;
    &lt;/S&gt;
    &lt;S OrderDate="2002-09-01T00:00:00" SalesOrderNumber="SO47395" TotalDue="198628.3054"&gt;
      &lt;C FirstName="Michael" LastName="Blythe" /&gt;
    &lt;/S&gt;
    &lt;/Root&gt;
    &lt;/XmlData&gt;
    &lt;/Query&gt;</CommandText>

Well, yeah, explicitly adding a data source does work, but that's not what I expect. I guess that when I run my report with the IDE, the report is parsed and a data source like that is created at runtime, before the report is rendered. Yet, in this case I don't see the very point of embedding XML if I still have to explicitly providing it. And BTW, the very same thing happens if I use a SQL data source. — Marius Bancila, Jan 23 '13 at 20:56
Ah, about the formatting, you are right; the browser displays `<` as < in text enclosed in the `pre` tags, so is ruining the actual code. — Marius Bancila, Jan 23 '13 at 20:58
@Marius - There's some useful information in this other stack post here, regarding RDL/RDLC: http://stackoverflow.com/questions/1079162/when-to-use-rdlc-over-rdl-reports — Kevin Dahl, Jan 23 '13 at 23:34
Thanks Kevin. That's the exact answer I've found in several places. "Q: What are the limitations of ReportViewer control? A: Unlike the Report Server the ReportViewer control does not connect to databases or execute queries. Also, in local mode the only export formats available are Excel, Word and PDF. (In remote mode all formats supported by the Report Server are available.) The ReportViewer control cannot be extended by adding custom renderers or custom report items." http://www.gotreportviewer.com/ — Marius Bancila, Jan 24 '13 at 07:50

score 0 · Answer 3 · answered Jan 23 '13 at 16:16

0

I am not sure from what you have stated if the data source has specified credentials.

This part here:

<ConnectionProperties>
            <DataProvider>XML</DataProvider>
            <ConnectString />
          </ConnectionProperties>

Generally speaking with SQL data sources when reports fail to view for others or from applications it is due to the hosting server assuming a different credential than your IDE building the application. It does not know if my name is Brett, that my credentials are running it when calling it remotely. When you specify the credentials on the server hosting the report you can usually get around this. You go into the server hosting the report, I assume you are doing this as you have an 'rdl' report versus an rdlc report. Find the datasource, click properties, change setting to be 'use these credentials'. Supply credentials that you know work.

This may fix the issue. I am not certain with Sharepoint connections and XML connections but this is common with viewing issues with SQL Server connections.

answered Jan 23 '13 at 16:16

djangojazz

14,131
10
56
94

I don't think this has anything to do with credentials. I would certainly expect a different exception in this case. Or maybe I just have to high expectations. – Marius Bancila Jan 23 '13 at 20:57
It was just a thought. Potentially if you are hosting code on a server that is using an access account different than your own, it may be using that account to access the file. When you specify an account it knows. However with XML and lists I am not certain of how SSRS handles those connection strings, I only know there were issues I had with accounts and access with SQL Connection Strings. – djangojazz Jan 23 '13 at 21:03
No, you see, I don't have external data. I don't host anything on a server. I have embedded the only that I want to display inside the RDL file itself. However, the `ReportViewer` simply seems to disregard it, and needs explicit data set instantiations. – Marius Bancila Jan 23 '13 at 21:20
Interesting, can you still embed the credentials possibly? I always thought that the default report was an rdlc in a C# app, meaning report definition language client. Versus RDL which is a usually designed on Business Intelligence Development Studio add on to Visual Studio and then deployed to a site. Was there a reason for not deploying and referencing the report through the web service but still using the RDL format? Generally speaking I usually build the Reports meant to be self contained in C# directly from the toolbox as a 'report' and it creates and rdlc file. – djangojazz Jan 23 '13 at 21:31
I think I got my answer here http://forums.asp.net/t/1173578.aspx/1: "ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all data sources, including non-database data sources. However this means that when an RDL file is used by the ReportViewer control, the SQL related information in the RDL file is simply ignored by the control. It is the host application's responsibility to connect to databases, execute queries and supply data to the ReportViewer control..." – Marius Bancila Jan 23 '13 at 21:55
I would suggest trying an RDLC if you have to have it be client based (self contained in application) and if you have to use an RDL report, deploy it on a server, set the setting of the datasource on the host server, call that report from a report viewer object in code. – djangojazz Jan 23 '13 at 23:15

SSRS ReportViewer problems with XML embedded data source

3 Answers3

Linked

Related