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: http://www.microsoft.com/sql/ctp.mspx.
Paul A. Mestemaker II
Microsoft SQL Server Manageability
Custom Reports in Management Studio
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.
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.
- 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.
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 Server service 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-SQL statement 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
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.
To create a simple report saved as an rdl file
To add a report to Management Studio