Filtering on multiple parameters

Question

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.

Answer 1

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;

        }