SQL Server Reporting Services London Database Developer Forum

SQL Server Reporting Services London Database Developer Forum Anoop Patel www. azteccomputing. com

Agenda l l l What is SQL Server Reporting Services Installation Report Authoring Report Management Report Delivery www. azteccomputing. com

What is Reporting Services l l l New Microsoft reporting software for creating, managing and delivering reports Centralized management Deliver browser based, file and email reports SQL Server based Create reports using Visual Studio. net Free with SQL Server 2000 www. azteccomputing. com

Reporting Services Architecture Browser Data Sources (SQL, OLE DB, ODBC, Oracle, Custom) Management URL WMI Custom App Web Service Report Server Data Retrieval Rendering Security Delivery Security Services (NT, Passport, Custom) www. azteccomputing. com SQL Server Catalog Output Formats (HTML, Excel, PDF, Custom) Delivery Targets (E-mail, File share, Custom)

Installation Component Prerequisite Report Server / Report Manager Windows XP / 2000 Server SP 4 / 2003 IIS 5 Microsoft. Net Framework version 1. 1 Report Server Database SQL Server 2000 SP 3 a Email Delivery Extension SMTP Server Report Designer Visual Studio. NET 2003 www. azteccomputing. com

Report Authoring l l l Integrated with Visual Studio. NET 2003 Full report authoring capabilities Generates Report Definition Language Report previewing Deploy directly to Report Server www. azteccomputing. com

Report Authoring Demo www. azteccomputing. com

Data Sources l l l Microsoft SQL Server 2000 / 7. 0 Oracle databases OLE DB including Analysis Services ODBC Shared data source www. azteccomputing. com

Data Sources Demo www. azteccomputing. com

Report Layout l List (free-form) – l Table – l Fixed number of columns Matrix (Pivot table / Crosstab) – l Data arranged free-form on the page The number of columns depends on the query results Chart – Data represented graphically (pie, line or bar chart) www. azteccomputing. com

Table Demo www. azteccomputing. com

Matrix Demo www. azteccomputing. com

Headers and Footers l Report Header/Footer – l Page Header/Footer – – l Add to page definition Optional for First and Last pages Table Header/Footer – l Area of report above or below data region Optional repeat on each page Group Header/Footer – – Force page break before or after Optional repeat on each page www. azteccomputing. com

Report Sorting and Filtering l Sorting l Filtering l Grouping l Aggregates www. azteccomputing. com (Sum, Avg, Count, etc)

Interactivity l l l Document Map Hyperlinks Bookmarks Drill Through Drill Down www. azteccomputing. com

Interactivity Demo www. azteccomputing. com

Report Parameters l l Report Parameters Query Parameters – – l SQL (named @ parameters) Access (unnamed parameters) Dynamic Parameter Lists www. azteccomputing. com

Report Parameters Demo www. azteccomputing. com

Import Access Reports l l Access 2002 Import all reports in mdb OK for simple reports Unsupported features: – – – l l Module Code Checkbox, Combo. Box, List. Box, Active. X Control Conditional Formatting Parameters Duplicate Object Names www. azteccomputing. com

Report Management l l l l Publish reports to Report Server Database Web based Report Manager Maintain report folders Data source Parameters Execution (cache or snapshot) Subscription and delivery Role-based Security www. azteccomputing. com

Report Execution l l On Demand Caching Snapshot Report History www. azteccomputing. com

Subscription and Delivery l l On-demand (“pull”) Event-based (“push”) – – l e-mail File System Data Driven www. azteccomputing. com

Report Rendering l Web Formats – l Print Formats – l HTML 4, HTML 3. 2, Web Archive TIFF, PDF Data Formats – Excel, XML, CSV www. azteccomputing. com

URL Addressability l Report Server – l Report – l &Category. ID=1&Employee. ID=1 SSL Support – l ? /Report. Directory/Report. Name Parameter directives – l http: //[servername]/Report. Server ? dsu: Data. Set 1=My. User. Name& dsp: Data. Set 1=My. Password Report Manager – http: //[servername]/Reports www. azteccomputing. com

Not covered l l Security Web Service Interface Rendering Extensions Delivery Extensions www. azteccomputing. com

Further information l Questions – – l Microsoft site – l anoop. patel@azteccomputing. com 020 88 66 55 77 http: //www. microsoft. com/sql/reporting Evaluation copy (120 day) – http: //www. microsoft. com/sql/reporting/productinfo /trial. asp www. azteccomputing. com