Build Your First Report with SQL Azure Reporting

Hands-On Lab

Build Your First Report with SQL Azure Reporting

Lab version:1.0.0

Last updated:10/20/2018

Contents

Overview

Getting Started: Provisioning a SQL Azure Reporting Account

Exercise 1: Designing and Deploying a Sales Report

Task 1 – Configuring the report's data source to access a SQL Azure database

Task 2 – Designing the Sales Report

Task 3 – Customizing the Sales Report

Task 4 – Deploying the Report to SQL Azure Reporting

Verification

Exercise 2: Accessing the Sales Report from a Windows Azure Application

Task 1 – Configuring the ReportViewer Control

Verification

Summary

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

Setup

In order to execute the exercises inthis hands-on lab you need to set up your environment.

1. Open a Windows Explorer window and browse to the lab’sSourcefolder.
2. Double-click the Setup.cmd file in this folder to launch the setup process that will configure your environment and install the Visual Studio code snippets for this lab.
3. If the User Account Control dialog is shown, confirm the action to proceed.

Note: Make sure you have checked all the dependencies for this lab before running the setup.

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: 30minutes.

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

1. Read the Terms of use for Provision SQL Azure Reporting and continue the process.

Figure 2

SQL Azure Reporting CTP - Terms of Use

1. Select your SQL Azure Subscription to be used and click Next.

Figure 3

Selecting a subscription

1. Enter your Invitation Codeand a password you would like to use to access the service.

Figure 4

Entering the Invitation Code

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

Figure 5

Subscription Home

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

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:

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 Projecttemplate.
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 MicrosoftSQLAzure data source type.
6. Click Edit. The ConnectionProperties dialog appears.
7. In the Servername field,type:auo9k073d4.database.windows.net.

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.

1. Select the Use SQL Server Authentication option, select the Save my password option, and enter the following credentials:

◦Username: ReportUser

◦Password: SqlAzureRocks1

1. In the Select or enter a database name field, typeAdventureWorksSalesReport.
2. Click OK. The resulting configuration for the Select the Data Source screen is shown in the following figure.

Figure 8

Data Source Configuration

1. 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 DesigntheQuery screen, click QueryBuilder. The Query Designer provides a visual interface that simplifies the creation of queries.
2. Select the following nodes:
3. Tables | DimEmployee| FirstName
4. Tables | DimEmployee| LastName
5. Tables | FactResellerSales| SalesAmount
6. Tables | DimSalesTerritory | SalesTerritoryRegion
7. Tables | DimSalesTerritory | SalesTerritoryCountry
8. Click Group and Aggregate.
9. In the drop down list for the SalesAmount aggregate type select Sum.Thiswillreturn the total sales (sum of all sales) for each employee. You can click Run Query to test the query you just created.
10. Click OK. The outcome should be similar to the one shown in the following figure.

Figure 9

Query outcome

1. In the DesigntheQuery screen, click Next.
2. In the Select the Report Type screen, make sure the Tabular option is selected, and click Next.
3. In the DesigntheTable screen, perform the following actions in the specified order:
4. Select SalesTerritoryCountry from the Availablefields list, and click Page.
5. Select SalesTerritoryRegionfrom the Availablefields list, and click Group.
6. Select FirstName from the Availablefields list, and click Details.
7. Select LastName from the Availablefields list, and click Details.
8. Select Sum_SalesAmount from the Availablefields list, and click Details.

The outcome is shown in the following figure.

Figure 10

Report design

1. Click Next.
2. In the ChoosetheTableLayoutscreen, check the Includesubtotals option and click Next.
3. In the ChoosetheTableStylescreen, 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".

1. In the CompletingtheWizard screen, update the report's name to SalesReport, and click Finish. The resulting report is shown in the following figure:

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 theFirstName 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:

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

Figure 12

Adding a TextBox

1. Set the properties for the Textbox as shown in the following table:

1. Delete the Last Name column. To do this:
2. Select the table to display the row and column handles.
3. Right-click the LastName column handleand select Delete Columns.
4. 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.

Figure 13

Table Header customization

1. Configure the sales amount for each region to be displayed using a currency symbol. To do this, perform the following steps:
2. Right-click the cell in the first row of the TotalSales column.
3. In the context menu, select the Text Box Properties option.

Figure 14

Selecting the Text Box Properties

1. Select the Numberoption of the left pane.
2. In the Category list, select Currency.
3. In the Decimal placesfield, set the value to 0.
4. Select the Use 1000 separator (,)option.
5. Click OK.

1. 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 TotalSales column.

Figure 15

Displaying values as currency

1. Configure the employees to be displayed using their first and last names in a single column. To do this, perform the following steps:
2. Right-click the cell in the second row of the Employee column.
3. Select the Expression option.

Figure 16

Selecting the Expression option

1. Update the Set expression for: Valuefield, with the following expression:

Expression

=Fields!FirstName.Value & " " & Fields!LastName.Value

1. Click OK.

1. Rebuild the project.
2. Click the Preview tab. This will display a preview of the report as shown in the following figure:

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”: .
3. In the TargetReportFolderproperty, 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.

Figure 18

Configuring the Deployment Options

1. Click OK.
2. Right-click the AWSalesReport project and select Deploy. Doing this, will connect with the SQL Azure Report Server you configured in task 1.
3. In the Reporting Services Login dialog, enter thecredentials you used during the provisioning tasks.
4. 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:.
2. Login using the following credentials you created during the provisioning.

Figure 19

SQL Azure Report Server

1. In the directory listing, open the folder named as you specified in Step 3 of Task 4.
2. 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:

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

ASP.NET

<?xmlversion="1.0"?>

<!--

For more information on how to configure your ASP.NET application, please visit

-->

configuration

appSettings

addkey="sqlAzureRSUser"value="[YOUR-REPORTING-SERVICE-USER-NAME]"/>

addkey="sqlAzureRSPassword"value="[YOUR-REPORTING-SERVICE-PASSWORD]"/>

addkey="sqlAzureRSDomain"value="[YOUR-REPORTING-SERVICE-WEBSERVICEURL]"/>

</appSettings

system.web

...

1. Open the Default.aspxfile.
2. Add a ScriptManager control as a child of the main content control, as shown in the following code.

ASP.NET

<%@ PageTitle="AdventureWorks Reports"Language="C#" MasterPageFile="~/Site.master"

AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SalesSummary_WebRole._Default" %>

asp:ContentID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"

</asp:Content

asp:ContentID="BodyContent"runat="server" ContentPlaceHolderID="MainContent"

asp:ScriptManagerID="ScriptManager1"runat="server" />

</asp:Content

1. From theReporting section of the Toolbox window add a ReportViewercontrol,as the last child of the main content control. The outcome is shown in the following code fragment.

ASP.NET

<%@ PageTitle="AdventureWorks Reports"Language="C#" MasterPageFile="~/Site.master"

AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SalesSummary_WebRole._Default" %>

<%@ RegisterAssembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

asp:ContentID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"

</asp:Content

asp:ContentID="BodyContent"runat="server" ContentPlaceHolderID="MainContent"

asp:ScriptManagerID="ScriptManager1"runat="server" />

rsweb:ReportViewerID="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.