Acropolis

Crystal Reports with Filtering

Introduction

Whenever a web site stores data in a database, you'll want to run reports on that data, to find ways to improve customer service, detect abuse, etc.

Fortunately, Visual Studio comes with a powerful report generator, Crystal Reports, included for free! Once you've learned how to use it, it allows you to quickly create web pages that show various reports. Its many features include grouping, sorting, filtering, graphs, exporting to PDF and Excel files, etc. Figuring out how to use this power can be a bit tricky though.

This article shows step by step how to use Crystal Reports to add a report to a web page. You'll see how to programmatically apply grouping and filters, and how to provide drill down from group level totals to the supporting detail records.

Needed to run the code

Visual Studio 2005 (it may also work with Visual Studio 2003, but the code wasn't tested with this)
You need to have Crystal Reports installed as part of Visual Studio. To see if you have it installed, add a new item in Visual Studio - Crystal Report should be included in the list of templates. If it's not there, install it from the Visual Studio installation.
Access to a SQL Server 2005, SQL Server 2000 or SQL Server Express server, and the ability to restore a database to the server and to create a database user.

Installing the code

Download the sources and unzip into a directory.
You'll find the sources for a simple ASP.NET 2.0 web site, and a database backup file CrystalReportIntro.bak.

The article will talk you through creating the web site, so the web site sources are for convenience only. However, you do need to install the database:

Restore CrystalReportIntro.bak into your database server as database "CrystalReportIntro".
Create a login "mp" with password "crystal2007".
On database "CrystalReportIntro", create a user "mp" based on login "mp".
Have a look at the database. You'll find it has just one table "Sales" with simple sales information. And a view "vSales" - based on table "Sales" - that adds a calculated column with sales totals. The report will be based on that view.

I. Create a blank web site

Create a new ASP.NET web site, with language Visual C#.

II. Create Sales.rpt definition file

The Sales.rpt file will hold the definition of the report. Further down you'll see how to deploy that definition on one of your web pages.

Add a new item to the web site.
In the list of templates, choose Crystal Report.
Call it Sales.rpt and click Add.
Dismiss the registration popup if you get one.
On the Crystal Reports Gallery popup, choose to use the report wizard and the Standard expert. Click OK.
On the following page, expand the "Create new connection" section.
Then expand the "OLE DB (ADO)" section. A dialog will pop up.
Choose "SQL Native Client" as your provider. Leave "Use Data Link File" unchecked. Click Next.
Enter the name of your database server. Set the username to "mp", the password to "crystal2007", and the name of the database to "CrystalReportIntro".
There is one more page, but it has advanced stuff we don't need. Click Finish.
You're now back at the Report Creation Wizard. It lets you drill down to the database server and database you just added, and onto the tables, views, etc. in the database.

Drill down until you see the view "vSales". Move it to the right hand field. Click Next.

You now get to choose what fields to show in your report.

Click the little plus next to "vSales" to see the individual fields, and move all fields over to the right. However, you won't need the Id field, so move that back to the left. Hit Next.

This page lets you add grouping on a particular field. The report will have grouping, and will be showing sales totals per group. To get Crystal Reports to add grouping right away, specify grouping on one of the fields. Later you'll see how to change the grouping field dynamically in C# code.

Move the "ProductName" field (under Report Fields) to the right hand box. Hit Next.

It now lets you specify summaries. Trying to be helpful, Crystal Reports has already specified a number of fields for you. For this example though, only do summation on the Total field.

Move all fields in the right hand box, except for the Total field, back to the left. Hit next.

Hit Next 3 times to skip through the Group Sorting page and the Chart page and the Record Selection page.
Choose a Report Style you like. Personally I tend to go for the "Red/Blue Border" style. Hit Finish.
The report definition file Sales.rpt should now open, showing your report.

III. Add the report to your Default.aspx page

Now that we have the report definition, lets add it to a ASP.NET page.

Open Default.aspx in design mode.
Expand the Crystal Reports section in the toolbox
Drag a CrystalReportViewer control onto the page. You'll see that it adds the CrystalReportViewer control.
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
Hit F5 to run Default.aspx. You'll get just a blank page. Some code needs to be added to bind the Sales.rpt file to the CrystalReportViewer control on the page.
First add a few assemblies to Default.aspx.cs that support Crystal Reports:
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
public partial class _Default : System.Web.UI.Page
{
Add a method CreateCrystalReportDocument that builds a ReportDocument object. It is this object that will tell the CrystalReportViewer control on the page where to find the report definition, what record selection and grouping to use, etc.

Collapse

public partial class _Default : System.Web.UI.Page

{

private const string sourceTableOrView = "vSales";

// -------------------------------------

private ReportDocument CreateCrystalReportDocument(

string filterString,

string groupFieldName)

{

ReportDocument rpt = new ReportDocument();

// ---- Load the report definition

string reportPath = Server.MapPath("Sales.rpt");

rpt.Load(reportPath);

// ---- Assign connection information for each table in the database

// Build connection info

ConnectionInfo connectionInfo = new ConnectionInfo();

connectionInfo.ServerName = @"YOUR DATABASE SERVER NAME";

connectionInfo.DatabaseName = "CrystalReportIntro";

connectionInfo.UserID = "mp";

connectionInfo.Password = "crystal2007";

// Assign to all tables used by the report

Tables tables = rpt.Database.Tables;

foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

{

TableLogOnInfo tableLogonInfo = table.LogOnInfo;

tableLogonInfo.ConnectionInfo = connectionInfo;

table.ApplyLogOnInfo(tableLogonInfo);

}

// ---- Set the record selection. If filterString is null, all records will be selected.

rpt.DataDefinition.RecordSelectionFormula = filterString;

// ---- Set grouping field

// We'll set the first level of grouping (group 0)

Group group = rpt.DataDefinition.Groups[0];

// We'll be grouping on a field in vSales ("vSales" is the value of sourceTableOrView)

CrystalDecisions.CrystalReports.Engine.Table groupFieldTable = rpt.Database.Tables[sourceTableOrView];

// Assign the field whose name is passed into this function via parameter groupFieldName

group.ConditionField = groupFieldTable.Fields[groupFieldName];

return rpt;

}

Don't forget to replace the string "YOUR DATABASE SERVER NAME" with the name of your database server.

In a real application, you'll want to structure this a bit better, factoring out methods, etc. The code here is structured to make it easy to follow.

CreateCrystalReportDocument starts by loading the report definition contained in the Sales.rpt file.
It then tells the report how to connect to the database. A Crystal Reports report has connection information per table - this way your report can show data from different databases. So the code loops through all tables used by the report to assign the connection information.
It then sets the record selection string, as passed in to the function via parameter filterString. Later on you'll see this is basically a SQL WHERE clause.
Finally it sets the field on which to group the data, as passed in to the function via parameter groupFieldName. The code keeps it simple and only sets one level of grouping (group 0). And it assumes that the group field will always come from our view, "vSales". It would be easy to have additional grouping levels, and use fields from different tables.

Finally, in the Page_Init method, assign the ReportDocument object that was made by CreateCrystalReportDocument to the CrystalReportViewer control. Pass in null for filterString (to show all records without filtering) and ProductName as the field to group on. Later we'll do something more useful with this.
private void Page_Init(object sender, EventArgs e)
{
ReportDocument rpt = null;
rpt = CreateCrystalReportDocument(null, "ProductName");
CrystalReportViewer1.ReportSource = rpt;
}

It needs to go in Page_Init rather than Page_Load due to technical issues with the Crystal Report software.

Hit F5 again to run your site. The default page will appear with the report, grouped by Product Name.

Click the export button to the far left in the grey bar (next to the print button). You'll find you now have the ability to export your report to lots of formats, including PDF and Excel files. Cool eh?

IV. Add dynamic filtering and grouping

First lay some groundwork by adding a number of input fields to Default.aspx to set grouping and filters.

Collapse

<div>

<p>

Filter

<table>

<tr><td>Product Name:</td><td><asp:TextBox ID="tbProductName" runat="server" /></td></tr>

<tr><td>Price:</td><td><asp:TextBox ID="tbPrice" runat="server" /></td></tr>

</table>

</blockquote>

</p>

<p>

Group on:

<asp:DropDownList ID="ddlGrouping" runat="server">

<asp:ListItem Selected="True" Value="ProductName">Product Name</asp:ListItem>

<asp:ListItem Selected="False" Value="Price">Price</asp:ListItem>

<asp:ListItem Selected="False" Value="NbrSold">Nbr Sold</asp:ListItem>

<asp:ListItem Selected="False" Value="SoldInCity">Sold In City</asp:ListItem>

</asp:DropDownList>

</p>

<p>

<asp:Button ID="btnReload" runat="server" Text="Reload Report" OnClick="btnReload_Click" />

</p>

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />

The TextBoxes will be used to filter on one or more fields. If a TextBox is left blank, it won't participate in the filter. If you enter a value, that value will be added to the filter. Great if you only want to see sales in Boston with item price $3.50.

To keep things simple, the filter only tests on equality (= operator). It wouldn't be hard to add more options, such as Like (for strings), greater than, and less than.

The dropdown box will let you specify the field to do grouping on.

Back in Default.aspx.cs, add a utility function AddFilter to help build a filter expression.

Collapse

private void AddFilter(

ref string filterString,

string databaseFieldName,

TextBox fieldInput,

string valueQuoteChar)

{

string fieldValue = fieldInput.Text.Trim();

// If input field left blank, don't filter on it.

if (fieldValue == "") return;

// Escape for quotes if we're going to quote the value

string escapedFieldValue = fieldValue;

if (valueQuoteChar == @"'") escapedFieldValue = fieldValue.Replace(@"'", @"''");

// Add boolean clause to the complete filter expression

// This method always uses operator =

string booleanSqlExpression =

"{" + sourceTableOrView + "." + databaseFieldName + "} = " +

valueQuoteChar +

escapedFieldValue +

valueQuoteChar;

// separate boolean clauses with AND operator

if (!string.IsNullOrEmpty(filterString)) filterString += " AND ";

filterString += booleanSqlExpression;

}

It takes one of the TextBoxes you just added to Default.aspx, the corresponding database field and a quote character (used for string values), generates a boolean clause and adds it to whatever is already in parameter filterString.

The code it generates is pretty much the same as you'd use in a SQL WHERE, except that field names are within curly braces { }.

Finally add the click handler btnReload_Click for the Reload button.

Collapse

protected void btnReload_Click(object sender, EventArgs e)

{

// Create filter expression, based on the filter input fields.

// If an input field is left blank, it isn't used in the filter expression.

string filterString = null;

AddFilter(ref filterString, "ProductName", tbProductName, @"'");

AddFilter(ref filterString, "Price", tbPrice, "");

AddFilter(ref filterString, "NbrSold", tbNbrSold, "");

AddFilter(ref filterString, "SoldInCity", tbSoldInCity, @"'");

// Get the name of the field to group on

string groupFieldName = ddlGrouping.SelectedValue;

// Create report document based on the new filter and grouping, and assign to

// CrystalReportViewer control. This will show the new report.

ReportDocument rpt =

CreateCrystalReportDocument(filterString, groupFieldName);

CrystalReportViewer1.ReportSource = rpt;

}

It simply uses AddFilter to build a filter string, gets the group field name from the dropdown, and then creates a new report using CreateCrystalReportDocument.

Hit F5 to run the web site again. Try grouping on differing fields. Put Toy in the Product Name filter field to see only toy sales, etc.

V. Hang on to report settings after page reload

While the page is running, in the grey horizontal box, you'll see a zoom drop down - it's the one saying 100%. Change it to 150%. The report will reload with a bigger font, but it will have lost any filtering and grouping! This is because the report settings aren't stored in ViewState. Some code is needed to hang on to the settings when the page reloads.

You may be wondering whether adding

if (!Page.IsPostBack) { .... }

to Page_Init will help by stopping it from re-initialising the CrystalReportViewer. Give it a try - the report won't load at all when the page reloads after changing the zoom.

Another approach could have been to have Page_Init construct a report document with the right filter string and grouping, based on the filter TextBoxes and the grouping dropdown. However, the value of those controls is not yet available when Page_Init executes.

A simple approach that does work is to store the report document in a Session object.

Update Page_Init so it stores the report document in a Session object "SalesRpt". Instead of always regenerating the report document, it now loads it from the Session object, if it is there.
private void Page_Init(object sender, EventArgs e)
{
ReportDocument rpt = null;
// Load the report document from the Session object.
// If it's not there, generate a new report document.
if (Session["SalesRpt"] == null)
{
rpt = CreateCrystalReportDocument(null, "ProductName");
Session["SalesRpt"] = rpt;
}
else
{
rpt = (ReportDocument)Session["SalesRpt"];
}
CrystalReportViewer1.ReportSource = rpt;
}
Also update btnReload_Click, so it updates the Session object with the new report document it has just generated - so Page_Init will pick it up at the next page reload.

Collapse

protected void btnReload_Click(object sender, EventArgs e)

{

// Create filter expression, based on the filter input fields.

// If an input field is left blank, it isn't used in the filter expression.

string filterString = null;

AddFilter(ref filterString, "ProductName", tbProductName, @"'");

AddFilter(ref filterString, "Price", tbPrice, "");

AddFilter(ref filterString, "NbrSold", tbNbrSold, "");

AddFilter(ref filterString, "SoldInCity", tbSoldInCity, @"'");

// Get the name of the field to group on

string groupFieldName = ddlGrouping.SelectedValue;

// Create report document based on the new filter and grouping, and assign to

// CrystalReportViewer control. This will show the new report.

ReportDocument rpt =

CreateCrystalReportDocument(filterString, groupFieldName);

CrystalReportViewer1.ReportSource = rpt;

// Store newly generated report document in Session object, so Page_Init will pick it up.

Session["SalesRpt"] = rpt;

}

Hit F5 to run the page again. Set a filter and reload the report. Now change the zoom - the zoom will change but the filtering and grouping on the report will remain.

VI. Fix up the headers and columns

Some of the elements on the report don't look all that good - little things like a header "ProductName" instead of "Product Name". Fortunately, changing the appearance of a report is very simple.

Open the Sales.rpt file in Visual Studio. It allows you to edit the report - change text, move fields, etc.
Double click in the ProductName header. It will let you insert a space between "Product" and "Name".
Crystal Reports probably put the sales total per group in the wrong place. Click on it, and drag to a better place.
Right click a field and click "Format Object" to change colors, borders, etc.

VII. Add drill down to the report

With drill down, the report starts off only showing group names and group totals. This gives users a nice overview without overwhelming them with detail. They can then drill down to the details of a group by simply clicking the group name.

Open Sales.rpt. Right click on the grey horizontal bar saying "Details", and choose "Hide (drill down ok)".
Hit F5 to run the page again. Click on one of the group headers (for example Toys) - a new page will open with the individual sales for that group. Use the drop down on the grey horizontal bar to open the main report again.
Note that the main report looks a bit odd.
- The headers really only make sense in the drilled down state, not on the main report.
- But when you drill down, the headers disappear!
- Each group header is shown twice.

Lets fix this now.

To hang on to the headers while drilling down:
- Go back to Sales.rpt (in Visual Studio).
- Right click on the report (in Visual Studio).
- Hover on "Report" to get the Report submenu to appear.
- Click Report Options.
- Check "Show All Headers on drill-down".
- Click OK.
Get rid of the duplicate group name. In Sales.rpt, you'll see the group name field twice (it says "Group #1 Name") - once above the detail section, once below it. Get rid of the group name field above the detail section - click on it to select it, then hit the delete button.
Finally ensure that the headers are only shown while drilling down:
- Drag all headers from the top of the report down to the GroupHeaderSection1 (just above the Details section). This is the same area from which you just deleted the duplicate group name field.
- Right click on the grey horizontal bar saying "GroupHeaderSection1", and choose "Hide (drill down ok)".
Done! Run the report again. The group headers should now show up only once with their totals. And the headers will only appear when you click a header to drill down.

Acropolis

Monday, October 22, 2007

Thursday, June 21, 2007

What is Acropolis

Blog Archive

About Me