How to: Add a Custom Report to Management Studio
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. For more information about how to create reports, see the Reporting Services tutorials. To create a report by using this topic, you must have Business Intelligence Development Studio installed on the computer. You do not have to install SSRS on SQL Server to run a custom report using Management Studio.
Report Samples, including the standard reports created by Microsoft, are available for download. These samples may be modified by using Business Intelligence Development Studio.
To create a simple report saved as an .rdl file
Click Start, point to Programs, point to Microsoft SQL Server, and then click Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the Project Types list, click Business Intelligence Projects.
In the Templates list, click Report Server Project Wizard.
In Name, type ConnectionsReport, and then click OK.
On the Report Wizard introduction page, click Next.
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.
In the Connection Properties dialog box, in the Server name box, type the name of your instance of the Database Engine.
In the Select or enter a database name box, type the name of any database on your SQL Server, such as AdventureWorks2008R2, and then click OK.
On the Select the Data Source page, click Next.
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;
On the Select the Report Type page, select Tabular, and then click Finish.
On the Completing the Wizard page, in the Report name box, type ConnectionsReport, and then click Finish to create and save the report.
Close BI Development Studio.
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.
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.
Adjust the number for Display files in recently used list.