Microsoft SQL Server 2000 Reporting Services

1. Microsoft SQL Server 2000Reporting Services (주)아이티즌 서정만 선임연구원 aejusjm@itisn.net

2. Reporting Services 소개 • Reporting Services Overview • Reporting Services Life Cycle • Report Authoring/Management/Delivery

3. The Reporting Life Cycle DELIVERY MANAGEMENT AUTHORING RDL Any .NET managed data provider Report Designer or custom • Runtime report rendering • Pull • Report Manager • Custom • Push • E-mails • Custom Central Report Server database Managed report execution Secured reports Web-based Report Manager or custom

4. Highlights of Reporting Services • Authoring • Wide range of supported data sources • Open report authoring options • Flexible report designs • Management • Parameterized reports • Execution properties • Report scheduling and history • Role-based security • Delivery • Range of rendering options • Flexible and extensible delivery

5. Reporting Services Scenarios • Internal reporting • Departmental • Corporate • External reporting • Business to business • Embedded reporting • Portals • Applications

6. Authoring Reports Report Definition Language Access through ADO.NET managed data providers • List / Table / Matrix / Chart • Sorting / Filtering / Grouping / Aggregates • Interactivity (drill-down, drill-through) • Parameters • Full Visual Basic .NET Expression Language • OLE DB • ODBC • OLE DB for OLAP Development options • Report Designer • Third Party

7. Managing Reports • Publish reports to a centralized report server database • General report management tasks: • Define execution schedule–on demand or in advance • Set up security • Maintain report folders • Administer user features • Management roles • Content manager • System administrator

8. Delivering Reports • Runtime rendering • Web – HTML, MHTML • Print – PDF, TIFF • Data – CSV, Excel, XML • Pull delivery – User issues report request • Report Manager • Custom • Push delivery – Report is automatically distributed to user • E-mail • File • Custom

9. Reporting Services Platform Browser Microsoft Office Custom Applications Web UI Programmatic Interfaces Report Server Report Processing Report Sources Security Data Processing Security Services Delivery Rendering Delivery Channels Output Formats Report Server Database

10. Reporting Services Components

11. Single Server Deployment Report Author User SMTP IIS Visual Studio .NET Report Manager Report Designer Report Server Client Report Server Database Server

12. Web Farm Deployment User IIS Report Manager MSRS MSRS Report Server SMTP Web Farm Virtual Report Server Report Server Database MSSQL

13. DEMO (Basic Reporting) • Authoring • Management • Delivery

14. DEMO (Interactive Navigation) • Using Dynamic Visibility • Creating a Document Map • Initiating Actions

15. DEMO (Parameter / Filter) • Using Parameter • Using Filter

16. Reporting Services • Executing Reports On-Demand • Executing Cached Instances • Executing Snapshot Reports • Subscriptions • Rendering Reports Using URL Access • Report Printing in Windows Applications

17. Executing Reports On-Demand Rendered Report Intermediate Report . Published Report • Every report request triggers the same execution process: • Retrieves up-to-date data and processes report • Creates intermediate report and temporarily stores result in the session cache in ReportServerTempDB • Renders using intermediate report Report Request

18. Executing Cached Instances Rendered Report Other Report Request Intermediate Report . Published Report • Initial request triggers execution process (subsequent requests only use step 4) • Retrieves most up-to-date data and processes report • Creates intermediate report and stores intermediate result in the cache in ReportServerTempDB • Flags intermediate report as a cached instance • Renders report from cached instance First Report Request Report Server Cache

19. Executing Snapshot Reports Rendered Report Report Request Intermediate Report . Published Report • Data retrieval and processing occurs in advance of report browsing • Scheduled event occurs • Creates the intermediate report and stores result as a snapshot in the report server database • Requests are satisfied by retrieving and rendering the snapshot Snapshot Creation Report Server Snapshot Cache

20. Using Report History Rendered Report History Request • Report histories store snapshots for future reference • History requests are satisfied by retrieving a specific historical snapshot Snapshot Published Report Snapshot History

21. DEMO (Cached Instance/Snapshot) • Executing Cached Instances • Executing Snapshot Reports • Using Report History

22. Subscriptions • Mechanisms to execute and deliver rendered reports • Two types of subscriptions: • Standard – end-user driven • Data-driven – administrator driven • Default delivery extensions: • E-mail (SMTP) • File share

23. DEMO (Subscriptions) • Data-driven Subscriptions

24. Rendering Reports Using URL Access • Use a hyperlink from a Web page • http://localhost/ReportServer?/Reports/SalesReport&rs:Command=Render • Additional parameters allow rendering control • http://.../SalesReport&rs:Command=Render&rc:Toolbar=false&rc:Zoom=200 • URL can include report parameters • URL sent as plain text – use SSL for security • Web service detects appropriate HTML level for browser

25. URL Access • ms-help://MS.RSBOL80.1042/RSPROG/htm/rsp_prog_urlaccess_374y.htm • ms-help://MS.RSBOL80.1042/RSPROG/htm/rsp_prog_soapapi_dev_5l7q.htm • ms-help://MS.RSBOL80.1042/RSPROG/htm/rsp_prog_urlaccess_959e.htm

26. DENO (URL Access) • Web Browser • VB 6.0

27. Report Printing in Windows Applications • Using Adobe Acrobat Reader • Using Adobe Acrobat Control for ActiveX (PDF.OCX) • Using Microsoft Office Document Imaging Viewer Control (MODI) • Using PrintDialog Control (Win Form)

28. DENO (Report Printing) • Using Adobe Acrobat Reader • Using Adobe Acrobat Control for ActiveX (PDF.OCX) • Using Microsoft Office Document Imaging Viewer Control (MODI) • Using PrintDialog Control (Win Form)