SSRS sending SP parameters and getting Resultant report by using Proxy(SQL 2008 R2)

Question

Hi to all,

I prepared a report in which used a SP as dataset and deployed it to my local development server (SQL 2008 R2) also used wsdl tool to create the proxy and successfully connect the local server with network credentials, get the server version, and return the report list under my server so far so good.

But now I am stuck try to figure out how to pass parameters to report and get the resultant report in pdf format by using wsdl services but most of those examples I am able to find in web was about SQL 2005 and seems like some of those functions are deprecated in SQL 2008 R2

Could you give me some code examples for sending stored procedure parameters to report and getting resultant data by using proxy

All helps will be appreciated, and thanks so much for helping.

Answer 1

Wrote a function for getting report parameters. Now I am able to get report parameters but still don't know how to set each values and save resulting report as pdf

 

Please help

 

        public static Microsoft.SqlServer.ReportingServices2010.ItemParameter[] GetParameterDefinitions(string reportName, string reportPath)
        {
            return GetParameterDefinitions(reportPath + "/" + reportName);
        }
       


        public static Microsoft.SqlServer.ReportingServices2010.ItemParameter[] GetParameterDefinitions(string path)
        {
            using (ReportingService2010 service = CreateManagementService())
            {
                Microsoft.SqlServer.ReportingServices2010.ParameterValue[] values = null;
                Microsoft.SqlServer.ReportingServices2010.DataSourceCredentials[] credentials = null;
                return service.GetItemParameters(path, null, false, values, credentials);
            }
        }

 

Answer 2

Hi Noyan,

Thanks for your post.

According to your description, you would like to take use of reporting services web services to render the relevant report to PDF format file. If you need to add the ReportExecution2005.asmx as reference to your project, and then refer to the code below to generate the report to PDF format file:

ReportExecutionService rs = new ReportExecutionService();

        rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

        rs.Url = "http://myserver/reportserver/ReportExecution2005.asmx";

 

        // Render arguments

        byte[] result = null;

        string reportPath = "/AdventureWorks Sample Reports/Employee Sales Summary";

        string format = "PDF";

        string historyID = null;

        string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

 

        // Prepare report parameter.

        ParameterValue[] parameters = new ParameterValue[3];

        parameters[0] = new ParameterValue();

        parameters[0].Name = "EmpID";

        parameters[0].Value = "288";

        parameters[1] = new ParameterValue();

        parameters[1].Name = "ReportMonth";

        parameters[1].Value = "6"; // June

        parameters[2] = new ParameterValue();

        parameters[2].Name = "ReportYear";

        parameters[2].Value = "2004";

 

        DataSourceCredentials[] credentials = null;

        string showHideToggle = null;

        string encoding;

        string mimeType;

        string extension;

        Warning[] warnings = null;

        ParameterValue[] reportHistoryParameters = null;

        string[] streamIDs = null;

       

        ExecutionInfo execInfo = new ExecutionInfo();

        ExecutionHeader execHeader = new ExecutionHeader();

 

        rs.ExecutionHeaderValue = execHeader;

 

        execInfo = rs.LoadReport(reportPath, historyID);

 

        rs.SetExecutionParameters(parameters, "en-us");

        String SessionId = rs.ExecutionHeaderValue.ExecutionID;

 

        Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);

 

 

        try

        {

            result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

 

            execInfo = rs.GetExecutionInfo();

 

            Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);

 

 

        }

        catch (SoapException e)

        {

            Console.WriteLine(e.Detail.OuterXml);

        }

        // Write the contents of the report to an PDF file.

        try

        {

      string path = @"D:\ShareFolder\Report.pdf";

      FileStream stream = File.Create(path, result.Length);

            stream.Write(result, 0, result.Length);

            Console.WriteLine("Result written to the file.");

            stream.Close();

        }

        catch (Exception e)

        {

            Console.WriteLine(e.Message);

        }

 

Thanks,
Bill Lu

---

Bill Lu

TechNet Community Support

Answer 3

Thanks for your Help Zilong Lu. Now I am able to present my well prepared presentation to my manager hope to persuade her for changing existing SAP reports.<abbr class="affil"></abbr>

Answer 4

@Bill Lu

i was try the sme thing,  i need to call a reportwebservice  from ssis using C# in a scripttask component. ..pass report parameters and execute the report save the report in a folder. i took the above code as it is.. but i get an error at catch block namespace Soapextension coud not be found.

also it shows credentials,  showHideToggle , reportHistoryParameters is assigned bu value never assinged. please suggest i dont know C#.