There are multiple ways of passing parameters to the crystal report. The simplest one is to use the crystal report design time feature to add the parameter manually. This article focuses on the design time approach to attach parameters.

Passing Parameters to Crystal Report Using Designer

This approach involves no coding at all. You will add the parameters using the Crystal Report designer. Let us see step-by-step how to accomplish this task. First, add a new crystal report to the project and name it "ProductsByCategoryReport.rpt." As soon as the report is added, the following dialog box appears on the screen.

Figure 1: Creating a new report

Select "Standard" and click on the "OK" button. The next screen will allow you to select the data source. In the screenshot below we have selected SQL SERVER and selected the "Categories" table and the "Products" table from the Northwind database.

Figure 2: Selecting the Tables

After selecting the tables select the "Next" button. The next screen shows the relationship between the Categories and Products table.

Figure 3: The Table Relationship Screen

 

The CategoryID has a one-to-many relationship with the Products table. This is because of the fact that a single category can contain multiple products.

Click the next button to move forward through the wizard.

Figure 4: Selecting the Fields

The above screen shows the fields available for selection. You can select any fields you want to display on the report.

We have selected several fields from the Products table as shown below in the screenshot.

Figure 5: Fields to Display Screen

Press "Next" to continue. The next screen shows the "Grouping" options for the report. This is useful when your report is presented in groups. A simple group example can be to list the total number of products in each category. Since, we are not using any grouping for this article we will not add any fields.

Figure 6: Grouping Screen

Click "Next" to continue.

The next screen is the record selection screen. We will also by-pass this screen by pressing the next button.

Figure 7: Record Selection Screen

The final screen is the layout screen. The layout screen enables the developer to select a layout for the report. Choose any layout you want and then press the "Finish" button.

Figure 8: Layout Selection Screen

As soon as you press the "Finish" button a prototype of the layout is created and displayed to you as shown in the screenshot below.

Figure 9: Report Prototype Displayed

Now, let us add a page which will display the crystal report.

Displaying Crystal Report on Page

ASP.NET includes a CrystalReportViewer control which enables us to display the crystal report on a web page. First, add a CrystalReportViewer on the page. The CrystalReportViewer needs a CrystalReportSource control. Create a new CrystalReportSource control and point it to the crystal report that you just created. Now, when you run the page, you will see the following output:

Figure 10: Report Displayed in the Browser

The report simply displayed all the records in the Products table. This is because we have not yet configured the "Select Expression" and the "Report Parameters." Let us add a parameter to the report. Go to the report, right click and select "Field Explorer." This will load the field explorer tab where you can add a parameter to the report.

Figure 11: Opening the Field Explorer Window

Now, right click on the Parameter Fields and add a new parameter as shown in the screenshot below.

Figure 12: Adding a Field Parameter

You can drag and drop the parameter field onto the crystal report if you would like to display the parameter field in the report. For this article we will not display the parameter field in the report.

The next step is to add a "Record Selection Formula." The record selection formula option is found under the "Special Fields" option. Right click on the record selection formula and add it to the report. Next, right click on the record selection formula you just added to the report and click on "Select Expert."

Select "CategoryName" as shown in the screenshot below.

Figure 13: Choosing a Field to Match Against the Parameter Field

 

Select the "OK" button.

The next screen shows the filter criteria. We are matching the CategoryName parameter field with the CategoryName in the Categories table.

Figure 14: Creating the Filter Criteria

Select the "OK" button to close the Select Expert window.

Now, run the application again and you will notice that the page prompts you to enter the parameter field, which in this case is the CategoryName.

Figure 15: Enter Parameter Field Value Screen

When the "Submit" button is clicked you will see the results filtered out by the CategoryName.

Figure 16: Filtered Crystal Report Displayed in Browser