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:
C. Report Creation
D. Sorting based on a parameter
E. Testing
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
- Open Eclipse.
- Browse your Crystal Reports project.
- Create a new SQL file called "sqlReportParamForSorting".
- Open the SQL file and write the following SELECT query:
select Product_ID, Product_Name, Product_Description
from REPORT_PARAM_FOR_SORTING
- Right-click inside the SQL file and click Crystal Reports > Add to a New Report.
- Set the location and name for the new report and click OK.
- Open the newly created report.
- In the Crystal Reports perspective, open the Field Explorer view.
- Expand the command table, in order to see the selected DB fields.
- While the Layout page is open, drag and drop the three fields in the Body section of the report.
- 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
- In the Field Explorer view, add a new parameter called "param_SortingColumn".
- In the Properties view for the new parameter, set the List of Values as shown in the following figure:
- In the Field Explorer view, add a new formula called "sort_Column".
- On the Formula page, write the following formula for "sort_Column":
if ({?param_SortingColumn} = 'Name') then
{sqlReportParamForSorting.PRODUCT_NAME}
else
{sqlReportParamForSorting.PRODUCT_ID}
- From the Crystal Reports menu, click Record Sorting.
- Select the formula "sort_Column" and click OK.
- Save the report.
E. Testing
- Switch to the Preview page.
- You would be prompted to select a sorting column.
- Select "Product Name" and the result should be as shown in the following figure:
- Refresh the report and select "Prompt for parameters before refresh".
- Select "Product ID" and the result should be as shown in the following figure:
