Lab

Hands-On Lab
Build Your First Report with SQL Azure
Reporting
Lab version: 1.0.0
Last updated: 2/27/2012
Page 1
CONTENTS
OVERVIEW ................................................................................................................................................... 3
GETTING STARTED: PROVISIONING A SQL AZURE REPORTING ACCOUNT .................................... 5
EXERCISE 1: DESIGNING AND DEPLOYING A SALES REPORT .......................................................... 9
Task 1 – Configuring the report's data source to access a SQL Azure database................................. 10
Task 2 – Designing the Sales Report ................................................................................................... 12
Task 3 – Customizing the Sales Report ............................................................................................... 15
Task 4 – Deploying the Report to SQL Azure Reporting...................................................................... 20
Verification .......................................................................................................................................... 21
EXERCISE 2: ACCESSING THE SALES REPORT FROM A WINDOWS AZURE APPLICATION ........ 23
Task 1 – Configuring the ReportViewer Control ................................................................................. 23
Verification .......................................................................................................................................... 29
SUMMARY .................................................................................................................................................. 31
Page 2
Overview
Microsoft SQL Azure Reporting provides a complete, cloud-based platform designed to support a wide
variety of reporting needs enabling organizations to deliver relevant information where needed across
the entire enterprise. Now developers can deliver highly visual, interactive reports as an integrated part
of a Windows Azure-based solution. There’s no need to install your own Reporting Services instance or
apply software updates, because SQL Azure Reporting runs as a highly available cloud service. Best of all,
SQL Azure Reporting is based on SQL Server Reporting Services, so you can build reports using the same
familiar tools that can be deployed on-premise or in the cloud.
Objectives
In this hands-on lab, you will learn how to:
 Create a SQL Azure data source and data set for a report.
 Add a table to a report.
 Work with report item properties to customize the report appearance.
 Add expressions to calculate values to be display in the report.
 Deploy the report in SQL Azure Report Server.
 Embed the SQL Azure report using the ReportViewer control.
Prerequisites
The following is required to complete this hands-on lab:
 Microsoft Visual Studio 2010
 Microsoft.NET Framework 4
 SQL Server 2008 R2 Business Intelligence Development Studio
 Windows Azure Tools for Microsoft Visual Studio (March 2011)
 Created SQL Azure Server
 SQL Azure Reporting CTP Invitation Code
Page 3
Setup
For convenience, much of the code used in this hands-on lab is available as Visual Studio code snippets.
To check the prerequisites of the lab and install the code snippets:
1. Open a Windows Explorer window and browse to the lab’s Source\Setup folder.
2. Double-click the Dependencies.dep file in this folder to launch the Dependency Checker tool
and install any missing prerequisites and the Visual Studio code snippets.
3. If the User Account Control dialog is shown, confirm the action to proceed.
Note: This process may require elevation. The .dep extension is associated with the Dependency
Checker tool during its installation. For additional information about the setup procedure and how to
install the Dependency Checker tool, refer to the Setup.docx document in the Assets folder of the
training kit.
Using the Code Snippets
Throughout the lab document, you will be instructed to insert code blocks. For your convenience, most
of that code is provided as Visual Studio Code Snippets, which you can use from within Visual Studio
2010 to avoid having to add it manually.
If you are not familiar with the Visual Studio Code Snippets, and want to learn how to use them, you can
refer to the Setup.docx document in the Assets folder of the training kit, which contains a section
describing how to use them.
Exercises
This hands-on Lab includes the following exercises:
1. Designing and Deploying a Sales Report
2. Accessing the Sales Report from a Windows Azure Application
Estimated time to complete this lab: 30 minutes.
Note: When you first start Visual Studio, you must select one of the predefined settings collections.
Every predefined collection is designed to match a particular development style and determines
window layouts, editor behavior, IntelliSense code snippets, and dialog box options. The procedures in
Page 4
this lab describe the actions necessary to accomplish a given task in Visual Studio when using the
General Development Settings collection. If you choose a different settings collection for your
development environment, there may be differences in these procedures that you need to take into
account.
Getting Started: Provisioning a SQL
Azure Reporting Account
To complete the exercises in this lab, you will need a Windows Azure Account with an existing database.
Once you have signed in, you will activate your SQL Azure Reporting account.
1. Enter to Windows Azure portal (http://windows.azure.com) and sign in with your account.
2. Click the Reporting link to enter the SQL Azure Reporting provisioning site.
Figure 1
Provisioning SQL Azure
Note: If you have not requested your invitation to SQL Azure Reporting, you will have to first
sign up by clicking the Register button and wait to receive the invitation code. Once you get it,
you will be able to proceed with this Hands On Lab.
Page 5
3. Read the Terms of use for Provision SQL Azure Reporting and continue the process.
Figure 2
SQL Azure Reporting CTP - Terms of Use
4. Select your SQL Azure Subscription to be used and click Next.
Page 6
Figure 3
Selecting a subscription
5. Enter your Invitation Code and a password you would like to use to access the service.
Figure 4
Entering the Invitation Code
6. Click the Finish button to end the provisioning and enter the Subscription Home. You will find
your subscription information (ID, Account administrator, Service Administrator, status), and
the list of Reporting service names with their Web Service URL. You can copy these values to
use it later through the exercises.
Page 7
Figure 5
Subscription Home
7. Click your reporting service name link, located in the left pane, to enter the Reporting Service
Home. You will see your Web Service URL and user name. You can copy these values to use it
later through the exercises.
Page 8
Figure 6
Reporting Service Home
Exercise 1: Designing and Deploying a
Sales Report
In this exercise, you will use SQL Server Business Intelligence Development Studio to design a report
that uses a query to retrieve sales data from a SQL Azure database. Additionally, you will deploy the
report into SQL Azure Report Server and be able to browse it. When you complete this exercise, the
report will look like this:
Page 9
Figure 7
Sales Summary Report
Task 1 – Configuring the report's data source to access a SQL Azure database
In this task you will create a new report server project and configure the SQL Azure data source that will
be used in the following tasks.
1. Open SQL Server Business Intelligence Development Studio.
2. Create a new project, named AWSalesReport, using the Report Server Project template.
3. Right click on the Reports folder in the Solution Explorer and select Add New Report.
4. In the Welcome to the Report Wizard screen, click Next.
5. In the Select the Data Source screen, rename the data source to AdventureWorks, and select
the Microsoft SQL Azure data source type.
6. Click Edit. The Connection Properties dialog appears.
7. In the Server name field, type: auo9k073d4.database.windows.net.
Page 10
Note: Alternatively, you can use your own SQL Azure server, to do this, install the
AdventureWorks database using the script located in the Setup folder and provide the
credentials for your server in the following steps.
8. Select the Use SQL Server Authentication option, select the Save my password option, and
enter the following credentials:
◦ Username: ReportUser
◦ Password: SqlAzureRocks1
9. In the Select or enter a database name field, type AdventureWorksSalesReport.
10. Click OK. The resulting configuration for the Select the Data Source screen is shown in the
following figure.
Figure 8
Page 11
Data Source Configuration
11. Click Next to move on to the Design the Query screen.
Task 2 – Designing the Sales Report
In this task, you will build a relational query to retrieve total sales for each employee. Territory
information will be used by the report for paging data by Country group it by Region.
1. In the Design the Query screen, click Query Builder. The Query Designer provides a visual
interface that simplifies the creation of queries.
2. Select the following nodes:
a. Tables | DimEmployee| FirstName
b. Tables | DimEmployee| LastName
c. Tables | FactResellerSales| SalesAmount
d. Tables | DimSalesTerritory | SalesTerritoryRegion
e. Tables | DimSalesTerritory | SalesTerritoryCountry
3. Click Group and Aggregate.
4. In the drop down list for the Sales Amount aggregate type select Sum. This will return the total
sales (sum of all sales) for each employee. You can click Run Query to test the query you just
created.
5. Click OK. The outcome should be similar to the one shown in the following figure.
Page 12
Figure 9
Query outcome
6. In the Design the Query screen, click Next.
7. In the Select the Report Type screen, make sure the Tabular option is selected, and click Next.
8. In the Design the Table screen, perform the following actions in the specified order:
a. Select SalesTerritoryCountry from the Available fields list, and click Page.
b. Select SalesTerritoryRegion from the Available fields list, and click Group.
c. Select FirstName from the Available fields list, and click Details.
d. Select LastName from the Available fields list, and click Details.
e. Select Sum_SalesAmount from the Available fields list, and click Details.
The outcome is shown in the following figure.
Page 13
Figure 10
Report design
9. Click Next.
10. In the Choose the Table Layout screen, check the Include subtotals option and click Next.
11. In the Choose the Table Style screen, select the Corporate style, and click Finish.
Note: You will configure deployment information for the report in "Task 4 - Deploying the
report to SQL Azure Reporting".
12. In the Completing the Wizard screen, update the report's name to Sales Report, and click
Finish. The resulting report is shown in the following figure:
Page 14
Figure 11
Sales Report
Task 3 – Customizing the Sales Report
In this task you will customize the look and feel of the Sales Report. You will add new controls, update
the sales field to use a currency format, and use expressions to show the FirstName and LastName fields
in only one column.
In this task you will not modify the report’s information, so if you are not interested in customizing the
appearance of the report, you can skip it.
1. Select the SalesTerritoryCountry Textbox control, and update the following members in the
Properties window:
Property Value
Size 3.63195in, 0.27in
Location 1.36805in, 0in
2. Add a new Textbox control inside the red rectangle shown in the following figure and set its
text to "Country:". To do this, you can use the Textbox control from the Toolbox window.
Important: Unless the TextBox is added inside that rectangle, it will be considered outside the
page's scope by the report. If this happens, and additional page will be added to the report
displaying the TextBox alone.
Page 15
Figure 12
Adding a TextBox
3. Set the properties for the Textbox as shown in the following table:
Property Value
Size 1.34847in, 0.26792in
Location 0in, 0.02in
FontFamily Tahoma
FontSize 14pt
4. Delete the Last Name column. To do this:
a. Select the table to display the row and column handles.
b. Right-click the Last Name column handle and select Delete Columns.
5. Update the text of each column header as shown in the following table. To do this, double-click
each of the headers and update its value.
Column New Value Original Value
1 Region Sales Territory Region
2 Employee First Name
3 Total Sales Sum Sales Amount
Figure 13
Table Header customization
Page 16
6. Configure the sales amount for each region to be displayed using a currency symbol. To do this,
perform the following steps:
a. Right-click the cell in the first row of the Total Sales column.
b. In the context menu, select the Text Box Properties option.
Figure 14
Selecting the Text Box Properties
c. Select the Number option of the left pane.
d. In the Category list, select Currency.
e. In the Decimal places field, set the value to 0.
f. Select the Use 1000 separator (,) option.
Page 17
g. Click OK.
7. Configure the sales amount for each employee to be displayed using a currency symbol. To
do this, perform the preceding procedure, but for the cell in the second row of the Total
Sales column.
Figure 15
Displaying values as currency
8. Configure the employees to be displayed using their first and last names in a single column.
To do this, perform the following steps:
a. Right-click the cell in the second row of the Employee column.
b. Select the Expression option.
Page 18
Figure 16
Selecting the Expression option
c. Update the Set expression for: Value field, with the following expression:
Expression
=Fields!FirstName.Value & " " & Fields!LastName.Value
d. Click OK.
9. Rebuild the project.
10. Click the Preview tab. This will display a preview of the report as shown in the following figure:
Page 19
Figure 17
Sales Report
Note: When previewing the report in Business Intelligence Development Studio, some
computers may experience a rendering bug where the data values for Region, Employee and
Total Sales are not displayed. Please disregard this and continue with the steps as the report
will be correctly rendered after it is deployed to SQL Azure Reporting.
Task 4 – Deploying the Report to SQL Azure Reporting
In this task you will deploy the report created in the previous tasks to the SQL Azure Report Server. To
do this you will configure the Report Server URL, folder and provide the necessary credentials.
1. In the Solution Explorer, right-click the AWSalesReport project and select the Properties option.
2. In the Property Pages dialog, update the TargetServerURL property to use the reporting Web
service URL you recorded in the Provisioning exercise “Getting Started: Provisioning a SQL Azure
Reporting Account”:
https://[yourreportingserver].ctp.reporting.database.windows.net/ReportServer.
3. In the TargetReportFolder property, provide the folder name where you want the report to be
deployed at. Use a meaningful name that helps you identify your report, such as
SQLAzureRSLab_<YourName> as shown in the following figure.
Note: You will have to use this folder name in the Verification to browse to the deployed
report.
Page 20
Figure 18
Configuring the Deployment Options
4. Click OK.
5. Right-click the AWSalesReport project and select Deploy. Doing this, will connect with the SQL
Azure Report Server you configured in task 1.
6. In the Reporting Services Login dialog, enter the credentials you used during the provisioning
tasks.
7. Click OK to deploy the report.
Verification
In order to verify that you have performed every step in the exercise correctly, proceed as follows:
1. Access the SQL Azure Report Server website. To do this, in a web browser navigate to the
following URL:
https://[yourreportingserver].ctp.reporting.database.windows.net/ReportServer.
2. Login using the following credentials you created during the provisioning.
Page 21
Figure 19
SQL Azure Report Server
3. In the directory listing, open the folder named as you specified in Step 3 of Task 4.
4. Inside the folder you will find a link to the Sales Report. Click the link to browse the report. You
can page the report as shown in the following figure:
Page 22
Figure 20
Report hosted on the SQL Azure Report Server
Exercise 2: Accessing the Sales Report
from a Windows Azure Application
In this exercise, you will embed the report in an ASP.NET Application, hosted in a Windows Azure Web
Role, using the ReportViewer control. To do that, you will use the authentication cookie obtained when
logging-in to the SQL Azure Report Server to provide authentication for the ReportViewer control.
Task 1 – Configuring the ReportViewer Control
1. Open Microsoft Visual Studio 2010 with Administrator privileges.
2. Open the SalesSummary.sln solution located in the \Source\Ex02-
EmbeddingReportViewer\Begin\CS folder of this Lab.
Page 23
3. Open the Web.Config file located in the SalesSummary_WebRole project.
4. Complete the appSettings section with your SQL Azure Account information. You can check the
steps 6 and 7 from exercise “Getting Started: Provisioning a SQL Azure Reporting Account” to
locate the Reporting Service user, password and URL (for instance:
https://myreportingserver/ReportServer):
ASP.NET
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<appSettings>
<add key="sqlAzureRSUser" value="[YOUR-REPORTING-SERVICE-USER-NAME]"/>
<add key="sqlAzureRSPassword" value="[YOUR-REPORTING-SERVICE-PASSWORD]"/>
<add key="sqlAzureRSDomain" value="[YOUR-REPORTING-SERVICE-WEBSERVICEURL]"/>
</appSettings>
<system.web>
...
5. Open the Default.aspx file.
6. Add a ScriptManager control as a child of the main content control, as shown in the following
code.
ASP.NET
<%@ Page Title="AdventureWorks Reports" Language="C#"
MasterPageFile="~/Site.master"
AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="SalesSummary_WebRole._Default" %>
<asp:Content ID="HeaderContent" runat="server"
ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server"
ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
</asp:Content>
7. From the Reporting section of the Toolbox window add a ReportViewer control, as the last
child of the main content control. The outcome is shown in the following code fragment.
ASP.NET
<%@ Page Title="AdventureWorks Reports" Language="C#"
MasterPageFile="~/Site.master"
Page 24
AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="SalesSummary_WebRole._Default" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="HeaderContent" runat="server"
ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server"
ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server">
</rsweb:ReportViewer>
</asp:Content>
Note: Take into account that copying the highlighted code might not be enough to get the
ReportViewer control working. Use the Toolbox to add the control instead, which configures
the application properly.
8. In the SalesSummary_WebRole project, add a reference to the
Microsoft.ReportViewer.Common assembly version 10.0.0.0.
9. Set the Copy Local property of both the Microsoft.ReportViewer.Common and
Microsoft.ReportViewer.WebForms assemblies to True. This is because the ReportViewer
needs both assemblies to be present when deployed in Windows Azure.
Page 25
Figure 21
Setting the Copy Local property to True
10. Add the following highlighted code as a child node to the <rsweb:ReportViewer> node. Replace
the value of the %YourReportPath% string with the path where your report is hosted. For
example, if the Report Folder (which you configured in step 3 under task 4 of exercise 1) is
named SQLAzureRSLab_<YourName>, and the report's name is Sales Report, you should set the
report path to /SQLAzureRSLab_<YourName>/Sales Report.
ASP.NET
<rsweb:ReportViewer ID="ReportViewer1" runat="server">
<ServerReport ReportPath="/%YourReportPath%" ReportServerUrl="
https://ctp.reporting.database.windows.net/ReportServer" />
</rsweb:ReportViewer>
11. For each property of the ReportViewer control specified in the following table, provide its
related value.
Property Value
ProcessingMode Remote
Page 26
SizeToReportContent True
The resulting code will should look like the following one:
ASP.NET
<%@ Page Title="AdventureWorks Reports" Language="C#"
MasterPageFile="~/Site.master"
AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="SalesSummary_WebRole._Default" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="HeaderContent" runat="server"
ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server"
ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server"
ProcessingMode="Remote" SizeToReportContent="True">
<ServerReport ReportPath="/%YourReportPath%" ReportServerUrl="
https://ctp.reporting.database.windows.net/ReportServer" />
</rsweb:ReportViewer>
</asp:Content>
12. Open the Web.config file under the SalesSummary_WebRole project.
13. If the <httpHandlers> elements (from the following XML snippets) are not included in the
Web.config file, you must include them. These are required by the ReportViewer control. To do
this, add an HTTP Handler under the <system.web> element, and another handler under the
<system.webServer> element, as shown in the following code fragments.
(Code Snippet – SQLAzureReportingServices-Ex2-SystemWebHTTPHandler-CS)
XML
<system.web>
<httpHandlers>
<add path="Reserved.ReportViewerWebControl.axd" verb="*"
type="Microsoft.Reporting.WebForms.HttpHandler,
Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"
validate="false" />
Page 27
</httpHandlers>
...
(Code Snippet – SQLAzureReportingServices-Ex2-SystemWebServerHTTPHandler-CS)
XML
<system.webServer>
<modules runAllManagedModulesForAllRequests="true" />
<validation validateIntegratedModeConfiguration="false" />
<handlers>
<add name="ReportViewerWebControlHandler" preCondition="integratedMode"
verb="*" path="Reserved.ReportViewerWebControl.axd"
type="Microsoft.Reporting.WebForms.HttpHandler,
Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a" />
</handlers>
</system.webServer>
Note: You can find more information about what these handlers are necessary for in this
MSDN article.
14. Open the code behind file for the Default.aspx page.
15. In the code behind file for the Default.aspx page, add the following using statements.
(Code Snippet – SQLAzureReportingServices-Ex2-UsingStatements-CS)
C#
using Microsoft.Reporting.WebForms;
using System.Net;
using SalesSummary_WebRole.Infrastructure;
using System.Configuration;
16. Use the code from the following snippet to replace the Page_Load method. This code gets the
authentication cookie obtained by logging in to the SQL Azure Reporting and uses it to provide
authentication for the ReportViewer.
(Code Snippet – SQLAzureReportingServices-Ex2-PageLoadMethod-CS)
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
Page 28
{
string user =
ConfigurationManager.AppSettings["sqlAzureRSUser"];
string password =
ConfigurationManager.AppSettings["sqlAzureRSPassword"];
string domain =
ConfigurationManager.AppSettings["sqlAzureRSDomain"];
this.ReportViewer1.ServerReport.ReportServerCredentials = new
ReportServerCredentials(user, password, domain);
this.ReportViewer1.ServerReport.Refresh();
}
}
17. In Visual Studio, save all files.
18. Optionally, you may want to deploy the web role to Windows Azure to test the ReportViewer in
the cloud.
Verification
Note: If you are running this Hands-on Lab connected to a VPN, you must configure Internet Explorer
to use a proxy and not to bypass the proxy server for local addresses. To do this, in Internet Explorer
open the Tools menu and click Internet Options. In the Connections tab, click LAN settings, check the
Use a proxy service for your LAN option, make sure the Bypass proxy server for local addresses
option is not selected and type your proxy's address in the Address field. The outcome is shown in the
following figure.
Page 29
Figure 22
Updating IE's LAN settings
In order to verify that you have performed every step in the exercise correctly, proceed as follows:
1. Set the SalesSummary project as the startup project.
2. In Visual Studio, press F5 to launch the report’s website. Wait for the application to launch in
the development fabric and for the browser to open pointing at the default site address.
3. Verify that the ReportViewer control displays the report that you created in the previous
exercises.
Figure 23
Page 30
Report embedded using the ReportViewer
4. Verify that you can browse through the different pages of the report and the information being
shown is correct.
Figure 24
Browsing through different pages of the report
Summary
In this lab, you have looked at the basics of Business Intelligence Development Studio and SQL Azure
Reporting. If you have any SQL Server Reporting Services experience, you may have found the lab
familiar and that is, indeed, the point. Working with SQL Azure Reporting should be very familiar to
anyone who has worked with SQL Server Reporting Services.
You created a SQL Azure data source and a relational query and added a table to a report. You saw how
to customize the appearance of your reports, create new controls, set their properties and use
Page 31
expressions for the displayed values of the report. You learned how to deploy the report to SQL Azure
Reporting, and navigate the report using the browser.
Finally, you learned how to embed a SQL Azure Reporting Services report in an ASP.NET application
using the ReportViewer control.
Page 32