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