It’s about 3 months later when I registered the SQL Azure Reporting CTP on the Microsoft Connect after TechED 2010 China. Today when I checked my mailbox I found that the SQL Azure team had just accepted my request and sent the activation code over to me. So let’s have a look on the new SQL Azure Reporting.

Concept

The SQL Azure Reporting provides cloud-based reporting as a service, built on SQL Server Reporting Services and SQL Azure technologies. Cloud-based reporting solutions such as SQL Azure Reporting provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead for report servers; and secure access, viewing, and management of reports. By using the SQL Azure Reporting service, we can do:

• Embed the Visual Studio Report Viewer ADO.NET Ajax control or Windows Form control to view the reports deployed on SQL Azure Reporting Service in our web or desktop application.
• Leverage the SQL Azure Reporting SOAP API to manage and retrieve the report content from any kinds of application.
• Use the SQL Azure Reporting Service Portal to navigate and view the reports deployed on the cloud.

Since the SQL Azure Reporting was built based on the SQL Server 2008 R2 Reporting Service, we can use any tools we are familiar with, such as the SQL Server Integration Studio, Visual Studio Report Viewer. The SQL Azure Reporting Service runs as a remote SQL Server Reporting Service just on the cloud rather than on a server besides us.

Establish a New SQL Azure Reporting

Let’s move to the windows azure deveploer portal and click the Reporting item from the left side navigation bar. If you don’t have the activation code you can click the Sign Up button to send a requirement to the Microsoft Connect. Since I already recieved the received code mail I clicked the Provision button.

Then after agree the terms of the service I will select the subscription for where my SQL Azure Reporting CTP should be provisioned. In this case I selected my free Windows Azure Pass subscription.

Then the final step, paste the activation code and enter the password of our SQL Azure Reporting Service. The user name of the SQL Azure Reporting will be generated by SQL Azure automatically.

After a while the new SQL Azure Reporting Server will be shown on our developer portal. The Reporting Service URL and the user name will be shown as well. We can reset the password from the toolbar button.

Deploy Report to SQL Azure Reporting

If you are familiar with SQL Server Reporting Service you will find this part will be very similar with what you know and what you did before. Firstly we open the SQL Server Business Intelligence Development Studio and create a new Report Server Project.

Then we will create a shared data source where the report data will be retrieved from. This data source can be SQL Azure but we can use local SQL Server or other database if it opens the port up. In this case we use a SQL Azure database located in the same data center of our reporting service. In the Credentials tab page we entered the user name and password to this SQL Azure database.

The SQL Azure Reporting CTP only available at the North US Data Center now so that the related SQL Server and hosted service might be better to select the same data center to avoid the external data transfer fee.

Then we create a very simple report, just retrieve all records from a table named Members and have a table in the report to list them. In the data source selection step we choose the shared data source we created before, then enter the T-SQL to select all records from the Member table, then put all fields into the table columns. The report will be like this as following

In order to deploy the report onto the SQL Azure Reporting Service we need to update the project property. Right click the project node from the solution explorer and select the property item. In the Target Server URL item we will specify the reporting server URL of our SQL Azure Reporting. We can go back to the developer portal and select the reporting node from the left side, then copy the Web Service URL and paste here. But notice that we need to append “/reportserver” after pasted.

Then just click the Deploy menu item in the context menu of the project, the Visual Studio will compile the report and then upload to the reporting service accordingly. In this step we will be prompted to input the user name and password of our SQL Azure Reporting Service. We can get the user name from the developer portal, just next to the Web Service URL in the SQL Azure Reporting page. And the password is the one we specified when created the reporting service. After about one minute the report will be deployed succeed.

View the Report in Browser

SQL Azure Reporting allows us to view the reports which deployed on the cloud from a standard browser. We copied the Web Service URL from the reporting service main page and appended “/reportserver” in HTTPS protocol then we will have the SQL Azure Reporting Service login page.

After entered the user name and password of the SQL Azure Reporting Service we can see the directories and reports listed. Click the report will launch the Report Viewer to render the report.

View Report in a Web Role with the Report Viewer

The ASP.NET and Windows Form Report Viewer works well with the SQL Azure Reporting Service as well. We can create a ASP.NET Web Role and added the Report Viewer control in the default page. What we need to change to the report viewer are

• Change the Processing Mode to Remote.
• Specify the Report Server URL under the Server Remote category to the URL of the SQL Azure Reporting Web Service URL with “/reportserver” appended.
• Specify the Report Path to the report which we want to display. The report name should NOT include the extension name. For example my report was in the SqlAzureReportingTest project and named MemberList.rdl then the report path should be /SqlAzureReportingTest/MemberList.

And the next one is to specify the SQL Azure Reporting Credentials. We can use the following class to wrap the report server credential.

   1: private class ReportServerCredentials : IReportServerCredentials

   2: {

   3:     private string _userName;

   4:     private string _password;

   5:     private string _domain;

   6:  

   7:     public ReportServerCredentials(string userName, string password, string domain)

   8:     {

   9:         _userName = userName;

  10:         _password = password;

  11:         _domain = domain;

  12:     }

  13:  

  14:     public WindowsIdentity ImpersonationUser

  15:     {

  16:         get

  17:         {

  18:             return null;

  19:         }

  20:     }

  21:  

  22:     public ICredentials NetworkCredentials

  23:     {

  24:         get

  25:         {

  26:             return null;

  27:         }

  28:     }

  29:  

  30:     public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority)

  31:     {

  32:         authCookie = null;

  33:         user = _userName;

  34:         password = _password;

  35:         authority = _domain;

  36:         return true;

  37:     }

  38: }

And then in the Page_Load method, pass it to the report viewer.

   1: protected void Page_Load(object sender, EventArgs e)

   2: {

   3:     ReportViewer1.ServerReport.ReportServerCredentials = new ReportServerCredentials(

   4:         "<user name>", 

   5:         "<password>", 

   6:         "<sql azure reporting web service url>");

   7: }

Finally deploy it to Windows Azure and enjoy the report.

Summary

In this post I introduced the SQL Azure Reporting CTP which had just available. Likes other features in Windows Azure, the SQL Azure Reporting is very similar with the SQL Server Reporting. As you can see in this post we can use the existing and familiar tools to build and deploy the reports and display them on a website. But the SQL Azure Reporting is just in the CTP stage which means

• It is free.
• There’s no support for it.
• Only available at the North US Data Center.

You can get more information about the SQL Azure Reporting CTP from the links following

• SQL Azure Reporting Limited CTP at MSDN
• SQL Azure Reporting Samples at TechNet Wiki

You can download the solutions and the projects used in this post here.

Hope this helps,

Shaun