A cool new feature of SQL Server 2005 Reporting Services is that you can report on XML data. These are the steps in the demo I did today:

  • Create a new web service.
  • Add a public class called product to the web service:

  Public Class Product
   Public pname As String
   Public pcat As String
   Public psubcat As String
   Public psales As Decimal
  End Class

  • Create a method that returns an array of these product items. In my case, I was reading total sales from Adventure Works grouped by Product Category, Product Subcategory, and Product Name. Updated: Code for the method is here.
  • Run the web service to check that it works.
  • At this point during the development process, I spent three hours debugging a security issue that prevented me from logging on to the database. I changed my permissions about a million times, switched from Windows authentication to SQL authentication and back again, tried logging on as SA, then restarted SQL Server, IIS, the entire machine, every other machine in my office, and my cell phone. Three hours later I determined that the Adventure Works database is actually called AdventureWorks, with no space between the words. After much swearing, I had a running web service.
  • In another project, create a new report. In my case, I opened up a second copy of Visual Studio 2005 to keep things clean.
  • Add a new data source. The data type is XML. The connection string is the URL for the web service. In my case, that was http://localhost/ProdService/Service.asmx
  • Using the generic query designer, enter a query in the XML syntax described in Books Online. In my case, the query was <Query><SoapAction>http://www.geoffsnowman.net/mytestsvc/GetProducts</SoapAction></Query>. The contents of the SoapAction element is the SoapAction associated with the method you want to call on the web service. In my case, I just grabbed it directly from the WSDL of the web service which is at http://localhost/ProdService/Service.asmx?WSDL
  • Next I executed the query in the query designer, just to check I had some data.
  • Finally, I switched to the Layout tab, dropped a table onto the report and dragged over each field, and the previewed the finished report. Voila!

The other samples I used in my webcast today were:

  • Adding green bar to a report using this expression for background color: =IIf(RowNumber(nothing) mod 2, "yellow", "lightgreen")
  • Adding code to a report using the code tab:

function ToEuros(dollars as decimal) as decimal
           return dollars * 0.83
end function

  • A custom data source: The file system information sample data source that ships with both SQL Server 2000 and SQL Server 2005.
  • Finally, the Dundas Chart for Reporting Services beta.

This webcast should be available on demand shortly. The final webcast in the current series will be on Friday.