copyright © 2005, 2006 beta v corporation all rights reserved hitchhiker’s guide to visual studio...

Copyright © 2005, 2006 Beta V Corporation All rights reserved

Hitchhiker’s Guide to Hitchhiker’s Guide to Visual Studio and SQL Server – Visual Studio and SQL Server –

Innovations in Reporting Innovations in Reporting

William R. Vaughn

Beta V Corporation

[email protected]

2Copyright © 2005, 2006 Beta V Corporation All rights reserved

Author, Mentor, TrainerHitchhiker’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#) ProgrammersHitchhiker’s Guide to Visual Basic and SQL Server

[email protected]

www.betav.com/blog/billva

Author, Mentor, TrainerHitchhiker’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#) ProgrammersHitchhiker’s Guide to Visual Basic and SQL Server

[email protected]

www.betav.com/blog/billva

William R. Vaughn


AgendaAgenda

What is Reporting Services? What is the ReportViewer Control? What’s the difference and the migration path? How are reports created and managed? Demos…


Internet Information Server (IIS)

Reporting Services ArchitectureReporting Services Architecture

XML Web Service InterfaceXML Web Service Interface

Report ServerReport Server

Report ProcessorReport Processor

Delivery Targets(E-mail, SharePoint,

Custom)

Data Processing

Data Sources(SQL, OLE DB, XML/A, ODBC, Oracle, Custom)

Report Rendering

Output Formats HTML, Excel, PDF…

SQL Server Catalog

SQL Server

VS.NET Report VS.NET Report DesignerDesigner

Report ManagerReport ManagerReportServer Virtual ReportServer Virtual DirectoryDirectory

Security

Scheduling & Delivery


What is the ReportViewer?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


Where Can I Get It?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.exeRun on deployment computer


Visual Studio 2005 Report ViewerVisual Studio 2005 Report Viewer


ReportViewer – Reporting ModesReportViewer – 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


What data sources can be used?What data sources can be used? ReportViewer or Reporting Services sources data

from…– Yes…

Any .NET Data SourceOLE 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 TableAdapterDataTable, DataView, DataSet, Array List…

– But…Single resultset queries—only first rowset used

Second and remaining resultsets are tossedWatch out for stored procedures that

return complex resultsets


How are reports written?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.comProclarity http://www.proclarity.com

– Crystal Import—only a rumor RDLC Report Designer

– Visual Studio 2005 “Report” Designer


Visual Studio 2005 Visual Studio 2005 Business Intelligence ProjectsBusiness Intelligence Projects


Report Designer Creates RDLReport Designer Creates RDL

RDLRDL

Local Local Report RendererReport Renderer

PreviewPreview

LayoutLayout


Reporting Services vs. Report ViewerReporting Services vs. Report Viewer

Visual Studio ReportViewer Control– Create Visual Studio Data Source or

populate DataTable(Optionally) Create Data Source TableAdapterData Source managed by applicationQuery/Report parameters captured/managed by

your code– Create RDLC report definition

Layout and render on workstation (WYSIWG)Query runs from workstationVisual Studio renderer shows final report

– Deploy finished application containing report– Reports launched via application

Application launches report (RefreshReport)Alternative rendering to PDF, Excel


ReportingReporting Services vs. Report Viewer Services vs. Report Viewer

Reporting Services– Renders to HTML (by default)– Exports to a variety of formats…



Reporting Viewer– Renders to Windows GDI– Exports to Excel, PDF or Print



Reporting Viewer and Reporting Services HTML reports support– Print and page setup, zoom and find


Visual Studio Typed Data SourceVisual Studio Typed Data Source

Visual Studio (interactively) creates TableAdapter– DataSource wizard

Specifies the ConnectionStringOne or more SQL query definitionsGenerates strongly typed TableAdapterGenerates Fill and/or GetTable methods

– Bind to ReportViewerInserts Fill calls into Form_Load

– Form or control events invoke Fill Populates strongly typed DataTable


Visual Studio Typed Data SourceVisual Studio Typed Data Source

Bind to TableAdapter Reset ReportParameters collection


ADO.NET ADO.NET UntypedUntyped Data Source 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


Managing ReportViewer DataSourceManaging ReportViewer DataSource

Use Task menu

Use Report DataSource dialog


How are Parameters Managed?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


Managing ReportViewer ParametersManaging ReportViewer Parameters

ReportViewer– Connection and query code managed by your code– All parameters (query and report) managed by your

codeWrite your own prompting codeManage pick lists, range checks, criteria checksReportParameters collection set before

rendering


Managing ReportViewer ParametersManaging ReportViewer Parameters

Set Parameters and Execute

Query

Address DataTable

Set Report Parameters


ReportViewer ControlReportViewer Control

Binding to Data Source Configuration wizard TableAdapter


ReportViewer FAQReportViewer 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)


SummarySummary Reports broadcast corporate information

– To the LAN or the WAN– Keep security in mind

Think SSLAvoid SSPI credentials

Leverage common report definitions(RDL and RDLc)


For more informationFor 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

Mentoring, training, and technical Mentoring, training, and technical content for professionals world wide.content for professionals world wide.Mentoring, training, and technical Mentoring, training, and technical

content for professionals world wide.content for professionals world wide.

www.betav.com(425) 556-9205

copyright © 2005, 2006 beta v corporation all rights reserved hitchhiker’s guide to visual studio...

Documents

versions of sql server

sql server innovations

vaughn beta v corporation

visual studio

reporting services ado

report viewer slide

reporting william

rdl report path reportviewer