Click here to Skip to main content
13,257,667 members (63,588 online)
Click here to Skip to main content
Add your own
alternative version


28 bookmarked
Posted 2 Jun 2011

Crystal Report Filtering Using Selection Parameters

, 2 Jun 2011
Rate this:
Please Sign up or sign in to vote.
Beginner's Guide to Crystal Report Filtering


Selection parameters allow you to filter Data returned from datasource (Table in our case).

Of course, it's better and faster to use Stored Procedures (explained here), but it's good to learn about this useful feature.

Step 1: Database

  1. Download and unzip the attached file.
  2. Run the script to create the database and table.
  3. You should now have the following schema.


(I tried to use variations of data types, text, datetime, float, etc.)

Step 2: Form

We’ll build the user input form, nothing fancy, textbox, datetimepicker and a customized numeric updown control.

(The numeric updown is augmented to have a Checked property, we’ll need that later.)


Step 3: Report

Next, we’ll add a new report to our Reports folder, explained here.

Step 4: Report’s Parameters

Very well, we have our report created; next, we want to create the parameters to be passed and searched with.

In addition to the 6 parameters on the user form, we need a Boolean parameter for each field, this Boolean tells us whether to include the field in search criteria or not.

We'll enter the first boolean to indicate if the From datetimepicker has value:

  1. In field explorer,right click Parameter fields>New (if you can't find field explorer, View>Other Windows>Document Outline or Ctrl+Alt+T)


  2. Enter name bHireDateFromChecked and type boolean


  3. Do this for the other fields. bHireDateToChecked bSalaryFromChecked etc…

We don’t need a Boolean to check if user wants to search by name, we’ll just inspect if the text length is greater than zero.

So now we’ve added the Booleans. Next, we’ll add the parameters that will be actually searched.

Similarly add the parameters, with the same datatype they are in database.

So now we have:


Step 5: Report’s Formulas

For each of our Main searchable parameters, we’ll create a formula, this formula examines the parameters attached Boolean, to decide whether to include it in the search or not.

So let’s add a new formula:

  1. Open Field Explorer
  2. Right click Formula Fields>New
  3. Enter name: HireDateFrom

After you click ok,this window appears:


You have all the parameters you created, if you double click on one of them, it's written into the editor. Great!

So now we’ll write the following formula while clicking parameters of the above panel.

if (double click bHireDateFromChecked) then 
double click t_Employees.dHireDate >= double click dtHireFrom 

So now you should have the following:

IF({?bHireDateFromChecked}) THEN

Click save and close on the top left.

Similarly, create the formula HireDateTo, which is:


Similarly create another 2 formulas for salary, from and to.

For the name field, we’ll take a different approach, we’ll check the passed parameter’s length.

(Notice the functions panel in the middle, like the formulas, functions are written into the editor once you double click on one of them.)

Expand Strings in the Functions Panel.


IF(double click length double click sName)>0)THEN
double click t_Employees.sName = double click sName 

And now you should have:

IF(Length ({?sName})>0)THEN

Step 6: Selection Parameters

Right click in the empty grey of the report>Report>selection formula>record.

Like the parameters, the formulas we just created are available in the editor.


Now all we have to do is to join these formulas using the logical operator AND.

  1. Double click on each formula to be written in the editor
  2. Write AND between each formula

..and you should end up with this:

{@HireDateFrom} AND
{@HireDateTo} AND
{@Manager} AND
{@Name} AND
{@SalaryFrom} AND

Points of Interest

You design the report in one place, then copy it somewhere else.

The report included in the application (the one you see under solution explorer->Reports Folder) is NOT the one the report viewer references to, the actual path is this:

string strReportName = "rptDemo.rpt";
string strPath = Application.StartupPath + "\\Reports\\" + strReportName;

which is the application's bin>Debug WHY?

In a Desktop application, when you want to deploy and add a setup project, this will be the path of the added Report's Folder.


You should NOT hard-code your Connection String.

Almost every CR tutorial I've seen uses hard-coded Connection Strings (they use Datatables too, someone tell me why?)

You should make use of little helpful class called DbConnectionInfo (not mine, unfortunately I can't remember the author to give him credit.)

private void frmReportViewer_Load(object sender, EventArgs e)
            TableLogOnInfo logOnInfo;
            ConnectionInfo connectionInfo;
            foreach (Table table in m_cryRpt.Database.Tables)
                logOnInfo = table.LogOnInfo;
                connectionInfo = logOnInfo.ConnectionInfo;
                // Set the Connection parameters.
                connectionInfo.DatabaseName = DbConnectionInfo.InitialCatalog;
                connectionInfo.ServerName = DbConnectionInfo.ServerName;
                if (!DbConnectionInfo.UseIntegratedSecurity)
                    connectionInfo.Password = DbConnectionInfo.Password;
                    connectionInfo.UserID = DbConnectionInfo.UserName;
                    connectionInfo.IntegratedSecurity = true;

            crystalReportViewer1.ReportSource = m_cryRpt;

You should let the user know what he entered.


Always display the parameters the user entered in a friendly way.

For example, the formula lblHireDateFrom displays the entered From date:

IF({?bHireDateFromChecked}) THEN
   ToText ({?dtHireFrom})


Hope I delivered a clear explanation. Feel free to post any questions/suggestions.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Omar Gameel Salem
Software Developer
Australia Australia
Enthusiastic programmer/researcher, passionate to learn new technologies, interested in problem solving, data structures, algorithms, AI, machine learning and nlp.

Amateur guitarist/ keyboardist, squash player.

You may also be interested in...


Comments and Discussions

Questionboolean required Pin
Member 1180357230-Jun-15 8:15
memberMember 1180357230-Jun-15 8:15 
Questionhow do you check the presence of an int value? Pin
Member 109006143-Jul-14 0:19
memberMember 109006143-Jul-14 0:19 
AnswerRe: how do you check the presence of an int value? Pin
Omar Gameel Salem3-Jul-14 16:33
memberOmar Gameel Salem3-Jul-14 16:33 
GeneralRe: how do you check the presence of an int value? Pin
Member 109006145-Jul-14 22:57
memberMember 109006145-Jul-14 22:57 
AnswerRe: how do you check the presence of an int value? Pin
Omar Gameel Salem6-Jul-14 19:40
memberOmar Gameel Salem6-Jul-14 19:40 
GeneralMy vote of 5 Pin
RookieCoder_NG5-Sep-12 2:20
memberRookieCoder_NG5-Sep-12 2:20 
GeneralMy vote of 5 Pin
bajwa0135-Jul-12 1:32
memberbajwa0135-Jul-12 1:32 
QuestionPassing parameter values in crystal reports to stored procedure Pin
Member 886365527-Jun-12 12:58
memberMember 886365527-Jun-12 12:58 
Thanks for the excellent article, how would one pass the value of a parameter in crystal reports to a stored procedure in MS SQL Server? I have something like:

Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
Dim myReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument()

Dim reportPath As String = C:\Reports\MCAZApprovedWHOEMLReport.rpt
myReportDocument.SetParameterValue("@ApplicantName", appComboBox.Text.Trim())
Dim frm As New frmReportViewer(myReportDocument)
End Sub

When I run my application i get an exeption at the SetParameterValue line code which looks as follows:

System.Runtime.InteropServices.COMException was unhandled
Message=Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
at CrystalDecisions.ReportAppServer.DataDefModel.FieldsClass.get_Item(Int32 Index)
at CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions.get_Item(Int32 index)
at CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions.get_Item(String fieldName)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetParameterValue(String name, Object val)
at MCAZApprovedWHOEMLAnal.frmMain.btnSearch_Click(Object sender, EventArgs e) in C:\Users\Chitemerere\Documents\Visual Studio 2010\Projects\MCAZApprovedWHOEMLAnal\MCAZApprovedWHOEMLAnal\frmMain.vb:line 55
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at MCAZApprovedWHOEMLAnal.Program.Main() in C:\Users\Chitemerere\Documents\Visual Studio 2010\Projects\MCAZApprovedWHOEMLAnal\MCAZApprovedWHOEMLAnal\Program.vb:line 10
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Can you kindly assist?
AnswerRe: Passing parameter values in crystal reports to stored procedure Pin
Omar Gamil28-Jun-12 0:07
memberOmar Gamil28-Jun-12 0:07 
GeneralRe: Passing parameter values in crystal reports to stored procedure Pin
Member 886365528-Jun-12 6:13
memberMember 886365528-Jun-12 6:13 
GeneralRe: Passing parameter values in crystal reports to stored procedure Pin
Member 88636551-Jul-12 13:32
memberMember 88636551-Jul-12 13:32 
GeneralMy vote of 5 Pin
Member 886365527-Jun-12 12:47
memberMember 886365527-Jun-12 12:47 
QuestionDefault Parameter Panel in Crystal Reports filtering Pin
Member 886365523-Apr-12 9:51
memberMember 886365523-Apr-12 9:51 
QuestionCrystal Report Filtering Pin
Member 886365523-Apr-12 3:54
memberMember 886365523-Apr-12 3:54 
AnswerRe: Crystal Report Filtering Pin
Omar Gamil23-Apr-12 4:16
memberOmar Gamil23-Apr-12 4:16 
GeneralMy vote of 5 Pin
manoj kumar choubey28-Mar-12 0:46
membermanoj kumar choubey28-Mar-12 0:46 
BugLinks broken Pin
Cyrusje23-Jan-12 5:59
memberCyrusje23-Jan-12 5:59 
Questionthis is very cool but what about if you want filter from listbox ? Pin
djramc21-Jan-12 3:28
memberdjramc21-Jan-12 3:28 
AnswerRe: this is very cool but what about if you want filter from listbox ? Pin
Omar Gamil21-Jan-12 22:09
memberOmar Gamil21-Jan-12 22:09 
AnswerRe: this is very cool but what about if you want filter from listbox ? Pin
djramc22-Jan-12 2:37
memberdjramc22-Jan-12 2:37 
GeneralRe: this is very cool but what about if you want filter from listbox ? Pin
Omar Gamil22-Jan-12 2:39
memberOmar Gamil22-Jan-12 2:39 
GeneralRe: this is very cool but what about if you want filter from listbox ? Pin
djramc22-Jan-12 3:07
memberdjramc22-Jan-12 3:07 
GeneralRe: this is very cool but what about if you want filter from listbox ? Pin
Omar Gamil22-Jan-12 3:08
memberOmar Gamil22-Jan-12 3:08 
GeneralRe: this is very cool but what about if you want filter from listbox ? Pin
djramc22-Jan-12 3:22
memberdjramc22-Jan-12 3:22 
AnswerRe: this is very cool but what about if you want filter from listbox ? Pin
djramc22-Jan-12 3:12
memberdjramc22-Jan-12 3:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 2 Jun 2011
Article Copyright 2011 by Omar Gameel Salem
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid