230 likes | 419 Views
Customizing SQL Server Management Studio Reports. Martin Bell SQL Server MVP. Where are these reports currently? . Open up the object browser (F8) and click on a node Show the summary window (F7) The report list drop down will be enabled if there are reports for this node
E N D
Customizing SQL Server Management Studio Reports Martin Bell SQL Server MVP
Where are these reports currently? • Open up the object browser (F8) and click on a node • Show the summary window (F7) • The report list drop down will be enabled if there are reports for this node • Reports can be exported to PDF or Excel formats
When the report drop down list is enabled you will see the list of reports available
Reports can be exported to excel or a pdf file by right clicking the report and choosing the format to export
To get to the custom reports in SP2! • Reports in SP2 can be viewed directly from object explorer • Right click a node and choose Reports from the menu • Three possible options: • Standard Report (only shown if available) • Custom Reports • MRU list (only shown if standard or custom report has been run)
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. • 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 SQL Server Database Engine, and then click Edit.
To create a simple report saved as an rdl file (2) • In the Connection Properties dialog box, in the Server name box, type the name of your instance of the SQL Server Database Engine. • 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. • On the Select the Data Source page, click Next. • 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.
To create a simple report saved as an rdl file (3) • 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 Business Intelligence Development Studio. • Copy ConnectionsReport.rdl to a folder you created on you database server for custom reports.
Removing a report from the MRU list • Through the GUI • Delete, rename, or move the .RDL file • Click on the old entry in the SSMS MRU report list. • SSMS will tell you the file could not be found and then ask you if it should be removed. • Change \Documents and Settings\%username%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Reports.xml to remove the entry
Report Parameters • The Reports can take the following parameters: • ObjectName • ObjectType • ServerName • FontName • DatabaseName
Custom Report Format • Reports should be in rdl format (was rdlc in earlier CTPs) • Sub-reports are not supported • A query parameter can only reference one report parameter • Only text and stored procedure command types are supported
Security • SSMS Reports can not be automatically run (through SSMS settings or command line) • Beware SQL Injection • Protect on File System • Reports run under current user’s permissions (may/may not have enough permissions!) • SQL Server service account needs read permission on report folder • .NET commands will not be executed
Acknowledgements • Paul Mestemaker - Microsoft • Jasper Smith - MVP • Simon Sabin - MVP • Aaron Bertrand - MVP • Anthony Brown
Resources • Jasper Smith - Database Permissions Reporthttp:/sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-database-permissions.aspx • Jasper Smith – Taskpad Reporthttp://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx • Simon Sabin – Updated Taskpad Report http://sqlblogcasts.com/blogs/simons/ • Aaron Bertrand – Show Blocking Report http://sqlblog.com/blogs/aaron_bertrand/archive/2006/12/19/448.aspx • Anthony Brown – Custom Reports in SQL Server 2005http://sqlblogcasts.com/blogs/antxxxx/archive/2006/11/15/1310.aspx
Resources • SQL Server Manageability Team Blog - Custom Reports in Management Studiohttp://blogs.msdn.com/sqlrem/archive/2006/11/20/custom-reports-in-management-studio.aspx http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS-Reports-1.aspx http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS-Reports-2.aspx http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS-Reports-3.aspx