Transcript Reporting Services

DAT368
SQL Server 2000: Report Authoring
with SQL Server Reporting Services
Peter Blackburn
Book: ‘Hitchhiker’s Guide to SQL Server 2000 Reporting Services’
Windows Server Systems – SQL Server MVP
Goals
Encourage you to purchase the book
Overview of Reporting Services
Demonstrate range of report
design features
Build two reports adding complexity
Discuss key report definition concepts
Mention some tips and tricks
What does Reporting Services do?
“SQL Reporting Services 2000 can serve reports
over the inter- or intra- net, based on data from
*any* source directly into many formats
including:- html, pdf, excel, csv, image formats.”
Yes, it’s a web application\service
Demo
Reporting Services
Reporting Services Architecture
VS.NET Report
Designer
Report Manager
ReportServer Web
Service
Internet Information Server (IIS)
URL & SOAP Interfaces
Data Sources
(SQL, OLE DB, XML/A,
ODBC, Oracle, Custom)
Report Server
Report Processor
Data Processing
Report Rendering
Output Formats
HTML, Excel, PDF, CSV, XML…
Security
Scheduling & Delivery
SQL Server Catalog
SQL Server 2000
Delivery Targets
(E-mail, FileShare,
Custom)
Principal Components
Report Designer
Add In to Visual Basic .NET 2003 (or Visual Studio .NET 2003)
Report Server
IIS hosted Web Service
Stores, Processes, Renders, Serves Reports over http(s)
Report Manager
IIS hosted Web Application
Manage Reports
Windows Report Service
System Service
Executes and delivers scheduled Reports
Minimum Requirements
Report Server
Windows 2000 Professional (Server Editions Recommended)
SQL Server 2000
Standard Edition with Service Pack 3A
Internet Information Server
Users
Any device with a web browser capable of html 3.0
HOWEVER Internet Explorer – is highly recommended
Developers
Visual Basic .NET 2003
Creating Reports
Use Report Designer hosted in
Visual Studio .NET 2003
Start with Report wizard or blank report
Import from Microsoft Access XP or 2003
Charts and code-behind are not imported
Redesign your top reports to use Reporting Services features
Use 3rd party tools that target
Reporting Services
Proclarity, Panorama, Cizer, MIS AG
Full list at: http://www.microsoft.com/sql/reporting
Demo
Creating Reports
Report Items
Textbox
£100,000
Image
Line
Rectangle
Subreport
Data Regions (List, Table, Matrix, Chart)
Custom Report Items coming in
SQL Server 2005!
Item Styles
All Report Items
Background Color
Background Image
Border Color
Border Style
Border Width
Padding
Charts
Background Gradient
Textboxes
Color
Font Family
Font Size
Font Style
Font Weight
Format
Line Height
Text Align
Text Decoration
Vertical Align
Lists
Repeating regions with
freeform layout
Brian
£10,000
Similar to “bands” in other
report writers
Jason
£20,000
Steve
£30,000
But multiple lists can sit sideby-side!
Lists inside lists to get
multiple levels
of grouping
Use for complex repeating
areas
Tables
Like lists with columns
Fixed columns,
repeating rows
Table cells can
span columns
Multiple table rows
per record
Table cells can contain any
report item
Faster and smaller than lists
Easier to align items
Use for most reports
Person
Region
Sales
Brian
North
£10,000
Dan
North
£20,000
Total
North
£30,000
Steve
East
£50,000
Total
East
£50,000
Grand Total
£80,000
Matrices
Repeats in row and
column directions
Sales
2000
2001
2002
Brian
£100
£110
£120
Also known as pivot table or
cross tab
Jason
£200
£250
£300
Steve
£300
£400
£300
Arbitrary formatting
in headers
Total
£600
£760
£720
Different formatting
for subtotals
Matrix cells can contain any
report item
Use when column headings
are records in your data
Charts
Data from fields in
the data set
or from expressions
Full set of chart properties
and types
Style enhancements
in SP1
Same data functionality
as Matrix
Expressions
Most properties take expressions
Values from the data sets
Values of other items on the report
Values of global properties and user properties
Expressions use Visual Basic .NET syntax
Aggregates: Sum, Avg, Count, Min, First, etc.
Examples
=Fields!Name.Value
=Fields!First.Value & " " & Fields!Last .Value
=Sum(Fields!Sales.Value)
=IIf(Sum(Fields!Sales.Value)>10, "green", "red")
Parameters
Report parameters can be used in report expressions
(including binding to query parameters)
Set of valid values and default values can be static or
query-based
Parameters can be hierarchical
Selection of parameter value can drive later
valid values
Accessed using global Parameters collection, e.g.
=IIF(Fields!Sales.Value > Parameters!Goal.Value, “PASS",
“FAIL")
Making Reports Interactive
Actions can be added to report items
Drill down within a report
Drill through to a different report
Bookmarks jump within a report
Hyperlinks any web page
Document map shows report structure
Green Bar
Conditional formatting on contents of table cells
Background color expression
=iif(RowNumber(Nothing) Mod 2,“Green","White")
Page Break After N Rows
“Hidden” page break grouping
Group expression
=Ceiling(RowNumber(Nothing)/30)
No header or footer
Page break at end of group
Running Total
Running aggregate function
=RunningValue(Fields!SalesAmt.Value, Sum,
Nothing)
Simulated End-User Sorting
“Sort By” report parameter
Values = Names of fields to sort by
“Direction” report parameter
Values = Ascending, Descending, None
Simulated End-User Sorting
Two sort expressions on table
Ascending sort
=iif(Parameters!Direction.Value= "Ascending",
Fields(Parameters!SortBy.Value).Value,0)
Descending sort
=iif(Parameters!Direction.Value= “Descending",
Fields(Parameters!SortBy.Value).Value,0)
SQL Server 2005 will have auto-sort and filter
built in!
Simulated “All” Parameters
String parameter (Country)
Query contains “like @Country”
Valid Values list contains “All”
Mapped to value of “%”
Deploying Reports
Copies reports, images, and data sources to
Report Server via Web Service
After you deploy
Set security permissions
Set up caching and scheduled execution
Set up subscriptions
Bask in the admiration of your co-workers!
For more information
Visit www.sqlreportingservices.net
Site offers premium content for book owners
ISBN: 0321268288;
Published: Oct 8, 2004;
Pages: 768
Includes DVD with
over 2.5GB of
tutorial videos
Only book to
include updates
for SP1
Microsoft Corporation
What the Development Team Say
“I learned new things when I read it”
Bill Baker,
General Manager,
SQL Server Business Intelligence,
Microsoft Corporation
[email protected]
Serious Complaint
From: Rob Gray (Microsoft UK)
Sent: 26 November 2004 10:53
To: Peter Blackburn
Subject: Serious Complaint about your book
Hi Peter
I have a massive complaint about your book. I usually use technical books as a cure for insomnia, and
they usually prove to be quite effective.
Your book, however, has let me down…
I’ve been reading the foreword etc. over the last few weeks whenever I’ve had a chance, but last night I
started reading the first “real” chapter. Now rather than the book being a cure for insomnia, I ended up
only getting to sleep sometime after 01:30am (I’m usually asleep by 11). A Technical book isn’t MEANT
to be interesting or amusing, and DEFINITELY not intriguing! What’s going on!!??
This is also the first time that I’ve laughed out loud while reading a technical book. In fact I’ve only
laughed out loud once or twice in my life when reading ANY book, including ones that are meant to be
funny.
PLEASE MAKE THE NEXT EDITION BORING SO THAT I CAN AT LEAST GET TO
SLEEP AT NIGHT!!
On a serious note, congrats on a great and fun-to-read book. I’m sure you can expect some feedback
from us as we work though it!
Definitely on my “recommended reading” list.
Cheers,
Rob
Additional Resources
Reporting Services web site
http://www.microsoft.com/sql/reporting
Reporting Services newsgroup
news:microsoft.public.sqlserver.reportingsvcs
Course 2030: Creating Reporting Solutions
http://www.microsoft.com/traincert/syllabi/2030AFin
al.asp
SQL Server Community Sites
http://www.microsoft.com/sql/community/
Community Resources
My Web Site
http://www.SqlReportingServices.NET
Community Resources
http://www.microsoft.com/communities/default.mspx
Most Valuable Professional (MVP)
http://www.microsoft.com/communities/mvp
Newsgroups
Converse online with Microsoft Newsgroups,
including Worldwide
http://communities2.microsoft.com/communities
/newsgroups/en-us/default.aspx
User Groups - Meet and learn with your peers
http://www.microsoft.com/communities/usergroups
default.mspx
© 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only.
MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.