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
