Using the Report Viewer Control with SQL Azure

  • Article

[This article was contributed by the SQL Azure team.]

Currently Microsoft doesn’t provide a PaaS version of SQL Server Reporting Service, where you can run your server-side (.rdl) reports in the cloud. However, if you want to run reports using Microsoft reporting technology you can use Windows Azure to host your client report definition (.rdlc) in a ASP.NET web page. This techique uses the ReportViewer control (that ships with ASP.NET) to execute, generate and display the reports from Windows Azure. This allows you to have your reports in the cloud (Windows Azure) along with your data (SQL Azure). This article will cover the basics for creating a client report definition that will work with SQL Azure using Visual Studio.

If you wanted to run the ASP.NET page with the ReportViewer control pointed at SQL Azure from your local on-premise IIS server you could do that too with this technique.

Here are the steps that need to get done:

  • Create a new data connection to SQL Azure using the ODBC data source.
  • Create a report client definition (.rdlc) style report using the data source.
  • Embed a ReportViewer control into the ASP.NET page that displays the .rdlc.

However, Visual Studio report wizard works in reverse order:

  1. Create an ASP.NET page to host the report.
  2. Embed a ReportViewer control into the ASP.NET page.
  3. Which prompts you for a report, since you don’t have a report yet, you choose New Report.
  4. Which prompts you for a data source, since you don’t have a data source yet you are prompted to create one.

Let’s go into the steps in detail.

Creating an ASP.NET Page

1) Open Visual Studio and create a new project, Choose Windows Azure Cloud Service

clip_image001

2) Choose the ASP.NET Web Role

clip_image002

3) That creates a default.aspx page. Navigate to default.aspx page and put the page into design mode.

4) Open the toolbox and drag over the MicrosoftReportViewer control found under Reporting section onto the ASP.NET page.

clip_image004

5) Once the ReportViewer control is embedded in the page, a quick menu will protrude from the embedded control. Choose Design a new report.

6) Once you client the Design a new report option, the Report Wizard opens and you are asked to choose a data source.

clip_image006

7) Click on the New Connection Button.

Creating a Data Connection

Currently, the ReportViewer control only allows you to create two types of data connection, using either an OLEDB or ODBC data source. While it might be tempting to try to use the SQL Server Native data source, this will not work. The SQL Server Native data source uses OLEDB and currently there isn’t an OLEDB provider for SQL Azure. Instead, you need to use the OBDC data source.

Now that we have the Add Connection dialog up, here is how to proceed to create an ODBC connection to SQL Azure:

1) Click on Change to change the data source.

clip_image007

2) This will bring up the Change Data Source dialog.

clip_image008

3) Choose the Microsoft ODBC Data Source from the Data Source dialog. Click OK.

4) This will return you to the Add Connection dialog, with the appropriate options for an ODBC data source.

clip_image009

5) Choose Use connection string for the Data source specification.

6) This connection string we are going to get from the SQL Azure Portal. Login to the portal, naviagte to the database that you want to connect to, select that database and press the connection string button at the bottom.

clip_image011

7) This will bring up the connection string dialog with the specifc information filled in for server, database and administrative login. You want to copy the ODBC connection string, by clicking on the copy to clipboard button.

clip_image013

8) Paste this into the Add Connection dialog under Use connection string.

clip_image014

9) Notice where the Pwd attribute is set to myPassword. It is very important to change this to your password for the administrator account. The one that matches the user account in the connection string. If you don’t change it now, the wizard will keep asking you for the password.

10) Click the Test Connection button to test the connection, then press OK.

11) This takes us back to the Data Source Configuration Wizard dialog with our new connection highlighted. Click on Next.

12) Choose to save the connection string, which is saved in the web.config file.

clip_image016

13) Choose the data objects, i.e. tables, from SQL Azure that you want to use in the report. In my screen shots I am using the AdventureWorks database.

clip_image018

14) Press Finish.

Now that we have the data source configured, we have to select it and create a report.

Designing a Report

The report we are going to design creates a client report definition file (.rdlc), that gets embedded in the project files that get deployed to Windows Azure. When the page is requested the ReportViewer control loads the .rdlc file, executes the transact-sql, which calls SQL Azure, and returns the data. When the data is returned the ReportViewer control (running on Windows Azure) formats and displays the report.

1) In the Report Wizard dialog. Choose a data source and press Next.

clip_image019

2) Walk through the dialog to design a report, you can read more about how to do this here.

When you are done with the Report Wizard dialog you will have the report embedded in the ReportViewer, inside the ASP.NET page. Next thing to do is deploy it to Windows Azure.

Deploy to Windows Azure

Before you can deploy to Windows Azure, you need to make sure that the Windows Azure package contains all the assemblies for the ReportViewer control that are not part of the .NET framework:

  • Microsoft.ReportViewer.Common.dll
  • Microsoft.ReportViewer.WebForms.dll
  • Microsoft.ReportViewer.ProcessingObjectModel.dll
  • Microsoft.RevportViewer.DataVisualization.dll

These four assemblies are available only if you have Visual Studio installed or if you install the free redistributable package ReportViewer.exe. In order to get them in your Windows Azure package you need to:

  1. Mark the Microsoft.ReportViewer.dll which is already added as a reference in your project, as Copy Local (true).
  2. Add the Microsoft.ReportViewer.Common.dll to the assembly references, by doing an add reference. Mark it Copy Local to true also.
  3. Find the Microsoft.ReportViewer.ProcessingObjectModel.dll and Mircosoft.RevportViewer.DataVisualization.dll in your GAC, copy them into your My Documents folder and then reference the copies as assemblies. Mark them Copy Local to true also.

You also need to make sure all the reports that you generated are tagged in your project as content, instead of embedded resources.

There is a good video about using the ReportViewer control with Windows Azure found here.

Summary

Have questions, concerns, comments? Post them below and we will try to address them.