SSRS Tutorial – Part 1 - WordPress.com

joeneetscompetitiveΑσφάλεια

3 Νοε 2013 (πριν από 3 χρόνια και 7 μήνες)

108 εμφανίσεις

MISSION CRITICAL PLATFORM

DEVELOPER & IT PRODUCTIVITY

PERVASIVE INSIGHT

Delivering business agility and innovation to gain strategic value out of your information

SQL Server

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

Security

Cost

Agility

Performance &

Administration

Pricing



MS Campus Agreement

Enterprise Edition


Much less than Web
Focus or Oracle

No Annual Maintenance

Already Owned

Enterprise Edition

= $ 0.0

+

Standard Edition


3x less than

SQL Enterprise Edition

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)


ASP.NET


Reporting Services is an ASP.NET application that
uses SQL Server for its data storage

Reporting Services is

Clusterable and

Scalable (Web Farm)

Report
Authoring

Report
Management

Report
Delivery

REPORTING LIFE CYCLE

REPORTING LIFECYCLE

Phase




Description

Authoring




Creation of the report definition, via an
authoring tool, containing:


Connection


Query


Layout

Management




The published report definition is saved
on a report server, and managed
with Report Manager by the report
server administrator.

Access and Delivery




The generated report is viewed via an
application, or is routed to a delivery
target where it is accessed by
consumers.


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 drill
-
down

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 Server

SQL Server Catalog

Report
Engine

Scheduling & Delivery

Rendering

Data Processing

Security

Delivery Targets

(E
-
mail, SharePoint, Custom)

Security
Services

Output Formats

Data
Sources

RDCE

Customized RDL

Custom Report Item

Custom

Visualization

SSMS

Report Viewer

Web
Service

Proxy

Report Viewer

Web Part

SharePoint


Web Services & URL Access

RDCE : Report
Definition Customization Extension for Multi
-
Language Reports


From SQL Server Books Online

REPORTING SERVICES ARCHITECTURE

SQL Sever Reporting and Alerting


Enabled as SharePoint Shared
Service


Built
-
in scale
-
out for RS Service
Apps


SharePoint Cross
-
farm
reporting


Integrated backup & recovery,
ULS logging, PowerShell etc.


End User Alerting


Defined from within
operational or ad
-
hoc reports


Intuitive Alert rules


Alerts self
-
managed through
SharePoint


XLS/Word
2007/2010


BIDS integrated with Dev10 shell


Power View


Highly visual design experience


Rich metadata
-
driven
interactivity


Presentation
-
ready

at all times


Increase efficiency

Increase Proactive Intelligence

Managed Self Service BI



Corporate BI

Empower users

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



Determine
Requirements

Create
Report Item

Create
Dataset(s)

Design /
Preview
Report

Deploy
Report

REPORT CREATION PROCESS

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 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

QUERY DESIGNER EXAMPLE

REPORT LAYOUT


Report


Page Header


Page Footer


Body (Report Area)


Table Regions


Header


Detail


Footer


Groups


Can specify page breaks

Data Output


Table


Matrix


List

Layout /
Formatting


Textbox


Line


Rectangle


Image

Chart


Data
visualization

SubReports


Drill
-
through


Complex
Reports


Dashboards

REPORT ITEMS (TOOLBOX)


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)

REPORT LAYOUT: DEMONSTRATION


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


INTERACTIVE SORTING

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

Sales Details

State

Country

India

UP

Sales

Sales

MP

HR

Sales

GROUPING EXAMPLE


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 +


UNDERSTANDING EXPRESSIONS

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


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 PARAMETERS


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


REPORTING SERVICES COMPONENTS

REPORT EXECUTION PROCESS

Data is retrieved from
source DB

Data is stored in
ReportServerTempDB

Report is Executed


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



REPORT EXECUTION OPTIONS


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 REPORT CACHING


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 SCHEDULES


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


UNDERSTANDING SNAPSHOTS


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 HISTORY


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



REPORT DELIVERY OPTIONS


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



SUBSCRIPTION TYPES


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

REPORTING SERVICES 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

MANAGING SECURITY


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


LINKED REPORTS


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


REPORTING CONTROLS

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/