Overview of Crystal Reports - Part 4

Hi Steemians,

This post is the fourth of the series of blogs I am writing on Reports design tool Crystal Reports. Below are the links for earlier posts in that order.

1. https://steemit.com/technology/@coolguy123/overview-of-sap-crystal-reports-part-1-2017926t16715219z
2. https://steemit.com/technology/@coolguy123/overview-of-crystal-reports-part-2
3. https://steemit.com/technology/@coolguy123/overview-of-crystal-reports-part-3-2017101t204541470z

We saw about history, versions, vendors and how to create a data source for Crystal Reports along with creating a simple report using command object in earlier posts.

In this post, let’s see how to filter report data using dynamic filter conditions called as “Parameters”.

Parameters can be used in 2 different ways in Crystal Reports.

1. If we are using Command Object for pulling data from database as we saw in previous post, then we can use parameters created at Command Object level
2. If we are not using Command Object or if there is a specific requirement to create parameters at report level, then we can create parameters at report level and use the same in something called as “Record selection” to filter the data.

Let’s see each of these with some screenshots for better understanding.

Query Level/Command Object Level parameters:

Using the Command Object we had created in previous post, we will continue with parameter creation. Check below screenshot where in, the highlighted parameter creation options.

As we can see, once we click “Create”, we get a parameter creation screen, where, we need to enter parameter name, prompting text, data type of the parameter and default value if any.
Note the checkbox for “Allow Multiple Values”. If we check this, then we can create a multi value parameter which provides an option to filter report data with more than one value.
Let’s create a multi value parameter for Department ID.

Once we include the required information as in above screen and click OK, we get the parameter created.

Now, we can use this parameter to filter the data by using it into the where clause of the query we have as in below screenshot.

All we need to do is create an additional where clause condition like and d.department_id in and double click the newly created parameter to use it along with where clause. The highlighted part in above screen has the condition.

P.S: The in condition in the where clause d.department_id in is required as we have created a multi value parameter.
Once we click OK, we will be taken to the screen as below, where in we need to enter valid values for initial filtering of data. Note the values 10 and 50 used as filter conditions for Department ID.

Click OK and we can see that the report output data is filtered for departments 10 and 50 only.

This concludes the first part of parameter creation i.e. Parameters at Command Object Level.
Now, let’s see the second part, i.e. creation of parameter at report level.
We will continue with the report we modified in the above method.

Report Level Parameter Creation:

Please check the right side of above screenshot, there is a Field Explorer which has all the objects used in report.
There is a link for Parameter Fields as well and we can see dept_id parameter we created in above steps.

Now, we will create a report level parameter on field Employee ID by right clicking on Parameter Fields and selecting New with parameter name as Employee ID with options set as highlighted in below screen. Note, “Allow multiple values” set to True to allow filtering for multiple Employee ID’s.

Newly created parameter will not be used for filtering the data until we use it as a sql condition using something called as Record Selection.

Navigate to Report Tab  Selection Formulas  Record as in below screenshot.

We need to write the filter condition manually in the record selection screen as below.

Note the record selection where clause highlighted. Command.EMPLOYEE_ID is the database field and {?Employee ID} is the report level parameter we had created.
Click on Save and Close, we will be asked to enter the Employee ID values. I have selected 200, 120, 121, 122 and 127 Emplyee ID’s.

Click OK and see that the Report data is refreshed to include only the Employee details we have filtered.

That completes the intention of this post. i.e Creation of parameters in Crystal Reports to filter Report Data.

I hope you enjoyed reading the content.

Please follow, resteemit, upvote and provide your comments.