Transcript TITLE 44 POINT VERDANA ALL CAPS

Introduction to Building Reports
Reporting Services
Dimitri Ayrapetov
November 9, 2013
© Copyright 2013 EMC Corporation. All rights reserved.
1
http://www.flickr.com/photos/boliyou/2884130773/
Dimitri Ayrapetov
 SharePoint Solutions Architect
@ EMC Professional Services
 Working with SharePoint since 2005
 Working with SQL Server since 2004
© Copyright 2013 EMC Corporation. All rights reserved.
3
Agenda
 What is SQL Server Reporting Services (SSRS)?
 Brief History
 SSRS Components
 Installation Overview
 Integration with SharePoint
 Security & Permissions
 Demo – Basic Report
 Demo – Report Enhancements
 Additional Resources
 Q&A
© Copyright 2013 EMC Corporation. All rights reserved.
4
What is SQL Server Reporting Services?
 A core component
of the SQL Server
family
 Used to present
data in a
meaningful way
 Consists of a set of
tools and services
© Copyright 2013 EMC Corporation. All rights reserved.
Database
Engine
Master Data
Services
Integration
Services
Reporting
Services
Data Quality
Services
Analysis
Services
5
Brief History
Version
Highlights
SSRS 2000
Initial release shipped in 2004 as a free extension to SQL Server
2000
SSRS 2005
SharePoint 2007 Integration, Report Builder 1.0, End-user Sorting,
Multi-valued Parameters, Drag & Drop Report Designing, Updated
Expressions Editor, Web Service Data Connections
SSRS 2008
SharePoint 2010 Integration, Tablix, Charts, Gauges, Rich Text,
Report Builder 2.0, Performance Improvements
SSRS 2008 R2
Largest jump in functionality: SharePoint List Data Source, Shared
Datasets, Support for Azure, Map Chart Type, Indicators, Pagination,
Countless Improvements in Building Reports
SSRS 2012
SharePoint 2013 Integration, Power View, Data Alerts, Mobile
Platforms
© Copyright 2013 EMC Corporation. All rights reserved.
6
SSRS Components
Report Designers
Report Processor
Report Definition
Presentation
© Copyright 2013 EMC Corporation. All rights reserved.
7
Report Designers
 SQL Server Data Tools
– The only Report Development Tool that can access the full suite of
available functionality within SSRS.
– Previously known as “Business Intelligence Studio”.
– Lives within a shell of Visual Studio 2010.
 Report Builder 3.0
– Basic Editor geared towards enabling Power Users with the ability to build
basic reports with pre-established boundaries.
– Maintains UI similar to Office 2007.
 Power View
– New feature with SQL Server 2012 to provide an interactive data
exploration, visualization, and presentation experience.
– Great for building ad-hoc reports on the fly.
© Copyright 2013 EMC Corporation. All rights reserved.
8
Report Definition
MyReport.RDL
Data
Connections
Datasets
Parameters
© Copyright 2013 EMC Corporation. All rights reserved.
Layout
Images
9
Report Definition
Data Connections
Microsoft SQL
Server
Microsoft SQL
Server Analysis
Services
Microsoft
SharePoint List
Windows Azure
SQL Database
Microsoft SQL
Server Parallel
Data
Warehouse
OLE DB
Oracle
SAP NetWeaver
BI
Hyperion
Essbase
Teradata
XML
ODBC
© Copyright 2013 EMC Corporation. All rights reserved.
10
Presentation Layer
 SharePoint Mode
– Report Definitions are published to a SharePoint Library
– Permissions to access the report definition are based on SharePoint
permissions
▪ Note: Permissions to access the data behind the report is often
different than permissions to access the report definition itself
– Reports are rendered within SharePoint
 Native Mode
– Installs a basic ASP.net Application to an IIS Site to handle navigation,
permissions, report repository, etc.
 Note: Upon installing SSRS from your SQL Server Media, you will be prompted
to use Native Mode or SharePoint Mode. Once you make a selection, you
cannot change it without reinstalling SSRS. However, reports can be migrated
from one instance of SSRS to another regardless of deployment mode.
© Copyright 2013 EMC Corporation. All rights reserved.
11
Report Processor
© Copyright 2013 EMC Corporation. All rights reserved.
12
SSRS 2012 Installation (SharePoint
Integrated Mode)
 App Server Installation
– Run the SQL Server 2012 installation and only install the
“Reporting Services – SharePoint” feature
 Web Front End Installation
– Install the Reporting Service add-in for SharePoint
– The add-in can be found on the SQL installation media or
downloaded from Microsoft
 Configuration
– Navigate to Central Admin > Services on Server
– Start the “SQL Server Reporting Services Service” on the
appropriate App Servers
– Create a “SQL Server Reporting Services Service
Application” and associated with your Web App(s)
© Copyright 2013 EMC Corporation. All rights reserved.
13
Integration with SharePoint
Prior to SQL Server 2012
© Copyright 2013 EMC Corporation. All rights reserved.
14
Integration with SharePoint
SQL Server 2012
© Copyright 2013 EMC Corporation. All rights reserved.
15
Security & Permissions
 There are 2 layers of security with SSRS and
SharePoint:
– The 1st layer is within the SharePoint library.
Unless you have appropriate permissions, you
will not be able to access the report, or even
know that it exists.
– The 2nd layer can be set within your report’s
connection to your data source, which can
restrict the data that the user can see.
© Copyright 2013 EMC Corporation. All rights reserved.
16
Security & Permissions
Credential Type
Account Context
Integrated Security
Impersonate the current user
Windows Credentials
Impersonate the domain login of a specified
user (username & password required)
Database Credentials
Database user
None
Anonymous
Prompt
Prompted username & password
© Copyright 2013 EMC Corporation. All rights reserved.
17
Demo
© Copyright 2013 EMC Corporation. All rights reserved.
18
Additional Resources
 SSRS Team Blog
– http://blogs.msdn.com/b/sqlrsteamblog/
 TechNet: SSRS 2012
– http://technet.microsoft.com/enus/library/ms170438.aspx
 TechNet: SSRS Tutorials
– http://technet.microsoft.com/enus/library/bb522859.aspx
 MSDN: SSRS
– http://msdn.microsoft.com/en-us/data/ff660783.aspx
© Copyright 2013 EMC Corporation. All rights reserved.
19
Wrap Up
 Thank you for attending!
Q & A
 This deck and project files can be found on
my new blog: http://dimitri.spgurus.com
 Follow me on my new Twitter: @dayrapetov
 Connect on LinkedIn:
linkedin.com/in/dimitriayrapetov/
 Email me: Dimitri.Ayrapetov at EMC.com
© Copyright 2013 EMC Corporation. All rights reserved.
20