Learn SQL Reporting on Windows Azure (9-Step Tutorial)

4 out of 4 rated this helpful - 

 

Learn how to create and deploy SQL reports on Windows Azure using Report Builder andAdventure Works for Azure sample data. Each step is clearly marked so that you can skip ahead to the skills you want to learn.

When you are finished with this tutorial, you will have a drill-down report showing Adventure Works annual sales by territory.

Before you start

Requirements for this tutorial include having a Windows Azure subscription, a report authoring tool, and sample data. You can use an existing subscription, a new subscription, or the free trial subscription. For more information, see .

A subscription to Windows Azure provides SQL Database and SQL Reporting, both of which are used in this tutorial. This tutorial includes steps for provisioning both services. Skip ahead if you already have this covered.

Report Builder 3.0 is used to create the reports that you’ll upload to SQL Reporting. As an alternative, you can use Report Designer in SQL Server Data Tools, but the steps will vary from those provided in this tutorial.

Adventure Works for Azure SQL Database. The sample database is optional, but you’ll need it if you want to follow all of the steps in this tutorial. Remember that SQL Reporting can only retrieve data from SQL Database, so any sample data you use for reporting must be hosted on SQL Database in Windows Azure.

Step 1: Set up SQL Database on Windows Azure

You can create just a server on SQL Database without creating a database at the same time. While you must have an existing server before you can install the database, you don’t need a database just yet.

Here are a few steps for setting up just the server.

1. Connect to the management portal at . If you are not signed in, you will be asked to enter credentials that are valid for your subscription.
2. In the management portal, click SQL Databases in the navigation sidebar to display the SQL Databases page.
3. Click Servers at the top of the page. 
4. Click Add at the bottom of the page to create a new service.
   
5. In SQL database server settings, provide a login name and password for the administrator account. Remember the administrator name and password; you’ll need to specify both when running the command that installs the Adventure Works database in Step 2. 
6. Choose a region that can be used for both SQL Database and SQL Reporting. Report performance improves significantly when both data and reports are in the same data center.
7. Be sure to check Allow Windows Azure Service to access the server. Without this option, SQL Reporting will be unable to retrieve data from SQL Database.
8. In the SQL Databases page, on the Server tab, click the server you just created and then click Configure at the top of the page to allow a connection from your local computer to the server. 
   The portal automatically creates a rule allowing an inbound connection you’re your local machine. Click the right arrow to accept the predefined rule, and then click Save to apply this change on your server. For more information, see .
   
   You will need to wait several minutes for this change to take effect. 
   Note

   Skipping this step or specifying an insufficient range will result in various errors when running the database installation script. If you get errors when running the database installation script in , scan the command line output for “Cannot open server <GUID> requested by the login. Client with IP address <IPaddress> is not allowed to access the server.” This error tells you that the IP range does not include the IP of the current device. You can resolve this error by expanding the range to include the IP address mentioned in the error. If you already did that, the solution might be to wait a little longer for the firewall rule to be processed in the data center.

    

    

Step 2: Download and install Adventure Works database on Windows Azure

Adventure Works for Azure is a SQL Server relational database that has been modified to work on Windows Azure. It includes a clustered index, as required by SQL Database.

In this step, you will download and extract the database files on your local computer, and then run a script that installs the database on SQL Database. There are no hardware or software requirements to consider. You can use any Windows workstation to perform this step.

1. Go to  to download the data and installation files. 
2. Right-click the .zip file to extract its contents.
3. Start a command prompt window using the Run as administrator option. For instructions, see  or Windows 8 Command Prompt –Run as Administrator (Youtube video).
4. Navigate to the \AdventureWorks subfolder that has the .cmd file you’ll use to install the database. 
5. Enter the following command, substituting actual values for the placeholder server name, administrator name, and password.
    

    

   CreateAdventureWorksForSQLAzure.cmd 
                
                 .database.windows.net 
                  
                  
                 
                
   If necessary, you can get the server name and administrator name from the portal. In the portal, go to the Servers page in SQL Databases. Click a server name to open the server page. In the server page, click Dashboard and then copy the Manage URL. The full address is the server name, but you should omit the ‘https://’ prefix when pasting the server name on the command line.
   You can paste this information directly onto the command line. For more information, see.
6. Press Enter to run the command. It will take several minutes to complete. At the end, you should see an Installation Succeeded message.
   Errors will occur at this step if the firewall configuration does not allow connections from the IP address of your router. If you get errors, go back to firewall configuration in .
7. You can verify database installation using the management portal. On, in the SQL Database page, you can click Databases at the top of the page to view existing databases on any server associated with your subscription.
   

Step 3: Set up SQL Reporting on Windows Azure

1. Connect to the management portal at 
2. Click New at the bottom of the page.
3. Click Data Services, click SQL Reporting, and then click Quick Create to specify the minimum information necessary to provision a service. This includes a service name, region, administrator, and password.
   
4. In Service Name, enter a name that will help you identify and manage the service in the portal. This name is used only in portal pages. You cannot use it as a Web service URL. Windows Azure will assign a service GUID that uniquely identifies your service instance in the cluster.
5. In the Region list, choose the same region chosen for SQL Database. Locating both services in the same regional data center will significantly reduce the amount of time needed to retrieve data. You can create only one report server in each of the available regions for each subscription.
6. Enter a user name and password for the administrator account.
7. Click the check mark next to Create SQL Reporting Service to create the service.

Step 4: Create a data source on SQL Reporting

In SQL Reporting terminology, a data source refers to reusable connection information that is defined once, managed centrally, and used in multiple reports. In this step, you will create a data source on SQL Reporting, and then use it in a future report.

1. In the management portal, on SQL Reporting, click the service you just created.
2. Click Items at the top of the page. This page lists reports, data sources, and folders that are available on the current service.
3. Click Add at the bottom of the page, and then click Create Data Source.
4. Type AW4Azure in the Data Source Name box. Optionally, type a description in Data Source Description.
5. Choose the SQL database to use in this data source. The database must be hosted on a SQL Database instance created under the same subscription used for SQL Reporting. You cannot choose a SQL Database from a different subscription.
   Because you installed the AdventureWorks for Azure database, you can select that database for your SQL Reporting data source.
   
6. On the second page, specify a credential option. Choose Provide Credentials to be stored securely in the report server, and then type a user name and password that has permission to access AdventureWorks on SQL Database. In this tutorial, the user name and password is the administrator login you provided in . 
7. Click the checkmark to save your changes.

Step 5: Download and install Report Builder

In this step, you install Report Builder 3.0 used to create reports that run on SQL Reporting. Go to and the run the Setup program.

Alternatively, you can use Report Designer in SQL Server Data Tools to create a report, but the steps will vary from those provided in the tutorial. See .

Step 6: Create a report

Now that you have access to sample data, services, and an authoring tool, you are ready to build a report using sales data in AdventureWorks. Report Builder requires a reporting service to perform data retrieval and rendering. One of the first things you’ll do is specify a SQL Reporting instance as the report server engine for this report.

1. Start Report Builder. On the Start menu, click All Programs, and then click Microsoft SQL Server 2012 Report Builder.
2. Close the Getting Started page so that you can specify the reporting service to use as the default server. Click Connect at the bottom of the page.
   
3. In the list box in the Connect to Report Server dialog box, click the Web service URL for the report server that contains the data source you want to use. Because you just configured a SQL Reporting instance in a previous step, you should see the Web service in the list of available servers.
   
   Note

   Starting the tutorial at its midpoint, or using a different computer to build a report, means that you might not see a reporting service in the list. In this case, use the management portal to get the web service URL of the reporting service. You can copy the URL from the SQL Reporting Dashboard page and then paste the URL into the text box.

    

    
   Click Connect once you have specified the server.
4. Enter a user name and password to connect to SQL Reporting. This is not the Windows Azure subscription account. Rather, this an account that you define in SQL Reporting and subsequently use to access reports and other items managed by SQL Reporting. Provide the user name and password that you specified in .
   You should now see a connected message similar to the following screenshot.
   
5. In Report Builder, in the Report Data pane, right-click Data Sources and select Add Data Source. Name the data source AW4Azure, select Use a shared connection or report model, and then select the shared data source connection that is already provided. This is the data source that you created on SQL Reporting in .
   
6. In Click to add title, type a report name, such as Annual Sales by Territory.
7. Right-click Datasets, click Add a dataset, and then provide a name, such asTerritorySales.
8. Click Use a dataset embedded in my report.
9. In Data Source, click AW4Azure.
10. Copy the following SQL query and paste it into the query window.
     

     

    SELECTsoh.[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 sohJOIN [Sales].[SalesTerritory] AS terrON terr.[TerritoryID] = soh.[TerritoryID]ORDER BY 'Year'
    Your screen should look similar to the following screenshot.
    
11. Click OK to create the dataset. Datasets should now include TerritorySales, with columns for order ID, year, territory, country, and total sales.
12. On the Insert menu, click Matrix and then Matrix Wizard.
13. Select the TerritorySales dataset and click Next.
14. In table layout, drag TotalSales to the Values area, drag Year to Columns, drag Countryto Rows, and then drag TerritoryName beneath Country.
    Your wizard should look similar to the following:
    
15. Finish the wizard by selecting default values on the remaining pages.
16. The report layout appears on the design surface. Adjust the columns so that there is more room for each column.
    Multi-select all of the cells containing SUM(Total Amount) and then click a Currency Symbol in the Number area to apply a currency format.
    
17. On the Home page, click Run to execute the report. Wait a minute for the report to load. Your report should look similar to the following screenshot.
    
18. Click Design to return to the workspace.
19. Click Save. Because SQL Reporting is the default server, you will be asked to save the report on Windows Azure. Give the file a descriptive name, such as AW-TerritorySales.
    

Step 7: View a report for Windows Azure

Return to the Windows Azure management portal to view the report you just uploaded from Report Builder. On the Items page, you should see the AW-TerritorySales report.

Rendering from the Items page is not currently possible, so to view the report you will need to use the Dashboard page.

1. Click SQL Reporting, click the reporting service, and then click Dashboard. 
2. Scroll down to click the Web service URL.
3. Enter a SQL Reporting user name and password, using the credentials you provided in .
4. A report server HTML page appears. Click the report AW-TerritorySales to view the report.
   
   The report opens in the same browser window. You can search the report for specific values or render it in other formats.

Step 8: Secure the report

Although you as a reporting service administrator can view the report, other people won’t be able to view the report until you set permissions that grant them access.

1. In the management portal, click SQL Reporting, and then open the reporting service that hosts the AW-TerritorySales report.
2. Click Users at the top of the page.
3. Click Create at the bottom of the page.
4. Enter a user name, password, and role. 
   The credentials you provide do not have to correspond to existing account information. The accounts you specify on the Users page are used only to access items managed by the reporting service.
   Browser is the default role. It conveys read permissions on items. For more information about other roles, see .

Role assignments are specified at the root node of the reporting folder hierarchy. Any folders, reports, or data sources that you publish to a reporting service inherit the permissions defined at the root node. You can ease or restrict permissions on individual items within that hierarchy by breaking permission inheritance and specifying new role assignments on individual items.

Step 9: Monitor report usage

You can track report execution in the Dashboard page on the management portal. You can also analyze the execution logs if you want additional insights, including who runs the report, which reports are the most active, and whether errors or warnings are being generated. For more information, see .

Each report view counts as a billable event. Understanding which events are billed will help you manage costs as the number of reports and report users increase over time. For more information, see .

Next Steps

Now that you know the steps for creating a report and saving to Windows Azure, your next step is to ensure that everyone who needs access to this service can do so.

Administrators, when configuring data access, consider the following:

• All users who want to save or use reports on SQL Reporting must have a SQL Reporting account and role assignment. The Browser role is sufficient for view only access, but report authors will need Publisher or Contributor access. This is the activity you performed in Step 9.
• Report authors who build reports that retrieve data from SQL Database will need Firewall rules created on their behalf to allow connections from local machines to SQL Database. For details, see .
• Report authors will also need a SQL Database login and permissions to access a database. As a SQL Reporting administrator, you can also create a shared data source as an alternative to allow connections using stored credentials. For more information, see.

 

Developers might want to explore other ways of using reports in new or existing applications. Reports on Windows Azure can be integrated into applications using the ReportViewer control. See the following links for more information:  and .

 

See Also

Tasks

 

Reference

 

Other Resources