Background
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
- Download and unzip the attached file.
- Run the script to create the database and table.
- 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:
- In field explorer,right click Parameter fields>New (if you can't find field explorer, View>Other Windows>Document Outline or Ctrl+Alt+T)
- Enter name
bHireDateFromChecked
and type boolean
- 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:
- Open Field Explorer
- Right click Formula Fields>New
- 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
Else
True
So now you should have the following:
IF({?bHireDateFromChecked}) THEN
{t_Employees.dHireDate}>={?dtHireFrom}
ELSE
TRUE
Click save and close on the top left.
Similarly, create the formula HireDateTo
, which is:
IF({?bHireDateToChecked})THEN
{t_Employees.dHireDate}<={?dtHireTo}
ELSE
TRUE
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.
Write:
IF(double click length double click sName)>0)THEN
double click t_Employees.sName = double click sName
ELSE
TRUE
And now you should have:
IF(Length ({?sName})>0)THEN
{t_Employees.sName}={?sName}
ELSE
TRUE
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.
- Double click on each formula to be written in the editor
- Write AND between each formula
..and you should end up with this:
{@HireDateFrom} AND
{@HireDateTo} AND
{@Manager} AND
{@Name} AND
{@SalaryFrom} AND
{@SalaryTo}
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)
{
DbConnectionInfo.SetConnectionString(ConfigurationSettings.AppSettings[0]);
TableLogOnInfo logOnInfo;
ConnectionInfo connectionInfo;
foreach (Table table in m_cryRpt.Database.Tables)
{
logOnInfo = table.LogOnInfo;
connectionInfo = logOnInfo.ConnectionInfo;
connectionInfo.DatabaseName = DbConnectionInfo.InitialCatalog;
connectionInfo.ServerName = DbConnectionInfo.ServerName;
if (!DbConnectionInfo.UseIntegratedSecurity)
{
connectionInfo.Password = DbConnectionInfo.Password;
connectionInfo.UserID = DbConnectionInfo.UserName;
}
else
{
connectionInfo.IntegratedSecurity = true;
}
table.ApplyLogOnInfo(logOnInfo);
}
crystalReportViewer1.ReportSource = m_cryRpt;
crystalReportViewer1.Refresh();
}
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})
ELSE
'Any'
Conclusion
Hope I delivered a clear explanation. Feel free to post any questions/suggestions.