Transcript SDB212 Reporting Services - Dallas Asp.net User Group

Hitchhiker’s Guide to
Visual Studio and SQL Server –
Innovations in Reporting
William R. Vaughn
Beta V Corporation
[email protected]
Copyright © 2005, 2006 Beta V Corporation All rights reserved
William R. Vaughn
Author, Mentor, Trainer
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
Hitchhiker’s Guide to SQL Server 2000 Reporting Services
ADO and ADO.NET Examples and Best Practices
For VB (and C#) Programmers
Hitchhiker’s Guide to Visual Basic and SQL Server
[email protected]
www.betav.com
www.betav.com/blog/billva
Copyright © 2005, 2006 Beta V Corporation All rights reserved
2
Agenda





What is Reporting Services?
What is the ReportViewer Control?
What’s the difference and the migration path?
How are reports created and managed?
Demos…
Copyright © 2005, 2006 Beta V Corporation All rights reserved
3
Reporting Services Architecture
VS.NET Report
Designer
ReportServer Virtual
Directory
Report Manager
Internet Information Server (IIS)
XML Web Service Interface
Data Sources
(SQL, OLE DB, XML/A,
ODBC, Oracle, Custom)
Report Server
Report Processor
Data Processing
Security
Report Rendering
Output Formats
HTML, Excel, PDF…
Scheduling & Delivery
SQL Server Catalog
SQL Server
Copyright © 2005, 2006 Beta V Corporation All rights reserved
4
Delivery Targets
(E-mail, SharePoint,
Custom)
What is the ReportViewer?

New UI class for Visual Studio 2005
– No SQL Server or other DBMS dependency
– Deployed with Smart Client or ASP.NET application
– Application binds ADO.NET DataTable to ReportViewer
– Project includes one or more RDLC (XML) Report files
– ReportViewer Renders Reports
Copyright © 2005, 2006 Beta V Corporation All rights reserved
5
Where Can I Get It?

Reporting Services
– Included in all versions of SQL Server
Including SQL Server Express Edition Advanced Services
Varying degrees of advanced functionality

ReportViewer
– Not included in the .NET Framework
– Included in all versions of Visual Studio .NET
– Visual Basic .NET—Standard or better
– Deployment considerations:
Self-extracting ReportViewer.exe
Run on deployment computer
Copyright © 2005, 2006 Beta V Corporation All rights reserved
6
Visual Studio 2005 Report Viewer
Copyright © 2005, 2006 Beta V Corporation All rights reserved
7
ReportViewer – Reporting Modes

ReportViewer.Server class
– Addresses Reporting Services Server,
RDL Report Path

ReportViewer.LocalReport class:
Addresses
– Local RDLC report path
– DataSource
– Parameters
– Reporting options

You configure
– At design-time, runtime
Copyright © 2005, 2006 Beta V Corporation All rights reserved
8
What data sources can be used?

ReportViewer or Reporting Services sources data from…
– Yes…
Any .NET Data Source
OLE DB, ODBC, Oracle, DB2, SQL Server (any version)
Even JET/Access…
Stored procedures, Views, CLR code…
– ReportViewer sources from …
Any IBindingList DataSource (not a DataReader)
Data Source Configuration wizard generated TableAdapter
DataTable, DataView, DataSet, Array List…
– But…
Single resultset queries—only first rowset used
Second and remaining resultsets are tossed
Watch out for stored procedures that
return complex resultsets
Copyright © 2005, 2006 Beta V Corporation All rights reserved
9
How are reports written?

RDL Report Designers
– Visual Studio
2003, 2005 BI Addin with SQL Server install
– Import Access reports
– Third-party RDL Report Designers
Panorama http://www.panoramasoftware.com/
Cizer http://www.cizer.com/
Panscopic http://www.panscopic.com
Proclarity http://www.proclarity.com
– Crystal Import—only a rumor

RDLC Report Designer
– Visual Studio 2005 “Report” Designer
Copyright © 2005, 2006 Beta V Corporation All rights reserved
10
Visual Studio 2005
Business Intelligence Projects
Copyright © 2005, 2006 Beta V Corporation All rights reserved
11
Report Designer Creates RDL
Layout
Local
Report Renderer
RDL
Preview
Copyright © 2005, 2006 Beta V Corporation All rights reserved
12
Reporting Services vs. Report Viewer

Visual Studio ReportViewer Control
– Create Visual Studio Data Source or
populate DataTable
(Optionally) Create Data Source TableAdapter
Data Source managed by application
Query/Report parameters captured/managed by your code
– Create RDLC report definition
Layout and render on workstation (WYSIWG)
Query runs from workstation
Visual Studio renderer shows final report
– Deploy finished application containing report
– Reports launched via application
Application launches report (RefreshReport)
Alternative rendering to PDF, Excel
Copyright © 2005, 2006 Beta V Corporation All rights reserved
13
Reporting Services vs. Report Viewer

Reporting Services
– Renders to HTML (by default)
– Exports to a variety of formats…
Copyright © 2005, 2006 Beta V Corporation All rights reserved
14
Reporting Services vs. Report Viewer

Reporting Viewer
– Renders to Windows GDI
– Exports to Excel, PDF or Print
Copyright © 2005, 2006 Beta V Corporation All rights reserved
15
Reporting Services vs. Report Viewer

Reporting Viewer and Reporting
Services HTML reports support
– Print and page setup, zoom and find
Copyright © 2005, 2006 Beta V Corporation All rights reserved
16
Visual Studio Typed Data Source

Visual Studio (interactively) creates TableAdapter
– DataSource wizard
Specifies the ConnectionString
One or more SQL query definitions
Generates strongly typed TableAdapter
Generates Fill and/or GetTable methods
– Bind to ReportViewer
Inserts Fill calls into Form_Load
– Form or control events invoke Fill
Populates strongly typed DataTable
Copyright © 2005, 2006 Beta V Corporation All rights reserved
17
Visual Studio Typed Data Source


Bind to TableAdapter
Reset ReportParameters collection
Copyright © 2005, 2006 Beta V Corporation All rights reserved
18
ADO.NET Untyped Data Source

Hand-code, populate ADO.NET DataTable
– Create Connection, DataAdapter, SQL for (every) query
– Capture query and report parameters
– Build ReportParameters collection
– ReportViewer.LocalReport.SetParameters
– Execute Fill
– Set ReportViewer.LocalReport.DataSources
– Call ReportViewer.RefreshReport
Copyright © 2005, 2006 Beta V Corporation All rights reserved
19
Managing ReportViewer DataSource

Use Task menu

Use Report DataSource dialog
Copyright © 2005, 2006 Beta V Corporation All rights reserved
20
How are Parameters Managed?

Reporting Services
– DataSource, queries and parameters managed by
Reporting Services
– Report UI (browser) exposes query and (optionally) parameters
– Definitions, defaults, criteria coded in report RDL
– Defaults, other parameter aspects managed by report DBA
– Special-case parameter management requires code
Copyright © 2005, 2006 Beta V Corporation All rights reserved
21
Managing ReportViewer Parameters

ReportViewer
– Connection and query code managed by your code
– All parameters (query and report) managed by your code
Write your own prompting code
Manage pick lists, range checks, criteria checks
ReportParameters collection set before rendering
Copyright © 2005, 2006 Beta V Corporation All rights reserved
22
Managing ReportViewer Parameters
Set Parameters
and Execute
Query
Address
DataTable
Set Report
Parameters
Copyright © 2005, 2006 Beta V Corporation All rights reserved
23
ReportViewer Control

Binding to Data Source Configuration wizard TableAdapter
Copyright © 2005, 2006 Beta V Corporation All rights reserved
24
ReportViewer FAQ

Requires Visual Studio 2005 (Standard or better)
– But not SQL Server

Can bind to Reporting Services 2005 reports
– Or “local” reports
– But not to Reporting Services 2000 reports

Freely distributable (no need for SQL Server license)
Copyright © 2005, 2006 Beta V Corporation All rights reserved
25
Summary

Reports broadcast corporate information
– To the LAN or the WAN
– Keep security in mind
Think SSL
Avoid SSPI credentials

Leverage common report definitions
(RDL and RDLc)
Copyright © 2005, 2006 Beta V Corporation All rights reserved
26
For more information

Visit www.sqlreportingservices.net
– Offers premium content for book owners

Visit www.hitchhikerguides.net
ISBN: 0321268288
Pages: 768
Includes DVD with
over 2.5GB of
tutorial videos,
sample reports
Copyright © 2005, 2006 Beta V Corporation All rights reserved
27
Mentoring, training, and technical content for
professionals world wide.
www.betav.com
(425) 556-9205
Copyright © 2005, 2006 Beta V Corporation All rights reserved