A great new feature in SAP Crystal Reports for Visual Studio
2010 is the dynamic sorting control. This allows user to change the sort order
of the report while they are viewing it in the report viewer. There are up and
down arrows placed next to the column headers that the user can click on to
sort ascending or descending. This is a feature that has been a long time
coming in my opinion. Here are the steps to show the sorting control in your
report.
1.
Right click on you project in the Solution Explorer and select Add New
Item… from the pop-up menu.
2.
Select Crystal Reports from the list of templates and rename the file
EmployeeList.rpt. Click the OK button.
3.
The Crystal Report Gallery dialog box will appear. Select "As a
Blank Report" and click the OK button.
4.
A blank Crystal Report should appear in the designer. Now you need to
tell the report what database to connect to so you can design the report. In
the Field Explorer window you should see the Database Fields node. Right click
on this node and select Database Expert…
5.
The Database Expert dialog box should appear. Under the Available Data
Sources list you should see the "Create New Connection" node. Click
the plus sign next to this node.
6.
Click the plus sign next to OLE DB (ADO). The OLE DB (ADO) dialog box
should appear. Select "Microsoft OLE DB Provider for SQL Server"
from the list and click the Next button.
7.
Enter the Server name, User ID, Password, and select the AdventureWorks
database from the list. Click the Finish button. If you entered all the
correct information the dialog box should close. If you get an error fix your
settings and click the Finish button again.
8.
You should see the AdventureWorks database listed under the OLE DB (ADO)
connections. Click the plus sign next to the database name.
9.
Click the plus sign next to the Person schema and then click the plus
sign next to tables. This should list all the tables in the Person schema.
10. Hold
down the Ctrl button and left click on the Address, Contact, and StateProvince
tables. Click the > arrow button to move these into the list of Selected
tables.
11. Expand
the Human Resources schema and expand the tables under it. Move the Employee
and the EmployeeAddress tables to the list of selected tables.
12. Click
the OK button. The Links dialog should appear. This lets you define the joins
between the tables. The links should be defined as shown in the following
diagram.
13. Click
the OK button again.
14. The
Database Fields node in the Field Explorer should now have a plus sign next to
it. Click the plus sign to view the tables and then click the plus sign next
to the Contact table to view the fields available for the report.
15. Drag
the Last Name field to the details section of the report.
16. Drag
the Address.City, StateProvince.StateProvinceCode, and Employee.HireDate to the
details section.
17. Now
you must define the sorting within the report. From the main menu select
Crystal ReportsàReportàRecord Sort Expert….
18. Move
all 4 fields to the Sort Fields list and click the OK button.
19. Right
click on the LastName textbox in the Page Header section. Select Bind Sort
Control… from the pop-up menu.
20. Choose
the Contact.LastName field and click the OK button. Do the same for the other
three fields.
21. Save
your report.
22. Now
you need to add a page to view the report. Right click on the project in the
Solution Explorer and select Add New Item… from the pop-up menu.
23. Choose
WebForm from the list of templates and change the name to Viewer.aspx. Click
the Add button.
24. Drag
the CrystalReportViewer control from the toolbox to the web form.
25. Open
the code behind page and add the following using statements.
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
26. Add
the following code:
protected void Page_Load(object sender, EventArgs e)
{
ReportDocument report = new ReportDocument();
report.Load(Server.MapPath("EmployeeList.rpt"));
SetTableLocation(report.Database.Tables);
CrystalReportViewer1.ReportSource = report;
}
private void SetTableLocation(Tables tables)
{
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.ServerName = "(local)";
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);
}
}
27. This
code will open the report and bind it to the viewer control. Be sure you
change the ServerName, UserID, and Password in the SetTableLocation method.
28. Set
this page as the startup page and run your project. You should see two arrows
next to each column header.
Clicking on the up arrow will sort the report ascending by
that column. Click the down arrow sorts descending.