Customizing SQL Server Management Studio Reports

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\Applicat
  ion 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 users 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 Report
  http/sqlblogcasts.com/blogs/sqldbatips/archive/20
  06/11/21/custom-ssms-reports-in-sp2-database-permi
  ssions.aspx
• Jasper Smith Taskpad Report http//sqlblogcasts.
  com/blogs/sqldbatips/archive/2006/11/21/custom-ssm
  s-reports-in-sp2-enterprise-manager-taskpad-view.a
  spx
• Simon Sabin Updated Taskpad Report
• http//sqlblogcasts.com/blogs/simons/
• Aaron Bertrand Show Blocking Report
• http//sqlblog.com/blogs/aaron_bertrand/archive/2
  006/12/19/448.aspx
• Anthony Brown Custom Reports in SQL Server 2005
  http//sqlblogcasts.com/blogs/antxxxx/archive/2006
  /11/15/1310.aspx

22
Resources

• SQL Server Manageability Team Blog - Custom
  Reports in Management Studio http//blogs.msdn.com
  /sqlrem/archive/2006/11/20/custom-reports-in-manag
  ement-studio.aspx
• http//blogs.msdn.com/sqlrem/archive/2006/05/16/S
  SMS-Reports-1.aspx
• http//blogs.msdn.com/sqlrem/archive/2006/05/16/S
  SMS-Reports-2.aspx
• http//blogs.msdn.com/sqlrem/archive/2006/05/16/S
  SMS-Reports-3.aspx