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.
