Crystal Reports for Eclipse: How to set the Record Sorting at runtime?

A. Introduction

In this article, I would like to explain how to sort the data in a report based on the value of a parameter.
The example used in this article is based on a simple database table containing three columns, namely Product ID, Product Name and Product Description. A Command Table representing the data of this DB table will be added to a new report. And finally, it is required to define a parameter for choosing the sorting column at runtime.

If you are new to Crystal Reports for Eclipse, I recommend that you watch the following QuickStart demos:
Crystal Reports for Eclipse 2.0 - Command Table Part 1: Adding SQL to a Report
http://www.sdn.sap.com/irj/scn/elearn?rid=/library/uuid/00b07182-3ed3-2b10-6695-fb255b4eae01

Crystal Reports for Eclipse 2.0 - Command Table Part 2: Updating Command Table SQL
http://www.sdn.sap.com/irj/scn/elearn?rid=/library/uuid/d027948f-3fd3-2b10-e582-f19c3946490f

Crystal Reports for Eclipse 2.0 - Designing a Report
http://www.sdn.sap.com/irj/scn/elearn?rid=/library/uuid/d0ee1df6-41d3-2b10-de8d-a4fdc5077c68


B. Database preparations

Create the example database table and fill it with data using the following script:

CREATE TABLE REPORT_PARAM_FOR_SORTING(
Product_ID varchar(6) not null,
Product_Name varchar(20) not null,
Product_Description varchar(100) not null,
constraint pk_report primary key (Product_ID)
);

insert into REPORT_PARAM_FOR_SORTING values
('F50Z13', 'Optical mouse', 'This is an optical mouse.'),
('A04K02', 'Keyboard', 'This is a wireless keyboard.'),
('A00B18', 'Speakers', 'Perfect speakers.');


C. Report Creation

1. Open Eclipse.
2. Browse your Crystal Reports project.
3. Create a new SQL file called "sqlReportParamForSorting".
4. Open the SQL file and write the following SELECT query:
select Product_ID, Product_Name, Product_Description
from REPORT_PARAM_FOR_SORTING

5. Right-click inside the SQL file and click Crystal Reports > Add to a New Report.
6. Set the location and name for the new report and click OK.
7. Open the newly created report.
8. In the Crystal Reports perspective, open the Field Explorer view.
9. Expand the command table, in order to see the selected DB fields.
10. While the Layout page is open, drag and drop the three fields in the Body section of the report.
11. Switch to the Preview page and check that you can see all three rows from the example DB table properly.

D. Sorting based on a parameter

1. In the Field Explorer view, add a new parameter called "param_SortingColumn".
2. In the Properties view for the new parameter, set the List of Values as shown in the following figure:
   
   
   
   
   
3. In the Field Explorer view, add a new formula called "sort_Column".
4. On the Formula page, write the following formula for "sort_Column":
if ({?param_SortingColumn} = 'Name') then
{sqlReportParamForSorting.PRODUCT_NAME}
else
{sqlReportParamForSorting.PRODUCT_ID}

5. From the Crystal Reports menu, click Record Sorting.
   
   
   
   
   
6. Select the formula "sort_Column" and click OK.
   
   
   
   
   
7. Save the report.

E. Testing

1. Switch to the Preview page.
2. You would be prompted to select a sorting column.
3. Select "Product Name" and the result should be as shown in the following figure:
   
   
   
   
   
4. Refresh the report and select "Prompt for parameters before refresh".
5. Select "Product ID" and the result should be as shown in the following figure: