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
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.
