This is the second part of a series of articles that use the free version of Crystal Reports to create ASP.NET web sites using the AdventureWorks sample database. The first part examined the creation of an invoice application. This article will build a quarterly sales report using the cross tab and the charting capabilities in Crystal Reports. The AdventureWorks sample database can be downloaded from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407 for SQL Server 2008. Download and install the SQL2008.AdventureWorks_All_Databases.x86.msi file.  The sample was written using Visual Studio 2008, but it will work with Visual Studio 2005 as well.  If you do not have SQL Server 2008 you can use SQL Server 2005, but you will need to download the 2005 AdventureWorks samples.

The goal of this article is to create a quarterly sales report that looks like the following figure.

Figure 1

This article will demonstrate how to use cross tabs, charts, create formula fields, attach to tables in a SQL Server database, use the Database Expert to create links between tables, and insert groups in a report.

Step 1 is repeated from Step 1 of the part 1 of this series. If you already have the AdventureWorksReports web site setup you can skip to Step 2.

1.    Launch Visual Studio 2008.

2.    Select FileàNew Web Site from the menu.

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

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

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

Visual Studio will create a new web site and add a Default.aspx page and web.config file. Now you need to add a Crystal Report file to your site.

1.    Right click on the web site in your Solution Explorer.

2.    Select Add New Item… from the pop-up menu.

3.    Click on Crystal Report from the templates. If you do not see Crystal Report as an option then you need to run the Visual Studio 2008 installation and select the option to install Crystal Reports. It comes free with Visual Studio 2008.

4.    Change the name of the report to Sales.rpt. The Language drop down should already be set to Visual C#, but if it is not then do so.

5.    Click the Add button.

Visual Studio will add the Crystal Report file to your web site and bring up the Crystal Reports Gallery dialog. You may be prompted to register your product if this is the first time you are using Crystal Reports. You can skip registering the product, but it will keep prompting you every time you open a report so it is worth taking the extra minute or two to register. Once you get to the Crystal Reports Gallery dialog you should select the "As a blank report" option and click OK.

Figure 2

Your report will be displayed in Visual Studio and you are ready to start building your report.

The first step in designing this report is connecting to the AdventureWorks database and bringing in the tables that you need. These steps assume you have already downloaded and run the install for the AdventureWorks database.

1.    Right click on the Database Fields node in the Field Explorer. If you do not see the Field Explorer window, select Crystal ReportsàField Explorer from the menu. Select Database Expert… from the pop-up menu.

2.    This report is going to connect directly to the database using OLE DB. To connect to the database click the plus sign next to the Create New Connection node.

3.    Click the plus sign next to OLE DB (ADO). This will display the OLE DB dialog which allows you to select your AdventureWorks database.

4.    Select SQL Native Client from the list of providers and click Next.

5.    Enter the server name where the SQL Server database is installed.

6.    You can either use integrated security or a SQL Login and password to connect to the database. This depends on how you setup your database when you installed it. I created a SQL Login called "aspalliance," gave it a password of "aspalliance" and added the Login to the db_owner role in the AdventureWorks database. Enter the User ID and Password.

7.    Now click on the Database drop down list. You should see AdventureWorks as one of the options. If you do not then the SQL Login does not have access to the database. You will have to use SQL Server Management Studio to add the aspalliance Login and add them to the db_owner role for the AdventureWorks database.

8.    Once you have selected AdventureWorks for the database, click Next and then click Finish.

Your SQL Server should appear under the OLE DB (ADO) node and the AdventureWorks database should be listed under the server name.

Figure 3

9.    Click the plus sign next to AdventureWorks and you will see the list of schemas defined in the database. If you click the plus sign next to a schema name, you will see two nodes, one for Tables and one for Views. Clicking the plus sign next to either will display the list of tables or views in that schema.

10. The next step is to select the appropriate tables and views for the report and move them to the Selected Tables list in the dialog box. Expand the Person schema and the Tables. Click on the Contact table and then click the > button to move this table to the Selected Tables list.  Do the same for the HumanResources.Employee, Sales.SalesOrderHeader, Sales.SalesPerson, and Sales.SalesTerritory tables.

11. Now that you have selected the tables, you need to define the relationships between these tables. Crystal does a pretty good job of figuring this out, but cannot figure everything out automatically. To manually configure the relationships click the Links tab in the Database Expert dialog.

12. This displays a graphical representation of the tables. You can make the dialog box bigger by dragging the lower right hand corner of the dialog box. I like to make this big so I can see as many tables as possible.

13. Crystal should have figured out the relationships between the SalesOrderHeader.SalesPersonId and SalesPerson.SalesPersonId, the SalesPerson.SalesPersonId and Employee.EmployeeId, the Employee.ContactId and Contact.ContactId, and the SalesPerson.TerritoryId and SalesTerritory.TerritoryId tables. You should see a line between the primary and foreign keys between these tables. If you do not, you can easily create the relationship by clicking on the foreign key field and dragging it to the primary key field. You will need to remove the relationships between the SalesOrderHeader.ContactId and the Contact.ContactId, and the relationship between the SalesOrderHeader.TerritoryId and the SalesTerritory.TerritoryID.

Figure 4

14. Click the OK button.

You should now see the report in Visual Studio 2008. Click the plus sign next to the Database Fields node in the Field Explorer. You see all your tables listed. Click the plus sign next to a table and you will see the fields in that table. These are now available to be used in the report.

The Page Header section repeats itself on every page. For the quarterly sales sample we want to repeat the company logo and company name. The image for the logo can be found with the sample code that you can download. Make sure you copy logo.bmp into your web site project.  You can simply copy the file using Windows Explorer into the web site folder and then refresh the project in Visual Studio for it to appear in the Solution Explorer. Follow these steps to create the page header.

1.    Make the height of the Page Header section taller by placing your mouse pointer over the top of the Details section. The mouse pointer should change to a horizontal bar. Once you see the horizontal bar for the mouse pointer, click and drag down. This will make the section larger. 

2.    Right click on the white space in the Page Header section and select InsertàPicture… from the pop-up menu. Select the logo.bmp file and click Open. Drop the picture in the upper left hand corner of the page header section.

3.    Using the Properties window change the Height property of the picture to 2058 and the Width to 3066.  

Figure 5

4.    Next you need to add the company name. Right click in the white space next to the picture and select InsertàText Object from the pop-up menu. Drop the text object to the right of the picture.

5.    Enter "Adventure Works Cycle" for the text. Be sure not to hit the Enter key when you are done typing. To stop editing the text object click somewhere else on the report. If you press the Enter key, it will put a hard return in the text object.

6.    Change the Height property of the text object to 720 and set the Width to 8160.

7.    Right click on the text object and click Format Object from the pop-up menu. This dialog box gives you more options for formatting than the Properties window.

8.    Click the Font tab. Change the font size to 28 and the color to Blue.

9.    Click the Paragraph tab. Change the horizontal alignment to Centered and click OK.

Figure 6

The quarterly sales report displays each sale by territory. A new page should be displayed for each territory with only the sales people in that territory. To accomplish this you can create a group on the SalesTerritory.Name field.

1.    Click back the Main Report view. Right click any white space on the report and select InsertàGroup… from the pop-up menu.

2.    The Insert Group dialog box should appear. Select the SalesTerritory.Name field from the drop down list. Click OK.

3.    You will notice that two new sections were added to the report. Group Header #1 and Group Footer #1. By default, Crystal adds the special field called Group #1 Name to the Group Header #1 section. This will display the value for whatever field you are grouping on. For this report you do not need the Group Name so you should remove this field by clicking on the field and pressing the delete key.

4.    We do not need to show Group Footer #1 so you can suppress this section by right clicking on the section header in the report and selecting Suppress (No Drill-Down) from the pop-up menu.

The cross tab functionality in Crystal Reports is a great way to summarize data. Cross tabs let you pivot data so that a field's value is displayed as columns in a table. Cross tabs are particularly useful when creating any date driven reports that need to see summarized data. This report displays each employee for the territory as the rows and the fiscal year's quarter as the column.  Before we create the cross tab we need to create a formula that will tell us the fiscal quarter for the SalesOrderHeader.OrderDate. We then will use this formula for the columns in the cross tab.

1.    In the Field Explorer window right click on the Formula Fields and select New… from the pop-up menu.

2.    The Formula Name dialog should appear. Enter FiscalQuarter for the name and click the Use Editor Button.

3.    Enter the following formula:

Listing 1

'Q' + totext(DatePart("q", {SalesOrderHeader.OrderDate}), 0) + '/' + 
totext(Year({SalesOrderHeader.OrderDate}), 0, '')

This formula creates a string based on the SalesOrderHeader.OrderDate. If the order date was 4-14-2009 then the formula would return 'Q2/2009.'

4.    Click the Save and close button. 

The cross tab also wants to use the employee's name as the row; however, we need to use the name formatted as Last Name, First Name. We must create another formula to accomplish this.

5.    In the Field Explorer window, right click on the Formula Fields and select New… from the pop-up menu.

6.    The Formula Name dialog should appear. Enter EmployeeName for the name and click the User Editor button.

7.    Enter the following formula:

Listing 2

{Contact.LastName} + ", " + {Contact.FirstName}

8.    Click the Save and close button.

9.    Now you can create the cross tab using these formulas.

10. Right click on the white space in Group Header #1 section. Select InsertàCross-Tab… from the pop-up menu.

11. Drop the cross tab in the Group Header #1 section on the left side of the report. The Cross-Tab Expert dialog should appear.

Figure 7

12. Click on the FiscalQuarter field from the list of Available Fields.

13. Click the > button next to the Columns list.

14. Click on the EmployeeName field from the list of Available Fields.

15. Click the > button next to the Rows list.

16. Now you need to specify which field to summarize. For this example we will summarize the SalesOrderHeader.SubTotal field. Click on this field in the list of Available Fields.

17. Click the > button next to the Summarized Fields list. By default, this uses the summary operation on this field which is what we want. You could change the summary operation to average, minimum, maximum, count, and a host of others. To see all available options click on the SalesOrderHeader.SubTotal field in the Summarized Fields list and click the Change Summary button.

Figure 8

18. Click the OK button. You should now see the cross tab in the Group Header #1 section.

You can adjust the size or font of any of the fields in the cross tab. By default, the summarized fields for the column and row totals are bold. Since we are summarizing a money field Crystal already formatted the numbers with a currency symbol and two decimals. For this report we want to remove the two decimals and make it a regular font instead of bold To do this, follow these steps.

1.    Hold the Ctrl key down and left click on the summary fields.

Figure 9

2.    Right click on any of the fields and select Format Multiple Objects from the pop-up menu.

3.    The Format Editor Dialog box should appear.Click on the Font tab.

4.    Change the Style drop down list to Regular.

5.    Click the Number tab and change the Style to $1,123. This will remove the decimal places.  Click the OK button.

6.    The row header for this cross table is the employee's name. You will need to expand the width of the row header to avoid truncating the name. Simply click on the Row #1 Name field and drag the right window handle to the right. The Total field will automatically change its width as you change the width of the Row #1 Name field.

The next step is to create a line chart for each Sales Territory. The chart will display a line for each employee, the y axis will be the total sales for the employee, and the x axis will be the fiscal quarter. 

1.    Right click in the white space in the Group Header #1 section.

2.    Select InsertàChart… from the pop-up menu.

3.    The Chart Expert dialog will appear. Change the Chart Type to Line.

4.    There are six different types of line charts. Click the button for the line chart on the bottom left. This will display a line and a marker at the data points.

Figure 10

5.    Click the Data tab.

6.    Change the Place Chart drop down list to Once for each SalesTerritory.Name.

7.    Click on the FiscalQuarter field in the Available Fields list and click the > button to move this field to the On Change Of list box.

8.    Click on the EmployeeName field in the Available Fields list and click the > button to move this field to the On Change Of list box.

9.    Click the SalesOrderHeader.SubTotal field in the Available Fields list and click the > button to move this field to the Show Values list.

Figure 11

10. Click on the Text tab.

11. Uncheck the Auto Text for the Title. Change the Title to Total Sales By Employee.

12. Uncheck the Auto Text for the Group title. Change the Group title to Order Date.

13. Uncheck the Auto Text for the Data title. Change the Data title to Sales.

14. Click the OK button.

The Group Header #1 section will now be divided in two with the chart being in the top part and the cross tab in the bottom. Expand the width of the chart to the width of the page.

Figure 12

1.    Right click on the Report Header section and select Suppress (No Drill-Down).

2.    Right click on the Details section and select Suppress (No Drill-Down).

3.    Right click on the Report Footer section and select Suppress (No Drill-Down).

4.    Right click on the Group Footer #1 section and select Section Expert from the pop-up menu. Check the New Page after checkbox and click the OK button.

This report should only show one fiscal year at a time. The ASP.NET application will prompt the user for the fiscal year and the report will filter the report dynamically. To do this we will create a formula in the report for the fiscal year and dynamically change this formula using the Crystal Reports Object Model. We will set the selection formula in the report to select only these records.

1.    First create the FiscalYear formula. Right click on the Formula Field in the Field Explorer and select New… from the pop-up menu.

2.    Enter FiscalYear for the formula name and click the Use Editor button.

3.    Enter 2003 for the formula text and then click the Save and Close button.

4.    Now you need to set the selection formula in the report. From the Visual Studio menu select Crystal ReportsàReportàSelection FormulaàRecord…

5.    Enter the following formula.

Listing 3

Year ({SalesOrderHeader.OrderDate}) = {@FiscalYear}

6.    Click the Save and Close button.

The last step in designing the report is to add the page number to the page footer section. 

1.    Right click in the Page Footer section and select InsertàSpecial FieldàPage N of M from the pop-up menu.

2.    Drop the field in the report's page footer section.

3.    Make the field the entire width of the page.

4.    Right click on the field and select Format Object from the pop-up menu.

5.    On the Common tab change the Horizontal Alignment to Centered and click OK.

At this point you can preview the report in Visual Studio by clicking on the Main Report Preview button. You should see the chart and cross tab for all orders placed in 2003.

Now that you have built the report you can build a web page to display it. I will create a simple page that lets the user enter a fiscal year and view the report.

1.    Right click on the Adventure Works web site in the Solution Explorer. 

2.    Select Add New Item… from the pop-up menu.

3.    Select Web Form from the templates and change the name to SalesReport.aspx. Make sure the Language is set to C# and click the Add button.

4.    Open the page and view its markup. Add the following Register directive after the Page directive.

Listing 4

<%@ Register assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, 
PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR"%>

This allows you to use the Crystal Reports Viewer control that comes with Visual Studio.

5.    Add the following code between the div tags.

Listing 5

<asp:TextBox runat="server" ID="txtFiscalYear"></asp:TextBox>
<asp:Button ID="btnPreview" runat="server" onclick="btnPreview_Click" 
Text="Preview" />
<br />
<br />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
AutoDataBind="true" />    

This adds a text box that will allow the user to enter the fiscal year to print the report.

6.    Add the following using statements in the code behind.

Listing 6

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

7.    Add the following code the Page_Load event.

Listing 7

if (IsPostBack)        
{
    if (CrystalReportViewer1.Visible == true)
    {
        //Rebind the report.
        BindReport();
    }
}

8.    Add the following code for the Preview button's click event. You can create the event handler by double clicking on the button in Design mode.

Listing 8

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

This code calls a custom method called BindReport() and then shows the Crystal Report Viewer Control.

9.    Now add the following custom methods.

Listing 9

private void BindReport()
{
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("Sales.rpt"));
 
    SetTableLocation(report.Database.Tables);
 
    report.DataDefinition.FormulaFields["FiscalYear"].Text = txtFiscalYear.Text;
 
    CrystalReportViewer1.ReportSource = report;
}
 
private void SetTableLocation(Tables tables)
{
    ConnectionInfo connectionInfo = new ConnectionInfo();
 
    connectionInfo.ServerName = @"LTMTI30\SQL2008";
    connectionInfo.DatabaseName = "AdventureWorks";
    connectionInfo.UserID = "aspalliance";
    connectionInfo.Password = "aspalliance";
 
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
        tableLogOnInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogOnInfo);
    }
}

The first method creates an instance of the ReportDocument class. This represents the report that you created earlier and allows you to manipulate it at runtime. The SetTableLocation() method sets the table location for each table in the report. Again, this assumes you have created an "aspalliance" SQL Login and have given it access to the database. The FiscalYear formula is then set to the year the user typed into the report. You could add validation to make sure the user entered a four digit year, but for simplicity I left this out. The Crystal Report Viewer's source is then set to the report object. 

You can now run the project. Set the SalesReport.aspx page to the start page and run the project. Enter 2002 for the Fiscal Year and click the Preview button.

Figure 13

You can scroll through the report using the navigation buttons at the top of the page or you can click directly to a sales territory by clicking the name in the group tree on the left hand side of the report.

This article showed you how to create a cross tab report and also display the data using a graph.  Hopefully you can take this example and apply it to your own requirements. Crystal Reports is a great tool for integrating reports directly in your ASP.NET application and best of all it is free with Visual Studio. Good luck on your project and happy coding.