Thursday, February 23, 2006 3:42 PM
Issue With Web Service as Data Source for Reporting Services
Something interesting I found utilizing the BI stuff available in Visual Studio 2005 and SQL Server 2005. Another developer and I were asked to create a quick demo version of a report, that sourced data from a list on Sharepoint. I have already done this using a custom Web Service I created that goes against a sharepoint site list. The web service gets list information and also queries a database to get other information not available on the list.
The problem I encountered in this new service goes like this. We needed a quick report. So the other developer and I created a stub web service. That service created a dataset, then created a datatable with defined columns. Then manually I added a couple of rows of data within the service. Next it takes the dataset, converts it to an xmldocument, and returns that xmldocument.
Code is something like this
public XmlDocument ReturnData(int ID)
DataSet dsTest = new DataSet();
DataTable dtNew = new DataTable();
// Create xmlDoc from DSTest
Now the other developer could take this stub, actually add the code to get data from the sharepoint lists, and I could create a report going against that web service stub. My reports worked fine against the stub all day. When the other developer finished the web service, and I hooked it up to my report, I could never get the fields to show in RS from web service. The differences in the web service actual from the stub, were that he was creating the column names etc. from display names in Sharepoint (stripping out spaces). In Reporting Services, even when I created a new report with no former references to the stub, I still could not see the field names returned from the actual web service. But in the dataset in RS, if I executed the query against web service, I could see the fields and the data. But report display would not show any data. Errors were shown about the dataset extension erroring out, field names were not available.
When I went in to the web service, and used my original column names the web service and RS worked out. But I'm still unsure as to why this problem occurred (and cost us 2-3 hours of time). Could this be a problem because of XMLDocument instead of a DataSet? I may look into this. I'd love to hear feedback on this.