Forums

On the page (jump to):

Windows Azure SQL Reporting for Application developers

This topic provides information about deploying a report server project to Windows Azure SQL Reporting report server, and getting started information for application developers who integrate reports hosted by Windows Azure SQL Reporting in their applications, as well as develop management tools against SQL Reporting report servers.

To get started with Windows Azure SQL Reporting, you must have a Windows Azure subscription. You can use an existing subscription, a new subscription, or the free trial subscription. For more information, see http://www.windowsazure.com/en-us/.

This guide assumes that you have some prior experience developing custom applications using SQL Server Reporting Services. For more information, see Developer's Guide (Reporting Services). The guide also assumes that you have some knowledge on usage of ReportViewer controls. For an overview of ReportViewer controls, see ReportViewer Controls (Visual Studio).

Objectives

In this tutorial you will learn how to:

Deploy a report server project to a SQL Reporting report server.
Render reports in ReportViewer Controls.
Programmatically access SQL Reporting using SOAP Management Endpoint

Tutorials Segments

Deploy a Report Project
Access SQL Reporting Reports in ReportViewer Controls
Programmatically access Reports using SOAP Management Endpoint
Next Steps

Deploy a Report Project

From Business Intelligence Development Studio(BIDS) of SQL Server, you can deploy all the reports and shared data sources in a Report Server project to a Windows Azure SQL Reporting report server. You can deploy the entire project, or individual reports or data sources. Before you deploy reports or data sources, you need to set the project properties of the Report Server project in Business Intelligence Development Studio.

To set deployment properties

Open an already existing report server project. In Solution Explorer, right-click the report server project and click Properties. (If the Solution Explorer window is not visible, from the View menu, click Solution Explorer.)

For more information about creating a report server project, see Creating a Report Server Project. For other tutorials, see Tutorials (SSRS).
In the Configuration list, select the deployment configuration that you want to use.
In OverwriteDataSources and OverwriteDatasets, select True to overwrite them on the server each time they are deployed or select False to keep the existing versions on the server.
In the TargetServerVersion list, verify that the value is set to SQL Server 2008 R2.
In TargetDataSourceFolder and TargetReportFolder, type the name of folder on the report server in which to deploy the report item.

If you leave the value of TargetDataSourceFolder blank, the data sources will be published to the location specified in TargetReportFolder. If TargetReportFolder is blank, reports and data sources are deployed to the root folder of the server.

Datasets and report parts cannot be managed directly on the SQL Reporting report server and you need not provide values for TargetDatasets and TargetReportPartFolder.
In the TargetServerURL box, type the URL of the target SQL Reporting report server. The syntax of the URL is https://<ServerName>.reporting.windows.net/ReportServer.

To deploy all reports in a project

In Solution Explorer, right-click the report project and click Deploy. You will be prompted for credentials for the SQL Reporting login.

LoginDialog

When you deploy a Report Server project, the shared data sources in the report project are also deployed.

To deploy a single report

In Solution Explorer, right-click the report and click Deploy. You can view the status of the publishing process in the Output window.

When you publish a single report, you must also deploy the shared data sources that the report uses.

To deploy a single data source

In Solution Explorer, right-click the data source and click Deploy. You can view the status of the publishing process in the Output window.

Access SQL Reporting Reports in ReportViewer Controls

Similar to reports deployed to on-premise SQL Server Reporting Services (SSRS) report servers, reports deployed to Windows Azure SQL Reporting report servers can be displayed in ASP.NET applications using the Visual Studio ReportViewer control.

ReportViewer controls are shipped with Visual Studio 2010, Standard Edition or higher editions. If you are using the Web Developer Express Edition, you must install the Microsoft Report Viewer 2010 Redistributable Package to use the ReportViewer runtime features.

To integrate ReportViewer into your Windows Azure application, you need to pay attention to the following:

Include the needed assemblies in the deployment package.
Configure authentication and authorization appropriately.

For more information, see How to: Use ReportViewer in a Web Site Hosted in Windows Azure.

For more information about the use of ReportViewer in a Windows Azure web site that uses more than one web role instance, see Using the ReportViewer ASP.NET Control in Windows Azure.

Creating the Windows Azure Project with ReportViewer control

Use administrator privileges to launch either Microsoft Visual Studio 2010 or Microsoft Visual Web Developer Express 2010.

To do this, in Start | All Programs | Microsoft Visual Studio 2010, right-click the Microsoft Visual Studio 2010 (or Microsoft Visual Web Developer Express 2010) and choose Run as Administrator. If the User Account Control dialog appears, click Continue.

The Windows Azure compute emulator requires that Visual Studio be launched with administrator privileges. For more information about Windows Azure Compute Emulator and other SDK tools, see Overview of the Windows Azure SDK Tools.

In Visual Studio, on the File menu, click New, and then click Project.
From Installed Templates, under Visual C#, click Cloud and then click Windows Azure Project. Name the application and click OK.
In the New Windows Azure Project dialog, inside the .NET Framework 4 roles panel, expand the tab for the language of your choice (Visual C# or Visual Basic), select ASP.NET Web Role from the list of available roles and click the arrow (>) to add an instance of this role to the solution. Before closing the dialog, select the new role in the right panel, click the pencil icon and rename the role. Click OK to create the cloud service solution.
In Solution Explorer, review the structure of the created solution. If Solution Explorer is not already visible, click Solution Explorer on the View menu.
On the designer mode of Default.aspx, drag the ReportViewer control from the Reporting group of the Toolbox to the Web form. To open the Toolbox, click Toolbox on the View menu. You can dock the Toolbox, and you can pin it open or set it to Auto Hide.
Set the ProcessingMode of the ReportViewer control to Remote. ReportViewer configured in Local processing mode is not supported in Windows Azure. Set the other properties on the ReportViewer control to determine the visibility and availability of viewing areas. Use the reference documentation to learn about each property. For more information, see ReportViewer Properties.

To use the ReportViewer control in a Web form, you must also add a ScriptManager control to your page. From the Toolbox window, in the AJAX Extensions group, drag a ScriptManager control to the design surface above the ReportViewer control.

The ReportViewer control manages the authentication cookie, making your tasks easier. To display reports deployed to a SQL Reporting report server in the ReportViewer control, you supply the report server URL and the report path as you would for any server report. Then implement the IReportServerCredentials interface and use it in ServerReport.ReportServerCredentials.

The following example shows how to implement the IReportServerCredentials:

/// <summary>
/// Implementation of IReportServerCredentials to supply forms credentials to SQL Reporting using GetFormsCredentials() 
/// </summary>
using Microsoft.Reporting.WebForms;
using System.Security.Principal;
using System.Configuration;
using System.Net;
public class ReportServerCredentials : IReportServerCredentials
{
    public ReportServerCredentials()
    {
    }   
    public WindowsIdentity ImpersonationUser
    {
        get
        {
            return null;
        }
    }   
    public ICredentials NetworkCredentials
    {
        get
        {
            return null;
        }
    }   
    public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority)
    {
        authCookie = null;
        user = ConfigurationManager.AppSettings["USERNAME"];
        password = ConfigurationManager.AppSettings["PASSWORD"];
        authority = ConfigurationManager.AppSettings["SERVER_NAME"];
        return true;
    }
}

In the Web.config or App.config file, specify the application settings in the <appSettings> element under <configuration>. The following example shows how the <appSettings> element might look like. (Consider specifying the values as per your SQL Reporting report server.)

<appSettings>
  <add key="SERVER_NAME" value="<INSTANCE_NAME>.report.int.mscds.com" />
  <add key="USERNAME" value="<USERNAME>"/>
  <add key="PASSWORD" value="<PASSWORD>"/>
  <add key="REPORT_PATH" value="<REPORT_PATH>"/>
</appSettings>

The following example shows how to use the IReportServerCredentials to access SQL Reporting reports:

using System;
using System.Configuration;
public partial class Default : System.Web.UI.Page
{
    protected void Page_Init(object sender, EventArgs e)
    {
         ReportViewer1.ServerReport.ReportServerUrl = new Uri(String.Format("https://{0}/reportserver", ConfigurationManager.AppSettings["SERVER_NAME"]));
         ReportViewer1.ServerReport.ReportPath = ConfigurationManager.AppSettings["REPORT_PATH"];
         ReportViewer1.ServerReport.ReportServerCredentials = new ReportServerCredentials();
    }
}

Programmatically access Reports using SOAP Management Endpoint

The SQL Reporting SOAP API provides several Web service endpoints for developing custom reporting solutions.The management functionality is exposed through the ReportService2005 and ReportService2010 endpoints. For the list of unsupported SOAP APIs in SQL Reporting, see Guidelines and Limitations for Windows Azure SQL Reporting.

When accessing the SOAP management endpoint, you use the endpoint’s LogonUser() method to authenticate with the endpoint. You then need to save the authentication cookie returned by the HTTP response and include it in each subsequent operation request. The easiest way to do this is to create a new instance of CookieContainer and assign that to the CookieContainer property of the proxy class before calling LogonUser().

To generate the proxy class

In the browser, go to the path for your endpoint. For example: https://<INSTANCE_NAME>.report.int.mscds.com/ReportServer/reportservice2010.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:"ReportServices2010"<WSDL_FILE_PATH>

You can also specify the file (or directory) to save the generated proxy code by specifying the parameter as /o[ut]:filename or directoryname. Else, the proxy file gets created under the default directory from where you are calling the wsdl.exe.

To open Visual Studio Command Prompt (2010) window, Click Start, point to All Programs, point to Microsoft Visual Studio 2010, point to Visual Studio Tools, and then right-click Visual Studio Command Prompt (2010) and choose Run as Administrator. If the User Account Control dialog appears, click Continue.

For more information and syntax for WSDL.exe tool, see Web Services Description Language Tool (Wsdl.exe).
In Visual Studio, add the generated .cs file to your project.

To authenticate and authorize with the management endpoint

The following code shows how to authenticate and authorize with the ReportingServices2010 management endpoint and perform the ReportingService2010.ListChildren() operation. Note that the CookieContainer property is set to a new instance of the CookieContainer class before the LogonUser() method runs. This ensures that the authentication cookie that is returned by the Web response of LogonUser() is saved and used in later Web service calls.

ReportingService2010 rs = new ReportingService2010();
rs.Url = String.Format("https://{0}:443/ReportServer/   ReportService2010.asmx", ConfigurationManager.AppSettings["SERVER_NAME"]);
rs.CookieContainer = new CookieContainer();
rs.LogonUser(ConfigurationManager.AppSettings["USERNAME"], ConfigurationManager.AppSettings["PASSWORD"], ConfigurationManager.AppSettings["SERVER_NAME"]);
CatalogItem[] items = rs.ListChildren("/", true);

Then, in the Web.config or App.config file, specify the application settings in the <appSettings> element. The following example shows how the <appSettings> element might look like. (Consider specifying the values as per your SQL Reporting report server.)

<appSettings>
      <add key="SERVER_NAME" value="<INSTANCE_NAME>.report.int.mscds.com" />
      <add key="USERNAME" value="<USERNAME>"/>
      <add key="PASSWORD" value="<PASSWORD>"/>
    </appSettings>

Next steps

Now you are familiar with SQL Reporting and how to integrate reports hosted by SQL Reporting in your applications, as well as develop management tools against SQL Reporting report servers. Now you can move to the next step by learning more about the available SOAP APIs in SQL Reporting. Refer the following topics: