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 - 553
  • Trackbacks - 0

Tag Cloud


Recent Comments


Recent Posts


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.

 

Wade Wegner introduced a good news about the Windows Azure platform, which is the new Silverlight-based portal for Windows Azure AppFabric Labs had been launched. You can have a look here.

image

As we know the new Silverlight-based portal of Windows Azure had been published on the Nov of last year but the AppFabric part still not changed. (Clicked the AppFabric link will direct to the old portal.) Now the Silverlight-based AppFabric portal is available for Labs.

For more information about this new portal and new features with the latest Windows Azure AppFabric CTP please refer to Wade’s blob post.

 

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.

 

Today is Dec the 29th in Chinese Traditional Calendar, that means on Thursday (3rd of Feb) we will have the Chinese New Year!

1

For those who doesn’t know about the Chinese New Year please visit the wikipedia site. This is the most important holiday not only for the Chinese in China, but the Chinese all around the world. Here I would like to say: ????. (Chun Jie Kuai Le, Happy Chinese New Year).

2

OK I have 3 news with my celebration:

  • The new windows azure developer portal had been published for a while and the windows azure team wants to get to know how do we think about it. Here is a survey avaiable you can send your feedback. PS, please refer to my previous blog for the features of this new site.
  • The latest Window Azure Platform Training Kit Jan Update had been released that you can download here. There is a demo and a hands-on lab about the Windows Phone 7 application with Windows Azure avaiable which should be interesting.
  • If you have heard about the new feature for SQL Azure named SQL Azure Federation, you might know that it’s a cool feature and solution about database sharding. But for now there seems no similar solution for normal SQL Server and local database. I had created a library named PODA, which stands for Partition Oriented Data Access which partially implemented the features of SQL Azure Federation. I’m going to explain more about this project after the Chinese New Year but you can download the source code 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.