This is the fourth 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, or Part 3, but it is not required. This article focuses on creating an ASP.NET web application that allows a user to dynamically change the grouping on a report without having to create a new report file. How many times have you created a report for a user and once they start using it they ask you to give them the same data just grouped a different way?  You may want to show a count of records or a summary in the group footer. This is especially true when working with dates. Users ask to see the same data by year, quarter, month, or week. You could create a separate report file for each group, but this can be difficult to maintain. Instead, Crystal Reports for .NET allows you to manipulate the report at runtime so the user can change the group themselves without creating multiple copies of the report.

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 as well.  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 will need to download the 2005 AdventureWorks samples.

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

Figure 1

The user can change the group in the report by either using a database field or a formula field.  The Tables and Fields drop down list are populated with the tables and fields that are defined in the Crystal Report file. Clicking the "Preview With DB Fields" button will change the group in the report to use the field selected in the drop down list. The Formula Fields drop down list is populated with all the formulas in the report. This particular report has formula fields for the Year, Quarter, Month, and Week for each employee's date of hire. The "Preview With Formula Fields" button changes the group in the report to the selected item in the Formula Fields drop down list.  The report is displayed in the web page by using the Crystal Report Viewer control.

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 willl use File.

4.    Set the Web Site Name to DynamicGrouping. 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. Follow these steps:

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

1.    Right click the Database Fields node in the Field Explorer window.

2.    Select Database Expert from the pop-up menu. The Database Expert dialog should appear.

3.    Click the plus(+) sign next to the Create New Connection node from the Available Data Sources.

4.    Click the plus(+) sign next to the OLE DB (ADO) node. A dialog box should appear.

5.    Select SQL Native Client from the Provider list and click the next button.

6.    Enter your SQL Server name, a valid user id and password. Select AdventureWorks as the database and click the Next button.

7.    Click the Finish button.

8.    Click the plus(+) sign next to the AdventureWorks database. Add the following tables to the report by clicking on the table and then use the arrow button (>) to move the table to the Selected Tables list. The tables are Person.Address, Person.Contact, Person.StateProvince, HumanResources.Employee, and HumanResources.EmployeeAddress.

9.    Click the OK button.

Figure 3

1.    Click the plus(+) sign next to the Database Fields in the Field explorer. Click the plus(+) sign next to the Contact table.

2.    Drag the LastName and FirstName fields to the Details section.

3.    Click the plus(+) sign next to the Employee table and drag the HireDate to the Details section.

4.    Click the plus(+) sign next to the Address table and drag the AddressLine1 and City fields to the Details section.

Figure 4

This report will allow the user to group by the year, quarter, month, or week of the employee's hire date. To do this you need to create a formula for each.

1.    Right click on the Formula Fields node in the Field Explorer window.

2.    Select New… from the pop-up menu and enter HireYear for the name. Click the Use Editor button.

3.    Enter the following formula.

Listing 1

totext(year({Employee.HireDate}), 0, '')

This converts the year of hire to text and tells Crystal to use no decimals or thousands separator.

4.    Click the Save and close button.

5.    Do the same for the other formulas. Here is the code for each.

Listing 2

HireQuarter:

totext(Year ({Employee.HireDate}), 0, "") + "-" + totext(DatePart ("q", 
      {Employee.HireDate}), 0, "")

HireMonth:

totext(Year ({Employee.HireDate}), 0, "") + "-" + totext(Month 
      ({Employee.HireDate}), 0, "")

HireWeek:

totext(Year ({Employee.HireDate}), 0, "") + "-" + totext(DatePart ("w", 
      {Employee.HireDate}), 0, "")

The groups must be defined in the Crystal Report at design time for you to manipulate them at run time. You can use any field to group by at design time because it will be overwritten at run time.

1.    Right click on any white space in the report. Click InsertàGroup from the pop-up menu.

2.    Select Address.City from the list and click the OK button.

Figure 5

3.    Right click on the LastName field in the Details section and select InsertàSummary from the pop-up menu.

4.    Change the "Calculate This Summary" drop down list to Count.

5.    Change the "Summary Location" drop down list to Group #1: Address.City - A.

Figure 6

6.    Click the OK button.

7.    This adds a count of the records based on Group 1. When you run the report you will see this number change as you change the group.

8.    Add a label to the left of the count field so the user will know what it refers to.

9.    To add the label you need to move the Count Field in the footer to the right by clicking and dragging it.

10. Right click to the left of the Count Field and select InsertàText Object from the pop-up menu.

11. Place the field to the left of the Count Field.

12. Enter "Count:" for the text and change the format to Bold.

Figure 7

13. Hide Section1, Section4, and Section5 by right clicking on the section header and selecting Suppress from the pop-up menu.

Now that you have built the report you can build a web page to display it. I will create a page that displays the database fields and formula fields that are defined in the report. We will do all of this in this Default.aspx page that is part of the DynamicGrouping web site we created.

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

Listing 3

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

Listing 4

<table>
  <tr>
    <td colspan="2">Group By:</td>
    <td colspan="2">or Group By:</td>
  </tr>
  <tr>
    <td>Tables:</td>
    <td><asp:DropDownList runat="server" ID="ddlTables" AutoPostBack="True"
       onselectedindexchanged="ddlTables_SelectedIndexChanged"></asp:DropDownList>
    </td>
    <td>Formula Fields:</td>
    <td><asp:DropDownList runat="server" ID="ddlFormulaFields"></asp:DropDownList> 
    </td>
  </tr>
  <tr>
    <td>Fields:</td>
    <td><asp:DropDownList runat="server" ID="ddlFields"></asp:DropDownList> 
    </td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td colspan="2"><asp:Button ID="btnPreview" runat="server" 
                  onclick="btnPreview_Click" Text="Preview With DB Fields" />
    </td>
    <td colspan="2"><asp:Button ID="btnPreviewWithFormula" runat="server" 
                    Text="Preview With Formula Fields"
                    onclick="btnPreviewWithFormula_Click" /> 
    </td>
  </tr>
</table>        
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
            AutoDataBind="true" />            

This adds the tables, fields, and formulas drop down lists. These will be populated in the page load event. The btnPreview and btnPreviewWithFormula buttons are used to display the report in the Crystal Report Viewer control. 

Figure 8

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

Listing 5

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

4.    Add the following code the Page_Load event.

Listing 6

if (!IsPostBack)
{            
  //Hide the crystal report viewer
  CrystalReportViewer1.Visible = false;
 
  //Load the drop down list with all the tables in the report.
  //Set the first item to "Select A Table"
  ddlTables.Items.Add(new ListItem("Select A Table"));
 
  //Load the report so you can get the tables
  ReportDocument report = new ReportDocument();
  report.Load(Server.MapPath("EmployeeListing.rpt"));
 
  //Loop around for each table in the report.
  foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in 
           report.Database.Tables)
  {            
    //Add an item to the drop down list for this table.
    ddlTables.Items.Add(new ListItem(tbl.Name));
  }
 
  //Load the formula drop down list
  foreach (FormulaFieldDefinition fld in report.DataDefinition.FormulaFields)
  {
    ddlFormulaFields.Items.Add(new ListItem(fld.Name));
  }
}
else
{
  //If this is a postback the rebind the report so the paging works.
  if (CrystalReportViewer1.Visible == true)
  {
    //Rebind the report.
    BindReport(UseDatabaseField);
  }
}

This code loads the Tables drop down list with the tables defined in the report. It also loads the formula drop down list with the formulas defined in the report.

The else logic handles a Postback. When the user pages through a report, a Postback event is fired on the server and the report must be repopulated. A custom property called "UseDatabaseField" is used to determine if the user clicked on the Preview With DB Fields or Preview With Formula Fields button. The property stores its value in the ViewState so it is persisted for each request.

Add the following code to add the property.

Listing 7

private bool UseDatabaseField
{
  set { ViewState["UseDatabaseField"] = value; }
  get { return Convert.ToBoolean(ViewState["UseDatabaseField"]); }
}

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

Listing 8

protected void btnPreview_Click(object sender, EventArgs e)
{
    BindReport(true);
}

This code calls a custom method called BindReport() and passes in True so the method knows to use the database fields to set the grouping condition.

6.    Add the following code for the Preview With Formula Field button's click event.

Listing 9

protected void btnPreviewWithFormula_Click(object sender, EventArgs e)
{
    BindReport(false);
}

7.    When the user selects a table from the drop down list, the web page should populate the Field's drop down list with the fields for that table. Add the following code to the Table drop down list's selected index changed event.

Listing 10

protected void ddlTables_SelectedIndexChanged(object sender, EventArgs e)
{
  ddlFields.Items.Clear();
 
  if (ddlTables.SelectedIndex != 0)
  {
    //Load the report so you can get the database fields
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("EmployeeListing.rpt"));
 
    CrystalDecisions.CrystalReports.Engine.Table tbl = 
      report.Database.Tables[ddlTables.SelectedItem.Text];
 
    foreach (DatabaseFieldDefinition fld in tbl.Fields)
    {
      ddlFields.Items.Add(new ListItem(fld.Name));
    }
  }
}

8.    Now add the following custom methods.

Listing 11

private void BindReport(bool useDatabaseField)
{
  //Set this property to the parameter so on postback you can retain the grouping.
  UseDatabaseField = useDatabaseField;
 
  ReportDocument report = new ReportDocument();
  report.Load(Server.MapPath("EmployeeListing.rpt"));
 
  SetTableLocation(report.Database.Tables);
 
  //If using a database field then use the database drop down lists.
  if (useDatabaseField)
  {
    report.DataDefinition.Groups[0].ConditionField =   
      report.Database.Tables[ddlTables.SelectedItem.Text].
      Fields[ddlFields.SelectedItem.Text];
  }
  else
  {
    //Use the formula drop down list.
    report.DataDefinition.Groups[0].ConditionField = 
      report.DataDefinition.FormulaFields[ddlFormulaFields.SelectedItem.Text];
  }
 
  CrystalReportViewer1.ReportSource = report;
 
  CrystalReportViewer1.Visible = true;
                
}
 
private void SetTableLocation(Tables tables)
{
  ConnectionInfo connectionInfo = new ConnectionInfo();
 
  connectionInfo.ServerName = @"YOURSERVERNAME";
  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 BindReport 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 connects the report to the database. You should replace the server name, database name, user id, and password with your information. You should put this in the web.config file, but for demo purposes I put it directly in the code.

The groups are changed by changing the ConditionField property of the report.DataDefinition.Groups[0] object. The Groups property is a collection of groups defined in the report. Since we only have one group we want to change the first group.

Now try running the project. Select a table and then select a field and click the Preview With DB Fields button. The report should appear with the new group. Try selecting a formula field and click the Preview With Formula Field button. The report should now be grouped by the formula.

This article demonstrated how to change groups in a Crystal Report at runtime using the Crystal Object model. This can be very handy in your ASP.NET applications. Good luck on your project and happy coding.

[Download Dynamic Grouping Source]