Introduction

This time, we will teach how to create reports using SQL Server Reporting Services (SSRS) connecting to an Azure SQL Data Warehouse (ASDW).

Requirements

1. An Azure Subscription.
2. SSRS (included in the SQL Server 2016 Installer) and SSDT installed 
3. The ASDW installed (see the our previous chapter to install it)

Get Started

This section will have 2 parts:

1. How to add a simple ASDW report
2. How to add filters to an ASDW report

How to Add a Simple ASDW Report

We will need to create a rule to enable the local IP in Azure. To do that, in the Azure Portal, click the > icon and select SQL servers:

Select the SQL Server with the ASDW created (sqlservercentral1) and then select Firewall. Add the client IP and press Save:

Select the SQL server and click propeties. Copy and paste the server name. This SERVER NAME will be used in the SSRS report:

You can get the ASDW name, click the database icon and check the ASDW name. This database name will be used in the connection from SSRS:

Create a Local SSRS Report

Go to your local machine. Open SSDT and in go to File>New Project. Select the Report Server Project Wizard: 

A Welcome Wizard will be displayed:

Press the Edit button to edit the connection properties:

The Server Name will be: sqlcentralserver1.database.windows.net (we get this information from the Azure Portal). The Database will be sqlservercental. Write the name. Do not use the combobox (we get this information from the Azure Portal). The User name and Password are the ones that we used when we created the Azure SQL Server:

You will have information similar to this one:

You will be asked to enter the Data Source Credential again:

Let's try to use the Query builder:

When we press the icon to add a table from ASDW, we receive the unknown property isAccessible message. This is a well-known issue:

You will need to write your query manually as text and you can execute with the ! icon to verify that the query is OK:

The query string will look as follows:

In Report Type, we will use a Tabular report:

In Design the table, leave the default values and press the finish button:

Specify a name and press finish:

Press the design Tab:

You will need to specify the Azure SQL Server login and password specified when you created the Azure SQL Server (check the requirements if you are lost here) and press View Report:

The SSRS report will show the following data:

How to Add Filters to an ASDW Rreport

We will add filters to the previous report. We will filter the reports per Department Name that we can choose dynamically. In SSRS, click the Report Data pane, right click Datasets and select Add Dataset:

Select the Use a dataset embedded in my report to get the connection to ASDW. In Data Source, select the DataSource1 created for the simple report:

In the Report Data pane, right click on Parameters and select Add Parameter:

Specify a name for the parameter. The Prompt can be a description of the field. The data type will be text:

We will get values from a query and we will specify the DepartmentNameDS Data set. This dataset contains all the Department names. The value field to be displayed is the DepartmentName. In this case, the Value field is the same than the level. This is different if there is an ID and a name:

Right click on DataSet1 and select Dataset Properties. This contains the data of the first report created:

In the query, add a WHERE clause as shown in this image:

@DepartmentName is a parameter that contains all the Department names.

Press the Preview Tab to view the report. Specify the login name and password and click the View Report button:

In DepartmentName, you can select a department name. Select one of them:

The report displayed will be similar to this one:

Conclussion

As you can see, it is possible to connect to ASDW using SSRS. However, it is necessary to create a Firewall rule in the Azure Portal to enable the local machine to access to ASDW.

References

• Azure SQL Data Warehouse
• Reporting Services (SSRS)
• Reporting Services Tutorials (SSRS)