Can XML data provider determine field types from .NET DataSet or DataTable?
Using SQL Server 2008 Reporting Services, I have a set of reports that take data from web service methods (.NET 4 C#) that return a DataTable. The problem is to get the type of each report field to match the type of the corresponding DataTable column. There are situations in a report where the default type (string) is invalid or wrong, like comparisons or aggregate functions (Avg, Sum, etc). It is possible to workaround this by converting values (using CInt, etc) each time they’re used in expressions, but that is not a preferred solution. When creating report datasets, I would expect the XML data provider to handle standard .NET objects like DataTable and DataSet returned by a web service method, but it seems that it doesn’t. It cannot apply the type information in the in-line schema to the elements in the data content. The auto-detection function of the XML data provider finds the schema in the response, but treats it as data and not as a schema, ignoring the actual data content that follows. If an explicit ElementPath is included in the report dataset’s query, the data provider can be directed to the data fields, but now the schema is ignored and the fields in the report dataset default to string. The only way round I can see is to declare any non-string types explicitly in the ElementPath, as in <ElementPath IgnoreNamespaces="true"> GetDataResponse{}/GetDataResult{}/DataSet1{}/tablename{Code,Name,Quantity(Decimal),Count(Integer) } </ElementPath> It’s not necessary to enumerate the fields like this for the SQL data provider, so it seems like an oversight that the XML data provider doesn’t work with .NET DataSet and DataTable. Have I missed something here? References: Reporting Services: Using XML and Web Service Data Sources http://msdn.microsoft.com/en-us/library/aa964129(v=sql.90).aspx Using the XML Data Extension to leverage an ADO.NET dataset in your SQL Reporting Services Report. http://blogs.msdn.com/b/bimusings/archive/2006/03/24/560026.aspx#8783097
January 19th, 2011 10:34am

Hi Robert, Thank you for your posting. Based on my testing, the behavior is same with yours. I would suggest you submitting a feedback at https://connect.microsoft.com/sqlserver. Appreciate your understanding. Thanks, Jin ChenJin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2011 4:42am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics