We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Reportviewer parameters

Thread ID:

Created:

Updated:

Platform:

Replies:

142571 Feb 11,2019 03:06 AM UTC Feb 15,2019 02:42 PM UTC ASP.NET Core 8
loading
Tags: ReportViewer
Justin
Asked On February 11, 2019 03:06 AM UTC

I have a report working in reportviewer fine with no parameters. I originally tried a query to supply values, but it wasn't loading the dropdown. To simplify I resorted to typing it in.

Allow blank values, no available values, no default value. My where clause for accepting blank values:
WHERE cIDNUM= Case when @PID = '' then cIDNum else @PID end

In Report Builder everything works fine. In my application, I can run the report but when I type in a value for the parameter and run it loads the report with all values. I'm at a loss as to what direction to go for troubleshooting from here. Please advise 

As far as setting Available Values to a query, I made a second dataset, Dataset2 and loaded it in my controller as a datasource just like a loaded the report datasource. Is this even necessary? Is there anything else I need to do on the controller side for a parameter loading from a query using a second dataset?


        public void OnInitReportOptions(ReportViewerOptions reportOption)
        {
            string basePath = _hostingEnvironment.WebRootPath;
            FileStream inputStream = new FileStream(basePath + @"\Incidents2.rdl", FileMode.Open, FileAccess.Read);
            reportOption.ReportModel.Stream = inputStream;
            //DataSet ds = new DataSet();
            //ds = Loadds();
            SqlConnection connection = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=GirlsHaven;uid=*****;password=*****;");
            var strsql = "SELECT * from qryIncidents";
            SqlDataAdapter adapter = new SqlDataAdapter(strsql, connection);
            adapter.SelectCommand = new SqlCommand(strsql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            strsql = "SELECT * from qryPID Order by PID ASC";
            SqlDataAdapter adapter2 = new SqlDataAdapter(strsql, connection);
            adapter2.SelectCommand = new SqlCommand(strsql, connection);

            DataSet ds2 = new DataSet();
            adapter2.Fill(ds2);
            reportOption.ReportModel.DataSources.Clear();
            reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "DataSet1", Value = ds.Tables[0] });
            //reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "DataSet2", Value = ds2.Tables[0] });

            DataSourceCredentials dsc = new DataSourceCredentials();

            dsc.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=GirlsHaven;Column Encryption Setting=Enabled;";
            dsc.IntegratedSecurity = false;
            dsc.UserId = "******";
            dsc.Password = "******";
            dsc.Name = "GirlsHaven";
            reportOption.ReportModel.DataSourceCredentials.Add(dsc);
            reportOption.ReportModel.ProcessingMode = ProcessingMode.Local;



        }

Ramesh Thangaraj [Syncfusion]
Replied On February 11, 2019 12:58 PM UTC

Hi Justin, 
 
Thanks for your interest with our Syncfusion products, 
 
If you going to use the RDL report designer from Report Designer without no more modifications then you no need set the data source explicitly from your application when the processing mode is Remote (reportOption.ReportModel.ProcessingMode = ProcessingMode.Remote) for loading the RDL Reports. Can you provide the following details to help further on your requirement? 
 
            reportOption.ReportModel.DataSources.Clear(); 
            reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "DataSet1", Value = ds.Tables[0] }); 
            //reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "DataSet2", Value = ds2.Tables[0] }); 
You are loading the RDL report. Can we know the reason to supply the data source for the RDL Report from application? 
            DataSourceCredentials dsc = new DataSourceCredentials(); 
            dsc.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=GirlsHaven;Column Encryption Setting=Enabled;"; 
            dsc.IntegratedSecurity = false; 
            dsc.UserId = "******"; 
            dsc.Password = "******"; 
            dsc.Name = "GirlsHaven"; 
            reportOption.ReportModel.DataSourceCredentials.Add(dsc); 
            reportOption.ReportModel.ProcessingMode = ProcessingMode.Local; 
If you are providing the data source from application with the understating of Local  processing mode then why you are trying to changing the credential information in report? 
 
Regards, 
Ramesh T. 
 
 


Justin
Replied On February 11, 2019 04:08 PM UTC

It is a local rdl
<ej-report-viewer id="reportviewer" report-service-url="/Incidents" report-print="onReportPrint"></ej-report-viewer>

            string basePath = _hostingEnvironment.WebRootPath;
            FileStream inputStream = new FileStream(basePath + @"\Incidents2.rdl", FileMode.Open, FileAccess.Read);
            reportOption.ReportModel.Stream = inputStream;

1. If I run without setting the Datasource, an error is thrown saying "Provide Dataset inputs for Report"
2. You are correct. Apparently I didn't need to set the  DataSourceCredentials. I've commented it out and the report does load. The parameter behavior is the same though. If  Available Values is  "None" it ignores whatever is typed in, and if it's "Get values from a query" I can't click the dropdown to select anything




Mageshyadav M [Syncfusion]
Replied On February 12, 2019 07:13 AM UTC

Hi Justin, 
 
Thanks for the update. 
 
Based on your update, we suspect that error occurred due to incorrect dataset name in your code behind. If you want to change dataset values at runtime you need to set processing mode as local (ej-report-viewer id="container" report-service-url="/Home" processing-mode="Local"></ej-report-viewer>). Please find the code snippet, 
 
public void OnInitReportOptions(ReportViewerOptions reportOption) 
{ 
    string basePath = _hostingEnvironment.WebRootPath; 
    FileStream inputStream = new FileStream(basePath + @"\ReportData\Territory Sales.rdl", FileMode.Open, FileAccess.Read); 
    reportOption.ReportModel.Stream = inputStream;            
} 
 
public void OnReportLoaded(ReportViewerOptions reportOption) 
{ 
    SqlConnection connection = new SqlConnection("Data Source=dataplatformdemodata.syncfusion.com;Initial Catalog=AdventureWorks;User ID=demoreadonly@data-platform-demo;Password=N@c)=Y8s*1&dh;"); 
    var strsql = "SELECT Top (50) ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue FROM Sales.SalesTerritory ST INNER JOIN Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN Person.Contact C ON E.ContactID = C.ContactID INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID GROUP BY    ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue ORDER BY ST.Name"; 
    SqlDataAdapter adapter = new SqlDataAdapter(strsql, connection); 
    adapter.SelectCommand = new SqlCommand(strsql, connection); 
    DataSet ds = new DataSet(); 
    adapter.Fill(ds); 
 
    reportOption.ReportModel.DataSources.Clear(); 
    reportOption.ReportModel.DataSources.Add(new ReportDataSource { Name = "TerritorySales", Value = ds.Tables[0] }); 
} 
 
 
 
Also ensure you are providing the correct dataset name while passing datasource at run time. We have created standalone sample based on your queries. It can be downloaded from the below location, 
 
 
Regards, 
Mageshyadav.M 


Justin
Replied On February 12, 2019 08:50 AM UTC

The sample doesn't have any parameters. My report works fine with no parameters but my problem is I can't get parameters working. I have confirmed both dataset names match. The parameter dropdown in the reportviewer is locked and i can't click it. If I try textbox instead of dropdown, then it isn't applied to report on "View Report" click

 I've attached a copy of the report so you can see what I'm trying to do, but basically if you created a DataSet2 "Select distinct SalesPersonID FROM Sales.SalesPerson" and based a parameter on DataSet2, defaulting to an empty string.

Attachment: Incidents2_26c109ee.zip

Justin
Replied On February 13, 2019 05:01 AM UTC

I figured this out. The reportviewer just can't handle a query that returns a '' as the top value. This must be some sort of bug in the reportviewer, since I uploaded the same report to the syncfusion report server and sql server report server and it worked fine. 

I had to rewrite my dataset queries to return an efor the parameter drop downs like so:
from

SELECT * FROM
(
SELECT '' AS PID /* Parameter Value and Label were both set to PID*/
UNION ALL 
SELECT DISTINCT cIDNUM AS PID FROM tblChildren_c) a 


to

SELECT * FROM
(
SELECT '0' AS PID, '' AS PIDlbl /* Parameter Value set to PID and Label set to PIDlbl */
UNION ALL 
SELECT DISTINCT cIDNUM AS PID, cIDNUM AS PIDlbl FROM tblChildren_c) a 

Mageshyadav M [Syncfusion]
Replied On February 13, 2019 01:47 PM UTC

Hi Justin, 
 
Please find the response for your queries, 
 
Queries 
Response 
The sample doesn't have any parameters. My report works fine with no parameters but my problem is I can't get parameters working. I have confirmed both dataset names match. The parameter dropdown in the reportviewer is locked and i can't click it. If I try textbox instead of dropdown, then it isn't applied to report on "View Report" click 
 
 I've attached a copy of the report so you can see what I'm trying to do, but basically if you created a DataSet2 "Select distinct SalesPersonID FROM Sales.SalesPerson" and based a parameter on DataSet2, defaulting to an empty string. 
We were able to reproduce the mention issue at our end in our essential studio version (16.4.0.42). We will get back to you with timeline details by tomorrow. 
 
Please confirm the version you are using for additional information. 
I figured this out. The reportviewer just can't handle a query that returns a '' as the top value. This must be some sort of bug in the reportviewer, since I uploaded the same report to the syncfusion report server and sql server report server and it worked fine.  
 
I had to rewrite my dataset queries to return an efor the parameter drop downs like so: 
from 
 
SELECT * FROM 
( 
SELECT '' AS PID /* Parameter Value and Label were both set to PID*/ 
UNION ALL  
SELECT DISTINCT cIDNUM AS PID FROM tblChildren_c) a  
 
to 
 
SELECT * FROM 
( 
SELECT '0' AS PID, '' AS PIDlbl /* Parameter Value set to PID and Label set to PIDlbl */ 
UNION ALL  
SELECT DISTINCT cIDNUM AS PID, cIDNUM AS PIDlbl FROM tblChildren_c) a  
We are still validating this issue at our end and will get back to you with further details by tomorrow. 
 
Regards, 
Mageshyadav.M 


Justin
Replied On February 14, 2019 09:39 PM UTC

I'm using the same version, 16.4.0.42

Mageshyadav M [Syncfusion]
Replied On February 15, 2019 02:42 PM UTC

Hi Justin, 
 
We were validated the mentioned problem with your shared version at our end. The mentioned problem occurred due to set processing-mode as local for loading parameter RDL report. To load RDL report, You have to set processing-mode as remote. At default, in our Syncfusion report server we set processing-mode as remote. So mentioned parameter report working fine in our Syncfusion report server. So we have prepared standalone sample for loading parameter RDL report by setting  processing-mode as remote. It can be downloaded from the below location, 
 
 
For RDL report, you don’t need to mention the connection string and dataset query in our “OnInitReportOptions” method. In your RDL report designing itself, you may provide connection string and query parameters. Please find the link to design the query parameter report in our report designer, 
 
 
Regards, 
Mageshyadav.M 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon