Reputation: 135
I want to filter on multiple parameters in crystal reports through combo box but the problem is one filter is active at a time.
Here is the code of index change of both combo boxes:
protected void drpUserName_SelectedIndexChanged(object sender, EventArgs e)
{
username = drpUserName.SelectedValue;
ReportDocument rd = new ReportDocument();
rd.Load(Server.MapPath("LeaveReport.rpt"));
rd.SetParameterValue("username", username);
rd.SetParameterValue("status", status);
rd.SetDatabaseLogon("cde_portal", "credyna", "SERVER\\SQLEXPRESS", "lbs");
CrystalReportViewer1.ReportSource = rd;
}
protected void drpStatus_SelectedIndexChanged(object sender, EventArgs e)
{
status = drpStatus.SelectedValue;
ReportDocument rd = new ReportDocument();
rd.Load(Server.MapPath("LeaveReport.rpt"));
rd.SetParameterValue("status", status);
rd.SetParameterValue("username", username);
rd.SetDatabaseLogon("cde_portal", "credyna", "SERVER\\SQLEXPRESS", "lbs");
CrystalReportViewer1.ReportSource = rd;
}
And here is record selection formula:
If {?username} = "-1" Then // -1 for all values
{tblEmployee.Employeer_UserName} <> {?username} // return all records
Else
{tblEmployee.Employeer_UserName} = {?username} // return selected records
and IF {?status} = "-1" Then
{tblLeave.leave_status} <> {?status}
Else
{tblLeave.leave_status} = {?status}
One more thing in above formula: if I check {?username}
first and then check {?status}
in this case {?username}
filtering is working, but if I check {?status}
first then {?status}
, filtering works fine.
Upvotes: 1
Views: 108
Reputation: 457
you can do this by this way...
protected void drpUserName_SelectedIndexChanged(object sender, EventArgs e)
{
string strSelection = "1=1";
rd.SetParameterValue("username", drpUserName.SelectedValue);
rd.SetParameterValue("status", drpStatus.SelectedValue);
if (drpUserName.SelectedValue != "-1")
{
strSelection = strSelection + "And {tblEmployee.Employeer_UserName}=" + "\""+ drpUserName.SelectedValue+"\"";
}
if ( drpStatus.SelectedValue != "-1")
{
strSelection = strSelection + "And {tblLeave.leave_status}=" +"\""+ drpStatus.SelectedValue+"\"" ;
}
rd.RecordSelectionFormula = strSelection;
rd.SetDatabaseLogon("cde_portal", "credyna", "SERVER\\SQLEXPRESS", "lbs");
CrystalReportViewer1.ReportSource = rd;
}
protected void drpStatus_SelectedIndexChanged(object sender, EventArgs e)
{
string strSelection = "1=1";
rd.SetParameterValue("username", drpUserName.SelectedValue);
rd.SetParameterValue("status", drpStatus.SelectedValue);
if (drpUserName.SelectedValue != "-1")
{
strSelection = strSelection + "And {tblEmployee.Employeer_UserName}=" + "\"" + drpUserName.SelectedValue + "\"";
}
if (drpStatus.SelectedValue != "-1")
{
strSelection = strSelection + "And {tblLeave.leave_status}=" + "\"" + drpStatus.SelectedValue + "\"";
}
rd.RecordSelectionFormula = strSelection;
rd.SetDatabaseLogon("cde_portal", "credyna", "SERVER\\SQLEXPRESS", "lbs");
CrystalReportViewer1.ReportSource = rd;
}
Upvotes: 1