SSRS Parameters

[Jon Christensen]

We have an angular frontend that talks to our API to display a report from our SSRS server (located on a different server).  I've got everything working for basic reports but have run into a lot of confusion when it comes to report parameters.  For example, we have a report that displays a dropdown of clients before the report is generated.  When the user chooses a vavlue the report displays.  The values for the dropdown are retrieved by calling the SSRS web service, GetItemParameters.  It returns a list of valid values(labels and values).  How do I use these in the API Controller?  Should these be retrieved in OnReportLoaded or OnInitReportOptions?  I've tried parsing the returned values and setting them on ReportModel.Parameters in OnReportLoaded but don't think I am doing this correctly:

ItemParameter[] parameters = GetReportParameters(reportOption.ReportModel.ReportPath);

             if (parameters != null && parameters.Length > 0)

             {

                 IList<ReportParameter> paramList = new List<ReportParameter>();

                 ReportParameter reportParameter = null;

                 foreach (ItemParameter item in parameters)

                 {

                     reportParameter = new ReportParameter();

                     reportParameter.Name = item.Name;                     

                     if (item.ValidValues != null)

                     {

                         foreach (var validValue in item.ValidValues)

                         {

                             reportParameter.Labels.Add(validValue.Label);

                             reportParameter.Values.Add(validValue.Value);

                         }

                     }

                     reportParameter.Nullable = item.Nullable;

                     reportParameter.Prompt = item.Prompt;

                     paramList.Add(reportParameter);

                 }

                 

                 reportOption.ReportModel.Parameters = paramList;

Can you give me an example that retrieves the dropdown values from an SSRS webservice and uses those values in the report.  I've attached what our report looks like in SSRS (including the dropdown).

Attachment: SSRS_Example_b07547bd.zip

[Soundara Rajan S]

Hi Jon,

Thanks for contacting Syncfusion support.

We have option to get available report parameters through ReportHelper in ReportLoaded method as shown in the below example,

        public void OnReportLoaded(ReportViewerOptions reportOption)

        {

            var _parameters = ReportHelper.GetParameters();

            IList<ReportParameter> _params = new List<ReportParameter>();

            foreach (var param in _parameters)

            {

                ReportParameter _param = new ReportParameter();

                _param.Name = param.Name;

                if (param.Name == "InvoiceID")

                {

                    _param.Labels = new List<string>();

                    _param.Values = new List<string>();

                    _param.Labels.Add("10250");

                    _param.Values.Add("10250");

                }

                _params.Add(_param);

            }

            reportOption.ReportModel.Parameters = _params;

        }

You can set/pass the report parameter values through ReportModel as shown in the above example.

We have a prepared sample and it can be downloaded from the below location,
https://www.syncfusion.com/downloads/support/forum/121007/ze/SSRSDemo-382675598.zip  

Please find the UG documentations for more details,
https://help.syncfusion.com/js/reportviewer/report-parameters

Regards,
Soundara Rajan S.

[Jon Christensen]

I might be mis-interpreting parameters.  In the demo you sent how are you populating the Invoice ID dropdown with the initial values?  Is that coming from SSRS?  Is it hardcoded into SSRS or is it coming a database query?  If coming from a database query is that why you need the datasource credentials?

[Jon Christensen]

I've provided the datasource credentials that are configured in SSRS in OnInitReportOptions:

         public void OnInitReportOptions(ReportViewerOptions reportOption)

         {

             try

             {

                 const string domain = "****";

                 string userName = ConfigurationManager.AppSettings["SSRSUsername"];

                 string password = ConfigurationManager.AppSettings["SSRSpassword"];

                 string reportServerURL = ConfigurationManager.AppSettings["ReportServerURL"];

                 reportOption.ReportModel.ReportServerCredential = new System.Net.NetworkCredential(userName, password, domain);                 

                 reportOption.ReportModel.ProcessingMode = ProcessingMode.Remote;                 

                 reportOption.ReportModel.ReportServerUrl = reportServerURL;

                 reportOption.ReportModel.DataSourceCredentials.Add(new Syncfusion.Reports.EJ.DataSourceCredentials("GBM_Prod_ReadOnly", "******", "****"));

                 reportOption.ReportModel.DataSourceCredentials.Add(new Syncfusion.Reports.EJ.DataSourceCredentials("GBM_PROD2012", "******", "*******"));

                 

             }

             catch (Exception ex)

             {

                 throw ex;

             }

         }

 but am seeing an error that doesn't provide a lot of information.  Do you know what would cause this:

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.)"}

This is being thrown in PostReportAction with the reportAction=UpdateDataSource.

[Soundara Rajan S]

Hi Jon,

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.)"} This is being thrown in PostReportAction with the reportAction=UpdateDataSource.	This error might have occurred as your Server instance was not running or blocking the firewall of your SQL connection in your machine. So, start/re-start your SQL instance in Services (services.msc) as shown in the below screenshot.   Please refer to the below links to configure the Firewall specifically for SQL connection, https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ We have prepared a simple utility to test your connection string and it can be downloaded from the below location, https://www.syncfusion.com/downloads/support/directtrac/137390/DataSourceConnectionTest-449402439.zip Please refer to the below steps to validate the connection string using the above utility. 1) Run the shared utility and choose the connection type. 2) Integrated security need to be checked for trusted connections (windows authentications). 3) If the connection string uses username and password, then Authentication checkbox need to be checked and the valid login details must be filled in the desired fields. 4) Click the test connection option to validate your connection string. 5) Connection status will be displayed in a message box. If there is any error, then the details of the errors will be shown along with descriptions. If the issue still persist, please share more information about your SQL configuration.
I might be mis-interpreting parameters.  In the demo you sent how are you populating the Invoice ID dropdown with the initial values?  Is that coming from SSRS?	The values shown in the drop down are populated based on the file specified to the parameter in Report. In our code sample, we have given code to set the default selected parameter from code behind. Also, we can retrieve parameter properties and we can’t retrieve the data values of the parameter dropdown.
Is it hardcoded into SSRS or is it coming a database query?  If coming from a database query is that why you need the datasource credentials?	The ReportParameter available values are retrieved from Dataset query. Since, our shared report uses shared DataSource in the provided sample, we have provided the credential information to the shared DataSource.

Regards,
Soundara Rajan S.

[Soundara Rajan S]

Hi Jon,

I might be mis-interpreting parameters.  In the demo you sent how are you populating the Invoice ID dropdown with the initial values?  Is that coming from SSRS?

Currently, we do not have option to retrieve the parameter values like MS ReportViewer. However, we are trying the possibilities of retrieving the parameter values from report and will get back to you with more information.

Is it hardcoded into SSRS or is it coming a database query?  If coming from a database query is that why you need the datasource credentials?

The ReportParameter available values are retrieved from Dataset query. Since, our shared report uses shared DataSource in the provided sample, we have provided the credential information to the shared DataSource. If the DataSource login details are saved in reportserver, it automatically runs in Reportserver without requesting for credentials. But we can’t retrieve the credential information from Reportserver. So, we have to specify the DataSource credentials for the DataSource that run using credentials or for type shared DataSource. Also, specify the credential details in webAPI for shared DataSource and DataSource that connects by using username password. Please refer to the below KB articles for more details, https://www.syncfusion.com/kb/5492/why-do-you-have-to-provide-ssrs-shared-embedded-datasource-credential-information

Regards,
Soundara Rajan S