This is the sixth 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 have read Part 1, Part 2, Part 3, Part 4, or Part 5 but it is not required.  This article will create a Sales Forecast report using the AdventureWorks database.  The report will use parameter fields to prompt the user for a sales forecast factor to be applied to historical sales and a date range to filter the report.  You will then build a web page to display the report to the user using the Crystal Report Viewer control.

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

This report uses the SalesOrderHeader table to determine prior sales and then applies the sales forecast factor, in this sample 5%, to calculate the sales forecast.  Creating parameters in Crystal Reports is quite simple and it gives you the power to customize a report at runtime rather than design time.

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'll use File.

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

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.  To do this follow these steps:

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

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

8.    Click on Crystal Report from the templates.  If you don't 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.

9.    Change the name of the report to SalesForecast.rpt.  The Language drop down should already be set to Visual C# but if it isn't do so.

10. 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're using Crystal Reports.  You can skip registering the product but it will keep prompting you every time you open a report so it's 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.

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

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

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

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

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

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

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

16. 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" and 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.

17. 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'll have to use SQL Server Management Studio to add the aspalliance Login and add them to the db_owner role for the AdventureWorks database.  IMPORTANT: Do not use the AdventureWorks2008 database.  This database uses the new geography type in the Person.Address table which is not recognized by Crystal Reports.

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

19. Click the plus sign next to AdventureWorks and you'll see the list of schemas defined in the database.  If you click the plus sign next to a schema name you'll 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.

20. 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 Sales schema and expand the Tables.  Click on the SalesOrderHeader table and then click the > button to move this table to the Selected Tables list.  Do the same for the SalesTerritory table.

21. Now that you have selected the tables you need to define the relationships between these tables.  To manually configure the relationships click the Links tab in the Database Expert dialog.

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

23. The line should already be drawn between the SalesOrderHeader and SalesTerritory tables.    If you don't see this line you can easily create the relationship by clicking on the foreign key field and dragging it to the primary key field.

24. Click the OK buton.

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'll see the fields in that table.  These are now available to be used in the report.

There are three parameter fields for this report.  The first is the forecast sales factor parameter.  This is the percentage applied to the original sales amount.  The second and third parameters are the start and end dates that should be used to filter the report.  Only records with an order date between the start and end date will be used to forecast the sales amount.

1.    Right click on the ParameterFields node in the FieldExplorer and select New… from the pop-up menu.

2.    Enter "Sales Forecast Factor" for the name.

3.    Enter "Enter the percentage to be applied." for the description.

4.    Select Number for the Value type.

5.    Click the OK button.

6.    You should now see a plus sign next to the ParameterFields node in the Field Explorer.  Click the plus sign to see the new parameter field you just created.

7.    Right click on the ParameterFields node in the FieldExplorer and select New… from the pop-up menu.

8.    Enter "Start Date" for the name.

9.    Enter "Enter the start date." for the description.

10. Select Date for the Value type.

11. Click the OK button.

12. Right click on the ParameterFields node in the FieldExplorer and select New… from the pop-up menu.

13. Enter "End Date" for the name.

14. Enter "Enter the end date." for the description.

15. Select Date for the Value type.

16. Click the OK button.

All three parameter fields should now appear under the ParameterFields node in the Field Explorer.

The Forecast formula field will apply the Sales Forecast Factor to the SalesOrderHeader.Subtotal field. 

1.    Right click the Formula Fields node in the Field Explorer and select New… from the pop-up menu.

2.    Enter Forecast for the name and click the User Editor button.

3.    Enter the following formula in the Formula Editor.

{SalesOrderHeader.SubTotal} * (1 + {?Sales Forecast Factor} / 100)

4.    Click the Save and close button.

You should see the Forecast formula under the Formula Fields node in the Field Explorer.

The Base Sales cross tab will display the original sales figure without the Sales Forecast Factor applied.  This will be displayed on the first page of the report.  The second page will have the Forecast Sales cross tab which displays the original sales figure multiplied by the Sales Forecast Factor.

1.    Right click in the Report Header1 section and select InsertàCross Tab… from the pop-up menu.  Drop the cross tab into the Report Header1 section.

2.    The cross tab expert will appear.

3.    In the available fields you should see a node for the SalesOrderHeader table.  Scroll down to the SubTotal field and then click the > button that is to the left of the Summarized Fields listbox.

4.    Expand the SalesTerritory table in the Available Fields list and click on the CountryRegionCode field.  Click the > button to the left of the Rows listbox.

5.    Click the Name field under the SalesTerritory table and click the > button to the left of the Rows listbox.

6.    Scroll up to the OrderDate field under the SalesOrderHeader table in the Available Fields listbox.  Click the > button to the left of the Columns listbox.

7.    By default when you put a date field in the columns of a cross table it will create a column for each date in the database.  This will make the report unreadable.  Instead we'll create a column for each quarter in the table.  To change how the columns are displayed click on the Group Options button under the Columns listbox.

8.    In the "The column will be printed:" dropdown select "for each quarter." and click the OK button.

The Cross Tab Expert should look like the following image.

9.    Click the OK button.  The cross tab will appear in the ReportHeader1 section.

10. By default the summary fields in the cross tab are bolded.  You can change the font to regular by click on the field in the cross tab and depressing the Bold button in the toolbar.

11. Since we are dealing with numbers in the millions you'll need to expand the size of each summary field in the cross tab.  You can do this by clicking on the field and dragging the right window handle.  If you click on the top most summary field in the column and resize it then Crystal Reports will automatically make the other fields in that column the same size.

The next step is to create the cross tab that displays the data with the Sales Forecast Factor applied.

1.    Right click the Report Header1 section header. Select Insert Section Below from the pop-up menu.

2.    Right click in ReportHeaderSection1 and select InsertàCross Tab… from the pop-up menu.

3.    The Cross Tab Expert should appear.  Click on the Forecast formula field in the Available Field listbox.  Click the > button next to the Summary Fields listbox.

4.    Expand the SalesTerritory table in the Available Fields list and click on the CountryRegionCode field.  Click the > button to the left of the Rows listbox.

5.    Click the Name field under the SalesTerritory table and click the > button to the left of the Rows listbox.

6.    Scroll up to the OrderDate field under the SalesOrderHeader table in the Available Fields listbox.  Click the > button to the left of the Columns listbox.

7.    Click on the Group Options button under the Columns listbox.

8.    In the "The column will be printed:" dropdown select "for each quarter." and click the OK button.

9.    Click the OK button.  The cross tab will appear in the ReportHeaderSection1 section.

10. Change the font for the summary field to regular and resize the fields as you did in Step 5.

This report will show the original sales info on the first page and the forecast sales info on the second page.  Each page should display the AdventureWorks logo and company name.  The logo can be found with the accompanying code.  You'll need to copy the Logo.bmp file to the folder where you created the web site.

1.    Make the Section1 (Report Header a) section bigger by waving your mouse over top of ReportHeaderSection1 (Report Header b) until the mouse pointer becomes a vertical bar.

2.    Expand the section by left clicking and dragging the mouse pointer down.

3.    Drag the cross tab in this section to the bottom of the section.

4.    Right click in this section and select InsertàPicture… from the pop-up menu.  Navigate to the Logo.bmp file and click the Open button.

5.    Drop the picture at the top of the section.  You may need to make the section bigger or drag the cross tab below the picture.

6.    Now add the title.  Right click in this section and select InsertàText Object from the pop-up menu.  Drop the text object next to the picture.

7.    Enter Adventure Works Cycle for the text.

8.    Right click on the text object and select Format Object from the pop-up menu.

9.    Click the Font tab and change the size to 28.  Change the Color to Blue.

10. Click the Paragraph tab and change the Horizontal alignment to Centered.

11. Click the OK button.  You should resize the text object so that Adventure Works shows on the top line and Cycle appears below it.

12. Right click on the ReportHeaderSection1 (Report Header b) section header and select Section Expert… from the pop-up menu.

13. Check the New Page Before checkbox and click the OK button.  This will create a page break before this section.

14. You can now hide the other sections by right clicking on the section header and selecting Suppress (No Drill-Down) from the pop-up menu.

The next step is to add the selection formula so the report only selects the data between the start and end dates entered by the user.

1.    From the Crystal Reports menu in Visual Studio select ReportàSelection FormulaàRecord…

2.    Enter the following formula.

{SalesOrderHeader.OrderDate} >= {?Start Date} and 
      {SalesOrderHeader.OrderDate} <= {?End Date}
The parameter fields can be used in a formula by using the {?Start Date} and {?End Date} syntax.

3.    Click Save and close.

Now that you have built the report you can build a web page to display it.  I'll create a simple page that lets the user click a button to preview the report.  We'll do all of this in this Default.aspx page that is part of the web site we created.

1.    Open the Default.aspx page and view its markup.  Add the following Register directive after the Page directive.

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

2.    Add the following code between the div tags.  

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

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

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

4.    Add the following code for the Preview button's click event.

protected void btnPreview_Click(object sender, EventArgs e)
{
    BindReport();
 
    ViewState["ParametersShown"] = "True";
}

This code calls a custom method called BindReport() and then stores a value of true to persist the fact that the parameters have been shown to the user.  Since we are using the Crystal Viewer Control it will automatically display a page to collect the values for the parameters in the report.

5.    Now add the following custom methods.

private void BindReport()
{
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("SalesForecast.rpt"));
 
    SetTableLocation(report.Database.Tables);
 
    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've created an "aspalliance" SQL Login and have given it access to the database.  The Crystal Report Viewer's source is then set to the report object. 

6.    Add the following to the Page_Load event.

//Check if the parmeters have been shown.
if ((ViewState["ParametersShown"] != null) && 
    (ViewState["ParametersShown"].ToString() == "True"))
{
    BindReport();            
}

This will call the BindReport method again once the user has entered the parameters.  Crystal keeps these values on postback.

You can now run the project by pressing F5.  Click the Preview button. You should be prompted for the Sales Forecast Factor, the Start Date, and the End Date.

Enter 5 for the Sales Forecast Factor.  This will increase the sales numbers by 5%.  Click on the icon next to each date to set the start date to 1/1/2002 and the end date to 12/31/2002.  Now click the Submit button.

The first page of the report should show the original sales numbers and the second page will show the forecast sales numbers.

[Download Source]

This article demonstrated how to use parameter fields to filter a report and how to use the parameter fields in a formula. Crystal Reports takes care of most of the plumbing behind the scenes to prompt the user for these values allowing you to concentrate on the business rules in the report rather than the technical details. Good luck on your project and happy coding.