ReportViewer with multiple parameter

Question

I have a stored procedure that will be used to create a report in reportviewer (the one supplied within VS2010). There are 4 parameters - Start Date, End Date - single parameters. Multi-value parameters - Status (6 possible choices), Location(250 possible choices).

I'm unable to determine the proper way to code it so that the report will show all items within the date range, for the various status/locations requested.

For example: Show me all parts between (start date) 7/1/2012 AND (end date) 9/3/2012 that are from (location) Hazleton or Butler and are (status)Available or Out.

The code to call the stored procedure:

public DataTable StatusRpt(DateTime StartDate, DateTime EndDate)
{
    SQLCON = new SqlConnection(connectionString);
    SQLCON.Open();
    SQLCommand = new SqlCommand("spStatusRpt",SQLCON);
    SQLCommand.CommandType = CommandType.Text;
    SQLCommand.Parameters.Add("@StartDate", SqlDbType.Date).Value = StartDate;
    SQLCommand.Parameters.Add("@EndDate", SqlDbType.Date).Value = EndDate;
    //SQLCommand.Parameters.Add("@Status", SqlDbType.Int).Value = Status;
    //SQLCommand.Parameters.Add("@OrgName", SqlDbType.VarChar).Value = OrgName;

    SqlDataAdapter adapter = new SqlDataAdapter(SQLCommand);
    DataTable Detailtable = new DataTable();
    adapter.Fill(Detailtable);

    return Detailtable;
}

And here's my "onClick" event

protected void btnStatusReport_OnClick(object sender, EventArgs e)
{
    int Status = Convert.ToInt32(lbxStatus.SelectedValue);
    string OrgName = lbxLocations.SelectedValue;
    DateTime StartDate = Convert.ToDateTime(CalStart.SelectedDate);
    DateTime EndDate = Convert.ToDateTime(CalEnd.SelectedDate);
    lblPrint.Visible = true;
    DataTable DetailTable = equip.StatusRpt(StartDate, EndDate);

    this.RV1.Reset();
    this.RV1.LocalReport.DataSources.Clear();
    this.RV1.LocalReport.ReportPath = "Reports/StatusReport.rdlc";
    ReportDataSource rds = new ReportDataSource("StatusDS", DetailTable);
    this.RV1.LocalReport.DataSources.Add(rds);
    this.RV1.DataBind();
}

I've done some research, but everything I've found refers to using SSRS. I'm ok with filtering, if someone can show me how to apply the filter via code.

Thank you in advance for any and all assistance. Cindy

You might want to add the 'sql' tag to this question – Dan Pichelman Sep 04 '12 at 18:18 — Dan Pichelman, Sep 04 '12 at 18:18

score 0 · Answer 1 · edited May 23 '17 at 12:20

Since you are using client-side reporting (RDLC), remember that you are the one generating the data used by the report. I believe that you could pass a parameter to the report and use that to filter the data for display but you certainly don't need to.

In the past, I have done this kind of thing using server-side reporting (RDL). In that case, I passed a comma-separated string to the stored procedure. In the procedure, I broke it down, inserted it into a temporary table, and joined against my dataset which filtered the result. You'll want to do this in the procedure if you have a lot of rows and will often be outputing only a few of them. If you want to move this report to the server-side at some point, this is also probably a better method.

If you don't have a lot of rows, it's probably just as simple to filter it in your code. You could use LINQ for this (see this question on using LINQ on a DataTable). Remember that you can use any IEnumerable to pass into the report through the ReportDataSet.

not sure how I would apply the Linq query in this instance, because of the multiple choices. For instance; if both "Available" and "Repair" and "Missing" status' were chosen, how would I filter for all 3 at the same time? the Linq example you gave is a simple field = this. — Cindy Brozyno, Sep 05 '12 at 17:51
@Cindy IN Linq, you would normally get a collection (List mychoices) with the selected choices and reverse your logic: .Where(r => mychoices.Contains(r.Status)) — Mark Peters, Sep 05 '12 at 18:15

ReportViewer with multiple parameter

1 Answers1