Introduction to Microsoft’s SQL Reporting Services in a Data Repository Environment

1. Introduction to Microsoft’s SQL Reporting Services in a Data Repository Environment By Acmeware, Inc.

2. Introduction • Acmeware • Participants • Course Objectives • Course Format

3. Course Summary • Overview of SQL Server Reporting Services (RS) • RS Licensing & Implementation • RS Development Environment • RS Management & Deployment • Two Examples of RS Reports from Authoring to Deployment

4. What is SQL Server Reporting Services? • A comprehensive, server-based reporting application • Enables the creation, management, and delivery of reports • Both traditional paper-based reports and web-based reports • Pushed or pulled reports, real-time or scheduled reports

5. In Microsoft's Words • “Microsoft SQL Server Reporting Services improves the productivity of organizations by providing a high-performance, managed reporting environment for the entire enterprise and makes it easier to get the right information to the right people, in virtually any business environment.”

6. Comparable Applications • Crystal Reports / Crystal Enterprise • Microsoft Access • Visual Basic / C# and .ASP • Cognos / Actuate / Business Objects • Medisolv RAPID, FCG CyberView

7. RS Required Software • SQL Server 2000 SP3 (which includes RS add-on) - Database • Windows 2000 SP4, XP, 2003 (Prof., Standard or Enterprise) – O.S. • Internet Information Server (5.0) - Web Report Server with O.S. • Visual Studio .NET 2003 – Report Development Environment (IDE) • Web Browser (Internet Explorer and others)

8. Typical MEDITECH DR with RS Internet Information Server IIS & DR may or may not be on the same server Visual Studio .NET 2003 Data Repository

9. RS Licensing & Implementation • RS is included as a free add-on as long as you have a valid SQL Server 2000 License • Report Server (IIS) is licensed as part of the O.S. (RS Report Manager component runs on the Report Server) • Visual Studio .NET 2003 (Report Designer) must be licensed for each report developer • Only a single instance of RS can run on a Server (even if the server had multiple instances of SQL Server – the same as Analysis Services) • The same pricing model is expected for Yukon (SQL 2005)

10. How to get RS • RS was released from beta earlier this spring • A RS Implementation disc can be ordered from Microsoft as long as you have a valid SQL Server 2000 License (and pay $5.75 shipping) • Microsoft Subscription clients may download RS from web • A 180 Day trial version of RS may be downloaded from web for free (you must have the supporting software)

11. RS Process Components Report Authoring - Visual Studio .NET 2003 Report Management - IIS Report Server Report Storage - SQL Server 2000 Databases Report Viewing – Browser, E-mail, File share

12. Visual Studio .NET 2003 Implementation • Visual Studio is installed on client(s) where RS authoring is going to occur • Installation requires significant prerequisites (e.g., Front Page Server Extensions, IIS, MDAC, .NET Framework) • Installation can take hours to completed • Installation can require over 1 GB of disc • The full VS.NET product is not required, a single component will do (i.e., VBasic)

13. Visual Studio .NET IDE Programmers will be familiar with this environment, report developers may not be IDE is significantly more complex than Access or Crystal

14. RS Implementation – IIS Report Manager • RS Report Manager is an ASP .NET Application • Virtual Directories are set up on IIS • Report Server is at http://<server>/ReportServer • Report Manager is at http://<server>/Reports • Actual reports can be accessed directly at: http://<server>/<folder name>/<report name> • RS has role-based security for Management and Items (works with NT authentication) • Configure resources (time-out defaults, caching, size of MRL, etc.)

15. Starting RS Report Manager By default, RS Manager can be accessed in Start Menu RS Manager can be accessed directly from Address Bar of Browser

16. RS – Report Manager Settings What a user can do to/with his her reports in Report Manager Report time-out (This is NOT NPR RS!) Security Scheduling Reports

17. RS – Implementation Report Server (database components) • RS databases are created when installing RS disc (or download and installing) from Microsoft Web • The database components of RS must run on an instance of SQL Server 2000 (MSDE database is not sufficient) • This likely will also be the DR Server, but need not be (though another SQL license would be required) • Two SQL database are installed (‘Report Server’ and ‘ReportServerTempDB’) • An Additional demo database may also be included

18. SQL Server – Database Components • ReportServer – Static Configuration Data (meta data) • Data sources • Users, policies, roles • Subscriptions • Report snapshots • ReportServerTempDB • Session data • Cached reports

19. RS Databases on SQL Server New RS Databases (Typically located on DR Server System Adventure Works is demonstration database for RS (books on-line can be downloaded too) RS Metadata RS Temporary Data

20. RS Implementation Walk Through

21. RS Install – Setup Files Check for prerequisites – Make sure you have your OS disc(s) available. Will likely have to download updates as well.

22. RS Install - Prerequisites You will see this when all prerequisites are loaded

23. RS Install – Start Setup Make sure to order the correct edition. I did not and it will not load (but doesn’t tell you so until install is complete!

24. RS Install - Registration GOLF

25. RS Install - Features By default, you get everything but Adventure Works demonstration database. I recommended installing everything Don’t be fooled, you cannot design reports with RS alone

26. RS Install – Service Account Account used to initiated NT Service. These are the default values GOLF

27. RS Install – IIS Directory I did not choose to install SSL. You should if this is planned for anything but development testing

28. RS Install – SQL Databases Likely this will be the DR Server GOLF I selected default configuration

29. RS Install – Email Delivery Info This would be your Exchange Server Acme_server.com These values are stored in a file called “RSReportServer.config” that can be opened form the Report Designer (i.e., Visual Studio .NET)

30. When USING Office As Mail-server • Modify RSReportServer.config in <Configuration> AS <RSEmailDPConfiguration> <SMTPServer>smtp.rcn.com</SMTPServer> <SMTPServerPort></SMTPServerPort> <SMTPAccountName>RCN</SMTPAccountName> <SMTPConnectionTimeout></SMTPConnectionTimeout> <SMTPServerPickupDirectory></SMTPServerPickupDirectory> <SMTPUseSSL></SMTPUseSSL> <SendUsing></SendUsing> <SMTPAuthenticate></SMTPAuthenticate> <From>COMPUTER@acmeware.com</From> <EmbeddedRenderFormats> <RenderingExtension>MHTML</RenderingExtension> </EmbeddedRenderFormats> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats> <ExcludedRenderFormats> <RenderingExtension>HTMLOWC</RenderingExtension> <RenderingExtension>NULL</RenderingExtension> </ExcludedRenderFormats> <SendEmailToUserAlias>True</SendEmailToUserAlias> <DefaultHostName></DefaultHostName> </RSEmailDPConfiguration>

31. RS Install - Licensing This should be the same as your SQL DR Server Licensing if on the DR system

32. RS – Report Designer • Use Visual Studio .NET to create a Solution. • A Solution can contain multiple Projects, which loosely correspond to a Report • Projects contain .RDL files (Report Definition Language) and .RDS (Report Data Source) • Solutions or Projects are Deployed to Web-Server

33. RS Project Definition • Use either RS wizard or perform manually • Define a new or use an existing Data Source (.RDS) • Design the Query (IDE looks like SQL View) • Grouping and/or data summarization • Look and style of output • Add Parameters

34. RS Project Creation From Solutions Explorer or from Startup (depending on configuration

35. Data Source Access from Solutions Explorer Data Source Properties Many are likely familiar with this sequence of dialog boxes List will depend on MDAC

36. Credentials Entered at Run-time Report Services will prompt for credentials at the time a report is executed. Reports requiring credentials at run time cannot be entered as subscriptions

37. RS Query Designer Note 3 main tabs in IDE This looks and acts a lot like creating / editing SQL Views SQL Code gets stored in XML-based .RDL file. Note that more sophisticated T-SQL Code can be entered than can be parsed by this editor.

38. RS Grouping and Summarization • Can group in T-SQL code or in Designer (which allows drill-down access to detail) • In Designer, you can use wizard to group, layout manually, or a combination • Grouping is established by selected field(s) a new page / header can be forced with each group • Fields can be summarized (e.g., count, sum, average) by field(s) in a group • Groups can be “rolled up” by default and a mouse-click used to drill down to detail

39. RS Wizard - Grouping This example groups and generates a new page for each visit (listing Account # and Name at the header Within a page, data is grouped be procedure charge category Detail charge Amount and Procedure Descriptions are displayed

40. RS Wizard – Subtotals and Drilldown This subtotals by by the groupings Within a group, detail can be rolled up or displayed using a mouse click on the (+) or (-) symbol

41. Grouping & Summarizing Output Summarization Layout is familiar to those who use Crystal Grouping

42. Group Drill Down Property Drilldown default Expression options

43. RS Report Format & Layout • Again, can use wizard, layout manually, or a combination • Report can incorporate parameters for customized output filtering • Report can include data bound objects (e.g., data fields, lists, sub-reports, charts, maps, etc.) • Reports can include design objects (page headers/footers, lines, boxes, images, etc.) • Can define object properties as fonts, colors, boundaries, margins, field format, etc. • Virtually all format and layout properties can be determined dynamically using a function. The function may consider data values.

44. RS Wizard – Layout Stepped or Block

45. RS Wizard – Output Style Style simple presets properties for fonts, sizes, spacing, margins, backgrounds, colors, lines, boxes, etc. Nothing magical, this can all be done by hand. Again, similar to Access or Crystal Wizards

46. Adding Report Objects Layout Tab Context Menus Toolbox with layout objects available

47. Formatting Report Objects Field Properties

48. Advanced Formatting Options Example to Bold output values when greater than $1000. Expressions provide access to all report objects

49. RS Designer – Preview Pane IDE is a sophisticated programmer development environment Bolded output

50. Solution / Project Deployment Deploy Solution Deploy Project Select Configuration