Add a Custom Report to Management Studio

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This topic describes how to create a simple Reporting Services report that is saved as an .rdl file, and then add that rdl file to SQL Server Management Studio as a custom report. SSRS can create a wide variety of sophisticated reports. To create a report by using this topic, you must have SQL Server Data Tools (SSDT) installed on the computer. You do not have to install SSRS on SQL Server to run a custom report using Management Studio.

To create a simple report saved as an .rdl file

1. Click Start, point to Programs, point to Microsoft SQL Server, and then click SQL Server Data Tools.

2. On the File menu, point to New, and then click Project.

3. In the Project Types list, click Business Intelligence Projects.

4. In the Templates list, click Report Server Project Wizard.

5. In Name, type ConnectionsReport, and then click OK.

6. On the Report Wizard introduction page, click Next.

7. On the Select the Data Source page, in the Name box type a name for this connection to your Database Engine, and then click Edit.

8. In the Connection Properties dialog box, in the Server name box, type the name of your instance of the Database Engine.

9. In the Select or enter a database name box, type the name of any database on your SQL Server, such as AdventureWorks2022, and then click OK.

10. On the Select the Data Source page, click Next.

11. On the Design the Query page, in the Query string box, type the following Transact-SQL statement that lists the current connections to your Database Engine, and then click Next. The Report Wizard Query string box will not accept report parameters. More complex custom reports must be created manually.

    SELECT session_id, net_transport FROM sys.dm_exec_connections;

12. On the Select the Report Type page, select Tabular, and then click Finish.

13. On the Completing the Wizard page, in the Report name box, type ConnectionsReport, and then click Finish to create and save the report.

14. Close SQL Server Data Tools.

15. Copy ConnectionsReport.rdl to a folder that you created on the database server for custom reports.

To add a report to Management Studio

• In Management Studio, right-click a node in Object Explorer, point to Reports, click Custom Reports. In the Open File dialog box, locate the custom reports folder and select the ConnectionsReport.rdl file, and then click Open.

  When a new custom report is first opened from an Object Explorer node, the custom report is added to the most recently used list under Custom Reports on the shortcut menu of that node. When a standard report is opened for the first time, it will also appear on the most recently used list under Custom Reports. If a custom report file is deleted, the next time that the item is selected, a prompt will appear to delete the item from the most recently used list.

  1. To change the number of files that are displayed on the recently used list, on the Tools menu, click Options, expand the Environment folder, and then click General.

  2. Adjust the number for Display files in recently used list.

See Also

Custom Reports in Management Studio
Use Custom Reports with Object Explorer Node Properties
Unsuppress Run Custom Report Warnings
SQL Server Reporting Services