Hitchhikers Guide to Visual Studio and SQL Server

Agenda l l l What is Reporting Services? What is the Report. Viewer Control?

Reporting Services Architecture VS. NET Report Designer Report. Server Virtual Directory Report Manager Internet

What is the Report. Viewer? l New UI class for Visual Studio 2005 –

Where Can I Get It? l Reporting Services – Included in all versions of

Visual Studio 2005 Report Viewer Copyright © 2005, 2006 Beta V Corporation All rights

Report. Viewer – Reporting Modes l Report. Viewer. Server class – Addresses Reporting Services

What data sources can be used? l Report. Viewer or Reporting Services sources data

How are reports written? l RDL Report Designers – Visual Studio 2003, 2005 BI

Visual Studio 2005 Business Intelligence Projects Copyright © 2005, 2006 Beta V Corporation All

Report Designer Creates RDL Layout Local Report Renderer RDL Preview Copyright © 2005, 2006

Reporting Services vs. Report Viewer l Visual Studio Report. Viewer Control – Create Visual

Reporting Services vs. Report Viewer l Reporting Services – Renders to HTML (by default)

Reporting Services vs. Report Viewer l Reporting Viewer – Renders to Windows GDI –

Reporting Services vs. Report Viewer l Reporting Viewer and Reporting Services HTML reports support

Visual Studio Typed Data Source l Visual Studio (interactively) creates Table. Adapter – Data.

Visual Studio Typed Data Source l l Bind to Table. Adapter Reset Report. Parameters

ADO. NET Untyped Data Source l Hand-code, populate ADO. NET Data. Table – Create

Managing Report. Viewer Data. Source l Use Task menu l Use Report Data. Source

How are Parameters Managed? l Reporting Services – Data. Source, queries and parameters managed

Managing Report. Viewer Parameters l Report. Viewer – Connection and query code managed by

Managing Report. Viewer Parameters Set Parameters and Execute Query Address Data. Table Set Report

Report. Viewer Control l Binding to Data Source Configuration wizard Table. Adapter Copyright ©

Report. Viewer FAQ l Requires Visual Studio 2005 (Standard or better) – But not

Summary l Reports broadcast corporate information – To the LAN or the WAN –

For more information l Visit www. sqlreportingservices. net – Offers premium content for book

Mentoring, training, and technical content for professionals world wide. www. betav. com (425) 556

Slides: 28

Download presentation

William R. Vaughn Author, Mentor, Trainer Hitchhiker’s Guide to Visual Studio and SQL Server (7 th 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 techinfo@betav. com www. betav. com/blog/billva Copyright © 2005, 2006 Beta V Corporation All rights reserved 2

Agenda l l l What is Reporting Services? What is the Report. Viewer 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 Report. Server 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, Share. Point, Custom)

What is the Report. Viewer? l 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 Data. Table to Report. Viewer – Project includes one or more RDLC (XML) Report files – Report. Viewer Renders Reports Copyright © 2005, 2006 Beta V Corporation All rights reserved 5

Where Can I Get It? l Reporting Services – Included in all versions of SQL Server Including SQL Server Express Edition Advanced Services Varying degrees of advanced functionality l Report. Viewer – Not included in the. NET Framework – Included in all versions of Visual Studio. NET – Visual Basic. NET—Standard or better – Deployment considerations: Self-extracting Report. Viewer. exe Run on deployment computer Copyright © 2005, 2006 Beta V Corporation All rights reserved 6

Report. Viewer – Reporting Modes l Report. Viewer. Server class – Addresses Reporting Services Server, RDL Report Path l Report. Viewer. Local. Report class: Addresses – Local RDLC report path – Data. Source – Parameters – Reporting options l You configure – At design-time, runtime Copyright © 2005, 2006 Beta V Corporation All rights reserved 8

What data sources can be used? l Report. Viewer or Reporting Services sources data from… – Yes… Any. NET Data Source OLE DB, ODBC, Oracle, DB 2, SQL Server (any version) Even JET/Access… Stored procedures, Views, CLR code… – Report. Viewer sources from … Any IBinding. List Data. Source (not a Data. Reader) Data Source Configuration wizard generated Table. Adapter Data. Table, Data. View, Data. Set, 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? l 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 l RDLC Report Designer – Visual Studio 2005 “Report” Designer Copyright © 2005, 2006 Beta V Corporation All rights reserved 10

Reporting Services vs. Report Viewer l Visual Studio Report. Viewer Control – Create Visual Studio Data Source or populate Data. Table (Optionally) Create Data Source Table. Adapter 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 (Refresh. Report) Alternative rendering to PDF, Excel Copyright © 2005, 2006 Beta V Corporation All rights reserved 13

Reporting Services vs. Report Viewer l 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 l Visual Studio (interactively) creates Table. Adapter – Data. Source wizard Specifies the Connection. String One or more SQL query definitions Generates strongly typed Table. Adapter Generates Fill and/or Get. Table methods – Bind to Report. Viewer Inserts Fill calls into Form_Load – Form or control events invoke Fill Populates strongly typed Data. Table Copyright © 2005, 2006 Beta V Corporation All rights reserved 17

ADO. NET Untyped Data Source l Hand-code, populate ADO. NET Data. Table – Create Connection, Data. Adapter, SQL for (every) query – Capture query and report parameters – Build Report. Parameters collection – Report. Viewer. Local. Report. Set. Parameters – Execute Fill – Set Report. Viewer. Local. Report. Data. Sources – Call Report. Viewer. Refresh. Report Copyright © 2005, 2006 Beta V Corporation All rights reserved 19

How are Parameters Managed? l Reporting Services – Data. Source, 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 Report. Viewer Parameters l Report. Viewer – 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 Report. Parameters collection set before rendering Copyright © 2005, 2006 Beta V Corporation All rights reserved 22

Report. Viewer FAQ l Requires Visual Studio 2005 (Standard or better) – But not SQL Server l Can bind to Reporting Services 2005 reports – Or “local” reports – But not to Reporting Services 2000 reports l Freely distributable (no need for SQL Server license) Copyright © 2005, 2006 Beta V Corporation All rights reserved 25

Summary l Reports broadcast corporate information – To the LAN or the WAN – Keep security in mind Think SSL Avoid SSPI credentials l Leverage common report definitions (RDL and RDLc) Copyright © 2005, 2006 Beta V Corporation All rights reserved 26

For more information l Visit www. sqlreportingservices. net – Offers premium content for book owners l Visit www. hitchhikerguides. net ISBN: 0321268288 Pages: 768 Includes DVD with over 2. 5 GB of tutorial videos, sample reports Copyright © 2005, 2006 Beta V Corporation All rights reserved 27