SQL Server 2000 Reporting Services for EPM Boris

SQL Server 2000 Reporting Services for EPM Boris Bazant Portfolio Manager, Central and Eastern Canada Quantum. PM

Agenda: Ø Ø Ø Ø EPM Reporting Platform Overview SQL Server 2000 Reporting Services Overview Why and When to Use SQL Server 2000 Reporting Services Benefits Examples and Demo Peak into the Future – SQL Server 2005 Reporting Services Q&A

EPM Reporting Platform Overview

EPM Architecture Components Team Participation Easily Create Enterprise Project Plans More Productive Users LOB Systems Get More from Your IT Investments Centrally Store Project, Resources, and Reporting Project Documents, Issues and Risks

SQL Server 2000 Reporting Services for EPM Solution EMP Solution based on Microsoft Office Project 2003 platform provides rich out-of-the-box reporting capabilities. Ø Nevertheless, sometimes you still may need to create a particular custom executive report that cannot be created by the tools available natively in Microsoft Office Project 2003 platform. If that is the case, you should consider using SQL Server 2000 Reporting Services. Ø Microsoft ® SQL Server™ 2000 Reporting Services provides server-based reporting functionality that you can use with SQL Server data sources and other data sources that are supported through Microsoft. NET data providers. Ø A complete installation of Reporting Services includes authoring tools, management tools, a report server engine, an API that is exposed as a Web service, documentation, and sample reports and applications. Ø

Reporting Platform For Business Intelligence Business Scorecards Project Transactional Tables Project Reporting Tables Portfolio Analyzer Cubes Analysis Services Reporting Services

Enterprise Reporting Essential To Better Decision Making Reporting is part of everyday business

Business Intelligence And Reporting User Profiles SQL Server Analysis Services 5 -10% of users Analysts Information 15 -25% of users Explorers SQL Server Reporting Services Information Consumers 65 -80% of users

Information Access Bottleneck Marketing CFO Operations HR Data Sources: • • OLE DB ODBC Other. NET Providers Access DB 2 Oracle SQL Server Knowledge Worker CEO Custom Reports for: IT Department and Systems • • • Marketing CFO Operations HR Knowledge Workers • CEO Asked to provide better access to information with less time and resources

Microsoft SQL Server Reporting Services Connects users to reports. Frees up IT department. Marketing Solution: l IT is a resource, not a middle man l Users have direct access to custom reports CFO Operations HR Knowledge CEO Worker Data Sources: • OLE DB • ODBC • Other. NET Providers • Access • DB 2 • Oracle • SQL Server Reporting Services IT Department and Systems

SQL Server 2000 Reporting Services Overview

Why and When to Use SQL Server 2000 Reporting Services "Today organizations demand real-time information from a multitude of systems on different platforms, and this visibility is crucial to making better business decisions. By interactively compiling and delivering information with Reporting Services, this puts the power to manage where it’s needed most, at the manager’s fingertips ” Ian Moulster, Microsoft UK Developer Product Manager for SQL Server 2005.

Why and When to Use SQL Server 2000 Reporting Services ·Are you drowning in data from different spreadsheets, systems and applications? ·Do you have real-time visibility of budgets, tasks, costs, work-in-progress, billing and profitability? ·Do you spend too much time manually compiling management reports? ·Are you looking to share information better between your business critical systems and applications? If you can answer yes to any of these questions. . .

Why and When to Use SQL Server 2000 Reporting Services Ø Managers and Executives spend an average of 26 hours per month collecting data and producing management reports, with 60% of decisions made from outdated reports. Research shows that 37% more projects and services are likely to finish on time using SQL Reporting Services.

Why and When to Use SQL Server 2000 Reporting Services Ø Cost-effective solution to report on projects performance and organizational costs Ø Providing a simple means for organizations to deploy interactive and attractive reports to the Web and Web Services. Ø If you are spending too much time on administration, and not enough on core business Ø Managers and executives of large organizations which typically have a myriad of different systems and platforms with non-standard data. Effectively share, aggregate and report on key information from disparate systems, applications and workforces. Ø See how easy it is to capture core business information, and share it with desktop, accounting and finance applications. Ø Share, aggregate and report on key information from disparate systems, applications and workforces. Improve corporate performance management through better visibility of task-based teams contributing to collaborative projects and services.

Enterprise Reporting SQL Server Reporting Services Benefits: Ø Real-time information from any data source Ø Traditional or interactive reports Ø Delivered via web browser or the office system Ø Enables faster, more relevant decisions Ø More people able to use the tools and applications

SQL Server 2000 Reporting Services Architecture Ø Ø What Is Microsoft SQL Server 2000 Reporting Services? Server-based reporting platform Framework including services, tools, and APIs to implement, deploy, and manage reporting solutions Take advantage of existing SQL Server, Internet Information Services (IIS), and. NET Framework infrastructures: l l Ø Ø Ø Ø Security Scalability Deployment Configuration Server-based report storage, processing, and management Rich report designing features Supports a variety of data sources Desktop and Web-enabled reporting On-demand scheduled processing of reports Report caching and history Role-based security Linked reports

Data Layer Report Server Layer Application Layer SQL Server 2000 Reporting Services Architecture

SQL Server 2000 Reporting Services Architecture Reporting Services: The Main Components Ø To author a report, the developer creates a report definition using the Report Designer add-in to Visual Studio. NET 2003 or a third-party authoring tools. While under development, each report definition is maintained in its own XML file, which is given the file extension of "RDL". Ø RDL files contain the layout, graphics, connection, and query information as well as Report Parameter definitions and almost all other report logic. Using the Report Designer, developers can author, tune, and refine reports without having to access the Report Server. Ø Once a report definition is complete, the report is published, or "deployed, " to a Report Server where it becomes a compiled managed report. At this point, the DBA or report administrator can decide how and to whom a report is to be made available. Ø

SQL Server 2000 Reporting Services Architecture Ø Main Components of Reporting Services: Ø An ASP. NET XML Web Service–based Report Server works in conjunction with a. NET Windows System Report. Server Service to process and provide managed reports in a variety of rendered output streams and stores its configuration, processing information, and other metadata in SQL Server. Ø The report server is the main component of Reporting Services. It is a Web service that exposes a set of programmatic interfaces, which client applications can use to access the report server. Ø Through its subcomponents, the report server: l l handles report requests retrieves report properties, formatting information, and data merges the formatting information with the data renders the final report

SQL Server 2000 Reporting Services Architecture The Report Server component contains several subcomponents: Programmatic Interface which accepts SOAP and HTTP GET requests, retrieve information from the report server database, and pass the information to other components. Report Processor component, which retrieves the report definition, combines it with data from the data processing extension, and renders it to the requested format with a rendering extension. Data Processing Extensions retrieve data from the report data source. Rendering Extensions transform the report layout and data into a device-specific format. Report Server Database stores report definitions, meta data and report history. Scheduling and Delivering Processor runs and delivers report on a schedule. Delivery Extensions deliver reports to specific devices or formats.

SQL Server 2000 Reporting Services Architecture Ø Main Components of Reporting Services: Ø Report Manager is a Web-based report access and management tool that is Ø Ø included with Microsoft SQL Server Reporting Services. Report Manager can be used to perform the following tasks: · View, search, and subscribe to reports · Create and manage folders, linked reports, report history, schedules, data source connections, and subscriptions · Set properties and report parameters · Manage role definitions and assignments that control user access to reports and folders Report Manager provides a user interface to a Report Server consisting of web pages and controls The ability to perform a task in Report Manager depends on user role assignment. A user who is assigned to a role that has full permissions, such as a report server administrator, has access to the complete set of application menus and pages. Users can be assigned to multiple roles. Each user can have different role assignments for different report servers, or even for the various reports and folders that are stored on a single server. The Report Manager permits developers or database administrators (DBAs) to define or modify Data Sources, locate and organize reports, and create Subscriptions, which permit reports to be e-mailed on a scheduled basis.

SQL Server 2000 Reporting Services Architecture Ø Main Components of Reporting Services: Ø Report Designer is a tool that you can use to publish reports to a Ø Ø Ø report server. Report Designer is integrated with Microsoft Visual Studio®. NET 2003. You install Report Designer features into Visual Studio. NET using a setup program. After installation, you can create reports by creating a new report project. To create a report using Report Designer, you create a new project in Visual Studio and add a report or set of reports to it. While developing a report, you have the option of testing it locally, without publishing it to a report server. You publish, or deploy, a report using the Visual Studio build process.

SQL Server 2000 Reporting Services Architecture Report Designer deploys the report to a report server that you select, after which you can manage properties and security using administration tools such as Report Manager.

SQL Server 2000 Reporting Services Data Sources Ø Data Sources Supported by Reporting Services: Reporting Services retrieves report data using data processing extensions. Ø Data processing extensions use ADO. NET managed providers. Ø Reporting Services provides extensions to retrieve data from the following data sources: Ø l l l SQL Server 2000 SQL Server 7. 0 SQL Server 2000 Analysis Services Oracle ODBC data sources OLE DB data sources

SQL Server 2000 Reporting Services - The Report Life Cycle Ø Authoring Ø Management Ø Deployment Ø Delivery

SQL Server 2000 Reporting Services - The Report Life Cycle Authoring: l Define the Data area where the Query is constructed to retrieve the data l Define the Layout Area where the report is constructed by dragging controls from the Toolbox and fields from the Fields window and dropping them in the Report design l The definition of the report is stored in a Report Definition Language(RDL) which is an XML l Visual Studio deploys the RDL to a Report Server, which stores it in a SQL Server database. When Reporting Services delivers a report to a user, it processes the RDL report definition and renders it into a more familiar format such as an HTML page or an Adobe PDF document.

SQL Server 2000 Reporting Services - The Report Life Cycle Management: l After a report definition is complete, the report designer publishes it to a report server and it becomes a managed report. l Report Manager is used to manage the reports and the reporting environment l Report definitions, folders, and resources are published and managed as a Web service l The Security, Properties and Scheduled operations applicable are defined for the items like reports, folders, data source connections etc. l It mainly consists of organizing folders to store various reports, Securing access rights to folders and reports and setting up shared schedules and shared data sources that are available for general use.

SQL Server 2000 Reporting Services - The Report Life Cycle Deployment: l The report can be deployed by uploading its RDL file directly from the Report Manager l l To use this method, you must locate the RDL file for the report you want to deploy This will also help to deploy a single report from a Project which might contain several reports

SQL Server 2000 Reporting Services - The Report Life Cycle Delivery: l The reports can be distributed in the Report Catalog through the Report Manager, Microsoft's Share. Point Portal Server, or a custom application l The Report Manager lets you organize reports into folders and lets users access the reports on demand or subscribe to them by email l Reports can be live reports in which the data is required each time they run, or they can be snapshots of standard reports that are updated regularly

To Get The Most Out Of Business Intelligence, you need… Ø Business expertise l l Identify key reporting requirements Well-defined processes to collect data • Garbage in, garbage out… Ø Technical expertise l l l SQL Server 2000 OLAP/MDX SQL Server Reporting Services Ø A partner can help

SQL Server 2000 Reporting Services Ø Steps involved in Implementing reporting solutions: Ø Designing and testing reports Previewing the report to verify functionality Deploying reports to a report server Managing a report server Accessing and viewing reports Report execution and caching Ø Ø Ø

Examples and Demo

Peak into the Future – SQL Server 2005 Reporting Services

Questions ? ? ? ?