Transcript Customizing SSMS reports - UK SQL Server User Group

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
Your chosen report will be rendered in the summary pane
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)
Right clicking a node in SP1
Right clicking a node in SP2
Right clicking a node in SP2 that has standard reports
Right clicking a node in SP2 that has custom reports
Running a custom report
Running a custom report
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
Report
http:/sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/customssms-reports-in-sp2-database-permissions.aspx
 Jasper Smith – Taskpad Report
http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/customssms-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 2005
http://sqlblogcasts.com/blogs/antxxxx/archive/2006/11/15/1310.aspx
Resources
 SQL Server Manageability Team Blog Custom Reports in Management Studio
http://blogs.msdn.com/sqlrem/archive/2006/11/20/customreports-in-management-studio.aspx
http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMSReports-1.aspx
http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMSReports-2.aspx
http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMSReports-3.aspx