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

电玩女神 2021-12-24 11:35 274阅读 0赞

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

4 out of 4 rated this helpful \- [Rate this topic][]

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

[Step 1: Set up SQL Database on Windows Azure][Step 1_ Set up SQL Database on Windows Azure]

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

[Step 3: Set up SQL Reporting on Windows Azure][Step 3_ Set up SQL Reporting on Windows Azure]

[Step 4: Create a data source on SQL Reporting][Step 4_ Create a data source on SQL Reporting]

[Step 5: Download and install Report Builder][Step 5_ Download and install Report Builder]

[Step 6: Create a report][Step 6_ Create a report]

[Step 7: View a report on Windows Azure][Step 7_ View a report on Windows Azure]

[Step 8: Secure the report][Step 8_ Secure the report]

[Step 9: Monitor report usage][Step 9_ Monitor report usage]

[Next Steps][]

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

![Annual sales by territory report][]

## 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 [Windows Azure Platform Offers][].

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 [http://manage.windowsazure.com][http_manage.windowsazure.com]. 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.  
      
    ![SQL Database page in Management Portal][]
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 [How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database)][How to_ Configure the Server-Level Firewall Settings _Windows Azure SQL Database].  
      
    ![Firewall configuration page for SQL Database][]  
      
    You will need to wait several minutes for this change to take effect.  
      
    
    
    <table> 
     <tbody> 
      <tr> 
       <th align="left"><img title="note" src="http://i.msdn.microsoft.com/dynimg/IC101471.gif" alt="note">Note</th> 
      </tr> 
      <tr> 
       <td>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&nbsp;<a href="http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step2" rel="nofollow">Step 2: Download and install Adventure Works database on Windows Azure</a>, scan the command line output for “Cannot open server &lt;GUID&gt; requested by the login. Client with IP address &lt;IPaddress&gt; 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. <p>&nbsp;</p> </td> 
      </tr> 
     </tbody> 
    </table>

## 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 [Adventure Works for Azure SQL Database (on CodePlex)][Adventure Works for Azure SQL Database _on CodePlex] 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 [Start a Command Prompt as an Administrator (Windows 7)][Start a Command Prompt as an Administrator _Windows 7] 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 <servername>.database.windows.net <username> <password>
    
    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[How to Paste into a Command Prompt][].  
      
    
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 [Step 1: Set up SQL Database on Windows Azure][Step 1_ Set up SQL Database on Windows Azure].  
      
    
7.  You can verify database installation using the management portal. On[http://manage.windowsazure.com][http_manage.windowsazure.com], 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.  
      
    ![SQL Database list on Windows Azure][]

## Step 3: Set up SQL Reporting on Windows Azure ##

1.  Connect to the management portal at [http://manage.windowsazure.com][http_manage.windowsazure.com]  
      
    
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.  
      
    ![Quick Create for SQL Reporting][]
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.  
      
    ![Create data source page in SQL Reporting][]
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 [Step 1][Step 1_ Set up SQL Database on Windows Azure].  
      
    
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[Download Report Builder][] 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 [Install SQL Server Data Tools to create reports on Windows Azure][].

## 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.  
      
    ![Connect link in Report Builder][]  
      
    
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.  
      
    ![Connect to Server dialog box][]  
      
    
    
    <table> 
     <tbody> 
      <tr> 
       <th align="left"><img title="note" src="http://i.msdn.microsoft.com/dynimg/IC101471.gif" alt="note">Note</th> 
      </tr> 
      <tr> 
       <td>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. <p>&nbsp;</p> </td> 
      </tr> 
     </tbody> 
    </table>
    
    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 [Step 3: Set up SQL Reporting on Windows Azure][Step 3_ Set up SQL Reporting on Windows Azure].  
      
    You should now see a connected message similar to the following screenshot.  
      
    ![Report Builder is connected to server][]  
      
    
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 [Step 4][Step 4_ Create a data source on SQL Reporting].  
      
    ![Create a data source in Report Builder][]  
      
    
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 as**TerritorySales**.  
      
    
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.  
      
    
    
        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
        JOIN [Sales].[SalesTerritory] AS terr
        ON terr.[TerritoryID] = soh.[TerritoryID]
        ORDER BY 'Year'
    
    Your screen should look similar to the following screenshot.  
      
    ![Dataset with rows, columns, values][Dataset with rows_ columns_ values]  
      
    
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 **Country**to Rows, and then drag **TerritoryName** beneath **Country**.  
      
    Your wizard should look similar to the following:  
      
    ![Matrix wizard showing rows, columns, values][Matrix wizard showing rows_ columns_ values]  
      
    
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.  
      
    ![Apply currency to SUM fields][]  
      
    
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.  
      
    ![Annual sales by territory report][]  
      
    
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**.  
      
    ![Save as dialog showing SQL Reporting URL][]

## 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 [Step 3: Set up SQL Reporting on Windows Azure][Step 3_ Set up SQL Reporting on Windows Azure].  
      
    
4.  A report server HTML page appears. Click the report **AW-TerritorySales** to view the report.  
      
    ![Report server HTML page][]  
      
    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 [Predefined Roles][].

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 [Understand SQL Reporting usage (Windows Azure SQL Reporting)][Understand SQL Reporting usage _Windows Azure SQL Reporting].

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 [Accounts and Billing in Windows Azure SQL Reporting (Windows Azure SQL Reporting)][Accounts and Billing in Windows Azure SQL Reporting _Windows Azure SQL Reporting].

## 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 [How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database)][How to_ Configure the Server-Level Firewall Settings _Windows Azure SQL Database].  
      
    
 *  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[Managing Databases and Logins in Windows Azure SQL Database][].

**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: [Use ReportViewer in a Web Site Hosted in Windows Azure][] and [Deploying an ASP.NET Web Application to a Windows Azure Web Site][].

## See Also ##

#### Tasks ####

[Manage Users (Windows Azure SQL Reporting)][Manage Users _Windows Azure SQL Reporting]

#### Reference ####

[Useful Links for SQL Azure Reporting][]

#### Other Resources ####

[Connect to SQL Reporting (Windows Azure SQL Reporting)][Connect to SQL Reporting _Windows Azure SQL Reporting]  
[Windows Azure SQL Reporting][]

转载于:https://www.cnblogs.com/licheng/p/3272919.html

[Rate this topic]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#feedback
[Step 1_ Set up SQL Database on Windows Azure]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step1
[Step 2_ Download and install Adventure Works database on Windows Azure]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step2
[Step 3_ Set up SQL Reporting on Windows Azure]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step3
[Step 4_ Create a data source on SQL Reporting]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step4
[Step 5_ Download and install Report Builder]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step5
[Step 6_ Create a report]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step6
[Step 7_ View a report on Windows Azure]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step7
[Step 8_ Secure the report]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step8
[Step 9_ Monitor report usage]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step9
[Next Steps]: http://msdn.microsoft.com/library/windowsazure/dn189155.aspx#bkmk_step10
[Annual sales by territory report]: http://i.msdn.microsoft.com/dynimg/IC652771.png
[Windows Azure Platform Offers]: http://www.microsoft.com/windowsazure/
[http_manage.windowsazure.com]: http://manage.windowsazure.com/
[SQL Database page in Management Portal]: http://i.msdn.microsoft.com/dynimg/IC652772.png
[How to_ Configure the Server-Level Firewall Settings _Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx
[Firewall configuration page for SQL Database]: http://i.msdn.microsoft.com/dynimg/IC652773.png
[Adventure Works for Azure SQL Database _on CodePlex]: http://msftdbprodsamples.codeplex.com/releases/view/37304
[Start a Command Prompt as an Administrator _Windows 7]: http://technet.microsoft.com/en-us/library/cc947813%28v=ws.10%29.aspx
[How to Paste into a Command Prompt]: http://technet.microsoft.com/en-us/library/cc722894.aspx
[SQL Database list on Windows Azure]: http://i.msdn.microsoft.com/dynimg/IC652774.png
[Quick Create for SQL Reporting]: http://i.msdn.microsoft.com/dynimg/IC652775.png
[Create data source page in SQL Reporting]: http://i.msdn.microsoft.com/dynimg/IC652776.png
[Download Report Builder]: http://go.microsoft.com/fwlink/?LinkID=280232
[Install SQL Server Data Tools to create reports on Windows Azure]: http://msdn.microsoft.com/en-us/library/windowsazure/jj944948.aspx
[Connect link in Report Builder]: http://i.msdn.microsoft.com/dynimg/IC650637.png
[Connect to Server dialog box]: http://i.msdn.microsoft.com/dynimg/IC650638.png
[Report Builder is connected to server]: http://i.msdn.microsoft.com/dynimg/IC650639.png
[Create a data source in Report Builder]: http://i.msdn.microsoft.com/dynimg/IC650640.png
[Dataset with rows_ columns_ values]: http://i.msdn.microsoft.com/dynimg/IC652777.png
[Matrix wizard showing rows_ columns_ values]: http://i.msdn.microsoft.com/dynimg/IC652778.png
[Apply currency to SUM fields]: http://i.msdn.microsoft.com/dynimg/IC652779.png
[Save as dialog showing SQL Reporting URL]: http://i.msdn.microsoft.com/dynimg/IC652780.png
[Report server HTML page]: http://i.msdn.microsoft.com/dynimg/IC650646.png
[Predefined Roles]: http://go.microsoft.com/fwlink/?LinkId=187491
[Understand SQL Reporting usage _Windows Azure SQL Reporting]: http://msdn.microsoft.com/en-us/library/windowsazure/jj151541.aspx
[Accounts and Billing in Windows Azure SQL Reporting _Windows Azure SQL Reporting]: http://msdn.microsoft.com/en-us/library/windowsazure/jj156044.aspx
[Managing Databases and Logins in Windows Azure SQL Database]: http://msdn.microsoft.com/en-us/library/windowsazure/ee336235.aspx
[Use ReportViewer in a Web Site Hosted in Windows Azure]: http://msdn.microsoft.com/en-us/library/windowsazure/gg430128.aspx
[Deploying an ASP.NET Web Application to a Windows Azure Web Site]: http://go.microsoft.com/fwlink/?LinkId=290924
[Manage Users _Windows Azure SQL Reporting]: http://msdn.microsoft.com/en-us/library/windowsazure/hh403969.aspx
[Useful Links for SQL Azure Reporting]: http://msdn.microsoft.com/en-us/library/windowsazure/fc500c2e-d79b-4dd2-9a53-cc7b49008431#BKMK_Useful_Links
[Connect to SQL Reporting _Windows Azure SQL Reporting]: http://msdn.microsoft.com/en-us/library/windowsazure/jj692562.aspx
[Windows Azure SQL Reporting]: http://msdn.microsoft.com/en-us/library/windowsazure/gg430130.aspx