perfectxml.com
 Basic Search  Advanced Search   
Topics Resources Free Library Software XML News About Us
home » Articles » XML & Web Services Support in Crystal Reports 9 - Part I Saturday, 14 July 2007

XML and Web Services Support in Crystal Reports 9 - Part I
Author: perfectxml.com Team
Article Date: December 12, 2002

Introduction

In this two-part article series, we'll explore the XML and Web services features available in Crystal Reports 9.

The first part will show you how to create a new report from a relational database, by running a stored procedure, then publish the report as a XML Web service, and finally use this report Web service from Windows and Web client applications.

In the second part, we'll look at using XML to generate reports and getting XML from the existing database reports.

You'll need the following to try out the examples given in this article:
  • Crystal Reports 9
  • Microsoft .NET Framework and Visual Studio .NET

Contents

Creating a Report using Crystal Reports 9

In this example, we'll connect to the Northwind sample SQL Server database and run an existing stored procedure called as Ten Most Extensive Products, to generate the report.
  1. Start Crystal Reports 9 and click on File | New to create a new report.

  2. Select Using the Report Wizard and Standard report, and then click on OK button.



  3. On the next screen in the Standard Report Creation Wizard expand Create New Connection and then expand OLE DB (ADO).

  4. It should bring up the OLE DB Provider configuration wizard. Select Microsoft OLE DB Provider for SQL Server from the Provider list.



  5. On the next screen provide the SQL Server details, such as name of the SQL Server, user id, password, and select the Northwind sample database. Click Next. You should see Advanced Information screen, click Finish.

  6. Now you should see Northwind under the OLE DB (ADO) connection node in the tree. Expand the Northwind node, select Ten Most Extensive Products from the Stored Procedures list and click on the > to move it under Selected Tables. Click Next.



  7. From the next Fields screen, move both the fields (TenMostExpensiveProducts and UnitPrice) to the right hand side under Fields to Display list. Click on Next three times.

  8. From the Template dialog, choose Corporate (Blue), and then click on Finish.

  9. The report is now ready and the preview screen should look similar to:



  10. Let's add a Chart to the above report.

  11. Click on the Design tab (or press Ctrl+D or click on View | Design), and then click on Insert | Chart...

  12. Select Bar Chart from the Type list. Click on the Data tab.

  13. On the Data tab, select For each record from the combo box and move TenMostExpensiveProducts from the Report Fields to the list under this combo box.

    And move UnitPrice field from the Report Fields to the list under Show value(s):, as shown in the following screen:



  14. Click OK, and then move to the Preview screen (Click on the Preview tab or press Ctrl+R or select View | Preview), and you should see a chart similar to:



  15. Save the report file as NW_TenExpensiveProducts.rpt. We are now ready to publish this report as a XML Web service.

Publishing the Report as XML Web Service

  1. Start Visual Studio .NET, create a new Visual Basic .NET ASP.NET Web Service project, and name it NW_TenExpensiveProducts.

  2. In the Solution Explorer (Ctrl+Alt+L or View | Solution Explorer) window, delete Service1.asmx. Click on Show All Files icon (or select Project | Show All Files from the menu).

  3. In the Solution Explorer, right click on the NW_TenExpensiveProducts project name, and select Add | Add Existing Item....

  4. Select Crystal Reports (*.rpt) from the Files of type list, then browse and select the crystal report RPT file (NW_TenExpensiveProducts.rpt) created earlier.

  5. In the Solution Explorer tree, right click on the report file (NW_TenExpensiveProducts.rpt) and select Publish as web service.

  6. It should create two new files named NW_TenExpensiveProductsService.asmx and NW_TenExpensiveProductsService.asmx.vb.

  7. Right click on NW_TenExpensiveProductsService.asmx and select Set As Start Page.



  8. Build (Ctrl+Shift+B) the solution and make sure there are no errors.

  9. The report is now ready as a XML Web service. Press Ctrl+F5 (or select Debug | Start without Debugging from the menu) to run the Web service application. You should see a standard ASP.NET Web service test page with about 15 methods listed under the NW_TenExpensiveProducts Web service.

Writing Windows Application Client

Let's now write a Visual Basic .NET Windows application that connects to the above Crystal Reports Web service and shows the report inside a Windows Form.
  1. Create a new Visual Basic .NET Windows Application project.

  2. From the Toolbox (Ctrl+Alt+X or View | Toolbox), drag and drop the CrystalReportViewer onto the form. Resize the form and the report control accordingly, and provide some meaningful caption for the form.

  3. Select the CrystalReportViewer control and change the DisplayGroupTree property to False. Also update the Anchor property to Top, Bottom, Left, Right, so that the report control resizes as the form is resized.

  4. Add a new Button control on the form, set it's caption as &View / Refresh Report.

  5. Now we have a Windows form ready with a Crystal reports viewer that can show the report. There are two ways to bind report data with this control. Let's start with the first method:

  6. Double click on the button, and write the following statement under the Button1_Click method.
    Cursor.Current = Cursors.WaitCursor
    CrystalReportViewer1.ReportSource = _ 
       "http://localhost/NW_TenExpensiveProducts/NW_TenExpensiveProductsService.asmx"
    Cursor.Current = Cursors.Default

  7. Build and run the application. Click on the &View / Refresh Report button and you should see the output similar to:



  8. In the above method of associating the report Web service with the control, we dynamically provide the Web service URL as the report source.

    The alternate approach would be to first add Web reference to the report Web service, this creates a proxy class, create an instance of the proxy class and set that as the report source. Let's try out this method too.

  9. In the Solution Explorer, right click on the project name and select Add Web Reference....

    Type http://localhost/NW_TenExpensiveProducts/NW_TenExpensiveProductsService.asmx in the address bar, hit Enter, and then click on Add Reference button.

  10. Drag and drop a new button control onto the form and provide the caption for it as &Use Web Service Proxy Class. Double click on this button and write the following code in the Button2_Click method:
    Cursor.Current = Cursors.WaitCursor
    CrystalReportViewer1.ReportSource = _ 
       New localhost.NW_TenExpensiveProductsService()
    Cursor.Current = Cursors.Default
    
    The only change in the above code snippet is that instead of providing the Web service URL, we are creating an instance of the Web service proxy class, which was created for us by Visual Studio .NET when we added the Web Reference.

  11. Build and run the application. Click on Use Web Service Proxy Class button and you should see the same report output as we saw in the earlier method.

Writing Web Application Client

Let's now write a Web Form application that connects to the above Report XML Web service. You'll notice the method to bring the report to the Web is exactly similar to adding report on a Windows application. The same two steps are followed here too: drag/drop the report control on the form and then either provide the Report Web service URL or first add the Web reference and use the proxy class instance, as the report source.
  1. Create a new Visual Basic ASP.NET Web Application Project, and name it TestWebReport.

  2. Drag and drop the CrystalReportViewer control from the toolbox to the Web Form.

  3. Double click on the Web Form and write the following code under the Page_Load method:
    CrystalReportViewer1.ReportSource = _ 
       "http://localhost/NW_TenExpensiveProducts/NW_TenExpensiveProductsService.asmx"
  4. Build and run the Web application and you should see the following output in the browser window:



Summary

This article illustrates how easy it is to expose reports as XML Web services and consume them in either Windows or Web applications. Just one mouse click publishes the Report as a Web service, and literally a single line of code binds the report control with the data returned by the Report Web service.

In the second part of this article, we'll see how to work with XML in Crystal Reports. We'll explore how to generate reports off XML data files, how to export report data as XML, and other such XML offering from Crystal Reports 9. Stay tuned!

  Contact Us | E-mail Us | Site Guide | About PerfectXML | Advertise ©2004 perfectxml.com. All rights reserved. | Privacy