Edit

Share via

Add a custom report to SQL Server Management Studio

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

This article describes how to create a basic 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 article, SQL Server Data Tools (SSDT) must be installed on the computer. You don't have to install SSRS on SQL Server to run a custom report using Management Studio.

Create a basic report saved as an .rdl file

  1. Select Start, point to Programs, point to Microsoft SQL Server, and then select SQL Server Data Tools.

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

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

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

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

  6. On the Report Wizard introduction page, select 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 select 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 select OK.

  10. On the Select the Data Source page, select 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 select Next. The Report Wizard Query string box doesn't 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 select Finish.

  13. On the Completing the Wizard page, in the Report name box, type ConnectionsReport, and then select 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.

Add a report to Management Studio

  • In Management Studio, right-click a node in Object Explorer, point to Reports, select Custom Reports. In the Open File dialog box, locate the custom reports folder and select the ConnectionsReport.rdl file, and then select 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 also appears 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, select Options, expand the Environment folder, and then select General.

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

Related content