dipal_bhavsar - Professional Profile

Simple Walk Through of SQL Server 2005 Reporting Services XML Data Source

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. 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.

Dipal Bhavsar
Chrysalis Information System
Mumbai

Introduction to Reporting Services Webcast Series On Demand

My webcast series on SQL Server 2005 Reporting Services is now available on demand. The ideal attendee for this series is someone who needs to develop reports, but hasn't used SQL Server Reporting Services before. In the series I assume that you've done some development in .NET, and won't freak out if I show you a SQL SELECT or some VB code, but you don't have to know anything about reporting. It might also be useful to folks who have used other reporting solutions, like Microsoft Access or Crystal Reports, and would like to know about a better solution for building reports.

One of my goals was to show some of the ways that developers can interact with the product. I'm a huge fan of the architecture of Reporting Services, which is really well thought out. (I don't work for the product group, so I had nothing to do with designing the product.) If you're a developer, there are loads of places that you can dive into the product and extend it to meet your needs. As an example, if you need to access a custom data source, you can do so by calling a web service or by writing a .NET DLL that provides the data to Reporting Services. If you want to export reports to some file format that the product doesn't support, you can build a custom rendering extension. If you need to see reports in the Microsoft Bob Word Processor, no problem, build a custom renderer.

Six hours of presentations is just a survey of the product, but there are tons of demos, so I think developers will enjoy it. All the webcasts were rated either four stars out of five, or four and a half stars out of five, so someone must have liked them. Either that, or my strategy of hacking the survey site so only my mother could rate the webcast is looking pretty good.

Session One: Introduction. This webcast gives a flavor of the entire report lifecycle: report design, report management, and report delivery.

Session Two: Delivering reports. This webcast is about ways to get reports to users. I showed an ASP.NET application that integrates with a report by linking to a report URL, a Windows Forms application that called the web service to browse available reports and then displays the report in a browser, building standard and data-driven report subscriptions, and uaing the Windows Forms control from Visual Studio 2005.

Session Three: Report Builder. Everything you always wanted to know but were afraid to ask about ad hoc reports. How does a user create an ad hoc report? What does a developer have to do to make the database available to end users?

Session Four: Report Design. I build reports with tables. I build reports with matrices. I build reports with lists. I build reports with charts. I build reports that link to other reports.

Session Five: Extensibility. Our intrepid hero calls custom code from a report. He gets data from a web service. He does a directory of the file system using a custom data source. He even uses a third-party charting control for Reporting Services.

Session Six: Management and Security. We learn about SQL Server Management Studio, and see the security model for reporting services. We also see diagrams of a scale-out architecture for a high-capacity, fault tolerant, enterprise grade reporting solution.

I'll be back in November with two more webcasts on reporting services. On November 2nd, I'll cover the controls that ship in Visual Studio 2005 for displaying reports in Windows Forms and ASP.NET applications. On November 16th, I'll be doing a session on charting in depth.

contect me on : dipal.bhavsar@gmail.com

Dipal Bhavsar
Chrysalis Information System
Mumbai, India

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

dipal_bhavsar - Professional Profile

Summary

Reputation

Privileges