一. Sqlserver Reporing Service 应用程序集成
To compile the following code example, you must reference the Reporting Services WSDL and import certain namespaces. For more information, see Compiling and Running Code Examples. The following code example renders a report in MHTML and saves it as an .mht file to disk.
using System; using System.IO; using System.Web.Services.Protocols; using myNamespace.MyReferenceName; class Sample { static void Main(string[] args) { 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 = "MHTML"; 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 MHTML file. try { FileStream stream = File.Create("report.mht", result.Length); Console.WriteLine("File created."); stream.Write(result, 0, result.Length); Console.WriteLine("Result written to the file."); stream.Close(); } catch (Exception e) { Console.WriteLine(e.Message); } } }
摘自:http://msdn.microsoft.com/zh-cn/library/reportexecution2005.reportexecutionservice.render.aspx
二. Windows Azure SQL Reporting应用程序集成
Device information settings in SSRS are used to pass parameters to a rendering extension. For more information regarding device information settings in reporting services, see Passing Device Information Settings to Rendering Extensions.
For SQL Reporting, none of the options specified in the above article are feasible. We see a lot of scenarios where we would want to modify these settings, and render/export the report in the format that we are looking for.
For SQL Reporting reports, we can explore the SOAP APIs in modifying these device information settings at the run time. If we have integrated SQL Reporting within our applications, this approach is really easy, by just creating a separate function.
Here are some steps to achieve this:
- Generate the proxy class for ReportExecution2005.asmx.
- In the browser, go to the path for your endpoint. For example: https://<INSTANCE_NAME>.report.int.mscds.com/ReportServer/ReportExecution2005.asmx
- If prompted for credentials, type your SQL Reporting username and password and click Sign In. A WSDL file will be displayed in the browser.
- Open the Visual Studio Command Prompt and run the wsdl.exe command to generate the proxy class. For example: wsdl /language:CS /n:"ReportExecution2005" https://<INSTANCE_NAME>.report.int.mscds.com/ReportServer/ReportExecution2005.asmx
public void Render_Export()
{
ReportExecutionService rs = new ReportExecutionService();
rs.Url = String.Format("https://{0}:443/ReportServer/reportExecution2005.asmx", ConfigurationManager.AppSettings["SERVER_NAME"]);
rs.CookieContainer = new CookieContainer();
rs.LogonUser(ConfigurationManager.AppSettings["USERNAME"], ConfigurationManager.AppSettings["PASSWORD"], ConfigurationManager.AppSettings["SERVER_NAME"]);
byte[] result = null;
string reportPath = "/SSRSReport/Report5";
string format = "CSV";
string historyID = null;
// Setting the deviceinfo parameters
string devInfo = @"<DeviceInfo><FieldDelimiter>;</FieldDelimiter></DeviceInfo>";
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
rs.ExecutionHeaderValue = execHeader;
execInfo = rs.LoadReport(reportPath, historyID);
String SessionId = rs.ExecutionHeaderValue.ExecutionID;
// code snippet to set the DataSource credentials.
if (execInfo.CredentialsRequired)
{
List<DataSourceCredentials> credentials = new List<DataSourceCredentials>();
foreach (DataSourcePrompt dsp in execInfo.DataSourcePrompts)
{
DataSourceCredentials cred = new DataSourceCredentials();
cred.DataSourceName = dsp.Name;
cred.UserName = ConfigurationManager.AppSettings["USERNAME"];
cred.Password = ConfigurationManager.AppSettings["PASSWORD"];
credentials.Add(cred);
}
Console.WriteLine("Setting data source credentials...");
execInfo = rs.SetExecutionCredentials(credentials.ToArray());
}
// Code snippet to render the file to a specified format.
try
{
result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
execInfo = rs.GetExecutionInfo();
}
catch (Exception ex)
{
throw ex;
}
// Code snippet to write the file to a specific location using FileStream object
try
{
FileStream stream = File.Create(@"D:\report.csv", result.Length);
Console.WriteLine("File created.");
stream.Write(result, 0, result.Length);
Console.WriteLine("Result written to the file.");
stream.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
<appSettings>
<add key="SERVER_NAME" value="SQLReportingServer.reporting.windows.net"/>
<add key="USERNAME" value="UserName"/>
<add key="PASSWORD" value="Password"/>
</appSettings>
For more information about unsupported APIs in SQL Reporting, see http://msdn.microsoft.com/en-us/library/windowsazure/gg430132#UnsupportedAPIs