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.
