Building Reports Using ASP.NET and Crystal Reports - Part 4: Dynamic Grouping
 
Published: 18 Aug 2009
Abstract
In this fourth part of the series, Vince Varallo examines the creation of an employee report and demonstrates the usage of Crystal Reports object model to change the grouping fields and manipulate the properties of the group at runtime to show or hide sections, reset the page number, or insert a page break using Visual Studio 2008. He provides a step-by-step analysis of each procedure with the help of relevant screen shots and source code.
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 70922/ 159

Introduction

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.

Step 1: Create a new solution

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. 

Step 2: Add the Crystal Report 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.

Step 3: Connect to the database

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

Step 4: Create the Details section

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

Step 5: Create the Date Formulas

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, "")
Step 6: Create the Group

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.

Step 7: Viewing the report in an ASP.NET page

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.

Summary

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]



User Comments

Title: CRystal REports Dynamically   
Name: Naresh
Date: 12/7/2012 11:48:18 PM
Comment:
Good
Title: t   
Name: n
Date: 12/7/2012 11:47:21 PM
Comment:
ertt
Title: Another nice article   
Name: Rick
Date: 12/1/2009 4:02:34 PM
Comment:
Easy to follow and godd explainations. Thanks for taking the time to put this article together. I have gone through the other 3 parts and really getting a better feel fot the reports
Title: ASP.NET Crystal Reports Hosting   
Name: Helen
Date: 10/13/2009 8:46:44 PM
Comment:
I real like this article and I could learn more things about Crystal Reports
Thanks

___________________________________________________
http://www.webhost4lifereview.com/crystal-report-web-hosting/
Title: Can anybody help me please?   
Name: Beto
Date: 10/2/2009 12:40:03 PM
Comment:
What's up? I hope you help me... I want to have a report that has 3 pages per sheet... How can I do that? Thanks beforehand =)
Title: Very Nice Artical   
Name: Gaurav Gupta
Date: 9/24/2009 5:13:31 AM
Comment:
Dear Vince Varallo

The article is really great.It is very useful to understand crystal report. And the way of communicating is also very simple.

Thanks
Gaurav Gupta
Title: Problem   
Name: Pankaj Rai
Date: 9/18/2009 2:16:54 AM
Comment:
Dear Vince Varallo,

I have to made a crystal report which show the sum of amount datewise. And also to display all the transaction on particular date when I click on date feild in crystal report.Is there any technique to pass date as parameter for subreport.Please reply ASAP.


Thanks in Advance.
Title: Really Nice Artical   
Name: Pankaj Rai
Date: 9/18/2009 2:13:10 AM
Comment:
I have read this and learn something good regarding crystal report.

Thanks
Title: Very Good   
Name: Edwin
Date: 9/10/2009 10:56:39 AM
Comment:
I have been reading all the 4 parts and its very very usefull. Thanks a million. Just on more wish !!

VB code along with C# would have been amazing. Does your book has VB code as well as C#?

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 9/19/2014 5:47:46 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search