1 / 22

Customizing SQL Server Management Studio Reports

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

haamid
Download Presentation

Customizing SQL Server Management Studio Reports

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Customizing SQL Server Management Studio Reports Martin Bell SQL Server MVP

  2. 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

  3. When the report drop down list is enabled you will see the list of reports available

  4. Your chosen report will be rendered in the summary pane

  5. Reports can be exported to excel or a pdf file by right clicking the report and choosing the format to export

  6. 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)

  7. Right clicking a node in SP1

  8. Right clicking a node in SP2

  9. Right clicking a node in SP2 that has standard reports

  10. Right clicking a node in SP2 that has custom reports

  11. Running a custom report

  12. Running a custom report

  13. 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.

  14. 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.

  15. 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.

  16. 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

  17. Report Parameters • The Reports can take the following parameters: • ObjectName • ObjectType • ServerName • FontName • DatabaseName

  18. 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

  19. 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

  20. Acknowledgements • Paul Mestemaker - Microsoft • Jasper Smith - MVP • Simon Sabin - MVP • Aaron Bertrand - MVP • Anthony Brown

  21. 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

  22. 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

More Related