Introduction

This article is an introduction to creating an SSRS Report from Azure SQL. SQL Server Reporting Services (SSRS) is still a popular tool to generate reports. It is true that PowerBI is the most popular Microsoft reporting tool (yes, Excel is not a reporting tool, let's face it), but SSRS still has some customers and is also used for backward compatibility, licenses, etc. Some customers still want to use SSRS and I still receive requests to generate new reports or update existing ones.

In this article, I will create a report based on data stored in an Azure SQL table named customer. In this article, we will do the following:

1. Firstly, I will configure Azure SQL and create a table with data.
2. Also, we will install the SSRS projects in VS if they were not installed before.
3. Next, we will create a report of the Azure SQL data.
4. Finally, we will edit and modify the format and add some columns to the report.

Requirements

1. First of all, you will need an Azure account.
2. Secondly, install Visual Studio. In this example, I am using VS 2019.

Configure Azure SQL and Create a Table

1. First, in the Azure Portal, create a resource.
   

   Create a resource

2. Secondly, go to SQL Database and select the create option
   

   Create an Azure SQL database

3. Thirdly, select a subscription and a resource group create a new server if you do not have any, and enter a name for the database used. 
4. In addition, when the Azure SQL Server is created, you will have the server admin which is the login name. In Show firewall settings, you may need to add your IP to           Configure the Azure Server
5. Also, we will use the query editor in the Azure SQL Database to create a table. You will need to log in to the query editor.
   

   Use the Azure query editor

6. We will run a query to create a table named dbo.customer.
   

   Query to create a table with data

7. The following table will be used.

   The following table will be used.CREATE TABLE [dbo].[Customer](
   [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [NameStyle] int NOT NULL,
   [Title] [nvarchar](8) NULL,
   [FirstName] [nvarchar](40) NOT NULL,
   [MiddleName] [nvarchar](40) NULL,
   [LastName] [nvarchar](40) NOT NULL,
   [Suffix] [nvarchar](10) NULL,
   [CompanyName] [nvarchar](128) NULL,
   [SalesPerson] [nvarchar](256) NULL,
   [EmailAddress] [nvarchar](50) NULL,
   [Phone] [nvarchar](40) NULL,
   [PasswordHash] [varchar](128) NOT NULL,
   [PasswordSalt] [varchar](10) NOT NULL,
   [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
   [ModifiedDate] [datetime] NOT NULL
   )​

8. Finally, we will insert some data in the customer table.

SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (1, 0, N'Mr.', N'Orlando', N'N.', N'Gee', NULL, N'A Bike Store', N'adventure-works\pamela0', N'orlando0@adventure-works.com', N'245-555-0173', N'L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w=', N'1KjXYs4=', N'3f5ae95e-b87d-4aed-95b4-c3797afcb74f', CAST(N'2005-08-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (2, 0, N'Mr.', N'Keith', NULL, N'Harris', NULL, N'Progressive Sports', N'adventure-works\david8', N'keith0@adventure-works.com', N'170-555-0127', N'YPdtRdvqeAhj6wyxEsFdshBDNXxkCXn+CRgbvJItknw=', N'fs1ZGhY=', N'e552f657-a9af-4a7d-a645-c429d6e02491', CAST(N'2006-08-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (3, 0, N'Ms.', N'Donna', N'F.', N'Carreras', NULL, N'Advanced Bike Components', N'adventure-works\jillian0', N'donna0@adventure-works.com', N'279-555-0130', N'LNoK27abGQo48gGue3EBV/UrlYSToV0/s87dCRV7uJk=', N'YTNH5Rw=', N'130774b1-db21-4ef3-98c8-c104bcd6ed6d', CAST(N'2005-09-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (4, 0, N'Ms.', N'Janet', N'M.', N'Gates', NULL, N'Modular Cycle Systems', N'adventure-works\jillian0', N'janet1@adventure-works.com', N'710-555-0173', N'ElzTpSNbUW1Ut+L5cWlfR7MF6nBZia8WpmGaQPjLOJA=', N'nm7D5e4=', N'ff862851-1daa-4044-be7c-3e85583c054d', CAST(N'2006-07-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (5, 0, N'Mr.', N'Lucy', NULL, N'Harrington', NULL, N'Metropolitan Sports Supply', N'adventure-works\shu0', N'lucy0@adventure-works.com', N'828-555-0186', N'KJqV15wsX3PG8TS5GSddp6LFFVdd3CoRftZM/tP0+R4=', N'cNFKU4w=', N'83905bdc-6f5e-4f71-b162-c98da069f38a', CAST(N'2006-09-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (6, 0, N'Ms.', N'Rosmarie', N'J.', N'Carroll', NULL, N'Aerobic Exercise Company', N'adventure-works\linda3', N'rosmarie0@adventure-works.com', N'244-555-0112', N'OKT0scizCdIzymHHOtyJKQiC/fCILSooSZ8dQ2Y34VM=', N'ihWf50M=', N'1a92df88-bfa2-467d-bd54-fcb9e647fdd7', CAST(N'2007-09-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (7, 0, N'Mr.', N'Dominic', N'P.', N'Gash', NULL, N'Associated Bikes', N'adventure-works\shu0', N'dominic0@adventure-works.com', N'192-555-0173', N'ZccoP/jZGQm+Xpzc7RKwDhS11YFNybwcPVRYTSNcnSg=', N'sPoUBSQ=', N'03e9273e-b193-448e-9823-fe0c44aeed78', CAST(N'2006-07-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (10, 0, N'Ms.', N'Kathleen', N'M.', N'Garza', NULL, N'Rural Cycle Emporium', N'adventure-works\josé1', N'kathleen0@adventure-works.com', N'150-555-0127', N'Qa3aMCxNbVLGrc0b99KsbQqiVgwYDfHcsK9GZSUxcTM=', N'Ls05W3g=', N'cdb6698d-2ff1-4fba-8f22-60ad1d11dabd', CAST(N'2006-09-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (11, 0, N'Ms.', N'Katherine', NULL, N'Harding', NULL, N'Sharp Bikes', N'adventure-works\josé1', N'katherine0@adventure-works.com', N'926-555-0159', N'uRlorVzDGNJIX9I+ehTlRK+liT4UKRgWhApJgUMC2d4=', N'jpHKbqE=', N'750f3495-59c4-48a0-80e1-e37ec60e77d9', CAST(N'2005-08-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (12, 0, N'Mr.', N'Johnny', N'A.', N'Caprio', N'Jr.', N'Bikes and Motorbikes', N'adventure-works\garrett1', N'johnny0@adventure-works.com', N'112-555-0191', N'jtF9jBoFYeJTaET7x+eJDkd7BzMz15Wo9odbGPBaIak=', N'wVLnvHo=', N'947bcaf1-1f32-44f3-b9c3-0011f95fbe54', CAST(N'2006-08-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (16, 0, N'Mr.', N'Christopher', N'R.', N'Beck', N'Jr.', N'Bulk Discount Store', N'adventure-works\jae0', N'christopher1@adventure-works.com', N'1 (11) 500 555-0132', N'sKt9daCzEEKWAzivEGPOp8tmaM1R3I+aJfcBjzJRFLo=', N'8KfYx/4=', N'c9381589-d31c-4efe-8978-8d3449eb1f0f', CAST(N'2006-09-01T00:00:00.000' AS DateTime))
INSERT [dbo].[Customer] ([CustomerID], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate]) VALUES (18, 0, N'Mr.', N'David', N'J.', N'Liu', NULL, N'Catalog Store', N'adventure-works\michael9', N'david20@adventure-works.com', N'440-555-0132', N'61zeTkO+eI5g8GG0swny8Wp/6GzZMFnT71fnW4lTHNY=', N'c7Ttvv0=', N'c04d6b4d-94c6-4c5c-a44c-b449c0ac1b45', CAST(N'2005-08-01T00:00:00.000' AS DateTime))

Install the SSRS Projects in VS

Once that VS is installed, you will need to add an extension to support SSRS reports projects. In the past, you only need to install the SSDT and select the reporting services option. In the last SSRS versions, you need to go to the VS Menu and select Extensions>Manage Extensions.

Manage extension

Secondly, select Microsoft Reporting Services Project and install it. You may need to close VS to install it.

Install SSRS

Create an SSRS Report from Azure SQL

Once you install the extension, go to a new project and select the Report Server Project Wizard. This option will help you to generate the report.

Create a project in Reporting Services

Secondly, write a Project name and select a location.

Create a name for the project

Thirdly, you will have the Welcome Wizard.

Start the reporting wizard

Write a name for the data source and press the Edit button to configure the connection.

Add a data source

For the simplicity of the article, we will use SQL Server Authentication, which is the simplest option. Check in the Azure Portal, the server name, the administrator name, and the password.

Create a connection in the SSRS Report from Azure SQL

The wizard may ask you to write the user name and Password again.

Add the Azure SQL credentials

Also, press the Query Builder to generate the query to Azure SQL using the UI or write the query in the Query string directly.

Use the query builder

We created a table named customer in the portal. If you have a different table in Azure, you can use it instead of this one. You also have filters to generate the T-SQL WHERE statements to filter data.

Include the columns in the query

In addition, check if the query string generated by the query builder is OK and press Next.

The query generated by the query builder

You will be asked to have a Tabular or a Matrix report. In this case, we will select Tabular.

Matrix or tablix

It is possible to group fields or add them to pages. Press Next.

Design option in SSRS

Finally, you will have the Completing Windows Wizard. Press Finnish.

Summary of SSRS config

Edit and Modify the Format

The Preview allows us to see the report.

SSRS Preview option

You will need to enter the Login and password. Then press View Report.

Enter Azure SQL login

Now, you can see a report with the Azure SQL data.

View the report used

In the properties, you can change the color, font of the title report.

Choose a color

You can also change the column header properties.

Properties of SSRS

In the Datasets, you can add more columns if required by right-clicking the Dataset and selecting Dataset Properties.

Modify DS properties

Add the company name to the report by modifying the query.

Add company name

If everything is OK, you will see the name column added.

Verify the column added

Also, you can add a column in the Table of the report by selecting the column and Inserting the column to the right.

SSRS add column in tablix

Finally, drag and drop the company name.

Add data to the column

Conclusion

In this article, we learned how to create an Azure SQL database, configure it, and then we installed SSRS to generate reports in Visual Studio. Also, we created a report in SSRS, and finally, we learned how to modify some properties.