Ajax toolkit
Ajax tutorials
|
|
14.2. Integrating Reports into ApplicationsSSRS lets you integrate reports into applications in three different ways:
A typical enterprise application uses more than one of these methods to meet reporting needs. The three methods are discussed in more detail in the following subsections. 14.2.1. URL AccessThe URL request contains information identifying the report server to use, as well as parameters that are processed by the report server and that control the formatting and rendering of the report. The parameters, parameter prefixes, and combination of supplied parameters in the URL determine how the report server handles a specific request. Report server URLs follow W3C/IETF formatting guidelines:
For example, enter the URL http://localhost/ReportServer?/AdventureWorksSampleReports/SalesOrderDetail&rs:Command=Render. The report shown in Figure 14-2 is rendered in the browser. Figure 14-2. Sales order detail report The information in the URL specifies that you want to do the following:
The report server URL syntax is:
protocol://server/virtualroot?[/pathinfo]&[prefix:]param=
value[&prefix:param=value]...n]
where:
Report server parameters are prefixed with rs and are used to control report processing. Table 14-3 describes report server parameters.
Table 14-4 describes the rs:Command parameter values .
For example, entering this URL in your browser will display all child items of the AdventureWorks Sample Reports folder: http://localhost/reportserver?/AdventureWorksSampleReports&rs:Command=ListChildren. HTML device-information settings control how the report is rendered in HTML format. HTML device-information settings, and other device-information settings, are prefixed with rc. Table 14-5 describes HTML device-information settings. See Microsoft SQL Server 2005 Books Online for details about settings for other devices.
You can use a report server parameter to control the rendering format for the report. The URL http://localhost/reportserver?/AdventureWorksSampleReports/CompanySales&rs:Command=Render&rs:Format=IMAGE renders the report as a TIFF image. The URL http://localhost/reportserver?/AdventureWorksSampleReports/CompanySales&rs:Command=Render&rs:Format=XML renders the report as an XML file. HTML device-information settings control how the report is displayed in a browser. The URL http://localhost/reportserver?/AdventureWorksSampleReports/CompanySales&rs:Command=Render&rc:Toolbar=false&rc:Zoom=200 renders the report at twice the default size without a toolbar. Report parameters are passed in the URL as name-value pairs separated by an equals sign (=) . Pass a null parameter using the syntax parameterName:isNull=true. Note that report parameters are not prefixed. For example, the report Employee Sales Summary takes three parameters: ReportMonth (integer), ReportYear (integer), and EmpID (string). When you run the report normally, the RDL populates the employee drop-down list using the DataSet named SalesEmps. This corresponds to the following query:
SELECT E.EmployeeID, C.FirstName + N' ' + C.LastName AS Employee
FROM HumanResources.Employee E
INNER JOIN Sales.SalesPerson SP ON E.EmployeeID = SP.SalesPersonID
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
ORDER BY C.LastName, C.FirstName
We need to run this query to determine the employee ID for each employee. Jillian Carson has employee ID 277. The URL: sets the report parameters to return the sales summary report for Jillian Carson for July 2002. The preceding examples show how to request a report using a URL. This is equivalent to an HTTP GET method . You can also request a report using an HTTP POST method, which transfers the parameter name/value pairs in the HTTP header instead of the URL. Using an HTTP POST method overcomes the maximum allowable URL length limit in cases where a parameter list is long, and is also more secure because the user cannot directly modify the parameter names and values. The following HTML returns the same sales summary report for Jillian Carson for July 2002 as in the preceding example but uses an HTTP POST method:
<form id="postRenderForm"
action="http://localhost/reportserver?/AdventureWorks Sample Reports/
Employee Sales Summary" method="post" target="_self">
<input type="hidden" name="rs:Command" value="Render"/>
<input type="hidden" name="EmpID" value="277"/>
<input type="hidden" name="ReportMonth" value="7"/>
<input type="hidden" name="ReportYear" value="2002"/>
<input type="submit" value="Render"/>
</form>
14.2.2. Report Viewer ControlThe report viewer control is a freely distributable control that ships with Visual Studio 2005. The control is called ReportViewer and is in the Data section of the Toolbox in the Visual Studio 2005 IDE. Use the control by dragging it onto either a Windows form or a web form surface. The ReportViewer control menu prompts you to either "Choose Report" from a drop-down list or "Design a new report." Click "Design a new report" to bring up Report Designer. Select <Server Report>, and you are prompted for the Report Server Url and the Report Path. Fill in these values and run the applicationthe report appears in the control. For example, set the value of Report Server Url to http://localhost/reportserver and the Report Path to /AdventureWorks Sample Reports/Company Sales. Run the applicationif you created a Windows Forms application, the resulting output will look like Figure 14-3. Figure 14-3. AdventureWorks 2002-2003 company sales report The report server and report path can be specified at runtime through the properties of the ServerReport object exposed by the ServerReport property of the control, as shown in the following code snippet:
reportViewer1.ServerReport.ReportServerUrl =
new Uri(@"http://localhost/reportserver");
reportViewer1.ServerReport.ReportPath =
@"/AdventureWorks Sample Reports/Company Sales";
14.2.3. Report Server Web ServiceSSRS provides full access to report server functionality through Report Server web services . The web service provides methods and properties for both report executioncontrolling the processing and rendering of reportsand report management. You can develop Reporting Services applications that use Report Server web services with the .NET Framework, by using the Reporting Services script environment and the rs utility (rs.exe), or by using any development tools capable of invoking SOAP methods. This section discusses only the first approach. For information on the other two approaches, see Microsoft SQL Server 2005 Books Online. These are the steps you follow to create an application that uses Report Server web services (you'll build a real example shortly):
As an example, let's build a Windows Forms application that presents a drop-down list of the reports available in the AdventureWorks Sample Reports folder, as shown in Figure 14-7. When you select a report from the list and click the Render button, the report is saved to a web page (HTML) file. Note that if you select a report that requires parameters, such as Employee Sales Summary, a ReportParameterValueNotSetException is raised. The ParameterValue array argument is always passed as null to keep this example simple. To build the example, create a Windows application named ReportServerWebService. Add a listbox to the form and name it reportListBox. Add a button to the form and name it renderButton. Add a web reference to the ReportService web service and give it the name ReportService. Copy the following code into the form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
Figure 14-5. Web Services on the Local Machine page
using System.Text;
using System.Windows.Forms;
using System.IO;
using ReportServerWebService.ReportService;
namespace ReportServerWebService
{
public partial class MainForm : Form
{
private string reportPath = @"/AdventureWorks Sample Reports";
public MainForm( )
{
InitializeComponent( );
}
private void MainForm_Load(object sender, EventArgs e)
{
ReportingService rs = new ReportingService( );
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
CatalogItem[] cis = rs.ListChildren(reportPath, false);
foreach (CatalogItem ci in cis)
{
if(ci.Type == ItemTypeEnum.Report)
reportListBox.Items.Add(ci.Name);
}
}
Figure 14-6. "ReportingService" Description page Figure 14-7. AdventureWorks sample reports viewer
private void renderButton_Click(object sender, EventArgs e)
{
if (reportListBox.SelectedIndex >= 0)
{
string reportName =
reportListBox.Items[reportListBox.SelectedIndex].ToString( );
SaveFileDialog sfd = new SaveFileDialog( );
sfd.Filter = "Web page format (*.html)|*.html";
sfd.FileName = reportName;
if (sfd.ShowDialog( ) == DialogResult.OK)
{
string renderFileName = sfd.FileName;
ReportingService rs = new ReportingService( );
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
string reportFullName = reportPath + "/" + reportName;
string format = "HTML4.0";
string historyID = null;
string deviceInfo = null;
ParameterValue[] pv = null;
DataSourceCredentials[] dsc = null;
string showHideToggle = null;
string encoding;
string mimeType;
ParameterValue[] parameterUsed = null;
Warning[] warning = null;
string[] streamIds = null;
byte[] report;
try
{
report = rs.Render(reportFullName, format, historyID,
deviceInfo, pv, dsc, showHideToggle, out encoding,
out mimeType, out parameterUsed, out warning,
out streamIds);
using (FileStream fs = File.OpenWrite(renderFileName))
fs.Write(report, 0, report.Length);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
}
Running the example launches the application window with a listbox containing the available reports. Select the Company Sales report and click the Render button. The report is saved as Company Sales.html in the location you specify. An excerpt from this file follows:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>
Company Sales
</title>
<META http-equiv="Content-Type" content="text/html; charset=utf-8">
<META http-equiv="Content-Style-Type" content="text/css">
<META http-equiv="Content-Script-Type" content="text/javascript">
...
<IMG BORDER="0" SRC="http://localhost/ReportServer?%2f
AdventureWorks+Sample+Reports%2fCompany+Sales&
rs:Command=Get&rc:GetImage=9.00.1399.00TogglePlus.gif"/></a>
Bikes</DIV></TD>
<TD class="a17"><DIV class="a16">$26,664,534</DIV></TD>
<TD class="a17"><DIV class="a16">$35,199,346</DIV></TD></TR>
<TR VALIGN="top"><TD WIDTH="0" style="HEIGHT:6.35mm"></TD>
<TD class="a8" COLSPAN="2"><DIV class="a10">
<a href="/">
<IMG BORDER="0" SRC="http://localhost/ReportServer?%2f
AdventureWorks+Sample+Reports%2fCompany+Sales&rs:Command=Get&
rc:GetImage=9.00.1399.00TogglePlus.gif"/></a>
Accessories</DIV></TD>
<TD class="a17"><DIV class="a16">$93,797</DIV></TD>
<TD class="a17"><DIV class="a16">$595,014</DIV></TD></TR></TABLE></TD></TR>
<TR><TD style="HEIGHT:1.59mm"></TD></TR></TABLE></DIV></TD>
<TD WIDTH="100%" HEIGHT="0"></TD></TR><TR>
<TD WIDTH="0" HEIGHT="100%"></TD></TR></TABLE></DIV>
</body>
</html>
Figure 14-8 shows the report when opened in Internet Explorer. The Render( ) method takes arguments that identify the report and specify how to render the results to a byte stream that can be saved to a file or displayed. Device-information settings are passed to the Render( ) method as a <DeviceInfo> XML element. HTML device-information settings are described in Table 14-5, earlier in the chapter. For example, to zoom the report 200%, set the deviceInfo argument of the Render( ) method to the following:
<DeviceInfo>
<Zoom>200</Zoom>
</DeviceInfo>
Figure 14-8. Results for rendering a report to a file example |
|
|
Ajax toolkit
Ajax tutorials
