1. Home
  2. Forum
  3. ASP.NET MVC
  4. Problems with Parameters
Starting in 2019, the Reporting control is no longer included in Essential Studio®. If you're experiencing issues with the Syncfusion� Reporting Platform, Report Viewer, Report Designer, or Report Writer, we recommend migrating to Bold Reports, our dedicated reporting platform.

Bold Reports offers a comprehensive suite of tools and features for all your reporting needs, and we will help you make a smooth transition from the discontinued control. Our support team at https://support.boldreports.com/ is here to assist you with any questions or difficulties you may encounter during the migration process.

We thank you for choosing Syncfusion� and appreciate your understanding.

Problems with Parameters

Hi,
I create a procedure in MS-SQL - Table Valued Function - that needs 2 parameter start date and Finish date

to call it i use this:
 public List ReadPivote(string fecha1, string fecha2)
        {
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["00WEB"].ConnectionString))
            {               
                return db.Query("select * from IRTerritorio ('" + fecha1 + "','" + fecha2 + "')").ToList();
            }
        }

in my controller i call it using this:
 public ActionResult VtaTerritorioPivote()
        {
            RepoDapper propRep = new RepoDapper(); 
            ViewData["reportDsource"] = propRep.ReadPivote("2017-11-09","2017-11-11");     
             return View();
        }

in the View i use:

@(Html.EJ().ReportViewer("viewer").ProcessingMode(Syncfusion.JavaScript.ReportViewerEnums.ProcessingMode.Local)
.ReportPath("~/App_Data/VentasTerritorial.rdlc")
.ReportServiceUrl("/api/ReportApi")
.DataSources(ds => ds.Name("DataSet1").Value(ViewData["reportDsource"]).Add()))

i also add the parameters in the rdlc designer , SDate and EDate, the problems comes that when i load it, i can put anything on the textbox and it load the info without any filter and also the label of the parameters is not the one i choose

this is the preview 



3 Replies
YD Yuvaraj Devarajan Syncfusion Team November 14, 2017 02:00 PM UTC

Hi Gian, 

Thanks for contacting Syncfusion support.  

I create a procedure in MS-SQL - Table Valued Function - that needs 2 parameter start date and Finish date 

to call it i use this: 
 public List ReadPivote(string fecha1, string fecha2) 
        { 
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["00WEB"] 
.ConnectionString)) 
            {                
                return db.Query("select * from IRTerritorio ('" + fecha1 + "','" + fecha2 + "')").ToList(); 
            } 
        } 

in my controller i call it using this: 
 public ActionResult VtaTerritorioPivote() 
        { 
            RepoDapper propRep = new RepoDapper();  
            ViewData["reportDsource"] = propRep.ReadPivote("2017-11-09", 
"2017-11-11");      
             return View(); 
        } 

in the View i use: 

@(Html.EJ().ReportViewer("viewer").ProcessingMode(Syncfusion.JavaScript 
.ReportViewerEnums.ProcessingMode.Local) 
.ReportPath("~/App_Data/VentasTerritorial.rdlc") 
.ReportServiceUrl("/api/ReportApi") 
.DataSources(ds => ds.Name("DataSet1").Value(ViewData 
["reportDsource"]).Add())) 

i also add the parameters in the rdlc designer , SDate and EDate, the problems comes that when i load it, i can put anything on the textbox and it load the info without any filter 
The mentioned problem occurs, when the parameter filter value for the RDLC report is not applied for the datasource. For RDLC report, we have to pass the parameter value from client side to server side in-order to filter the datasource value in WebAPI “OnReportLoaded” method. So, we request you to get the parameter value of the SubReport in “OnReportLoaded” method and apply filter for the datasource value specified in WebAPI as shown in below code example, 

View: 
    <script type="text/javascript"> 
    var _params = null; 
    ej.ReportViewer.prototype._viewReportParamsClick = function (event) { 
        var proxy = $('#reportsample').data('ejReportViewer'); 
        var parameters = event.data.params; 
        var reportParams = proxy._getParameterJson(parameters); 
        proxy._refresh = true; 
        $('#' + proxy._id + '_viewBlockContainer .e-reportviewer-viewerblockcontent table:first').attr('isviewclick', 'true'); 
        _params = reportParams;       
        proxy._refreshReport(); 
    } 
 
    ej.ReportViewer.prototype.doAjaxPost = function (type, url, jsondata, onSuccess) { 
        var proxy = $('#reportsample').data('ejReportViewer'); 
        var inVokemethod = onSuccess; 
 
        $.ajax({ 
            type: type, 
            url: url, 
            crossDomain: true, 
            contentType: 'application/json; charset=utf-8', 
            dataType: 'json', 
            data: jsondata, 
            beforeSend: function (req) { 
                if (inVokemethod == "_getDataSourceCredential") { 
                    var _json = jQuery.parseJSON(this.data); 
                    if (_params != null) { 
                        _json["params"] = _params; 
                    } 
                    this.data = JSON.stringify(_json); 
                } 
 
                if (inVokemethod == "_getPageModel" || inVokemethod == "_getPreviewModel") { 
                    if (!proxy._isToolbarClick) { 
                        proxy._showloadingIndicator(true); 
                        proxy._updateDatasource = true; 
                    } else { 
                        proxy._showNavigationIndicator(true); 
                    } 
                } 
                req.setRequestHeader('ejAuthenticationToken', proxy._authenticationToken); 
            }, 
            success: function (data) { 
                if (data && typeof (data.Data) != "undefined") { 
                    data = data.Data; 
                } 
                if (typeof (data) == "string") { 
                    if (data.indexOf("Sf_Exception") != -1) { 
                        proxy._renderExcpetion(inVokemethod + ":" + data); 
                        return; 
                    } 
                } 
                proxy[inVokemethod](data); 
            }, 
        }); 
    } 
    </script> 


WebAPI: 
        public void OnReportLoaded(ReportViewerOptions reportOption) 
        { 
            var parameters = new List<Syncfusion.Reports.EJ.ReportParameter>(); 
            if (System.Web.HttpContext.Current.Items.Contains("parakey")) 
            { 
                parameters = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<List<Syncfusion.Reports.EJ.ReportParameter>>( 
System.Web.HttpContext.Current.Items["parakey"].ToString()); 
                System.Web.HttpContext.Current.Items.Remove("parakey"); 
            } 
            reportOption.ReportModel.Parameters = parameters; 
 
            string queryStr = "SELECT Sales.SalesPerson.SalesPersonID , Sales.SalesPerson.TerritoryID , Sales.SalesPerson.ModifiedDate FROM Sales.SalesPerson Where ModifiedDate BETWEEN @StartDate AND @EndDate"; //StartDate=01/01/2001 and End Date=12/30/2001 
            string connection = "Data Source=mvc.syncfusion.com;Initial Catalog=AdventureWorks;user id=ssrs1;password=RDLReport1"; 
 
            reportOption.ReportModel.DataSources.Clear(); 
            reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "DataSet1", Value = GetDataTable(queryStr, connection, parameters) }); 
 
        } 

We have prepared a sample and it can be downloaded from below location, 


also the label of the parameters is not the one i choose 
We were unable to reproduce the mentioned problem at our end. We have tested the mentioned problem by specifing the default value of the parameter in the RDLC file and its working properly at our end. So, can you please share your issue reproducible RDLC file to validate the mentioned problem at our end. Also, we request you to specify the datatype of the parameter as Date/Time in designer for date parameter as shown in below screenshot, 

 


Regards, 
Yuvaraj D. 


GC Gian Carlo November 14, 2017 08:19 PM UTC

Hi, reproduce your sample code and im getting this error

Collection class or datatable Input is needed

also i will add more than 2 rldc. do i need to add more report appi?


  // GET api/

        public object PostReportAction(Dictionary jsonResult)

        {

            if (jsonResult.ContainsValue("GetDataSourceCredential") && jsonResult.ContainsKey("params"))

            {

                System.Web.HttpContext.Current.Items.Add("parakey", jsonResult["params"]);

            }

            return ReportHelper.ProcessReport(jsonResult, this);

        }

  public void OnReportLoaded(ReportViewerOptions reportOption)

        {

            var parameters = new List();

            if (System.Web.HttpContext.Current.Items.Contains("parakey"))

            {

                parameters = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize>(System.Web.HttpContext.Current.Items["parakey"].ToString());

                System.Web.HttpContext.Current.Items.Remove("parakey");

            }

            reportOption.ReportModel.Parameters = parameters;


            string queryStr = "SELECT * from IRTerritorio (@SDate, @EDate)"; <--- this is an stored procedure table valued function //StartDate=01/01/2001 and End Date=12/30/2001

            string connection = "Data Source=192.X.X.X;Initial Catalog=WEB;user id=web;password=PASS12.+";


            reportOption.ReportModel.DataSources.Clear();

            reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "DataSet1", Value = GetDataTable(queryStr, connection, parameters) });


        }

        public DataTable GetDataTable(string querystr, string connection, IEnumerable _reparams)

        {

            DataTable dt = new DataTable();

            try

            {

                using (SqlConnection sqlConn = new SqlConnection(connection))

                using (SqlCommand cmd = new SqlCommand(string.Format(querystr), sqlConn))

                {

                    sqlConn.Open();

                    if (_reparams.Count() > 0)

                    {

                        foreach (var item in _reparams)

                        {

                            cmd.Parameters.AddWithValue("@" + item.Name, item.Values.First());

                        }

                    }

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))

                    {

                        dt.Load(reader);

                        var r = dt.Rows.Count;

                    }

                    return dt;

                }

            }

            catch

            {

                return null;

            }

        }

@(Html.EJ().ReportViewer("vTerritorio")

               .ProcessingMode(Syncfusion.JavaScript.ReportViewerEnums.ProcessingMode.Local)

               .ReportPath("~/Report/VTerritorio.rdlc")

               .ReportServiceUrl(VirtualPathUtility.ToAbsolute("~/api/ReportApi")))



YD Yuvaraj Devarajan Syncfusion Team November 15, 2017 07:17 AM UTC

Hi Gian, 

Thanks for the update.  

We have checked the mentioned problem with our local database store procedure value and its working properly at our end. We suspect the mentioned problem might be occurred when the data from the store procedure is not retrieved at your end. We have modified the previously shared sample when loading data from store procedure and it can be downloaded from below location,  

If the issue still persists, then share the issue reproducible RDLC file to validate the mentioned problem at our end.  

Regards, 
Yuvaraj D. 


Loader.
Up arrow icon