Building Reports Using ASP.NET and Crystal Reports - Part 4: Dynamic Grouping
page 8 of 9
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 70457/ 180

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.


View Entire Article

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 8/23/2014 1:32:10 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search