Transcript SSRS Tutorial – Part 1

SQL Server
Delivering business agility and innovation to gain strategic value out of your information
MISSION CRITICAL PLATFORM
DEVELOPER & IT PRODUCTIVITY
PERVASIVE INSIGHT
MICROSOFT SQL SERVER
REPORTING SERVICES
What is REPORT ?
Individuals and Organizations wants :
way of displaying data in standardized way that facilitates decision making.
Be able to access those reports from variety of applications, locations and devices.
A report is any informational work made with the specific intention of relaying information
in a widely presentable form.
Reports use features such as graphics, images, voice, or specialized vocabulary in order to persuade
that specific audience to undertake an action. One of the most common formats for presenting reports
is IMRAD : Introduction, Methods, Results and Discussion
What is SSRS?
SQL Server Reporting Services (SSRS) is a server-based report generation software system
from Microsoft. Administered via a web interface, it can be used to prepare and deliver a
variety of interactive and printed reports.
SQL Server Reporting Services is Microsoft's SERVER-CENTRIC reporting plateform.
SSRS competes with Crystal Reports and other business intelligence tools.
Reporting Services was first released in 2004 as an add-on to SQL Server 2000. The second
version was released as a part of SQL Server 2005 in November 2005. A third version was
released as part of SQL Server 2008 R2 in April 2010. The latest version was released as part
of SQL Server 2012 in March 2012.
Why Reporting Services?
•
•
•
•
•
•
Distributed processing - Portal
Uses Active Directory
Easy to install and administer
Integrated report development
Uses standard Internet technology
– (HTTP & XML)
Works with Oracle and more
The Real Reason
•
•
•
•
•
•
Needed Reporting Tool Now!
Least expensive solution (No budget for Reporting Tool)
Already owned software SQL Server
Works with Oracle and many more…
Installed RS ourselves
Very Good Product Help available
Reporting Services
Agility
Cost
Security
Performance &
Administration
Pricing –
MS Campus Agreement
Enterprise Edition
•Much less than Web
Focus or Oracle
Standard Edition
3x less than
SQL Enterprise Edition
Already Owned
Enterprise Edition
+
No Annual Maintenance
= $ 0.0
SQL Server License
•
•
•
•
•
•
•
RDBMS;
OLAP/Data warehouse;
Management Tools;
Integrated Security;
Reporting Services;
Email Notification Services;
More…
• License
includes all
Features.
• Features are
not priced
separately
Requirements
• Microsoft Windows
– 2000, 2003, & XP
• SQL Server
• IIS (Web Server)
Reporting Services is
Clusterable and
Scalable (Web Farm)
– ASP.NET
Reporting Services is an ASP.NET application that
uses SQL Server for its data storage
REPORTING LIFE CYCLE
Report
Authoring
Report
Delivery
Report
Management
REPORTING LIFECYCLE
Phase
Description
Authoring
Creation of the report definition, via an
authoring tool, containing:
• Connection
• Query
• Layout
The published report definition is saved
on a report server, and managed
with Report Manager by the report
server administrator.
The generated report is viewed via an
application, or is routed to a delivery
target where it is accessed by
consumers.
Management
Access and Delivery
•Authoring: Report developers can create reports to be published to
the Report Server using Microsoft or third-party design tools that use
Report Definition Language (RDL), an XML-based industry standard
used to define reports
•Management. Report definitions, folders, and resources are
published and managed as a Web service. Managed reports can be
executed either on demand or on a specified schedule, and are
cached for consistency and performance
•Delivery. Supports both on-demand (pull) and event-based (push)
delivery of reports. Users can view reports in a Web-based format
or in e-mail
REPORTING SERVICES FEATURES
•
•
Part of the SQL Server 2005 Platform
Report Development
•
•
•
Visual report design
Business Intelligence Development Studio
Report Features
•
•
•
•
•
•
Grouping
Sorting
Filtering
Drill-Down and Drill-Through
Charting
XML-based Report Files (.rdl)
REPORTING SERVICES FEATURES



Report Types
◦
◦
◦
Table
Matrix
Charts
Report output:
◦
◦
◦
Report Viewer (web site)
Page-based (HTML, TIFF, PDF)
Application integration (Web / Windows Forms)
Export Formats:
◦
◦
Adobe PDF, XML, Microsoft Excel, CSV, TSV
CSV
REPORTING SERVICES FEATURES
 Management
◦
◦
Web-based interface
Command-line management tools
 Report Builder
◦
Data models for creating ad-hoc reports
 Programmability / Integration:
 Application Programming Interface (API)
 Web Services / Simple Object Access Protocol (SOAP)
 Command-line utilities
REPORT SECURITY
• Report Authentication & Authorization via by Active Directory
• Can Utilize Web Server & other security technology
• SSL, IPSec, Firewall, etc
REPORT STYLES
Report Layout
Description
Tabular
Fixed number of columns
Matrix
The number of columns depends
on the query results
Chart
Data represented graphically (pie,
line or bar chart)
List (free-form)
Data arranged free-form on the
page; useful for creating forms
INTERACTIVE REPORTS
Feature
Description
Parameterized
Parameterized reports filter data to provide only that
which is appropriate for the user
Hidden or drilldown
Hidden items on a report can toggle in and out of view
based on user actions
Hyperlinks
Hyperlinks embedded in reports direct users to Web
pages
Drill-through links Drill-through links open another report from within a
report, such as a detailed report from a summary. The
originating report typically passes parameters to the
drill-through report
Bookmark links
Bookmark links direct users to another area of the
current report
Document maps
In HTML Viewer, a document map appears as a table of
contents next to the report. Users can click an item in
the table, and the browser jumps to that item in the
report
Report Viewer
SharePoint
SSMS
Report Viewer
Web Part
Web Service Proxy
Data Sources
Web Services & URL Access
Security Services
Report Server
Report Engine
Output Formats
Data Processing
Security
Delivery Targets
(E-mail, SharePoint, Custom)
Rendering
Custom
Visualization
Scheduling & Delivery
Custom Report Item
RDCE
Customized RDL
SQL Server Catalog
RDCE : Report Definition Customization Extension for Multi-Language Reports
REPORTING SERVICES ARCHITECTURE
• From SQL Server Books Online
SQL Sever Reporting and Alerting
Empower users
• Power View
• Highly visual design experience
• Rich metadata-driven
interactivity
• Presentation-ready
at all times
Increase Proactive Intelligence
• End User Alerting
• Defined from within
operational or ad-hoc reports
• Intuitive Alert rules
• Alerts self-managed through
SharePoint
• XLS/Word 2007/2010
Increase efficiency
• Enabled as SharePoint Shared
Service
• Built-in scale-out for RS Service
Apps
• SharePoint Cross-farm
reporting
• Integrated backup & recovery,
ULS logging, PowerShell etc.
• BIDS integrated with Dev10 shell
Managed Self Service BI
–
Corporate BI
Alerting Key Capabilities
Scenario
Operational Reports
Stored Credentials
SharePoint Mode
SharePoint Permissions
Tools
Alert Designer
Alert Manager
Alert Rules
Simple rules
Static thresholds
Alert Messages
Description
Alert Rules
Alert Parameters
Alert Schedules
Daily, Weekly, Hourly, Minute
Start/End Date
Production Capabilities
Execution Log
Globalized Alert Rules
Localization ready
REPORT CREATION PROCESS
Determine
Requirements
Create
Report Item
Create
Dataset(s)
Design /
Preview
Report
Deploy
Report
USING THE REPORT WIZARD
• Report Wizard Goals:
•
•
•
•
Provides a quick way to create basic reports
Defines a data connection and query
Includes formatting and grouping options
Creates a new RDL file
• Launching the Report Wizard:
• New Project  Report Server Project Wizard
• Add Item  Report Wizard
REPORT WIZARD STEPS
Define
Data
Source
Design
Query
Choose
Report
Type
Define
Report
Layout
Choose
Report
Formatting
UNDERSTANDING DATA SOURCES
• Specifies connection information for reporting data
• Supported Data Sources:
• Any OLEDB / ODBC-compliant data source
• Relational
•
•
•
SQL Server
Oracle
MS Access
• OLAP / Multi-Dimensional
•
SQL Server Analysis Services
• XML, Excel, CSV, TSV, etc.
CREATING DATA SOURCES
• Data Source Details
• Data source type
• Connection options
• Security credentials
• Private Data Sources (Report-specific)
• Stored within the report (.RDL) file
• Shared Data Sources
• Defined at the Project / Server level
• Can be used across multiple reports
• Useful for development/production environments
DATASET DETAILS
• Identifies data to be used for report generation
• Can have many different datasets per report
• Requires a data source (shared or embedded)
• Fields are available for use in reports
• Dataset Options
•
•
•
•
•
Query (Text or Stored Procedure)
Fields
Data Options
Parameters
Filters
QUERY DESIGNER
•
Query Designer Features
•
•
•
•
•
•
Visual creation of joins
Can access tables, views, and functions
Column names and aliases
Query sorting and filtering options
Query results
Screen sections
•
•
•
•
Diagram Pane
Grid Pane
SQL Pane
Result Pane
QUERY DESIGNER EXAMPLE
REPORT LAYOUT
• Report
• Page Header
• Page Footer
• Body (Report Area)
• Table Regions
• Header
• Detail
• Footer
• Groups
• Can specify page breaks
REPORT ITEMS (TOOLBOX)
Data Output
• Table
• Matrix
• List
•
•
•
•
Layout /
Formatting
Chart
SubReports
Textbox
Line
Rectangle
Image
• Data
visualization
• Drillthrough
• Complex
Reports
• Dashboards
REPORT LAYOUT: DEMONSTRATION
• Report Requirements:
• Show a list of all products by Category / Subcategory
• Drill-down, sorting, and grouping are not required
• Report Components:
•
•
•
•
Page Header
Report Title
Page Number
Report Data (Table)
INTERACTIVE SORTING
• Query Sorting
• Useful for setting a “default” sort order
• Use an ORDER BY clause in the dataset query
• Table-Level Sorting
• Default sort order specified in the “Sorting” tab
• Interactive Sorting
•
•
•
•
Data is sorted during report generation
Sorted values are used for report output
Can use a field or complex sort expression
May be dependent on grouping scope
GROUPING AND DRILL-DOWN
• Grouping
• Helps to logically organize data
• Can create sub-totals in group footer
• Drill-Down
• Group visibility can be dynamically-controlled by other columns/values
• Report exports are based on the current view
GROUPING EXAMPLE
India
Country
UP
State
Sales Details
Sales
MP
Sales
HR
Sales
UNDERSTANDING EXPRESSIONS
•
•
•
Statements used to specify values
Can be used in table cells
Expression Editor
•
•
•
Supports Intellisense
Uses Visual Basic-style syntax
Examples:
•
•
•
•
Globals!ReportName
Globals!PageNumber
Sum(Fields!SalesTotal.Value, “Sales")
CountDistinct(Fields!ProductCategory)
• Fields!Employee.LastName + “,” + Fields!Employee.FirstName +
EXPRESSION OPTIONS
Constants
• Based on
context
Globals
• Report
Name
• Page
information
• Execution
Time
Parameters
• From
report
settings
Fields
• From
datasets
EXPRESSION OPTIONS (CONT’D.)
Datasets
• Dataset column
values
• Single Values:
May include
“First” or “Sum”
Operators
• Arithmetic
• Comparisons
• String functions
Common Functions
• Aggregates
• Financial
• Type
Conversions
• Text
• Date/Time
• Math
• Program Flow
(IIF, Choose,
Switch)
FILTERING OPTIONS
• Dataset / Query Level
• Uses parameter variables to restrict data returned
• Can also use stored procedure variables
• Report Parameters
• Determined at report run-time
• Useful when users will be frequently changing settings
• Object Filtering
• Filter options for tables, charts, etc.
DATASET FILTERING
• Can improve performance by minimizing data returned
• Best used when filtering details are known before report generation
• Implemented using query parameters
• Variables: @StartDate, @EndDate
Query:
SELECT * FROM Sales
WHERE TransactionDate
BETWEEN @StartDate AND @EndDate
REPORTING PARAMETERS
• Evaluated at report run-time
• Report Parameter Options:
• Data Types
• Prompt Options
•
Allow blank / null; Multi-value
• Available Values
•
Non-Queried or From Query
• Default values:
•
Non-Queried or From Query
• Cascading Parameters
REPORTING SERVICES COMPONENTS
• SQL Server Reporting Services Service
• Report Manager Web Site
• Business Intelligence Development Studio
• Databases:
• ReportServer:
•
Report definitions, security settings, etc.
• ReportServerTempDB:
•
Cached data and user session information
• Components may be installed on different servers
REPORT EXECUTION PROCESS
Data is retrieved from
source DB
Data is stored in
ReportServerTempDB
Report is Executed
REPORT EXECUTION OPTIONS
• Always run this report with the most recent data
• Enable caching
•
•
Expired based on number of minutes
Expired based on a schedule
• Render report from a snapshot
• Report Execution timeouts
• System Default
• Specified number of seconds
• None
UNDERSTANDING REPORT CACHING
• Cache is created when a report is first run
• Stores a copy of data in ReportServerTempDB
• Can reduce impact on production performance
• Data may be out-of-date
• Expires after a pre-defined amount of time
• Data source security settings must be configured
UNDERSTANDING SCHEDULES
 Events are executed by SQL Server Agent service
 Schedule Types
◦
◦
Report-Specific Schedules
Shared Schedules
 Defined at the system level
 Tips:
◦
◦
◦
Keep track of time zones
Use shared schedules whenever possible to allow centralized management
Distribute reporting processing workload over time
UNDERSTANDING SNAPSHOTS
• Point-in-time view of the contents of a report
• Data never changes
• Report parameters must be defined before running the snapshot
• Usually created on a schedule
• End-of-month or end-of-year reports
• Scheduling
• Report-specific schedule
• Shared schedule
REPORT HISTORY
• Used to maintain snapshot copies over time
• Often used for auditing or historical reference
• Scheduling:
• Store all snapshots
• Use a report-specific schedule
• Use a shared schedule
• Options:
• Keep an unlimited number of snapshots
• Limit the number of copies of report history
REPORT DELIVERY OPTIONS
• E-Mail
• Uses SMTP server defined in Reporting Services Configuration tool
• Can send report as attachment
• Can send a link to the report
• File Share
• Stores the output of a report to a file share
• Requires a shared folder accessible via UNC
•
Example: \\ReportServer\MarketingReports
REPORT DELIVERY OPTIONS
• Output file types
•
•
•
•
•
•
•
XML
Comma-separated values (CSV) – text file
TIFF image files
Web Archive
Adobe Acrobat (PDF)
Microsoft Excel (XLS)
File Share Only
•
•
Web Page (HTML)
Web Archive
SUBSCRIPTION TYPES
• Snapshot-Based Subscriptions
• Notification is sent whenever a snapshot is created
• Schedule-Based Subscriptions
• Uses a custom schedule (e.g., daily, monthly, etc.)
• Can have start and stop dates
• Data-Driven Subscriptions
• Report recipients are defined by a query
• Table and query must be created manually
• Useful when managing large or very dynamic lists of recipients
REPORTING SERVICES SECURITY
• Hierarchical Security Model
• Folders can be used for logical organization
• Items inherit permissions
• Security Layers
• System-Level Role Definitions
• Site-wide Security
• Item-Level Role Definitions
MANAGING SECURITY
• Role-Based system
• Roles are sets of permissions/capabilities
• Users can be assigned to multiple roles
• Based on Windows Authentication
• Provides for centralized security management
• May use Active Directory users and groups
• Other authentication can be developed
LINKED REPORTS
• Creates a “virtual report”
• Uses the same report definition (.rdl) as the parent report, but with
independent settings
• Purpose / Benefits
• Can setup different sets of permissions
• Can setup different sets of parameters
REPORTING CONTROLS
• Windows Forms Applications
• Reporting Services Control
• Pointed to Reporting Services web site
• Web Applications
• Can point directly to the Reporting Services Web Site
• Creating customized security for accessing reports by automating the API
• Other Options:
• SharePoint Integration
• Using the Reporting Services API
LAB SESSIONS
• Demonstration of basic reporting:
•
•
•
•
•
Create BI Project
Add Datasource
Add Dataset
Design Report
Deploy Report
LAB SESSIONS - 1
 Configuring SSRS – Reporting Services Configuration Manager
Steps –
Start -> All Programmes -> MS SQL Server 2008/2012 -> Configuration Tools->Reporting Services Configuration Manager
For more details click here
LAB SESSIONS - 2
 Sample Report – Demo 1
* Creating Report
* Configuring Parameters
* Filtering Reports
 Sample Report – Demo 2
* Creating Report
* Applying Grouping
* Grouping Totals
* Configuring Parameters
* Filtering Reports
 Sample Report – Demo 3
* Creating Report
* Applying Grouping
* Configuring Parameters
* Filtering Reports
* Implementing Indicators
 Sample Report – Demo 4
* Creating Report
* Applying Row Numbering
 Sample Report – Demo 5
* Creating Report
* Applying Row Numbering within group.
Q&A
THANK YOU !
Virendra Yaduvanshi
http://wikidba.wordpress.com/