Tuesday, October 16, 2012

Retrieve default and available values of Report Parameters using C# : ReportingService2010

I have been playing around with Reporting Services Web service ReportingService2010 for some time and I got really surprised from the things that we can do using this web service. Today I am going to write about retrieving the default and available values of a report parameter using C#. Sometimes back I wrote a post about Accessing Report Server using Report Server Web Service - Microsoft SQL Server 2008R2. So I am not going to write a more detailed post here.

Without much information, I will just paste the code down. Value retrieving part is in bold.
        /// <summary>
        /// pName is the parameter I want to get the default and available values for
        /// </summary>
        /// <param name="pName"></param>
        private void GetDefaultAndAvailableValues(string pName)
        {
            //credentials to connec to the web service
            var userName = "username";
            var password = "password";

            //connecting to web service
            var clientCredentials = new NetworkCredential(userName, password);
            var rs = new ReportingService2010SoapClient();
            if (rs.ClientCredentials != null)
            {
                rs.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;
                rs.ClientCredentials.Windows.ClientCredential = clientCredentials;
            }

            //open the connection
            rs.Open();

            var oTrustedUserHeader = new TrustedUserHeader();
            string historyId = null;
            ParameterValue[] values = null;
            DataSourceCredentials[] credentials = null;
            ItemParameter[] itemParameter = null;

            //all the parameters of the report will be filled to out parameter 'itemParameter'
            rs.GetItemParameters(oTrustedUserHeader, "/MyReports/Dev/MyReport", historyId, true, values, credentials, out itemParameter);

            //iterate through parameters
            foreach (ItemParameter ip in itemParameter)
            {
                //check for the parameter I want to get default and available values for
                if (pName == ip.Name)
                {
                    //get default values
                    string[] defValues = ip.DefaultValues;

                    //get valid/available values
                    ValidValue[] validValues = ip.ValidValues;
                    foreach (ValidValue vValue in validValues)
                    {
                        //ValidValue has Label property and Value property
                        //if you check the parameters in report designer, every parameter has Label field and Value field
                        ddlParamValues.Items.Add(new ListItem(vValue.Label,vValue.Value));
                    }
                }
            }
            //close the connection
            rs.Close();
        }

And this is the output I am getting.
Untitled
Output
Hope this helps.

Happy Coding.

Regards,
Jaliya

2 comments:

  1. Thanks Jaliya for wonderful post. I have a question here. If I make a call to GetReportParameters() with forRender as true, system is taking 400 milliseconds, whereas, if I keep forRender as false, system is taking 160 milliseconds. Is there anyways, where we can get report parameter details with forRender as false and then make sequential call to SSRS to get datasource information?

    ReplyDelete
  2. I am getting the below error while running the above code. Any idea how to fix it?
    An unhandled exception of type 'System.ServiceModel.Security.MessageSecurityException' occurred in mscorlib.dll

    Additional information: The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'NTLM'.

    ReplyDelete