Steve Rezhener, Consultant
Bank of America (thru Axis Technology)
Steve Rezhener
Saturday, October 03, 2015
1





Human, Husband, Father, and SQL Server
advocate 
10+ years experience with SQL Server
MCITP SQL Server 2005/2008 DBA
Chapter Leader of PASS SQL Malibu
Member of Friends of Red Gate 2014
www.linkedin.com/in/steverezhener/
@SteveRezhener
[email protected]
Steve Rezhener
Saturday, October 03, 2015
2
 Why?
 How?
 Summarize
Disclaimer - what is outside of the scope:
 Complicated SSRS development
 Comparison between SSRS and other reporting tools
 Extending SSRS using VB.NET
Steve Rezhener
Saturday, October 03, 2015
3
Introduction
Internals
Benefits
Steve Rezhener
Saturday, October 03, 2015
4

The many questions:
◦ What is SQL Server Reporting
Services aka SSRS?
◦ When do you need to use SSRS?
◦ How do you create SSRS reports?
◦ The ultimate question – why do you
use SSRS?
◦ What can you do with SSRS that you
can’t do with ASP.NET and T-SQL?
Steve Rezhener
Saturday, October 03, 2015
5
Separate IIS server (starting from 2008)
ReportManager web site
SSRS Windows Service
ReportServer database
3 main tables:
 Catalog – RDL files
 Users – users
 ExecutionLogStorage – report execution log
◦ ReportServerTempDB database
◦
◦
◦
◦
◦
Steve Rezhener
Saturday, October 03, 2015
6

SSRS benefits vs. custom development
◦ Included with typical SQL Server licensing
◦ Rapid development using drag & drop
◦ Built-in controls:
 Fully functional ASP.NET web site aka ReportManager
 End-user usability features:




Paging support
Export to PDF, Excel
Print-friendly
Search-box
Steve Rezhener
Saturday, October 03, 2015
7

SSRS benefits vs. custom development
 Scalability and flexibility




Caching
Subscriptions
Extensive security modules
Web site integration using ReportViewer
 Scheduled Subscription engine
 Email
 Fileshare
Steve Rezhener
Saturday, October 03, 2015
8




What does it take to build a report?
Build a simple report
Code transparency report
SSRS on SSRS
Steve Rezhener
Saturday, October 03, 2015
9
Sample report building using Business
Intelligence Development Studio
 What do we need to know:
◦ Tools:
 Visual Studio
 SQL Server Management Studio aka SSMS
◦ Languages:
 T-SQL
 VB.NET (sometimes)
Steve Rezhener
Saturday, October 03, 2015
10
Write a stored procedure
 Create a SSRS report
 Use stored procedure as a dataset
 Auto-populate all the columns using
the Wizard
 Deploy report
 Access report via Report Manager
interface

Steve Rezhener
Saturday, October 03, 2015
11
 What
is code transparency
technique?
 What tools do we use:
◦Stored procedure
◦2nd SSRS datasource
◦Use sp_helptext as a dataset
Steve Rezhener
Saturday, October 03, 2015
12
◦Reports inventory
◦ Daily user usage
◦ Subscription information
Steve Rezhener
Saturday, October 03, 2015
13
◦ Reports inventory
◦Daily user usage
◦ Subscription information
Steve Rezhener
Saturday, October 03, 2015
14
◦ Reports inventory
◦ Daily user usage
◦Subscription information
Steve Rezhener
Saturday, October 03, 2015
15


Introduction to SSRS
Demonstration of SSRS use cases
Steve Rezhener
Saturday, October 03, 2015
16
Descargar

About me