Custom Reports in Management Studio

  • Article

One of the improvement areas we've introduced in Service Pack 2 is the ability to run your own reports inside Management Studio. Here is a draft of the Books Online topics. This is subject to change at any time.

The November CTP of Service Pack 2 can be obtained here: https://www.microsoft.com/sql/ctp.mspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability

Custom Reports in Management Studio

Introduction

Many Object Explorer nodes in SQL Server Management Studio display a set of standard reports created by Microsoft which summarize typically requested server information. Starting with SQL Server 2005 service pack 2, SQL Server Managemetn Studio lets administrators run custom reports created in SQL Server Business Intelligence Development Studio from SQL Server Management Studio.

Implementation

Custom reports stored as report definition (.rdl) files are created by using Report Definition Language (RDL). RDL contains data retrieval and layout information for a report in an XML format. RDL is an open schema; developers can extend RDL with additional attributes and elements. Reports may execute any valid tsql statement within the report.

If Object Explorer is connected to a server, custom reports can execute in the context of the current Object Explorer selection if they reference that node's report parameters. This enables a report to use the current context, such as the current database, or a consistent context, such as specifying a designated database as part of the tsql statement that is contained in the custom report.

Running a Custom Report

A custom report may be run in two ways in SQL Server Management Studio:

  • Right-click a node in Object Explorer, point to Reports and left-click Custom Reports. Using the Open File dialog box, browse to a folder that contains .rdl files and open the appropriate report file.
  • Right-click a node in Object Explorer, point to Reports, point to Custom Reports and select a custom report from the most recently used file list.

Limitations

  • To prevent the unintended execution of malicious code, SQL Server Management Studio cannot be configured to automatically run a report, even if the file system is configured to associate rdl files with SQL Server Management Studio. Reports cannot be programmatically executed in SQL Server Management Studio, and cannot run from the command line through SQL Server Management Studio.
  • You can run custom reports in a context that does not produce the expected values. For example, you can run a report regarding replication in the context of a database which is not involved in replication, or run a report as a user who does not have permission to access information that is required to generate an accurate report. The creator of the custom report is responsible for the validity of the report structure and its context.
  • You cannot add a custom report to the list of standard reports.
  • The code processed by the report may affect server performance.
  • Custom reports will not support subreports.
  • The command text for each query within the report must not be defined through an expression
  • Any query parameter used in a command (query) can only reference a single report parameter and cannot use any expression operators.
  • Only Text and Stored Procedure command types are supported for report commands (queries)
  • The report framework does not provide any parameter escaping for the queries. The query author needs to ensure that the queries are free from SQL injection attacks.

Managing Custom Reports

We recommend that users who have many custom reports organize them using file system folders.

Permissions

Custom reports run using the permissions of the current user. Permissions on the file system folder that contains the report files should be set to restrict access to prevent a malicious user from changing the queries run by the report.

Both the user and the account that is used by the SQL Serverservice require read access to the file system folder that contains the report files.

Any valid .NET command can be embedded in a report but it will not be executed.

Warning Any valid T-SQLstatement can be embedded in and executed from a report. Running a report under a high privileged user account makes it possible that any of these embedded instructions can execute without challenge.

How to: Add a Custom Report to Management Studio

Introduction

This topic describes how to create a simple SQL Server Reporting Services report saved as an .rdl file, and then add that rdl file to SQL Server Management Studio as a custom report. This topic focuses on adding the report to Management Studio. SQL Server Reporting Services can create a wide variety of sophisticated reports. For more information about creating reports, see Reporting Services Tutorials. To create a report using this topic, you must have SQL Server Business Intelligence Development Studio installed on your computer. You do not need to install SQL Server Reporting Services to run a custom report using Management Studio.

Procedures

To create a simple report saved as an rdl file
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Business Intelligence Development Studio.
  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 SQL Server 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 SQL Server Database Engine.
  9. In the Select or enter a database name box, type the name of any database on your SQL Server, such as AdventureWorks, 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 tsql statement that lists the current connections to your SQL Server Database Engine, and then click Next.

    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 Business Intelligence Development Studio.
  15. Copy ConnectionsReport.rdl to a folder you created on you database server for custom reports.

To add a report to Management Studio
  1. In Management Studio, right-click a node in Object Explorer, point to Reports, click Custom Reports. In the Open File dialog box, navigate to your custom reports folder and select the ConnectionsReport.rdl file and click Open.
  2. When a new custom report is first opened from an Object Explorer node it is added to the most recently used list under Custom Reports on the context menu of that node. A standard report will also appear on the most recently used list under Custom Reports when it is opened for the first time. If a custom report file is deleted, the next time the item is selected a prompt will appear to delete the item from the most recently used list. To change the number of files displayed on the recently used list click Options on the Tools menu, expand the Environment folder, and then click General. Adjust the number for Display files in recently used list.