1 / 28

Hitchhiker’s Guide to Visual Studio and SQL Server – Innovations in Reporting

Hitchhiker’s Guide to Visual Studio and SQL Server – Innovations in Reporting. William R. Vaughn Beta V Corporation techinfo@betav.com. William R. Vaughn. Author, Mentor, Trainer Hitchhiker’s Guide to Visual Studio and SQL Server (7 th Edition)

edana
Download Presentation

Hitchhiker’s Guide to Visual Studio and SQL Server – Innovations in Reporting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hitchhiker’s Guide to Visual Studio and SQL Server – Innovations in Reporting William R. Vaughn Beta V Corporation techinfo@betav.com

  2. 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 techinfo@betav.com www.betav.com www.betav.com/blog/billva

  3. 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…

  4. 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 Scheduling & Delivery Report Rendering Security Data Processing SQL Server Catalog Output Formats HTML, Excel, PDF… SQL Server Reporting Services Architecture Delivery Targets (E-mail, SharePoint, Custom)

  5. 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

  6. 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

  7. Visual Studio 2005 Report Viewer

  8. 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

  9. 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

  10. 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

  11. Visual Studio 2005 Business Intelligence Projects

  12. RDL Report Designer Creates RDL Layout Local Report Renderer Preview

  13. 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

  14. Reporting Services vs. Report Viewer • Reporting Services • Renders to HTML (by default) • Exports to a variety of formats…

  15. Reporting Services vs. Report Viewer • Reporting Viewer • Renders to Windows GDI • Exports to Excel, PDF or Print

  16. Reporting Services vs. Report Viewer • Reporting Viewer and Reporting Services HTML reports support • Print and page setup, zoom and find

  17. 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

  18. Visual Studio Typed Data Source • Bind to TableAdapter • Reset ReportParameters collection

  19. 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

  20. Managing ReportViewer DataSource • Use Task menu • Use Report DataSource dialog

  21. 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

  22. 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

  23. Managing ReportViewer Parameters Set Parameters and Execute Query Address DataTable Set Report Parameters

  24. ReportViewer Control • Binding to Data Source Configuration wizard TableAdapter

  25. 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)

  26. 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)

  27. 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

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

More Related