Microsoft Azure SQL Database Step by Step
Chapter 6. Cloud reporting
—Leonard Lobel
In the database world, getting information into a database is only part of the job. Another major part is extracting meaningful information, strategically analyzing the data you collected, and making valuable sense of it. To that end, an exhaustive suite of Business Intelligence (BI) services and components have evolved and integrated themselves as part of the on-premises Microsoft SQL Server product. Of these, SQL Server Reporting Services (SSRS) is the primary reporting tool. This component of the SQL Server stack provides a rich reporting dialect known as Report Definition Language (RDL), a service to render RDL-based reports, as well as client tools and designers for authoring and deploying RDL files.
In this chapter, you will set up a Microsoft Azure virtual machine (VM) with SSRS and build some simple RDL reports. You will do this using two front-end tools: Report Builder and Microsoft Visual Studio (specifically, the SSDT Business Intelligence add-in for Visual Studio) to author the reports locally, and then deploy them to SSRS on the VM in Microsoft Azure.
What about Microsoft Azure SQL Reporting?
Microsoft formerly offered SQL Reporting as a cloud-based version of SSRS on Azure, but it has recently discontinued the service. At the time of this writing, you cannot create a new Microsoft Azure SQL Reporting server, and existing servers that are still running will be shut down on October 31, 2014.
SQL Reporting was, essentially, a Platform-as-a-Service (PaaS) version of SSRS that ran on Microsoft Azure. It can help to compare Microsoft Azure SQL Reporting with on-premises SSRS in the same way you can compare Microsoft Azure SQL Database with on-premises SQL Server; the former is a PaaS implementation of the latter that is tailored to run on Microsoft Azure and does not support every feature available with the on-premises product.
Unfortunately, there were several problems with SQL Reporting that prompted Microsoft to discontinue the service and to recommend SSRS on a Microsoft Azure VM instead. Arguably, the most significant issue was that SQL Reporting supported only SQL Database as a data source. In contrast, SSRS supports numerous data sources, including multidimensional data stored in a Microsoft SQL Server Analysis Services cube, as well as non-Microsoft data platforms, including Oracle and DB/2. This meant that the only source of the data you can render in reports with SQL Reporting is a relational database hosted on SQL Database. Also, SSRS allows you to embed custom code in your reports and schedule automated report execution and delivery, whereas SQL Reporting did not support either of those features. Finally, there were performance and pricing issues with the SQL Reporting service itself. It ran slower than SSRS, and it could not be shut down when not needed, so charges would accrue steadily even when no reports were being requested and served.
In the end, Microsoft deemed it best to discontinue SQL Reporting and recommend instead the SSRS-in-a-VM solution that we cover in this chapter. This means that all on-premises capabilities are available in the cloud as well; for example, you can build reports that run in the cloud that are based on a variety of data sources, not just SQL Database. You can also embed custom code (even code that calls into your own .NET assemblies) into your reports, schedule the execution and delivery of reports, and do everything else that SSRS allows. Because the report server catalog resides on the VM’s local disk, performance is similar to the performance you might expect from an on-premises SSRS instance. And when you don’t need to serve reports, you can simply shut down the VM to stop compute charges from accruing on your Microsoft Azure subscription.
There are quite a number of procedures in this chapter, and together, they guide you through the process of setting up a VM with SSRS on Microsoft Azure, designing reports locally using authoring tools, and then deploying those reports to the VM.
You will first create a simple report with Report Builder using the WineCloudDb database, and deploy the report to the VM. Then you’ll move on to creating a report with the SSDT BI tools in Visual Studio. The simple WineCloudDb database doesn’t have enough schema (tables and columns) and data (rows) to effectively demonstrate more advanced reports, so you’ll also download and install AdventureWorks2012 on SQL Database for the Visual Studio report. The AdventureWorks database (available on Codeplex) has been serving as the standard sample database for SQL Server for many years, and there is a special version of the database designed specifically for Microsoft Azure SQL Database.
Note
Many of these procedures are one-time-only installations. Once the VM, tools, and databases are in place, it is actually remarkably fast to put together a report and deploy it. But because the one-time installations can be quite lengthy, you should be prepared to take a lot of coffee breaks while you wait (or perhaps, some wine?).
RDL lies at the heart of SSRS. The RDL format completely describes a report, which essentially consists of three things: how the report connects to the database (the data source), how it queries for its data (the dataset), and how it actually looks (the layout). All that information is contained inside a single RDL file, which is really just a plain XML text file you can view easily in Notepad. While doing so can be very educational and enlightening, Notepad is far from the ideal tool for you to compose RDL. To be productive, there are two primary RDL authoring tools available for both designing and deploying reports:
Report Builder
Visual Studio Report Server projects, with the SSDT Business Intelligence add-in
As you progress through this chapter, you will learn how to use both of these tools for creating and deploying simple reports. Of course, there are numerous features and far more complex scenarios that are possible with RDL that fall outside the scope of this chapter. However, you will complete the chapter with a good foundation from which to grow your reporting skills.
Creating a SQL Server Reporting services virtual machine
You will start by creating a VM to host SSRS, although you technically don’t actually need an SSRS instance to start building reports. This is because Report Builder and Report Server projects in Visual Studio both provide a preview feature that lets you run and view reports locally as you design them. Once you are satisfied with a report design, you can then deploy it to SSRS in the VM and make the report available to users in the cloud.
In this procedure, you will get a VM up and running quickly by selecting a predefined image from the Microsoft Azure VM gallery. This image already has SQL Server with SSRS installed, which gets you almost all the way there. However, SSRS is not configured in the VM, nor does the VM have the necessary firewall rule to allow access to the SSRS over TCP port 80. Furthermore, a VM endpoint must be configured to match the firewall rule. So in the next few procedures, you will do the following:
1. Create the VM.
2. Configure SSRS in the VM.
3. Create a firewall rule in the VM that allows access to the reporting service.
4. Create a corresponding endpoint for the VM in the Microsoft Azure management portal that allows access to the reporting service.
Note
Depending on which SQL Server features you require, you might find it more cost effective to run a licensed copy of SQL Server that you purchase and install separately on the VM. If all you need on the VM is SSRS, you can also consider the free version of Reporting Services that is available with the SQL Server Express With Advanced Services download. The Introduction provides instructions for downloading the SQL Server Express database edition, and you can follow a slightly modified version of those instructions inside a bare-bones Microsoft Azure VM to get SSRS. Just choose the ENU\x64\SQLEXPRADV_x64_ENU.exe file (the Express With Advanced Services download that includes SSRS) instead of ENU\x64\SQLEXPR_x64_ENU.exe (which includes only the relational database engine) to install a free version of SSRS on the VM.
Creating the virtual machine from the image gallery
To create the VM, follow these steps:
1. Log in to the Microsoft Azure portal. This takes you to the ALL ITEMS page.
2. Click VIRTUAL MACHINES on the left side of the page.
3. Click the NEW button at the bottom of the page.
4. Click QUICK CREATE.
5. In the data entry area on the right, do the following:
a. For DNS NAME, type a short but meaningful globally unique name to give the VM, We’ll use WineCloudVM (but you’ll need to choose another name if this one is still unavailable).
b. For IMAGE, choose the latest version of the SQL Server Standard edition running on the latest version of Windows Server from the drop-down list. (At the time of this writing, this is SQL Server 2012 SP1 Standard on Windows Server 2012.)
c. For USER NAME, type a name for the VM administrator account. We’ll use WineAdmin.
d. For NEW PASSWORD and CONFIRM, type and retype a strong password for the VM’s administrator account. The password must contain at least eight characters and include a combination of uppercase and lowercase letters, digits, and symbol characters.
e. For REGION/AFFINITY GROUP, choose the region to host the VM from the drop-down list. To avoid bandwidth charges (as discussed in Chapter 2, “Configuration and pricing”), select the same region in which the SQL Database server with the database you’re reporting on is hosted. Your screen should appear similar to Figure 6-1.
FIGURE 6-1 Creating a new Microsoft Azure VM with SQL Server 2012 SP1 from a predefined gallery image
f. Click CREATE A VIRTUAL MACHINE. It can take a few minutes to create the VM before the portal indicates that it has been started, as shown in Figure 6-2.
FIGURE 6-2 The Microsoft Azure portal showing that the VM has been started
Important
Once the VM starts running, it begins accruing compute charges on your Microsoft Azure subscription. Therefore, you should shut down the VM when you are not working with the procedures in this chapter by clicking the SHUT DOWN button at the bottom of the page.
Configuring SSRS in the virtual machine
The VM you just created from the gallery has SSRS already installed but not yet configured. It’s pretty quick and easy to configure SSRS. You just connect to the VM using Remote Desktop and run the SQL Server Reporting Services Configuration Manager to set up the SSRS database and services URL.
Continuing from the previous procedure in the Microsoft Azure management portal, the row for the VM you just created should still be selected. To configure SSRS in the VM, follow these steps:
1. Log in to the VM:
a. Click the CONNECT button at the bottom of the page. This generates an .rdp file for the remote desktop session and sends it to the browser.
b. When prompted to open or save the .rdp file, click Open, as shown in Figure 6-3.
FIGURE 6-3 Downloading the .rdp file to start a Remote Desktop session with the VM
c. When prompted that the publisher of the remote connection can’t be verified, click Connect.
d. When prompted by the Windows Security dialog, type the user name (WineAdmin, for example) and password you assigned to the VM administrator account in the previous procedure. Note that the user name might already be set, and you might only be prompted to supply the password.
e. Click OK.
f. When prompted that the identity of the remote computer can’t be verified, click Yes. This will start a Remote Desktop session and log you in to the VM.
2. Launch the SQL Server Reporting Services Configuration Manager in the VM.
a. From the VM’s Start screen, you can either scroll through the tiles to find it or just type reporting services to run an app search, and then click on the Report Services Configuration Manager tile.
b. In the Reporting Services Configuration Connection dialog, click Connect. This displays the configuration window for SSRS running in the VM, as shown in Figure 6-4.
FIGURE 6-4 Running the Reporting Services Configuration Manager inside the VM
3. Create the virtual directory for the reporting service. This will be the web service URL that clients use to deploy and retrieve reports, and perform other SSRS operations. It typically ends with /reportserver.
a. Click on Web Service URL in the left panel.
b. Click Apply in the lower-right side of the dialog. This creates the virtual directory that exposes the reporting service over HTTP through port 80.
4. Create the Reporting Services database. This database will be stored on the local SQL Server instance running on the VM (unlike the SQL Databases that you will be building reports for). This database is used internally by Reporting Services to store metadata about the reports it is hosting.
a. Click on Database in the left panel.
b. Click Change Database to open the Report Server Database Configuration Wizard.
c. Click Next all the way through the wizard without changing any default settings, and then wait for the wizard to create the database.
d. Click Finish.
5. Create the virtual directory for Report Manager. This is a friendly front-end website that lets you navigate the report folder hierarchy, render reports, and manage the folder structure, user roles, and permissions. It typically ends with /reports.
a. Click on Report Manager URL in the left panel.
b. Click Apply on the lower-right side of the dialog. This creates the virtual directory that exposes Report Manager over HTTP through port 80.
6. Click Exit.
You’ve now configured SSRS in the VM. However, until you open the firewall for port 80, all client requests will be blocked by the VM.
Opening firewall access to the report server
The next thing to do is create a firewall rule that opens port 80 in the VM so that the VM can accept SSRS requests from clients over HTTP (TCP).
To open TCP port 80 in the VM, follow these steps:
1. Launch Windows Firewall With Advanced Security in the VM. From the VM’s Start screen, you can either scroll through the tiles to find it or just type firewall to run an app search, and then click on the Windows Firewall With Advanced Security tile.
2. Click Inbound Rules in the tree view on the left.
3. Click New Rule in the panel on the right.
4. In the New Inbound Rule Wizard, do the following:
a. On the Rule Type page, choose Port and click Next.
b. On the Protocols And Ports page, type 80 for Specified Local Ports and click Next.
c. On the Action page, accept the Allow The Connection default setting and click Next.
d. On the Profile page, accept all the default settings and click Next.
e. On the Name page, type TCP Port 80 and click Finish. The new rule should appear at the top of the list, as shown in Figure 6-5.
FIGURE 6-5 Creating a firewall rule to open TCP port 80 in the VM and make SSRS accessible to clients
5. Close the Windows Firewall With Advanced Security window.
From this point forward, it is no longer necessary to work with the VM directly over Remote Desktop. SSRS is completely configured in the VM, and it can be accessed by clients directly using the reporting services URL and the Report Manager URL. To log out of the VM now, follow these steps:
1. From the VM’s Start screen, click the user account name WineAdmin on the upper-right side.
2. Click Sign Out.
This logs you out of the VM, but the VM is still running of course (and it’s also billing, as we mentioned previously). There is still one last thing you need to do to make this VM function as an SSRS server in the cloud. You need to create an endpoint for the VM in the Microsoft Azure management portal. Without the endpoint, TCP requests over port 80 will get blocked from ever reaching the VM by Microsoft Azure, and thus the rule you just created in the previous procedure inside the VM would never even have the chance to allow client requests to SSRS.
Back in the Microsoft Azure management portal, the row for the VM should still be selected. To create the endpoint, follow these steps:
1. Click on the name of the VM. This navigates to the Quick Start page for the VM.
2. Click the ENDPOINTS link at the top of the page, as shown in Figure 6-6.
FIGURE 6-6 Clicking the ENDPOINTS link to open TCP port 80 for the VM
3. Click the ADD button at the bottom of the page.
4. Leave the ADD A STAND-ALONE ENDPOINT option selected, and click Next (the right arrow icon on the lower-right side).
5. For NAME, choose HTTP from the drop-down list. This sets the rest of the dialog values for TCP on port 80, as shown in Figure 6-7.
FIGURE 6-7 Creating an HTTP endpoint opens TCP port 80 for the VM
6. Click Finish (the checkmark icon on the lower-right side). It takes a few moments to add the endpoint. Wait until the UPDATE IN PROGRESS message disappears before proceeding.
With SSRS up and running in the VM, you can start thinking about creating your first report. This will be a simple customer list based on a variation of the WineCloudDb database you created in Chapter 1, “Getting started with Microsoft Azure SQL Database.”
Creating the sample database
For the report in this chapter, you will create a new WineCloudDb database very similar to the one used demonstrate in other chapters throughout this book. Listing 6-1 shows the script you will execute to create a few tables and populate them with some data. Once the database is set up, you will create a report that lists each customer’s name, that customer’s favorite wine, and the total sum of all the customer’s orders.
LISTING 6-1 Code to prepare the sample WineCloudDb database for reporting
CREATE TABLE Wine (
WineId int PRIMARY KEY,
Name nvarchar (50),
Category nvarchar (15),
Year int,
Price money)
CREATE TABLE Customer (
CustomerId int PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(15),
FavoriteWineId int,
CONSTRAINT FK_Customer_Wine FOREIGN KEY (FavoriteWineId) REFERENCES Wine (WineId))
CREATE TABLE [Order] (
OrderId int PRIMARY KEY,
OrderedOn datetime2,
CustomerId int,
WineId int,
Quantity int,
Price money,
CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId),
CONSTRAINT FK_Order_Wine FOREIGN KEY (WineId) REFERENCES Wine(WineId))
INSERT INTO Wine VALUES
(1, 'Chateau Penin', 'Bordeaux', 2008, 34.90),
(2, 'McLaren Valley', 'Cabernet', 2005, 48.50),
(3, 'Mendoza', 'Merlot', 2010, 42.00),
(4, 'Valle Central', 'Merlot', 2009, 52.00)
INSERT INTO Customer VALUES
(1, 'Jeff', 'Hay', 4),
(2, 'Mark', 'Hanson', 3),
(3, 'Jeff', 'Phillips', 2)
INSERT INTO [Order] VALUES
(1, '2013-01-01', 1, 1, 5, 34.90),
(2, '2013-01-03', 2, 3, 1, 42.00),
(3, '2013-01-05', 1, 2, 3, 48.50),
(4, '2013-01-06', 2, 4, 2, 52.00),
(5, '2013-01-06', 3, 3, 2, 42.00),
(6, '2013-01-08', 1, 1, 1, 34.90)
This script creates Wine, Customer, and Order tables and loads some sample data into them. Notice the rows being inserted into the Order table at the bottom of the script. The customer ID is the first number after the order date, so you can see that of six orders, three of them were placed by customer 1, two by customer 2, and one by customer 4. The next number is the wine ID, followed by the quantity and price for the order. With this sample data, you will build a report that groups each customer’s orders together and calculates the sum of their orders (based on quantity and price).
To create and set up the WineCloudDb database, follow these steps:
1. From the Windows Start screen, launch SSMS. You can either scroll through the app tiles to find it (in the Microsoft SQL Server 2012 category) or just type sql server management studio to run a search, and then click on the tile. After a brief moment, the Connect To Server dialog appears.
2. In the Connect To Server dialog:
a. For Server Name, type <servername>.database.windows.net. This is the fully qualified name to the SQL Database server, where <servername> should be replaced by the name assigned to your server.
b. For Authentication, select SQL Server Authentication from the drop-down list. (SQL Database does not support Windows Authentication.)
c. For Login and Password, type the user name and password you assigned the server when you created it.
d. Click the Connect button.
3. In the Object Explorer, expand the Databases node.
4. If the WineCloudDb database exists from a previous chapter, delete it now.
a. Right-click the WineCloudDb database, and choose Delete.
b. In the Delete Object dialog, click OK.
5. Create the database.
a. In the Object Explorer, right-click the server name and choose New Query to open a new query window connected to the master database.
b. In the query window, type CREATE DATABASE WineCloudDb.
c. Press F5 (or click the Execute button in the toolbar) to create the database.
d. Close the query window without saving the script.
6. In the Object Explorer, right-click the Databases node and choose Refresh. The WineCloudDb database you just created should now appear.
7. Right-click the WineCloudDb database, and choose New Query to open a new query window connected to the WineCloudDb database.
8. Type the code shown in Listing 6-1 into the query window (or paste it in from the listing file downloaded from the book’s companion website).
9. Press F5 (or click the Execute button in the toolbar) to create the database.
You now have a new WineCloudDb database that will serve as the data source for your first report.
Note
It was necessary to create the database and populate it in two separate query windows because SQL Database does not support the USE statement found in SQL Server for switching the connection from the master database to the WineCloudDb database. See Chapter 3, “Differences between SQL Server and Microsoft Azure SQL Database,” for more information.
Now that the VM and database are all set, you’re ready to start focusing on the report. You’ll use Report Builder to create your first report, and later in the chapter, you’ll use Visual Studio to create a more advanced report.
Using Report Builder
Report Builder is a desktop report authoring tool with a Microsoft Office (ribbon) environment. With Report Builder, you can define a report’s data source (connection information to SQL Database) and dataset (data for the report produced by a query). You can then use designers and wizards to lay out the report, customizing fonts and colors, adding tables and matrixes for numeric data, and adding charts and graphs for visual impact.
Once your data source, dataset, and layout are set, Report Builder lets you preview the report so that you can iteratively design and then view your report without deploying it. When you’re ready, Report Builder can deploy your report to SSRS, which is essentially just a matter of copying the RDL file from your development environment to the VM where SSRS is running.
Alternatively, you can use Visual Studio to perform the same tasks using similar tools. You will do just that to create another report later in the chapter. Unfortunately, there is (currently) no web-based report designer available on the Microsoft Azure portal to run in the browser, so you need to download either Report Builder or the SSDT BI plug-in for Visual Studio to design reports for SSRS.
Installing Report Builder
To install Report Builder (which is a one-time procedure), follow these steps:
1. Open Internet Explorer, and navigate to http://www.microsoft.com/en-us/download/details.aspx?id=29072.
2. Click the large Download button.
3. When prompted to run or save the file, choose Run, as shown in Figure 6-8.
FIGURE 6-8 Downloading Report Builder 3.0
4. When the installation wizard’s welcome page appears, click Next, as shown in Figure 6-9.
FIGURE 6-9 The Report Builder setup wizard welcome page
5. Choose to accept the license agreement terms, and click Next.
6. On the Feature Selection page, click Next.
7. On the Default Target Server page, leave the default target server URL text box empty and click Next.
8. Click Install.
9. When the User Account Control dialog appears, click Yes to begin the installation.
10. When setup completes, click Finish to close the installation wizard.
Creating a report using Report Builder
When you launch Report Builder, the Getting Started dialog appears. You use this dialog to choose among several wizards, which are designed to help you get started quickly with a variety of report types. For your first report, however, you won’t use any of these wizards. Instead, you will start with a clean canvas by choosing to create a blank report.
To launch Report Builder, follow these steps:
1. From the Windows 8 Start screen, launch Report Builder. You can either scroll through the tiles to find it or just type report builder to run an app search, and then click on the Report Builder 3.0 tile.
2. On the Getting Started dialog, click Blank Report, as shown in Figure 6-10.
FIGURE 6-10 Choosing to create a blank report from the Getting Started dialog
The Report Builder window should now appear as shown in Figure 6-11.
FIGURE 6-11 The Report Builder open and ready to create a new report
Notice the Office-style ribbon user interface—with Home, Insert, and View tabs—and the large round Office button in the upper left corner, which displays a menu with options for saving and deploying reports. The main design surface already has a text box for you to type the report’s title, and it also uses the built-in field &ExecutionTime in a text box (at the lower right side of the report) to display the date and time at which the report was executed on the bottom of each page.
On the left side of the window, the Report Data pane gives you access to the key elements of your report. The most important of these are your data sources, which define the connection to the SQL Database that the report is querying. Next, your dataset defines exactly what data gets fed to the report. This can be a table or view, the result of calling a stored procedure, or any Transact-SQL (T-SQL) query that gets sent to SQL Database for processing. Data sources and datasets are typically embedded in individual reports, but you can also define shared data sources andshared datasets and deploy them as reusable objects on the reporting service. This makes it easy to share the same query and provide consistent data across multiple reports.
Once you have at least one data source, and at least one dataset that consumes data from that data source, you are ready to design the layout (and behavior) of the report. To present numerical data, you can use either the table or matrix control. Both of these controls render data in tabular form; the difference lies in the way columns are handled. With both tables and matrixes, there are a variable number of rows in the output. However, tables have fixed columns, while matrixes have variable columns, just like they do on rows.
When you are defining rows and columns, the fields you choose for these axes appear in the Row Groups and Column Groups panes displayed at the bottom of the window. And yes, as implied by those names, both tables and matrixes support grouping both on rows and columns. In fact, the user can even drill down interactively into the levels of the hierarchy defined by your groupings, as you’ll see in a report created later in the chapter.
The Report Data pane also lets you define report parameters, which are values that are typically used by the dataset query to filter the report (for example, by date range or by product category). There is also a place to add images to the report, and the pane provides easy access to built-in fields (a set of handy values including things like current date and time, page number, total pages, user ID, and so on) to include in the report.
That’s more than enough information for you to get started. So go ahead and create your data source.
Creating a data source for the report
To create the data source for the report, follow these steps:
1. Right-click Data Sources in the Report Data pane on the left side of the Report Builder window, and choose Add Data Source.
2. In the Name text box, replace DataSource1 with WineCloudDataSource.
3. Choose the Use A Connection Embedded In My Report option.
4. Choose Microsoft SQL Azure from the Select Connection Type drop-down list.
5. Click the Build button on the right side of the Connection String text box. This opens the familiar Connection Properties dialog.
6. Supply the connection information to the WineCloudDb database you created in the previous section.
a. For Server Name, type <servername>.database.windows.net (replacing <servername> with the server name that was assigned when you created your server).
b. Choose the Use SQL Server Authentication option, and type the user name and password you assigned the server when you created it.
c. Select the Save My Password check box.
d. In the drop-down list beneath the Select Or Enter A Database Name option, select the WineCloudDb database. Or, if the drop-down list appears empty, type WineCloudDb directly into it.
e. Click OK to close the Connection Properties dialog. The Data Source Properties dialog should now appear similar to Figure 6-12.
FIGURE 6-12 Creating a data source with the Data Source Properties dialog
7. Click OK to close the Data Source Properties dialog.
In the Report Data pane, WineCloudDataSource now appears beneath the Data Sources node. Your next task is to create a dataset.
Creating a dataset for the report
A dataset feeds data to a report. As such, it is generated by some form of database query. This can be as simple as dumping all columns and rows from a single table with SELECT * FROM Customer. Or it can be a more complex query that joins on multiple tables, uses expressions to build its output columns, and aggregates data, such as the one shown in Listing 6-2. This query produces a customer list that shows each customer’s total order amount. The list also includes each customer’s favorite wine, if the customer has one.
LISTING 6-2 Query code for the CustomerList report
SELECT
CONCAT(c.LastName, ', ', c.FirstName) AS [Customer Name],
CONCAT(w.Name, ' (', w.Category, ')') AS [Favorite Wine],
COUNT(*) AS Orders,
SUM(o.Quantity * o.Price) AS Total
FROM
Customer AS c
LEFT OUTER JOIN Wine AS w ON c.FavoriteWineId = w.WineId
LEFT OUTER JOIN [Order] AS o ON c.CustomerId = o.CustomerId
GROUP BY
c.FirstName, c.LastName, w.Category, w.Name
ORDER BY
c.LastName, c.FirstName
Notice how the Customer and Wine tables are joined in the query’s FROM clause. The Customer table is aliased as c and the Wine table is aliased as w, joining on the customer’s favorite wine ID. These aliases correspond to the c and w prefixes used on the names of the first two columns in the SELECT list, [Customer Name] and [Favorite Wine]. The CONCAT function combines all the strings passed to it, which formats the customer’s name as “last, first” and the customer’s favorite wine as “name (category).”
Back in the FROM clause, the Customer table is also further joined on the Order table (aliased as o), which returns each customer’s orders. This normally results in returning one row per order, which in turn duplicates customer information in each order row belonging to that customer. However, using a GROUP BY clause and the COUNT and SUM functions for the Total column, this query aggregates (summarizes) the related order rows into a single row with the number of orders and the order total, for each customer. Thus, the query still returns exactly one row per customer. Note that the SUM function is dynamically calculating each order’s total by multiplying the order quantity and price. This is because our Order table doesn’t have a total column with this information. (A real database probably would, but our simple WineCloudDb database doesn’t.) Thus, for each individual order row belonging to a customer, that order’s total is calculated as quantity multiplied by price, and then that total is aggregated (summed) across all the order rows for the customer.
In the next procedure, you will create a dataset for the report based on this query. Although you will embed the query from Listing 6-2 directly into the report, you could just as easily create a stored procedure and put the query there. Using a stored procedure offers an alternative to shared datasets for reusing queries across multiple reports, because then even different reports that use their own (nonshared) datasets can still be fed the same data by calling the same stored procedure.
To create the dataset for the report, follow these steps:
1. Right-click Datasets in the Report Data pane on the left side of the Report Builder window, and choose Add Dataset.
2. In the Name text box, replace DataSet1 with CustomerListDataset.
3. Choose the Use A Dataset Embedded In My Report option.
4. Choose WineCloudDataSource from the Data Source drop-down list.
5. Type the code in Listing 6-2 into the Query text box. The Dataset Properties dialog should now appear similar to Figure 6-13.
FIGURE 6-13 Creating a dataset with the Data Properties dialog
6. Click OK to close the Dataset Properties dialog.
The Report Builder window should now appear similar to Figure 6-14.
FIGURE 6-14 The Report Builder window with a data source and dataset configured
In the Report Data pane, CustomerListDataSet now appears beneath the Datasets node. The fields Customer_Name, Favorite_Wine, Orders, and Total also appear nested beneath CustomerListDataset. (These fields were created based on Report Builder’s discovery of the columns returned by the query.) Now that you have a dataset, you’re ready to design the layout of the report.
Designing the report layout
The report designer already has a text box formatted for the report title at the top of the page. In the next procedure, you’ll fill in the report title and use the Table Wizard to create a table for the customer list.
To design the layout of the report, follow these steps:
1. Click inside the text box in the upper-left portion of the report’s design surface (where it says Click To Add Title), and type Wine Customers.
2. Click Insert at the top of the window to display the Insert ribbon.
3. Click Table in the Insert ribbon, and choose Table Wizard, as shown in Figure 6-15. This displays the New Table Or Matrix wizard.
FIGURE 6-15 Invoking the Table Wizard
4. On the Choose A Dataset page, click CustomerListDataset and click Next.
5. In the Available Fields list box, click the first field, Customer_Name.
6. Hold down the SHIFT key and click on the last field, Total. This selects all the available fields.
7. Drag the selected fields from the Available Fields list box, and drop them in the Values list box, as shown in Figure 6-16.
FIGURE 6-16 Selecting all available fields for the report
8. Click Next to advance to the Choose The Layout page.
9. Click Next to advance to the Choose A Style page. The Ocean style is selected by default.
10. Click Finish to complete the wizard, and add the table to the report design surface.
11. Click in any of the table’s cells to display the gray selection bars on the left and top sides of the table.
12. Click and drag on the lines between columns in the gray selection bar on the top side of the table to widen the Customer Name column.
13. Repeat the previous step to widen the Favorite Wine column.
14. Click the [Sum(Total)] cell beneath the Total column header.
15. Click the dollar-sign button in the Number panel of the Home ribbon at the top of the window. This will cause the order total value to be formatted as currency in the report (rather than an ordinary decimal number).
16. Click any unused (white) area of the report design surface to hide the gray selection bars from the table. The completed report design should appear as shown in Figure 6-17.
FIGURE 6-17 Viewing the completed report design
Saving and running the report locally
With Report Builder, you can preview the report before deploying it. This speeds report development, because you can iteratively design, save, and preview reports locally, and then deploy them only when desired.
To save and run the report locally, follow these steps:
1. Click the Save icon in the Quick-Access Toolbar at the top of the Report Builder window (above the ribbon), as shown in Figure 6-18. Alternatively, press CTRL+S. This displays the Save As Report dialog which, by default, is set to save the report locally in your Documents folder.
FIGURE 6-18 The Save icon on the Quick-Access Toolbar
2. Type CustomerListLocal in the Name text box, as shown in Figure 6-19.
FIGURE 6-19 Saving the report locally
3. Click Save.
4. Click the Run button on the Home ribbon to execute the report, as shown in Figure 6-20.
FIGURE 6-20 Clicking the Run button on the Home ribbon
5. The report should appear similar to Figure 6-21. After viewing the report, click the Design button on the Run ribbon to return to the report designer.
FIGURE 6-21 Running the report locally
The preview shows that the query correctly returned the count and sum of each customer’s orders. The query also correctly formatted the customer and wine names. Satisfied with the report, you are now ready to deploy it so that it’s available to users in the cloud.
Deploying the report to SSRS on the Microsoft Azure VM
To deploy the report, all you need to know is the URL for SSRS running on the VM. This is always a URL in the form of http://<vmname>.cloudapp.net/reportserver, where <vmname> is the globally unique name you assigned to the VM when you created it earlier in this chapter. (We’ve been using winecloudvm.)
To deploy the report, follow these steps:
1. In Report Builder, click the round Office button at the top left of the window and click Save As, as shown in Figure 6-22.
FIGURE 6-22 Clicking the Save As item in the Office menu
2. In the Name text box, type http://<vmname>.cloudapp.net/reportserver (replacing <vmname> with the name you assigned to the VM), and press Enter.
3. In the Connect To Report Server dialog (which can take a few moments to appear), type WineAdmin and the password you assigned to the VM’s administrator account.
4. Select the Remember My Password check box to prevent Report Builder from prompting you for the credentials when performing future deployments, and then click OK.
5. In the Name text box, type CustomerList, as shown in Figure 6-23.
FIGURE 6-23 Saving the report to the Microsoft Azure VM running SSRS
6. Click Save.
The report is now running in the VM on Microsoft Azure, and it can be accessed from any browser. Currently, administrator account WineAdmin is the only user, but you can create other users with varying permission levels, as we discuss at the end of the chapter.
Running the report from a browser
There are two ways to run reports from the browser. You can either use the reporting service URL (the one that ends with /reportserver) or the Report Manager URL (the one that ends with /reports). Recall that you enabled both of these URLs on the VM when you created it at the beginning of this chapter.
If you navigate your browser to the reporting service URL, you will experience a rather bare-bones interface with simple blue hyperlinks on plain white pages. These hyperlinks let you navigate the folders and subfolders in the hierarchy of reports, and choose any report to render in full fidelity. The Report Manager URL offers a friendlier interface to navigate the report folder hierarchy and render reports. It also provides many other features you can use to manage the hierarchy structure, users, groups, permissions, and roles.
To run the deployed report from your browser using Report Manager, follow these steps:
1. Open a new browser window.
2. Type http://<vmname>.cloudapp.net/reports (replacing <vmname> with the name you assigned to the VM) into the browser’s address bar, and press Enter.
3. If prompted for credentials by the Windows Security dialog, type WineAdmin and the password you assigned to the VM’s administrator account. The Report Manager home page is displayed with links for all the available folders and reports on the server. At this point, there is only one link for the CustomerList report in the root folder, as shown in Figure 6-24.
FIGURE 6-24 The Report Manager home page with links to available reports on the VM
4. Click the CustomerList report link to run the report, as shown in Figure 6-25.
FIGURE 6-25 Running the report in a browser window, served by SSRS in the VM
Before closing the browser, take the time to discover the features exposed by the tool bar at the top of the page. You can page through the report (although this simple report has only one page), increase the magnification, search for text within the report, export the report to various formats, and print the report. These standard capabilities are available for every report rendered by SSRS.
Note
Some versions of Internet Explorer require the compatibility view to be set for Microsoft Azure VMs that serve reports using SSRS. Otherwise, the toolbar does not render correctly at the top of the page, and it appears as multiple toolbars with individual items rather than a single toolbar with multiple items, as shown in Figure 6-25. If you experience this behavior, drop down the Tools menu in Internet Explorer and choose Compatibility View Settings. Click Add to set the compatibility view for all cloudapp.net websites. Then click Close to close the Compatibility View Settings dialog. Internet Explorer will refresh the page automatically, and the toolbar will render correctly.
Using Visual Studio Report Server projects
While Report Builder is a great standalone tool for ad-hoc reporting, Visual Studio offers a much richer design-time environment with Report Server projects. As with Report Builder, you can use Visual Studio to create Report Server projects with data sources and datasets, leverage wizards and designers for layout, and deploy to SSRS running on a VM. But much more than that, Report Server projects are actual Visual Studio projects, which means that they enjoy many of the same benefits as any Visual Studio project. This includes source control and the ability to participate in a larger multiproject solution alongside other related projects of different types (for example, .NET projects written in Microsoft C# or Visual Basic .NET, or SSDT database projects).
A tool by any other name
For years, Visual Studio has served as the primary authoring tool for SQL Server Reporting Services, although some confusion has arisen by branding changes and release cycles. Until recently, Business Intelligence Developer Studio (BIDS) was the name for this special version of Visual Studio, which also includes similar tooling for Analysis Services and Integration Services. But since 2012, that functionality has been rebranded as SQL Server Data Tools (SSDT).
Unfortunately, the BI project support (whether you call it BIDS or SSDT) has not been very well aligned with the Visual Studio product release cycles. For years after the release of Visual Studio 2010, BIDS ran only under the Visual Studio 2008 shell, requiring developers building .NET applications and reports to toggle between the two Visual Studio versions. Then, in 2012, SSDT replaced BIDS (while introducing new relational database tooling) and finally brought unity to all project types under the single Visual Studio 2010 shell. However, that pleasure was short-lived once Visual Studio 2012 was released, where SSDT lost the BI project support for Reporting Services, Analysis Services, and Integration Services and retained only the new relational database tools and features. And at the time of this writing, Visual Studio 2013 still does not have SSDT BI support. Thus, you need to download and install the SSDT BI support to create Report Server projects in the Visual Studio 2012 shell (a procedure you will see coming up shortly), even if you are running Visual Studio 2013 otherwise.
Essentially, however, it has always been (and continues to be) the Visual Studio shell that provides project templates, designers, and deployment tools for Reporting Services. So despite the sometimes awkward brand names and untimely release cycles, we’ll often refer to it simply as Visual Studio.
In this section, you will learn how to use Visual Studio to design and deploy Report Server projects to SSRS. But first, you’ll do two things in preparation:
Install the AdventureWorks database for SQL Database.
• This sample database is much larger than WineCloudDb, and it will serve as a better source of reporting data for your next report. Furthermore, it will help you explore many additional reporting capabilities beyond what we cover in this chapter.
Install the SSDT Business Intelligence project templates for Visual Studio.
• If you’re running Visual Studio 2012, you’ll need these project templates to create Report Server projects.
• If you’re running Visual Studio 2013, you’ll still need these project templates, which will run in the Visual Studio 2012 shell.
• If you’re running Visual Studio 2010, you already have these project templates.
Installing AdventureWorks2012 for SQL Database
To install AdventureWorks2012 for SQL Database, follow these steps:
1. Open a new browser window.
2. Navigate to http://msftdbprodsamples.codeplex.com/releases/view/37304.
Note
This URL might have changed by the time this book goes to press. In this case, run an Internet search for “download adventureworks2012 for windows azure sql database” to find the updated link.
3. Beneath RECOMMENDED DOWNLOAD, click the AdventureWorks2012ForWindowsAzureSQLDatabase link.
4. When prompted to open or save, click the drop-down portion of the Save button and choose Save As, as shown in Figure 6-26.
FIGURE 6-26 Saving the AdventureWorks2012ForSQLAzure.zip file
5. In the Save As dialog, navigate to any folder of your choice (or accept the default Downloads folder) and click Save.
6. When the download completes, click the Open Folder button. This opens a new Windows Explorer window to the folder where the downloaded AdventureWorks2012ForSQLAzure.zip file was saved.
7. Right-click the AdventureWorks2012ForSQLAzure.zip file, and choose Extract All.
8. In the Extract Compressed (Zipped) Folders dialog, click Extract to unzip the file and open a new Windows Explorer window to the extracted files.
9. Navigate into the AdventureWorks folder by double-clicking on it.
10. Click the File tab on the ribbon, expand the Open Command Prompt item, and then click on Open Command Prompt As Administrator, as shown in Figure 6-27.
FIGURE 6-27 Opening a command prompt with administrator privileges
11. In the User Account Control dialog, click Yes.
12. In the command-prompt window, type CreateAdventureWorksForSQLAzure <servername>.database.windows.net <username>@<servername> <password>, replacing <servername>, <username>, and <password> with the name of the SQL Database server, administrator account, and administrator password you assigned to the server, as shown in Figure 6-28.
FIGURE 6-28 Deploying the AdventureWorks2012 database to a Microsoft Azure SQL Database server
Note
This step requires the .NET Framework 3.5 (even if .NET Framework 4.5 is installed as part of Visual Studio 2012 or 2013). If it's not already installed, this command will generate errors and a Windows Features dialog will appear prompting you to download .NET Framework 3.5. Click on Download And Install This Feature to install the .NET Framework 3.5. A reboot is required, after which you should open a new Windows Explorer window to the AdventureWorks folder and restart this procedure from step 10.
In addition, this step relies on the bcp command-line utility that ships with SQL Server. If you don’t have already have SQL Server installed on your local machine, you don’t have bcp. However, you don't need to install SQL Server locally just to obtain the bcp utility needed to create the AdventureWorks2012 SQL Database on Microsoft Azure. The bcp utility can be installed without SQL Server by downloading the Microsoft ODBC Driver 11 for SQL Server from http://www.microsoft.com/en-us/download/details.aspx?id=36434, and then downloading the Microsoft Command Line Utilities 11 for SQL Server from http://www.microsoft.com/en-us/download/details.aspx?id=36433.
13. After the script completes, log in to the Microsoft Azure portal to verify that the AdventureWorks2012 database is now up and running on your server, as shown in Figure 6-29.
FIGURE 6-29 Viewing the deployed AdventureWorks2012 database in the Microsoft Azure management portal
Tip
The script creates the database very quickly, but then takes a long time to populate the tables with data. It’s not necessary to wait for the script to complete before proceeding to install SSDT Business Intelligence for Visual Studio 2012, which is the next step. Because that installation is also somewhat lengthy, you can save a lot of time by not waiting for the AdventureWorks2012 database to fully populate before starting the next install, and then waiting for both processes to complete before creating your first Report Server project.
Next, you’ll install the SSDT BI tools for Visual Studio so that you can start creating Report Server projects.
Installing SSDT Business Intelligence for Visual Studio 2012
Both Visual Studio 2010 and 2012 can be used for building Report Server projects. As we explained, if you are using Visual Studio 2010 and installed SSDT with it, you already have the BI project template support for SSRS available to you, and you can skip the next procedure. Otherwise, whether you are running Visual Studio 2012 or 2013, you need to install the SSDT Business Intelligence tools for Visual Studio 2012 (even if you have “regular” SSDT already installed).
Note
During installation, you might be prompted to restart your computer. In this case, click OK, and the installation procedure will resume automatically after your computer restarts.
To install the SSDT Business Intelligence tools for Visual Studio 2012, follow these steps:
1. Open a new browser window.
2. Navigate to http://www.microsoft.com/en-us/download/details.aspx?id=36843. This takes you to the Download Center page for Microsoft SQL Server Data Tools - Business Intelligence For Visual Studio 2012, as shown in Figure 6-30.
FIGURE 6-30 The Download Center page for Microsoft SQL Server Data Tools - Business Intelligence For Visual Studio 2012
Note
This URL might have changed by the time this book goes to press. In this case, run an Internet search for “download business intelligence for visual studio” to find the updated link.
3. Click the Download link. You will be given the choice to Run or Save.
4. Click Run to start the download.
5. When the User Account Control dialog appears, click Yes to start the SQL Server 2012 Setup Wizard.
6. On the License Terms page, select the I Accept The License Terms check box and then click Next.
7. On the Product Updates page, click Next to begin the installation process.
8. If you already have an instance of SQL Server 2012 installed on your machine, the setup wizard will now display an Installation Type page that lets you choose between performing a new installation of SQL Server 2012 and adding features to an existing instance.
a. Be sure to choose the option for performing a new installation (which is selected by default), even though SQL Server is already installed. Otherwise, the setup will fail with an “architecture mismatch” error.
b. Click Next.
9. On the Feature Selection page, click Select All and then click Next.
10. On the Error Reporting page, click Next. When the installation completes, the Complete page is displayed indicating success, as shown in Figure 6-31. If prompted to restart the computer, click OK.
FIGURE 6-31 A successful installation of the Microsoft SQL Server Data Tools - Business Intelligence For Visual Studio 2012
11. Click Close.
Creating a report using Visual Studio
The AdventureWorks database has a lot of data in many tables, and those tables are broken down into schemas, such as Sales, Person, and HumanResources. These schemas are nothing more than logical category groupings—rather than storing all the tables in one flat space (the default dboschema), sales-oriented information is stored in the Sales schema, person information in the Person schema, and so on.
In this report, you will query the SalesOrderHeader and SalesTerritory tables in the Sales schema. This returns order data, along with related territory information (a hierarchy of country and territory), although our report will summarize the order data to the territory level. (If you can’t resist, you can sneak a peek at the final report in Figure 6-46.) The query for the report is shown in Listing 6-3.
LISTING 6-3 The query for this report returns detailed sales information with related territory information
SELECT
soh.SalesOrderID,
DATEPART(YEAR, soh.OrderDate) AS [Year],
soh.CustomerID,
soh.TerritoryID,
terr.Name as TerritoryName,
terr.CountryRegionCode as Country,
soh.TotalDue as TotalSales
FROM
Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesTerritory AS terr ON terr.TerritoryID = soh.TerritoryID
ORDER BY
[Year]
This example differs quite significantly from our previous report of wine customers. Recall from Listing 6-2, that report’s query used a GROUP BY clause with COUNT and SUM aggregate functions to summarize order information for each customer, so that the SQL Database query engine performed the aggregation. SSRS merely dumped that information into a table; the number of rows returned by the query is always the same as the number of rows in the report.
In this query, you’ll notice that there is no aggregation, meaning that the query engine is returning order-level information that merely includes territory information (duplicated across orders in the same territory), and it’s the reporting engine that will aggregate those totals to the territory level. That is, the query itself returns 31,465 rows of order data, and the report you will create summarizes that set down to 10 rows, one per territory. This means you can summarize at the database level wherever it makes sense (or is convenient), and then summarize further if needed at the report level. Also, by joining on the territory table and bringing in the country-territory hierarchy, you can deliver automatic drill-down capabilities to your users (in this case, expanding and collapsing the territories within each country). These are some great examples of the flexibility you get with RDL and SSRS.
In another difference from the previous example, this report will use a matrix rather than a table. Notice that the query uses the DATEPART function with YEAR to extract the year of each order into its own column. You will use this column to create a matrix so that in addition to rendering a variable number of rows (one per territory), the report will render a variable number of columns (one per year) as well.
Using the Report Wizard
The quickest way to create a Report Server project is using the Report Server Project Wizard, and that’s what you’ll do next. Of course, there’s nothing you can do with the wizard that can’t also be done as an independent manual step. As you’ll see, the wizard walks you through the similar steps of creating a data source, dataset, and defining a report layout, as you did earlier using Report Builder.
To create a Report Server project in Visual Studio, follow these steps.
1. Launch SQL Server Data Tools For Visual Studio 2012. From the VM’s Start screen, you can either scroll through the tiles to find it or just type sql server data tools to run an app search, and then click on the SQL Server Data Tools For Visual Studio 2012 tile.
2. If this is the first time you have started SQL Server Data Tools for Visual Studio 2012, you will be prompted for default environment settings. In this case, choose Business Intelligence Settings and click the Start Visual Studio button.
3. Click the FILE menu, and then choose New | Project to display the New Project dialog.
4. On the left side of the New Project dialog, expand Templates, Business Intelligence, Reporting Services, and choose Report Server Project Wizard.
5. Name the solution and project AWReporting, and choose any desired location, as shown in Figure 6-32.
FIGURE 6-32 Creating a new Report Server project using the Report Server Project Wizard
6. Click OK to start the Report Wizard.
7. On the welcome page, click Next.
8. On the Select The Data Source page, do the following:
a. Name the data source AWDataSource.
b. Choose Microsoft SQL Azure from the Type drop-down list.
c. To the right of the connection string text box, click Edit to open the familiar Connection Properties dialog.
d. For Server Name, type <servername>.database.windows.net (replacing <servername> with the name that was assigned when you created your server).
e. Choose the Use SQL Server Authentication option, and type the user name and password you assigned the server when you created it.
f. Select the Save My Password check box.
g. In the drop-down list beneath the Select Or Enter A Database Name option, select the AdventureWorks2012 database.
h. Click OK to close the Connection Properties dialog. Your screen should appear similar to Figure 6-33.
FIGURE 6-33 Defining the report’s data source as AdventureWorks2012 on Microsoft Azure SQL Database
i. Click Next.
9. On the Design The Query page, type the code shown in Listing 6-3 into the Query String text box (or paste it in from the listing file downloaded from the book’s companion web site). Your screen should appear similar to Figure 6-34.
FIGURE 6-34 Defining the report’s query to return sales details with territory information
10. Click Next.
11. On the Select The Report Type page, choose Matrix and click Next.
12. On the Design The Matrix page, do the following:
a. Drag TotalSales from Available Fields, and drop it in the Details list.
b. Drag Year from Available Fields, and drop it in the Columns list.
c. Drag Country from Available Fields, and drop it in the Rows list.
d. Drag TerritoryName from Available Fields, and drop it in the Rows list beneath Country.
e. Select the Enable Drilldown check box. The Report Wizard should appear as shown in Figure 6-35.
FIGURE 6-35 Designing the report’s matrix layout
f. Click Next.
13. On the Choose The Matrix Style page, click Next.
14. In the Report Server text box on the Choose The Deployment Location page, type http://<vmname>.cloudapp.net/reportserver (replacing <vmname> with the name assigned to your VM), and click Next.
15. In the Report Name text box on the Completing The Wizard page, type Annual Sales By Territory.
16. Click Finish to complete the wizard.
The wizard generates the report as shown in Figure 6-36. You can see how Visual Studio provides many of the same features as Report Builder. You get the same report designer surface, flanked on the left by the Report Data pane (expanded in Figure 6-36 to show the data source and dataset) and on the bottom by the row and column groups defined for the matrix. (Notice the country-territory hierarchy in the row groups.) The designer also has a Preview tab to run the report locally, without deploying it.
FIGURE 6-36 The project generated by the Report Server Project Wizard, open in design view
The wizard is great for quickly bringing together all the elements of a report, but you’ll almost always need to customize or adjust the report that it produces. In our wine customer report, we didn’t bother formatting the numbers as currency, but business users usually like to see numbers in the language that counts—money!
Formatting numeric data with the Report Designer
To format the sum of total sales as currency, follow these steps:
1. Right-click the [Sum(TotalSales)] column, and choose Text Box Properties.
2. Click Number in the left navigation pane of the Text Box Properties dialog.
3. Click Currency in the Category list box.
4. Change Decimal Places to 0. (Pennies are often considered an annoyance.)
5. Select the Use 1000 Separator check box. The Text Box Properties dialog should appear as shown in Figure 6-37.
FIGURE 6-37 Formatting the currency text box of the matrix
6. Click OK.
You’re all set to preview the report, so click the Preview tab at the top of the window. At first, the report displays only six rows, because the country-territory hierarchy is collapsed, and the report is showing aggregated information at the country level. As it turns out, the AdventureWorks database has only one territory per country except for the U.S., which has territories defined for Central, Northeast, Northwest, Southeast, and Southwest. Click the plus sign next to US to expand those territories and view the total sales for the various regions within the U.S., as shown inFigure 6-38.
FIGURE 6-38 Previewing the drill-down capabilities of the report
Adding a bar chart to the report
It’s very easy to add charts to your reports. RDL supports many different types of charts, including column, line, bar, and area charts. You use the report designer in Visual Studio to define the data for the chart and customize chart properties, such as 3D options, visibility, fill, border, and shadow settings.
Let’s add a bar chart to this report that reflects the same information as the table. The chart will show total sales for each country (it won’t drill down into the territory level), across all years. You will first enlarge the working space for the report, and then place the chart just beneath the table. To configure the chart data, you will set its values to TotalSales, its category groups to Country, and its series groups to Year.
To create the bar chart, follow these steps:
1. Click the Design tab at the top of the window to leave the report preview and return to design mode.
2. Click the bottom border of the report, and drag down to lengthen the height of the report’s design surface. Give it a generous amount of vertical space to accommodate the chart.
3. Click VIEW | Toolbox to display the toolbox (if it’s not already currently visible).
4. Click and drag the Chart item from the toolbox, and drop it on the report, just below the table, and all the way to the left.
5. In the Select Chart Type dialog, the Column chart is selected by default, as shown in Figure 6-39. Click OK to choose it.
FIGURE 6-39 Viewing the gallery of available chart types
6. Click the sizing handle on the right of the chart, and drag it all the way over to the right to widen the chart across the entire page.
7. Click directly on the chart to open the Chart Data panel.
8. For Values, click the green plus sign and choose TotalSales.
9. For Category Groups, click the green plus sign and choose Country.
10. For Series Groups, click the green plus sign and choose Year. The designer should appear similar to Figure 6-40.
FIGURE 6-40 Setting the values, category groups, and series groups that define the chart’s data.
11. Customize the labels:
a. Click once on the Chart Title to select the text box, and then once again to enter edit mode. Replace Chart Title with By Country, and press Enter.
b. Click once on the vertical Axis Title to select the text box, and then once again to enter edit mode. (The text box will temporarily shift to horizontal display so that you can type.) Replace Axis Title with Total Sales, and press Enter.
c. Click once on the horizontal Axis Title to select the text box, and then once again to enter edit mode. Replace Axis Title with Country, and press Enter.
12. Click the Preview tab.
The bar chart now renders beneath the numbers, and it automatically adjusts its location if you drill down on the numbers. For example, if you expand US to drill down on its territories in the preceding table, the chart below moves down to accommodate, as shown in Figure 6-41. Total sales for each country are displayed, with bars for the years rendered in a cluster for each country.
FIGURE 6-41 Previewing the report with a bar chart
Deploying the report to SSRS on the Microsoft Azure VM
Earlier, when you created the report using the wizard, you supplied the VM’s SSRS service URL to the report wizard. That URL is stored in the project properties, and it can be changed at any time simply by modifying TargetServerURL property. To view the properties now, right-click theAWReporting project in Solution Explorer and choose Properties.
As shown in Figure 6-42, the Property Pages dialog shows the TargetServerURL set to the URL you supplied to the wizard, along with several other interesting properties. For example, notice the TargetReportFolder, which is set to AWReporting. This means that, when you deploy, the report itself (which is the file Annual Sales By Territory.rdl) will be created beneath a folder called AWReporting (named, by default, after the project, but easily changed here if desired). Now click Cancel to close the project Property Pages dialog.
FIGURE 6-42 Viewing the properties of the Report Server project
To deploy the report to SSRS on the VM and then render it, follow these steps:
1. Right-click the AWReporting project in Solution Explorer, and choose Deploy.
2. When prompted, enter the administrator user name WineAdmin and its password that you assigned when you created the VM, as shown in Figure 6-43.
FIGURE 6-43 Entering login information to deploy the report
3. Click OK to deploy. When completed, Visual Studio shows the results in the Output window, as shown in Figure 6-44.
FIGURE 6-44 Viewing the output of a successful deployment
Running the report from a browser
To run the deployed report from your browser using Report Manager, follow these steps:
1. Open a new browser window.
2. Type http://<vmname>.cloudapp.net/reports (replacing <vmname> with the name you assigned to the VM) into the browser’s address bar, and press Enter.
3. If prompted for credentials by the Windows Security dialog, type WineAdmin and the password you assigned to the VM’s administrator account. The Report Manager home page is displayed with links for all the available folders and reports on the server. This includes theAWReporting folder that Visual Studio deployed the report to, as shown in Figure 6-45.
FIGURE 6-45 The Report Manager home page with the AWReporting folder link
4. Click the AWReporting link to drill down into the folder.
5. Click the Annual Sales By Territory link to display the report.
As you can see in Figure 6-46, the report looks and works the same in the browser as it did in the designer preview inside Visual Studio.
FIGURE 6-46 Viewing the final report in Internet Explorer
By now, you understand the basic steps involved in working with SSRS. Whether you choose to use Report Builder or Visual Studio Report Server projects, you define your data sources or datasets and lay them out in one or more reports (RDL files). You can then preview the report locally and deploy to the cloud whenever desired. After you deploy a report, you need to start thinking about security.
Implementing report security
Anonymous requests are never accepted by SSRS. All users and applications who request access to the report server must be authenticated. By default, SSRS uses Windows Integrated authentication, meaning that standard Windows Active Directory accounts are used to log in to the report server. Throughout this chapter, you used Windows Integrated authentication with the WineAdmin account that you created as administrator of the VM. (In this scenario, the Active Directory is stored on the VM, although quite often it is stored on a separate VM designated as a domain controller, and shared by all VMs that are joined in to the domain.) You can create more users on the VM, which adds them to the VM’s Active Directory. This enables different individuals to access reports using their own credentials.
Windows authentication is convenient and easy, but it is not the best choice in all situations. SSRS also supports several other types of authentication, including basic authentication, forms-based authentication, and custom authentication. Basic authentication encodes the user name and password in clear text as a base-64 encoded string in the HTTP header, so it is only secure if you also encrypt the channel to make the HTTP header unreadable, typically using Secure Sockets Layer (SSL). Forms-based authentication is a security extension you use to manage your own user store, which can be something like a database table or configuration file. If you have very particular requirements that cannot be met by any of these supported authentication types, you can also implement custom authentication. This is an advanced scenario that requires custom code as well as a good deal of expertise in ASP.NET security.
Authorization is separate and distinct from authentication. Once a user is authenticated, what that user can and cannot do is controlled by role assignments you define using Report Manager. The least privileged role is Browser, which just allows users to view folders and reports. The most privileged role is Content Manager, which allows users to publish reports and gives them total permission (including delete) to folders, reports, and report definitions. You assign users to roles for specific reports or report folders, which determines whether a user can access that particular resource, or if the user can perform a specific operation (for example, delete a report or deploy a report). You can also create groups of users, and then assign groups to a role. This effectively adds every user in the group to the role, which makes it easy to manage multiple users as a single entity.
More Info
Security in SSRS is sophisticated and potentially complex. The MSDN online documentation provides a thorough and detailed treatment of SSRS security that you should familiarize yourself with as you contemplate the security model for your particular requirements. The documentation can be found at http://msdn.microsoft.com/en-us/library/bb522728.aspx.
Shutting down the SSRS virtual machine
Before concluding the chapter, we strongly recommend shutting down the VM you created to host SSRS, or else your subscription will continue getting charged—even if no users are deploying or viewing reports.
To shut down the VM, follow these steps:
1. Log in to the Microsoft Azure portal. This takes you to the ALL ITEMS page.
2. Click VIRTUAL MACHINES on the left side of the page.
3. Click on the virtual machine to select it. (Click anywhere on the row except in the name column, or the portal will navigate you away to the VM’s Quick Start page.)
4. Click the SHUT DOWN button at the bottom of the page.
5. Click YES when prompted to confirm.
The VM uses a virtual hard disk that is stored in Microsoft Azure Blob Storage, so it retains its state while it remains shut down. Whenever you need to start delivering reports again to your users, simply boot the VM from the Microsoft Azure management portal to bring SSRS back online.
Summary
This chapter introduced you to SQL Server Reporting Services (SSRS) and showed you how to create a Microsoft Azure virtual machine (VM) with SSRS to implement cloud reporting. You started by creating the VM and configuring it for SSRS with reporting service and Report Manager URLs. You then used the standalone Report Builder tool to create a simple customer report for the WineCloudDb database. In the process, you created a data source and dataset for the report, designed the layout using a table, and deployed the report to SSRS on the Microsoft Azure VM.
You then downloaded the AdventureWorks sample database to use as the data source for an annual sales report built in Visual Studio with a Report Server project. After downloading the SSDT Business Intelligence add-in for Visual Studio 2012, you used the Report Server Project Wizard to create the data source and dataset, and design the table layout. After applying some custom formatting and adding a bar chart to the report, you then deployed the report to SSRS on the Microsoft Azure VM directly from inside Visual Studio. We ended the chapter with a high-level discussion of report security, user authentication, and authorization.
There is certainly much more for you to discover on your own with SSRS and RDL. Now that you understand the most important concepts and features, you have the foundation you need to further explore these technologies and advance your cloud reporting skills.