Transcript Introduction to Microsoft’s SQL Reporting Services in a

Introduction to Microsoft’s
SQL Reporting Services in a
Data Repository Environment
By Acmeware, Inc.
Introduction




Acmeware
Participants
Course Objectives
Course Format
Course Summary





Overview of SQL Server Reporting
Services (RS)
RS Licensing & Implementation
RS Development Environment
RS Management & Deployment
Two Examples of RS Reports from
Authoring to Deployment
What is SQL Server Reporting
Services?




A comprehensive, server-based
reporting application
Enables the creation, management, and
delivery of reports
Both traditional paper-based reports and
web-based reports
Pushed or pulled reports, real-time or
scheduled reports
In Microsoft's Words

“Microsoft SQL Server Reporting
Services improves the productivity of
organizations by providing a highperformance, managed reporting
environment for the entire enterprise
and makes it easier to get the right
information to the right people, in
virtually any business environment.”
Comparable Applications





Crystal Reports / Crystal Enterprise
Microsoft Access
Visual Basic / C# and .ASP
Cognos / Actuate / Business Objects
Medisolv RAPID, FCG CyberView
RS Required Software





SQL Server 2000 SP3 (which includes RS add-on)
- Database
Windows 2000 SP4, XP, 2003 (Prof., Standard or
Enterprise) – O.S.
Internet Information Server (5.0) - Web Report
Server with O.S.
Visual Studio .NET 2003 – Report Development
Environment (IDE)
Web Browser (Internet Explorer and others)
Typical MEDITECH DR with RS
Internet Information
Server
IIS & DR may or
may not be on
the same server
Visual Studio .NET 2003
Data Repository
RS Licensing & Implementation





RS is included as a free add-on as long as you have a valid
SQL Server 2000 License
Report Server (IIS) is licensed as part of the O.S. (RS
Report Manager component runs on the Report Server)
Visual Studio .NET 2003 (Report Designer) must be
licensed for each report developer
Only a single instance of RS can run on a Server (even if
the server had multiple instances of SQL Server – the same
as Analysis Services)
The same pricing model is expected for Yukon (SQL 2005)
How to get RS




RS was released from beta earlier this spring
A RS Implementation disc can be ordered from
Microsoft as long as you have a valid SQL Server
2000 License (and pay $5.75 shipping)
Microsoft Subscription clients may download RS
from web
A 180 Day trial version of RS may be downloaded
from web for free (you must have the supporting
software)
RS Process Components
Report Authoring - Visual Studio .NET 2003
Report Management - IIS
Report Server
Report Storage - SQL Server 2000 Databases
Report Viewing – Browser,
E-mail, File share
Visual Studio .NET 2003
Implementation





Visual Studio is installed on client(s) where RS
authoring is going to occur
Installation requires significant prerequisites
(e.g., Front Page Server Extensions, IIS,
MDAC, .NET Framework)
Installation can take hours to completed
Installation can require over 1 GB of disc
The full VS.NET product is not required, a
single component will do (i.e., VBasic)
Visual Studio .NET IDE
Programmers will
be familiar with
this environment,
report developers
may not be
IDE is significantly
more complex
than Access or
Crystal
RS Implementation – IIS
Report Manager


RS Report Manager is an ASP .NET Application
Virtual Directories are set up on IIS





Report Server is at http://<server>/ReportServer
Report Manager is at http://<server>/Reports
Actual reports can be accessed directly at:
http://<server>/<folder name>/<report name>
RS has role-based security for Management and
Items (works with NT authentication)
Configure resources (time-out defaults, caching,
size of MRL, etc.)
Starting RS Report Manager
By default, RS Manager can
be accessed in Start Menu
RS Manager can be accessed
directly from Address Bar of Browser
RS – Report Manager Settings
What a user can
do to/with his
her reports in
Report Manager
Report time-out
(This is NOT
NPR RS!)
Security
Scheduling
Reports
RS – Implementation Report
Server (database components)





RS databases are created when installing RS disc (or
download and installing) from Microsoft Web
The database components of RS must run on an
instance of SQL Server 2000 (MSDE database is not
sufficient)
This likely will also be the DR Server, but need not be
(though another SQL license would be required)
Two SQL database are installed (‘Report Server’ and
‘ReportServerTempDB’)
An Additional demo database may also be included
SQL Server – Database
Components

ReportServer – Static Configuration Data
(meta data)





Data sources
Users, policies, roles
Subscriptions
Report snapshots
ReportServerTempDB


Session data
Cached reports
RS Databases on SQL Server
New RS Databases (Typically
located on DR Server System
Adventure Works is demonstration database
for RS (books on-line can be downloaded too)
RS Metadata
RS Temporary Data
RS Implementation Walk Through
RS Install – Setup Files
Check for prerequisites – Make sure you
have your OS disc(s) available. Will
likely have to download updates as well.
RS Install - Prerequisites
You will see this when all
prerequisites are loaded
RS Install – Start Setup
Make sure to order the correct edition. I did not and it will
not load (but doesn’t tell you so until install is complete!
RS Install - Registration
GOLF
RS Install - Features
By default, you get
everything but Adventure
Works demonstration
database. I recommended
installing everything
Don’t be fooled, you cannot
design reports with RS alone
RS Install – Service Account
Account used to initiated
NT Service. These are
the default values
GOLF
RS Install – IIS Directory
I did not choose to
install SSL. You should
if this is planned for
anything but
development testing
RS Install – SQL Databases
Likely this will be
the DR Server
GOLF
I selected default
configuration
RS Install – Email Delivery Info
Acme_server.com
This would be your
Exchange Server
These values are stored in a file
called “RSReportServer.config” that
can be opened form the Report
Designer (i.e., Visual Studio .NET)
When USING Office As Mailserver

Modify RSReportServer.config in <Configuration> AS
<RSEmailDPConfiguration>
<SMTPServer>smtp.rcn.com</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName>RCN</SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>[email protected]</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
</RSEmailDPConfiguration>
RS Install - Licensing
This should be the same as
your SQL DR Server Licensing
if on the DR system
RS – Report Designer




Use Visual Studio .NET to create a Solution.
A Solution can contain multiple Projects, which
loosely correspond to a Report
Projects contain .RDL files (Report Definition
Language) and .RDS (Report Data Source)
Solutions or Projects are Deployed to Web-Server
RS Project Definition






Use either RS wizard or perform manually
Define a new or use an existing Data Source
(.RDS)
Design the Query (IDE looks like SQL View)
Grouping and/or data summarization
Look and style of output
Add Parameters
RS Project Creation
From Solutions Explorer or from
Startup (depending on
configuration
Data Source Properties
Data Source Access
from Solutions Explorer
List will depend on MDAC
Many are likely familiar with
this sequence of dialog boxes
Credentials Entered at Run-time
Report Services will prompt for
credentials at the time a report
is executed. Reports requiring
credentials at run time cannot
be entered as subscriptions
RS Query Designer
Note 3 main tabs in IDE
This looks and acts a lot
like creating / editing
SQL Views
SQL Code gets stored in
XML-based .RDL file. Note
that more sophisticated T-SQL
Code can be entered than can
be parsed by this editor.
RS Grouping and
Summarization





Can group in T-SQL code or in Designer (which
allows drill-down access to detail)
In Designer, you can use wizard to group, layout
manually, or a combination
Grouping is established by selected field(s) a new
page / header can be forced with each group
Fields can be summarized (e.g., count, sum,
average) by field(s) in a group
Groups can be “rolled up” by default and a
mouse-click used to drill down to detail
RS Wizard - Grouping
This example groups and generates
a new page for each visit (listing
Account # and Name at the header
Within a page, data is
grouped be procedure
charge category
Detail charge Amount and Procedure
Descriptions are displayed
RS Wizard – Subtotals and
Drilldown
This subtotals by by the groupings
Within a group, detail can be rolled
up or displayed using a mouse click
on the (+) or (-) symbol
Grouping & Summarizing Output
Summarization
Layout is
familiar to those
who use Crystal
Grouping
Group Drill Down Property
Drilldown default
Expression options
RS Report Format & Layout






Again, can use wizard, layout manually, or a
combination
Report can incorporate parameters for
customized output filtering
Report can include data bound objects (e.g.,
data fields, lists, sub-reports, charts, maps, etc.)
Reports can include design objects (page
headers/footers, lines, boxes, images, etc.)
Can define object properties as fonts, colors,
boundaries, margins, field format, etc.
Virtually all format and layout properties can be
determined dynamically using a function. The
function may consider data values.
RS Wizard – Layout Stepped
or Block
RS Wizard – Output Style
Style simple presets properties for fonts,
sizes, spacing, margins, backgrounds,
colors, lines, boxes, etc. Nothing
magical, this can all be done by hand.
Again, similar to Access or Crystal Wizards
Adding Report Objects
Layout Tab
Context Menus
Toolbox with layout objects
available
Formatting Report Objects
Field Properties
Advanced Formatting Options
Expressions
provide access to
all report objects
Example to Bold
output values
when greater
than $1000.
RS Designer – Preview Pane
IDE is a
sophisticated
programmer
development
environment
Bolded output
Solution / Project Deployment
Deploy Solution
Deploy Project
Select Configuration
RS – Report Manager





ASP .NET Web-Based Application (IIS)
Perform Report Management (e.g., security)
Establish Subscriptions & Data Driven Subscriptions
Define Report Execution Features (e.g., scheduling)
Define Report Delivery Strategy – (e.g., parameters,
pull from web, push e-mail, push .PDF, etc.)
Report Management - Security
Site Settings
Security Settings
Scheduling Settings
Security – System Roles
New System Role
defined
Two default
System Roles
defined
System Roles determine
what management activities
a user can perform
Security – Assigning System Roles
System Role(s)
assigned to
specific NT User or
Group
“Golf” is no longer a System Administrator
Security – Item Level Roles
This defines a
new Item role.
Items are reports,
folders, data
sources, etc.
Security – Item Level Roles
Assignment
Select a report
from a folder
Security – Item Level Roles
Assignment (2)
Select security
menu for item
of interest
Security – Item Level Roles
Assignment (3)
Select a new Role
Assignment for this Item
Administrator defaults as
“Content Manager” (Item
role that can do anything)
for all reports
Security – Item Level Roles
Assignment (4)
This Assigns the NT User
or Group to an Item
Level Role for the Item
(Charge Detail Report)
Report Subscriptions



An alternative to on-demand reports
‘pull’, subscriptions allow for ‘push’
reporting
A request to deliver a report at a specific
time or in response to a specific event
A desire to have the report presented in
an output format that is most useful to
the end user
Subscriptions Require Credentials
Subscriptions require that credentials (i.e., user name & password) be
included in data source definition. This is why Secure Socket Layer
(SSL) is necessary for secure access to subscribed reports
Subscription E-mail Options
Subscription delivery
properties are
independent of Report
Design properties
Subscription File Share Options
File Share
subscriptions can be a
wide array of formats.
Data Driven Subscriptions




Define the subscription (like a standard
subscription)
Specify a connection and query that will return
on record for each recipients
Define delivery option and parameters based on
data in query result set
Schedule the report (or configure it to be
updated on a snapshot refresh)
Scheduling Reports



Scheduled reports must be part of a
subscription
Schedules can be for a specific report or
a shared schedule (e.g., monthly
reports) can be created
Scheduled reports can use parameters
(like ‘today’)
Scheduling Types
RS Supports both
Shared Schedules as
well as Report specific
schedules
Schedule Detail - Daily
Scheduler is extremely
flexible making
available virtually any
combination and
permutation of dates
and times
The same report may
have multiple
Subscription, each with
differing schedules.
Schedule Detail – Other Options
Other scheduling
options
Scheduled Jobs – Are actually
initiated by SQL Server Agent
RS – Schedules are actually set up in SQL
Server Management / Jobs, and processed
by the SQL Server Agent.
I do not recommend editing from within SQL
Server as it seems to introduce problems
RS- Parameters




Report Parameters are constructed into a report
in the Visual Studio .NET report designer
Report Parameters are entered when executing a
Report
Report Subscriptions can have report parameters
hard-coded or derived from data drive
subscriptions
Parameters can be viewed, and some properties
modified from within Report Manager
Parameters – In VS Designer
From VS Layout tab,
bring up the context
menu and select
Report Parameters
Note, parameter is tied to a query that
generates a separate dataset from
the primary result set of the report
(next slide)
Parameters – New dataset to
query for default values
A second Dataset is added to this
Project to collect a list of unique
“lookup” entries for the parameter list
Insert Parameter into Report
Filter for Main Dataset
The Project’s primary query is
modified to use the parameter value
(prefixed using an @ sign) in the
criteria filtering section.
Parameter Preview in VS
After complete, re-deploy the report, project or, solution
Parameters – In Report Manager
Parameters appear in
report header by default.
Example #1 Demographic List




Identify all Inpatient Discharges that occurred
last month
List the Patient’s Unit Number (once), Account
Number, Discharge Date, Primary Insurance
Sort By Discharge Date
No Parameters –Scheduled to Run once Per
month on the first Monday and be pushed to an
e-mail address.
Example #2 – Compute Average
Inpatient LOS, Charges, and a Procedure
Count, By Provider and DRG





Use a parameter to allow the specific DRG to be
selected
Perform aggregation on the server for efficiency
List Providers sorted alphabetically
Detail includes the Average Charges, Average
LOS, Count of Procedures (CPTs)
Make this an ad-hoc query available to anyone