AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1883&pId=-1
Building Reports using ASP.NET and Crystal Reports - Part 5 - Creating Reports as a Web Service
page
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 40952/ 133

Introduction

This is the fifth article of a series that uses ASP.NET and Crystal Reports to build reports using the Adventure Works Sample Database.  Before reading this article it would be helpful to read Part 1, Part 2, Part 3, and Part 4, but it is not required.  At a minimum you should read Part 1. This article creates a web service that delivers the Invoice report developed in Part 1. The article then covers creating a web site to consume the web service, filter the report, and display the report to the user using the Crystal Reports Viewer control. Reading Part 1 would be most beneficial because I will use the Invoice.rpt file developed in that article to create the web service. You can also download the code here to get the report file.

Before you begin you will need to have installed Visual Studio 2008 with Crystal Reports for .NET.  The samples are written in Visual Studio 2008 but they will work with Visual Studio 2005 also.  You also will need to download the AdventureWorks sample database for SQL Server 2008.  Download and install the SQL2008.AdventureWorks_All_Databases.x86.msi file. If you do not have SQL Server 2008 you can use SQL Server 2005, but you'll need to download the 2005 AdventureWorks samples.

The goal of this article is to create a web page that looks like the following image. 

As stated earlier, this is the same report that was created in Part 1 of this series. The user can select a customer from the drop down list and preview all the invoices for the selected customer.

Step 1: Create the Web Service

1.    Launch Visual Studio 2008.

2.    Select File -> New Web Site from the menu.

3.    Choose ASP.NET Web Service from the list of templates.  You can select File or HTTP from the Location drop down list.  For this sample I'll use File.

4.    Set the Name to "ReportingService".  The path should be "..\Visual Studio 2008\WebSites\ReportingService"

5.    Select Visual C# from the Language drop down list and then click OK.

Visual Studio will create a new web service and add a Service.asmx page and web.config file.  The App_Code folder will contain the Service.cs file.

Step 2: Add the Crystal Report file

You'll need the Invoice.rpt file created in Part 1 for this step.  We'll simply copy the file to the ReportingService folder using Windows Explorer and then add the report to the Web Service project.

1.    Open Windows Explorer and copy the Invoice.rpt file created in Part 1 to the ReportingService folder that was created in Step 1.

2.    Go back to Visual Studio.  Right click on the ReportingService project and select Add Existing Item… from the pop-up menu.  Navigate to the ReportingService folder and click on the Invoice.rpt file.  Click the Add button.

3.    If you are using the Invoice.rpt file that you created in Part 1 you shouldn't need to change anything.  If you are using the Invoice.rpt file in the sample code you'll need to change the database connection string to your AdventureWorks database by opening up the report in Visual Studio.  Right click on the Database Fields in the Field Explorer Window and select Set DataSource Location… from the pop-up menu.

Step 3: Publish the Report as a Service

1.    Right click on the Invoice.rpt file in the Solution Explorer.

2.    Select "Publish as web service" from the pop-up menu.

3.    An InvoiceReport.asmx file will be added to the project.  This file contains a class called InvoiceService that inherits from ReportServiceBase.

4.    Right click on the InvoiceService.asmx file select "Set as Start Page" from the pop-up menu.

5.    Delete the Service.asmx and Service.cs file from the project.

6.    Run the project by pressing F5.  You'll be prompted to turn debugging on.  Select "Modify the Web.config file to enable debugging." and click the OK button.

7.    Your browser should display the methods available for your Invoice service.

8.    Click on the TestReport link at the bottom of the page.

9.    Click the Invoke button.  A new window will open and you should see the XML that the web service produces.

10. Close this new window.

11. Navigate back to the original page with the list of methods for the service by clicking the "Click here for a complete list of operations." at the top of the page.

12. Leave this project running and go to step 4.

Step 4: Create the Web Site

1.    Launch another instance of Visual Studio.

2.    Select File -> New Web Site from the menu.

3.    Choose ASP.NET Web Site from the list of templates.

4.    Choose "File" for the location.

5.    Enter "ReportingWebSite" for the name of the web site.  The path should be "..\Visual Studio 2008\WebSites\ReportingWebSite"

6.    Click the OK button.

7.    Open the Markup for the Default.aspx page if it isn't already open.

8.    Copy the following Register directive after the Page directive.

Listing 1

<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, 
PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

9.    Enter the following between the div tags.

Listing 2

Select a customer:<asp:DropDownList ID="ddlCustomer" runat="server">
</asp:DropDownList>
    
<asp:Button ID="btnPreview" runat="server" onclick="btnPreview_Click" 
    Text="Preview" />
<br />
<br />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
    AutoDataBind="true" />

10. Click the Design button.  Your design environment should look like the following image.

11. Double click on the Preview button to generate the click event handler.

12. Enter the following code for the click event.

Listing 3

protected void btnPreview_Click(object sender, EventArgs e)
{
    BindReport();
    CrystalReportViewer1.Visible = true;
}

13. Create the BindReport method.

Listing 4

private void BindReport()
{        
    CrystalReportViewer1.ReportSource = 
        "http://localhost:2632/ReportingService/InvoiceService.asmx";                
 
    CrystalReportViewer1.SelectionFormula = "{SalesOrderHeader.ContactID} = " 
        + ddlCustomer.SelectedItem.Value;
}

14. ReplaceCrystalReportViewer1.ReportSource = "http://localhost:2632/ReportingService/InvoiceService.asmx"; with the path to the web service you created in step 3.  If you have the browser still running from step 3 you can copy and paste the URL from the browser.

15. Add the code to the Form Load event to load the customer drop down list.

Listing 5

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    //Load the drop down list with customers.
    SqlConnection cn = new SqlConnection(
        ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString);
    cn.Open();
 
    SqlCommand cmd = new SqlCommand(
  "SELECT DISTINCT LastName + ', ' + FirstName AS Name, Person.Contact.ContactID " +
        "FROM Sales.SalesOrderHeader " +
        "INNER JOIN Person.Contact " +
        "ON Sales.SalesOrderHeader.ContactID = Person.Contact.ContactID " +
        "ORDER BY LastName + ', ' + FirstName", cn);
    SqlDataReader dr = cmd.ExecuteReader();
 
    ddlCustomer.DataSource = dr;
    ddlCustomer.DataTextField = "Name";
    ddlCustomer.DataValueField = "ContactId";
    ddlCustomer.DataBind();
 
    CrystalReportViewer1.Visible = false;
  }
  else
  {
    if (CrystalReportViewer1.Visible == true)
    {
      //Rebind the report.
      BindReport();
    }
  }
}

16. Add the connection string for the AdventureWorks database to the web.config file.  Be sure to set the server, user name and password for your database.

Listing 6

<connectionStrings>
  <add name="AdventureWorks" connectionString=
    "Data Source=YOURSERVER\SQL2008;User ID=aspalliance;Password=aspalliance;
Initial Catalog=AdventureWorks;"/>
</connectionStrings>

17. Add the following using statements to the top of you file.

Listing 7

//Custom using statements
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

18. Press F5 to run the web site.  You'll be prompted to enable debugging for the web site.  Click the OK button.

19. The Default page should be displayed and the customer drop down list should be populated with the list of customers.

20. Select Abercrombie, Kim from the drop down list and click the Preview button.

21. You should see the following report.

The BindReport method does all the work.  The first line tells the viewer where the report is located.

Listing 8

CrystalReportViewer1.ReportSource = 
    "http://localhost:2632/ReportingService/InvoiceService.asmx";                

The second line sets the selection formula for the report so only the selected customer is displayed in the report.

Listing 9

CrystalReportViewer1.SelectionFormula = "{SalesOrderHeader.ContactID} = " 
    + ddlCustomer.SelectedItem.Value;
Summary

This article demonstrates how to create a web service to distribute your Crystal Reports and also consume the report using a web site.  You could also consume the service using a WinForms application.  As you can see it is quite simple to create. For those of you that are gung ho about web services this should be like getting your cake and eating it too.  Good luck on your project and happy coding.


Product Spotlight
Product Spotlight 

©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-03-23 9:17:17 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search