Shaun Xu

The Sheep-Pen of the Shaun


News

logo

Shaun, the author of this blog is a semi-geek, clumsy developer, passionate speaker and incapable architect with about 10 years’ experience in .NET and JavaScript. He hopes to prove that software development is art rather than manufacturing. He's into cloud computing platform and technologies (Windows Azure, Amazon and Aliyun) and right now, Shaun is being attracted by JavaScript (Angular.js and Node.js) and he likes it.

Shaun is working at Worktile Inc. as the chief architect for overall design and develop worktile, a web-based collaboration and task management tool, and lesschat, a real-time communication aggregation tool.

MVP

My Stats

  • Posts - 122
  • Comments - 549
  • Trackbacks - 0

Tag Cloud


Recent Comments


Recent Posts


Article Categories


Archives


Post Categories


Image Galleries


.NET



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.

sqlazurereporting01

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.

sqlazurereporting03

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.

sqlazurereporting04

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.

image

 

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.

image

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.

image

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

image

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.

image

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.

image

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.

image

 

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.

image

 

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

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

 

Hope this helps,

Shaun

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.

Comments

Gravatar # re: SQL Azure Reporting Limited CTP Arrived
Posted by Rob S on 7/20/2011 11:57 AM
Thanks for the article, it was good. However, the Reporting CTP is only available at the South East Data Center, not the North US Data Center.
Gravatar # re: SQL Azure Reporting Limited CTP Arrived
Posted by G. on 7/27/2011 9:01 PM
J'aurais aimé savoir si votre adresse http://rsprodctp2135.ctp.reporting.database.windows.net/reports
fonctionne ? est-ce que vous pouvez aller supprmiervos rapports ? car moi je n'y arrive pas. J'atteinds juste le portail pour ovir les rapports avec l'adresse suivante :
https://rsprodctp____.ctp.reporting.database.windows.net/reportserver/login.aspx

Merci par avance
Gravatar # re: SQL Azure Reporting Limited CTP Arrived
Posted by G. on 7/27/2011 9:10 PM
J'aurais aimé savoir si votre adresse http://rsprodctp2135.ctp.reporting.database.windows.net/reports
fonctionne ? est-ce que vous pouvez aller supprimer vos rapports ? car moi je n'y arrive pas. J'atteinds juste le portail pour voir les rapports avec l'adresse suivante :
https://rsprodctp____.ctp.reporting.database.windows.net/reportserver/login.aspx

Merci par avance

Gravatar # re: SQL Azure Reporting Limited CTP Arrived
Posted by G. again on 7/27/2011 9:16 PM
Hello,
after having my invitation code to the Reporting I created reports using the Report Builder 3.0 software
Once created I wanted to manage, but then delete them at:
http://<server_name>/reports not working

Can you give me information if you please

Gravatar # SQL Azure Reporting Limited CTP Arrived
Posted by G. on 7/28/2011 5:17 PM
First of all thank you for your answer. Then do your report once generated with Visual Studio you managed to edit or delete your relationship? If so what address you are using to connect?
Because I tried to create reports in visual studio but the main problem I encounter is that I can not choose a SQL database Azure. And reports created does not work with https://<servername>/reportserver /login.aspx

Thank you in advance
Marion

Gravatar # re: SQL Azure Reporting Limited CTP Arrived
Posted by Shammif2 on 10/28/2011 9:22 PM
Excellent article. Thanks a lot!!
Post A Comment
Title:
Name:
Email:
Comment:
Verification: