locked
Multivalue Parameters for RDLC Visual Studio 2015 RRS feed

  • Question

  • User1513064915 posted

    Currently I have a dropdownlist where a user can select one unique id.  On the same page I have a reportviewer.  I created a rdlc with multidatasets and one parameter.  This parameter is assigned based on the unique id selected in the dropdownlist.  I am able to display results correctly.

    I have added a check box labeled "All", when this checkbox is checked, I iterate through all the values in the dropdownlist and assign those values to the Report parameter.  This is the same report parameter used when selected just one value from the dropdownlist.  I have assigned the multivalues in the dropdownlist to a list and added this list to the List<ReportParameters> lsrptparams = new List<ReportParameters>();

    Reportviewer.LocalReport.SetParameters(lsrptparams). 

    I have assigned these values to a hidden Label on the page just to confirm I am getting the correct values in the correct format(1,2,3,4,5).  But I don't get no results in the ReportViewer.  I have set a textbox in the ReportViewer with the expression "=Join(Parameters!Field.Value) and I can see those values but I don't get the results.  I am using a stored proc with a Where clause (Where field IN (@unique).

    My end result is when a user checks the Checkbox...get the same report based on the different multivalues.  So if there are 5 values within the multvalue parameter there should be 5 pages within the report for the results of the 5 different multivalues.  Any information is appreciated.

    Tuesday, June 21, 2016 7:06 PM

All replies

  • User1559292362 posted

    Hi Jon11sin,

    My end result is when a user checks the Checkbox...get the same report based on the different multivalues.  So if there are 5 values within the multvalue parameter there should be 5 pages within the report for the results of the 5 different multivalues.  Any information is appreciated

    According to your description, I would suggest that we could pass all as parameter to stored procedure when check box labeled "All", then we could modify store procedure. when the value of parameter is all, we could retrieve all the records.

    Best regards,

    Cole Wu

    Wednesday, June 22, 2016 9:54 AM
  • User1513064915 posted

    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.

    Wednesday, June 22, 2016 9:25 PM
  • 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

    Thursday, June 23, 2016 2:51 AM
  • User1513064915 posted

    Sorry for just replying back.  Yes I was able to get data back using this method but report isn't seeing the parameters.  I can see the data within the code behind debugging through the code.

    Friday, July 29, 2016 9:08 PM