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:
- Firstly, I will configure Azure SQL and create a table with data.
- Also, we will install the SSRS projects in VS if they were not installed before.
- Next, we will create a report of the Azure SQL data.
- Finally, we will edit and modify the format and add some columns to the report.
Requirements
- First of all, you will need an Azure account.
- Secondly, install Visual Studio. In this example, I am using VS 2019.
Configure Azure SQL and Create a Table
- First, in the Azure Portal, create a resource.
Create a resource
- Secondly, go to SQL Database and select the create option
Create an Azure SQL database
- 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.
- 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
- 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
- We will run a query to create a table named dbo.customer.
Query to create a table with data
- 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 )
- 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.
![Select SSRS](https://www.sqlservercentral.com/wp-content/uploads/2022/01/SSRS-Install-extension.png)
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 SSRS project](https://www.sqlservercentral.com/wp-content/uploads/2022/01/create-project.png)
Secondly, write a Project name and select a location.
![add a name to the SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/azure-project.png)
Thirdly, you will have the Welcome Wizard.
![Welcom wizard to the SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/welcome-report.png)
Write a name for the data source and press the Edit button to configure the connection.
![Select a data source in the SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/selec-data-source.png)
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.
![connect to Azure](https://www.sqlservercentral.com/wp-content/uploads/2022/01/connection-properties.png)
The wizard may ask you to write the user name and Password again.
![Grant credentials](https://www.sqlservercentral.com/wp-content/uploads/2022/01/edit-data-source-credentials.png)
Also, press the Query Builder to generate the query to Azure SQL using the UI or write the query in the Query string directly.
![Create a query](https://www.sqlservercentral.com/wp-content/uploads/2022/01/query-builder.png)
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.
![Add the columns](https://www.sqlservercentral.com/wp-content/uploads/2022/01/query-used-1.png)
In addition, check if the query string generated by the query builder is OK and press Next.
![Query generated](https://www.sqlservercentral.com/wp-content/uploads/2022/01/query-made.png)
You will be asked to have a Tabular or a Matrix report. In this case, we will select Tabular.
![Type of report](https://www.sqlservercentral.com/wp-content/uploads/2022/01/report-type.png)
It is possible to group fields or add them to pages. Press Next.
![Group colums](https://www.sqlservercentral.com/wp-content/uploads/2022/01/design-the-table.png)
Finally, you will have the Completing Windows Wizard. Press Finnish.
![Summary](https://www.sqlservercentral.com/wp-content/uploads/2022/01/report-summary.png)
Edit and Modify the Format
The Preview allows us to see the report.
![Report Preview](https://www.sqlservercentral.com/wp-content/uploads/2022/01/preview-report.png)
You will need to enter the Login and password. Then press View Report.
![Enter login and pwd](https://www.sqlservercentral.com/wp-content/uploads/2022/01/view-report.png)
Now, you can see a report with the Azure SQL data.
![View Report created](https://www.sqlservercentral.com/wp-content/uploads/2022/01/report-created.png)
In the properties, you can change the color, font of the title report.
![Select a color of the SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/color.png)
You can also change the column header properties.
![SSRS properties of the SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/change-background-color.png)
In the Datasets, you can add more columns if required by right-clicking the Dataset and selecting Dataset Properties.
![Change DS properties in SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/dataset-properties.png)
Add the company name to the report by modifying the query.
![Change query of SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/modify-query.png)
If everything is OK, you will see the name column added.
![Verify Company name added in SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/column-added.png)
Also, you can add a column in the Table of the report by selecting the column and Inserting the column to the right.
![Insert column in SSRS Report from Azure SQL](https://www.sqlservercentral.com/wp-content/uploads/2022/01/add-column-right.png)
Finally, drag and drop the company name.
![Drag and drop data](https://www.sqlservercentral.com/wp-content/uploads/2022/01/drag-and-drop.png)
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.