User1559292362 posted
Hi Jon11sin,
Well that does give me all the records but doesn't help with the grouping of the data within the report based on the parameter. Each page having the data from each parameter. Instead
all records display.
It seems that we could use Table-Valued parameters to pass multi-value parameters to stored procedure. I create a simple demo as below for your reference.
#table type.
CREATE TYPE [dbo].[filterTable] AS TABLE (
[CustomerId] INT NULL);
#stored procedure
CREATE PROCEDURE Customers_GetCustomers
@filter filterTable READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerId
,ContactName
,Country
FROM Customer
Where CustomerId in (select CustomerId from @filter)
END
#Usage:
using Microsoft.Reporting.WebForms;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
namespace ADONETDEMO.SSRS
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
if (!IsPostBack)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/SSRS/RptCustomer.rdlc");
DataSet1 dsCustomers = GetData();
ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
}
}
}
private DataSet1 GetData()
{
string conString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlCommand cmd = new SqlCommand("Customers_GetCustomers");
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("CustomerId", typeof(int)));
dt.Rows.Add(1);
dt.Rows.Add(2);
dt.Rows.Add(3);
dt.Rows.Add(4);
dt.Rows.Add(5);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@filter", dt);
sda.SelectCommand = cmd;
using (DataSet1 dsCustomers = new DataSet1())
{
sda.Fill(dsCustomers, "DataTable1");
return dsCustomers;
}
}
}
}
}
}
For more information about Table-Valued parameters, please refer to:
https://msdn.microsoft.com/en-sg/library/bb510489.aspx
Best regards,
Cole Wu
