Provide Feedback on this Broadcast

Note This document is based on the original spoken WebCast transcript. It has been edited for clarity.

Karthik Ravindran: Hello, everybody, and thank you for joining us for this Support WebCast. In today's presentation, we're going to be introducing you to a recently released product, which is Microsoft® SQL Server™ 2000 Reporting Services. I'll provide you with an end-to-end overview of its architecture, the different components that make up its architecture, the features that it offers you, and how you can actually use the product to design, implement, deploy, and manage reporting solutions.

Prior to getting started, I would like to let you people know that today's presentation is going to be an end-to-end overview that introduces all the various features of the product. We would like you to make note of the specific areas that you would like to see follow-up WebCasts on, presentations that can go into more detail on those particular product features or areas. And we will be happy to comply by putting together sessions for you, where you'll be able to get more detailed information on some of the topics that we will touch on today.

The agenda for today's presentation is threefold (slide 2). We will begin by talking about the Reporting Services architecture, to understand how the product actually works under the covers, and we'll then introduce you to the different components that make up the architecture. After covering that, we will go on to talk about features that the product offers, and that help you to design, implement, deploy, and manage reporting solutions.

Finally, we'll talk a little bit about the process of implementing a reporting solution, and using an end-to-end scenario, where we use the different tools that the product offers. We'll look at initiating this right at the design phase, creating a report, deploying it to a Report Server, reviewing a report, and then managing security on the report and other management tasks. One of the things in this particular presentation, because we won't be able to show you any demos, is that many of the slides contain a lot of screen shots that depict user interfaces from the Report Designer and other tools that ship with the product. This will help you get a visual feel of what the product features actually are.

We will begin our discussion by talking about what exactly the product is, what its goals are, and what it's trying to address (slide 3). By and large, the goal of Microsoft SQL Server Reporting Services is to be a unified platform that provides you with all the services, tools, and application programming interfaces that you need to design, implement, deploy, and manage reporting solutions. One of the key features of this product is that the whole framework is based on a server-based reporting platform, which has a lot of benefits associated with it, and we will talk about that when we move on to the next slide in this presentation.

One of the key things in this server-based reporting platform is that we leverage a lot of the existing server-based technologies that Microsoft ships, such as SQL Server, Internet Information Services, and the .NET Framework itself. By leveraging these frameworks, we stand to gain the benefits of the security, scalability, deployment, and configuration features that these frameworks make available to the users.

For instance, security: anytime you wish to design reports that may need to access code in custom .NET assemblies, the whole security framework, which governs what the code in those custom assemblies can access and execute, is governed by the code access security framework, which is built into the .NET Framework itself. By building the architecture based on IIS and SQL Server, we also get all the scalability benefits that are associated with those server platforms.

The configuration for many of the components that ship with the product is based on standard .NET Framework integration files. For those of you who are familiar with opening Web.Config files or app.exe.config files, and editing Config elements to configure a particular .NET application, you will find that the process to do the same thing in the Reporting Services components is very similar.

Moving on to an overview of the different product features (slide 4), we'll start by talking a little bit about the server-based platform, which the product is. The server-based platform basically translates to three main areas: the storage of reports and metadata associated with the reports, the processing of the actual reports, and management tasks related to configuring security, caching, and other execution options for reports.

When we talk about report storage, in this particular architecture all the reports that users design and create to pull data from back-end data sources and display them in reports basically gets stored into a back-end SQL Server catalog database that ships with the product. So you have a back-end catalog that is residing on SQL Server, which stores the definitions of all the reports and the metadata information that is associated with those reports. The processing of the report, which is the actual execution of the report to retrieve data from the back-end data source, combining the data with the format information that has been defined for the report, and rendering the report to the end users, is also executed on the server.

Finally, management tasks — such as configuring security for your reports, and configuring caching properties for reports that are commonly viewed by multiple users, to improve the performance of the report generation — can also be configured on the server itself. So by and large, it's a complete server-based platform, but it's not just restricted to server-based technologies, because we also provide you with a number of client-side tools that you can use to design reports and write programs to administer the Report Server itself.

By and large, the Reporting Services product gives you rich report designing features, which we will walk through when we talk about designing reports in a subsequent topic in this presentation.

It supports a variety of data sources, and what that implies is the data sources from which you can access data to display in your reports.

Out-of-the-box, the product ships data providers with four main categories of data sources. We ship a data provider for SQL Server, which allows you to access data from SQL Server 2000 databases; we ship a provider for Oracle, which is basically our managed .NET provider for Oracle data sources; and then we have an OLE DB and an ODBC provider. Essentially, all of these providers that ship with the Reporting Services product are wrappers that sit on top of the .NET Framework, ADO.NET data providers, which people currently use in their ADO.NET applications to access data from the mentioned data sources.

In addition to that, users also have the flexibility to create their own data providers and plug them into the Reporting Services architecture, which is an advanced programming topic that we'll talk about briefly toward the end of this presentation. As far as the reporting capabilities itself are concerned, the product supports both desktop and Web-enabled reporting. Web-enabled reporting is the ability for a user to open up a browser window and specify a URL that will then render the report and display the report inside the browser itself. In addition to that, we also ship a component called a Report Manager. It's basically an out-of-the-box Web user interface that users can use to view reports and configure report properties.

The desktop part of reporting basically refers to the capability to take a report and then export that report into a desktop format. For instance, you can view a report in the HTML Viewer, and after viewing the report, if you want to view offline using a desktop application, then you can export the report to a variety of formats. For instance, you can export reports to .pdf format or to Excel format. You can export them out of image files. Then you can use the reader applications for those specific formats to open your report and view it on the desktop.

On-demand viewing of reports is the ability to interactively view a report using the Report Manager applications or using the URL access interface. This allows you to interactively view the report in a Web browser window itself.

The other option is to define what are called schedules or subscriptions. These will enable users to define a particular schedule for when a report needs to be processed, executed, and then delivered to a specified location. In terms of the delivery for the scheduled reports, out-of-the-box we ship two delivery providers. One is to deliver the report to a specified e-mail address or set of e-mail addresses, and we also provide you with another delivery provider that will generate the report to a user-specified file share. If users have other delivery requirements, then we expose interface APIs that they can implement to create their own delivery extensions, and plug it into the Reporting Services framework, so that reports that are processed through subscriptions can be delivered to those specific destination targets.

The product offers report caching and history features. Caching is typically used to optimize the performance of report rendering when users interactively view reports. So you can define cached schedules for your reports, which basically defines the schedule information for when the cache needs to be updated.

We also have another feature called Snapshot, which is a nonvolatile mechanism to cache reports. Snapshot allows you to get the execution snapshot of a specific report across various periods of time. For instance, you can define a Snapshot schedule that says, "Every day at 5:00 P.M., generate this report, and add the generated report to my history list." You can then go out into the history list and take a look at the different instances of the snapshots that have been generated for the report, and compact that data in the report across multiple snapshot instances.

In addition to that, the whole security model that is used to control access to report items and Report Server folders is based on role-based security. You can map your Windows® user logon accounts to specific report items that you deploy to a Report Server, and you can use that configuration to control who has the rights to access specific report items and folders.

Linked reports is the ability to have a single report and generate multiple linked reports off that single report. So a typical scenario for this would be, let's assume you have a sales summary report that displays sales summary data for all the regions in your company. So you defined a report that has a query that pulls up the sales summary for every different region for your company, across the globe. And you want to be able to generate linked reports off that single report, that in each linked report is going to display data that is specific to one particular region.

For instance, you can create a linked report that displays the data only for the Americas region, or you can create another linked report that displays only data for the European region, and so on. So when users from the different regions access those reports, they will be able to view the data that has been defined for the linked report and that they have been provided access to. We'll talk a little bit more about this feature when we get into the Report Server management topic.

A few more of the product features: Currently Reporting Services provides you the ability to import reports from Access (slide 5). So if you have Access reports in MDB databases, and you wish to migrate those reports to SQL Server Reporting Services, we offer you a feature wherein you can import those reports, edit them, and enhance their design. In the online documentation that comes with the product, there's a section that talks about importing Access reports. This feature does have a few limitations in terms of what it supports and what it does not support, and users are strongly encouraged to go in and look up the documentation to figure out some of the areas around which they would have to do some manual tweaking to get the Access report to function as required in the Reporting Services platform itself.

It has a modular and extensible architecture. Primarily, what is meant here is that users can plug additional extension components into the Reporting Services architecture. If you have a data source that is not currently supported — for instance, if you want to go and retrieve data from XML files on your disk and display the data in your reports — then you can create your own data extension, which is used to access and display XML data in reports that you design using Reporting Services. So a data extension is an example of one of the extensions that you can develop and plug into the Reporting Services framework.

Another example is a delivery extension. We briefly spoke about subscriptions in the previous slide. If you want the reports that are generated by processing subscriptions to be delivered to a particular location that is not a file share or that is not an e-mail address, then you will have to create your own delivery extension, and you can plug that into the framework seamlessly. All of this is done by using interfaces that are exposed by the Reporting Services framework. You can implement those interfaces in your custom DLLs and develop extensions that can then be plugged into the Reporting Services framework.

One of the good things about the product is that it also has extensive tool support for every phase in the reporting solution development lifecycle. So when you want to design reports, we give you a Visual Studio® .NET 2003 project system called the Report Designer, which you can use to visually design and test reports. Report Manager is a Web-based UI that gives you a Web-based application to view reports and to manage the configuration of reports and the Report Server.

Those are a couple of good tools, and we also give you a whole bunch of command-line tools. For those of you who want to configure the Report Server or run Report Services from the command line, you also have a set of command-line tools that you can use to do the same.

Another outstanding feature in the product is the diagnostics that are built into the product from the ground up. Examples of this include component-specific tracing. In the next slide, when we talk about the architecture, we'll get a better understanding of what the different components of the product are and how they all interrelate to provide the functionality.

We can configure tracing individually for each of the different components, and trace logs are generated for each of the components. That's very useful, because at execution time, when you run into exceptions or when you run into problems, you can figure out which particular component is causing an observed problem by looking at the log files for that particular component. So component-specific placing is one of the diagnostic features that are being built into the stack.

Another feature that is provided is PerfMon counters. So we provide PerfMon counters for our core server components, and these can be used to monitor the health of those components and, basically, see how they are working together to process user requests.

This slide (slide 6) gives a diagrammatic representation of the architecture and components that constitute the Reporting Services framework. What we want to focus on first is the central piece, which is the Report Server itself. The Report Server is a primarily a .NET Web service. So the Programming Interfaces rectangle that you see in the Report Server rectangle refers to the core component of the Reporting Services infrastructure, which is a .NET Web service. This Web service implements a set of APIs that can be used to publish reports to other parts of our catalog database, and that can be used to configure properties on the report and other items that it uses.

As far as the Report Server itself is concerned, there are two main components here. One component is the Web service, which is what the programmatic interfaces are. The other component is the scheduling and delivery processor, which is actually implemented as a .NET Windows service. So the server itself is composed of a Web service and a Windows service.

The Windows service is primarily used to execute the background tasks, such as when a user goes in and subscribes to a report, and defines a schedule wherein a subscription needs to be processed periodically over a period of time. Events are generated to trigger those subscriptions, and those events are processed by the scheduling and delivery processor, which is implemented as a Windows service.

The Web service itself provides a programmatic API that is used to publish reports to the Report Server, to access reports that have been deployed to the Report Server database, and to configure the Report Server, security permissions, execution properties on reports, and so on.

Let's talk a little bit about the report processing functionality itself. In this slide, the items on the top layer of this particular diagram reflect all the different clients that can call into the Report Server. We have the Report Manager application, which is an out-of-the-box ASP.NET Web user interface that is provided with the product. Report Manager can be used to not only view reports interactively, but to also configure management, and to manage the Report Server and configure report properties.

Another example is a browser. For instance, you might have your own custom application in which you want to integrate report viewing and rendering functionality. When you want to do that, you can do so by using an interface that is provided by Reporting Services, which is called the URL Access Interface. So Reporting Services implements a URL access syntax that users can use to access and view reports in their own custom applications. If you have a Web application that you're viewing in a browser, the Web application can make use of the URL access syntax to access and display reports.

Report Designer is a tool that we shipped with the product. This is essentially a Visual Studio .NET 2003 project system that plugs into Visual Studio .NET 2003, which you can use to visually design, create, test, and deploy reports to a Report Server. We also ship a bunch of command-line utilities with the product, which can be used to configure the Report Server and execute Report Server management scripts from the command-line.

Finally, different third-party vendors can also write tools for the Reporting Services platform. We can have third-party vendors who write their own report designers, which in turn are capable of publishing reports to our Report Server catalog database. So those are examples of client applications that communicate with the Report Server.

Anytime these client applications want to communicate with the Report Server, they need to call into the core interface, which is the Web service APIs that are implemented by the framework. So the Web service interfaces in turn are used to pass and report metadata information, and report configuration properties, all of which are stored in a back-end SQL Server database known as the Report Server Catalog.

After installing Report Service, if you were to go in, using Query Analyzer, and connect to the SQL Server instance in which you installed the catalog database, you would see two databases there, one called Report Server and another called Report Server TempDB. All the primary metadata related to reports, and the configuration and management properties, get stored in the Report Server database. The Report Server TempDB database is used for user session-specific information and for storing volatile cached reports. And we'll talk a little bit more about that when we get to the topic of caching reports and the Report Server Catalog.

When a user requests a report, what essentially happens is the definition of the report gets retrieved from the Report Server database, and that definition then has to be processed to generate the report and send it back to the user. The processing of the report is comprised of two main stages. The first stage is the stage where the report processor has to connect to the data processing extension that is required to access the data from the data source, which provides the data for the report. So every report is going to be based on queries that need to retrieve data from a back-end data source, which then needs to be rendered and displayed in the report.

The very first step is going back and querying the data source, using an appropriate data processing extension, and retrieving the data from the data source. The next step is combining the data that is retrieved with the layout information. So when a user defines and publishes a report to the Report Server, the report definition is going to contain not just the information about the query that needs to be used to retrieve the data, but it will also contain information about the layout of the report itself. The layout definition will basically reflect what the user visually designed, using a tool like Report Designer.

After the data that has been retrieved is combined with the layout information, the next step is to look into what we call a rendering extension, to render the report out into its final format. So, rendering extensions are actually .NET DLLs, or assemblies, that contain the implementation code to render a report to a variety of different formats. For instance, we provide rendering extensions to render to formats like HTML 4.0, to render to Excel, .pdf, comma-separated value (CSV) files, and TIFF images. These are examples of some of the rendering extensions that we provide.

After the report processor has the data and the layout information, it then calls into the rendering extension to render out the report in the final required format. The rendered output is then sent out to the client application that is requesting a report.

The scheduling and delivery processor is the Windows service component of the Report Server, and it's primarily used to execute background tasks, which include items like processing subscriptions, updating cached reports, and updating snapshots that have been generated for reports. Basically, any process that needs to execute on its own, in the background, and generate output is going to be executed by the Windows service component. So that's known as the scheduling and delivery processor.

As you can see, the scheduling and delivery processor makes use of delivery extensions to deliver processed reports to the target specified location. Reporting Services ships two delivery extensions out-of-the-box. You get an e-mail delivery extension, which you can use to e-mail processed reports directly to users. It also ships a file share delivery extension, which you can use to deliver processed reports to a specified file share.

In this particular slide we'll look at the different components that make up the Reporting Services architecture. On the server side we have the two main components, which are the Web service and the Windows service. The Web service exposes all the programmatic interfaces that are used to interact with the Report Server. The Report Server database is a back-end SQL Server catalog database in which all the report metadata and configuration information pertaining to the reports and the resources that they use are stored.

Finally, we also provide a couple of out-of-the-box client interfaces, namely the Report Manager and the Report Designer, which users can use to visually design reports and to deploy reports. And the Report Manager can be used to view reports and set properties and configuration settings for the different report items. We also ship some command-line utilities, which can be used to configure the Report Server from the command line, and which can also be used to execute management scripts from the command line. Finally, users can integrate report viewing and rendering functionality, and report management functionality in their own applications, which can be Windows Forms applications, Web-based applications, or even command-line applications. And third-party vendors can create tools to follow the Reporting Services platform by also calling into the programmatic interfaces.

This slide (slide 7) lists all the main server-side components that constitute the server-based implementation of Reporting Services. First, we have the Web service, which we spoke about in the previous slide. We have the Windows service, which is the scheduling and the delivery processor. The Report Server catalog is the back-end SQL Server database that stores all the metadata information pertaining to reports and the resources that they use — not just the reports and resources, but also configuration properties, such as the security settings that you define for reports, the caching and execution settings that you define for reports, and the user security permissions that you assign to access different items on a Report Server. All that metadata information gets stored in the Report Server catalog.

The Report Processor is the core DLL that handles the task of addressing user requests to process and deliver reports to users. So the Report Processor, in turn, uses data processing extensions to connect to back-end data sources and execute the queries that need to be displayed in reports. The Report Processor also uses rendering extensions to take the retrieved data that has been combined with the layout information and to publish the final output of the report in a user-specified format. So this slide also lists the rendering extensions that ship out-of-the-box, and you will be able to generate reports to any of these formats using them.

Finally, delivery extensions are used by the subscription and delivery processor to deliver processed reports that are generated by processing subscriptions to user-specified locations. By default, we ship two delivery extensions out-of-the-box. One is the file share delivery extension, which can be used deliver a report to a user-specified UNC path, and the other is an e-mail delivery extension, which can be used to send a report out to user-specified e-mail addresses.

Having discussed the features and the architecture, what we'll now do is move on to talk a little bit about implementing reporting solutions (slide 8), and we'll examine some of the tools that the product offers to assist you with the process. This slide lists the main steps that are involved in implementing reporting solutions using SQL Server 2000 Reporting Services.

The very first step is designing and testing reports, even prior to deploying it out to a Report Server. The tool that is used to do this is the Report Designer. After you've designed and tested your reports locally, the next step is to publish those reports out to the Report Server. This essentially involves calling into the Report Server Web service API, which in turn will enable you to publish report items into the back-end catalog database. From there they will be retrieved for subsequent processing and execution.

After you have deployed the reports, you can then go in and configure and manage the Report Server and specific report folders. So you can do things like define role-based security to determine which users have access to which particular reports that you've deployed. You can also configure execution properties for individual reports to define caching schedules and to define snapshot schedules. So, basically, all the management that you do against the Report Server can also be done by interfacing with the Web service API.

To interface with the Web service API you have two options. You can use the out-of-the-box Report Manager application that we ship with the product. Report Manager is an ASP.NET Web form application that provides a nice visual GUI that you can use to visually execute a large majority of the management tasks that you would want to execute against the Report Server. If you want to integrate Reporting Services management functionality into your own custom applications, then you can program directly against a Web service API to do so.

Programming and extensibility refers to the various programming features that are available in the product. These are primarily targeted at advanced .NET developers and users. The programming features allow you to create your own .NET DLL assemblies, which implement methods that you want to reference and use in your reports. For instance, you can have a report that needs to call into a custom .NET assembly method to retrieve data that it has to display in it. So when you want to do something like that, you can create a .NET DLL and call into methods that are implemented by the .NET DLL in your report design, which would then access the DLL execution time, obtain the data from the DLL, and use it for display in the report. In addition to that, you can also embed code inside your report itself, which is another option for adding user-defined functions to a report.

The other aspect is programming directly against the Reporting Services Web service API. So anytime you want to integrate custom Report Server management functionality in your own applications, you can add a reference to the Reporting Services Web service API and use that API in your own applications to program those tasks.

The URL Access syntax is a powerful feature that defines a specific URL format that you can use to access reports and Report Server items. And it is primarily used to integrate report rendering and report viewing in custom applications. For instance, you can have a Windows Forms application that hosts an Internet Explorer client Web browser control. And you can construct a URL dynamically in your code, and then instruct the Web browser control to access that URL and display the data that is returned. So that's one of the examples of what you can do.

Another example is you can have an HTML form that has fields that map the report parameters, and then you can set the Action property of the form to point to a URL to a particular report on the Report Server. Then you can force the data that the user enters into a form to the Report Server URL, which would then result in the Report Server URL being accessed and the requested report being generated. So there are a bunch of things that you can do with programming itself.

Extensibility is a really advanced topic, and that basically refers to building and plugging in your own extension assemblies into the Reporting Services framework. There are four main types of extensions that you can create and integrate into Reporting Services. We spoke about data extensions; we spoke about delivery extensions; and what you can also create are your own rendering extensions. So if you feel that the out-of-the-box rendering extensions do not cater to your requirements, or if you have an additional format that you need to support that has not been implemented by the out-of-the-box extensions, then you can inherit interfaces from the Reporting Services libraries and write your own rendering extension.

What you can also do is implement your own security extensions. So, by default, the Report Server and the Report Manager use Windows Integrated IIS-based authentication. If you want to do something fancy like use ASP.NET forms-based authentication to control access to your Report Server, then you can double-up and plug in your own security extensions.

More documentations and samples on writing security extensions will be made available on our MSDN® site toward the end of this month. The books online contains information and some samples that show you how to create custom data extensions and custom delivery extensions. We are also working on publishing a few more KB articles and white papers that give you more information on how to address those requirements.

The very first stage in implementing a reporting solution is the process of designing reports (slide 9). As far as designing reports is concerned, we want to talk about the process of designing reports and some of the building blocks, which basically map the features that are available to report designers. Then we'll talk a little bit about the Report Definition Language, which is the language that is used to store all report definitions that users design. Then, finally, we'll go over tool support for designing reports, which is the Report Designer project system, for which I'll walk you through a few screen shots that will show some of the primary interfaces of the Report Designer, and how you can use it to test, preview, and deploy reports.

As far as the process of designing reports is concerned (slide 10), the very first step that you would have to execute is defining report data sources, because every report needs to retrieve data from a data source. So out-of-the-box, we support four main categories of data sources, namely SQL Server, Oracle, OLE DB data sources, and ODBC data sources.

The very first step that you would execute, before even starting to design a report, would be to define a report data source, and then specify connection information that is required to connect to the back-end data source, from where the report data has to be retrieved. So the report data source essentially houses connection information that is used to connect to your back-end data source.

After you have your data source set up, you would then define what are known as DataSets. DataSets contain query definitions and theme definitions that map to queries that need to be executed against a back-end data source to retrieve the data that must be displayed on a report. So the very first step is setting up data sources and defining the DataSets that will supply the data for the reports that you design using reporting services.

After defining the data sources and the DataSets, your next step is to define the report layout. This is where you basically use the designer to drag and drop controls onto a visual surface, and then bind DataSet fields to those controls to display the data in the final format that you wish. We'll talk a little bit more about report layout and the different toolbox controls that you have, which you can use while designing reports, in subsequent slides.

After you define the basic report layout, you might want to enhance the report layout. You might want to add your own custom report expressions to individual items, to control their style and formatting. An example of this would be, for instance, if you have a sales summary report that lists the grand total sales achieved by the sales people in the organization over a period of a year. If you wanted to be able to highlight the sales people who achieved sales figures that exceeded a user-specified amount, then you can plug in a user-defined formatting expression that changes the font color of the sales amount value when it exceeds a certain value. For instance, if a sales amount exceeds $15,000, you might want to highlight that. You can go in and write an expression that dynamically evaluates the value of every sales figure, and it changes the font color, depending on whether or not that sales figure matches your condition.

You can also use report expressions to generate values for report items. What this means is other than the columns that you retrieve back from the data source, you might want to have some computed data displayed on the report, data that is computed off the fields that are contained in the report DataSets. So when you want to do that, you can write expressions that execute those types of computations and display the returned value on your report.

You can also add interactive features. Examples of interactive features include drill-down/drill-through functionality. Drill-down will enable you to make a report that displays multiple levels of data. You can hide all the detail levels and display only the top level grouping, and then give users the ability to drill down selectively into the lower levels of detail, on demand. Drill-through is the ability to seamlessly navigate from one report to another report, which will typically involve passing parameters from the first report to the second report, so that the second report can display data that is related to the first report.

Another example of an interactive feature is what is called a document map. If you are generating something like a catalog report for your company, then you can build a document map into the report by setting a few simple properties on your report items. That will result in generating a table of contents in the left-side pane that is displayed alongside a report, which will give a hierarchical listing of different sections in the report that users can navigate to by selecting items from that document map. There are more interactive features, such as parameters and filters, which we will talk about when we to get to them later on in this presentation.

After you've defined and enhanced the basic layout, and after you have the layout in place, the next step is to preview the report functionality itself. Inside the Report Designer you have the ability to preview the report or test the report locally. When you are previewing the report inside the Report Designer, the report does not get deployed to the Report Server, and it is not executed off of the Report Server. The whole preview functionality is local on the machine where you have Visual Studio® .NET installed, where you have the Report Designer installed. This is a good way for you to check and make sure that your report is rendering the way you want it to render and that it looks correct.

This slide (slide 11) lists the building blocks that you would use when you design reports for SQL Server Reporting Services. We have spoken about the first two on this slide. Data sources basically define connection information to a report data source. DataSets define the queries that are used to retrieve data from the data source. Data regions basically map to your different types. So in SQL Server Reporting Services, you can create tabular reports, cross-tab matrix reports, chart reports, and free form reports where the user is free to lay out the report in a free form fashion.

The data regions are bound to DataSets. Each data region is bound to a DataSet, and they're used to repeat the rows of data that are retrieved by executing the DataSet query. For instance, you can have a DataSet query that does a Select* from a customer's table. You can bind the DataSet to a table data region. What the table data region would do is take each record from the DataSet that is returned by the DataSet query and display it as an individual row in the table. So data regions are typically used to repeat rows of data that are retrieved from the DataSet to which they are bound.

Report items are controls that are added to the data region to actually display the data. For instance, you would add text box controls, or you can add image controls onto the data region, and bind those report items to individual fields in the DataSet. Then at run time, when the report is executed and processed, data from those fields will be displayed in the individual report items. When we go down further and look at a few screen snapshots, most of these concepts will become a lot clearer.

Expressions are also widely used in reports. The basic expressions are used to bind report items to individual fields in a DataSet, but you can in turn go in and write advanced expressions to do advanced styling and formatting, and then generate computed values that you want to display in reports. So expressions are very powerful and can be used to go over and beyond just binding fields to simple data items and displaying the data values.

(Slide 12) As stated earlier, all report definitions basically are comprised of two main elements of data. The first element is the report layout, which defines how this report needs to be rendered in its final form. It defines the layout information. And then you have the query definitions, which contain information about the data source that the report uses, and the query that the DataSets in the report use to pull data that needs to be displayed in the report.

RDL, or Report Definition Language, is essentially an XML-based grammar that is used to store and generate report definitions. When you publish reports to the Report Server, it's essentially the Report Definition Language format of the report that gets stored in the Report Server catalog.

As stated earlier, the report processing engine, when it has to execute a report, will retrieve this RDL definition from the report catalog, and then it will invoke the data extension to go query the back-end data source, get back the data, combine the data with the layout information, and then call into a rendering extension, which will handle the task of taking the report definition and generating it in the requested target format. So the report definition, which contains the report layout and the query definition, is the format that is used to store reports in the Report Server catalog.

These .rdl files can exist as files on your file system. Because when you are working off the Report Designer, when you are designing reports in the Report Designer and viewing them locally, you're not actually publishing the reports to the Report Server. So at that point in time, the RDL is going to exist as .rdl files on your local file system. Then when you deploy the reports to your Report Server, that's the point in time when these .rdl files are populated into the Report Server catalog database and are stored on the Report Server catalog.

One point I wanted to clarify in the previous slide is "Proprietary to SQL Server Reporting Services." What this means is that RDL is a format that we use to represent reports in SQL Server Reporting Services. Other reporting engines will not understand this particular format. This is a format that is being used by the Reporting Services framework to store report definitions. But this does not mean that third-party vendors cannot write their own tools that generate .rdl files to create reports that are targeted at Reporting Services. So that can be done.

Third-party vendors who want to build their own reporting design tools for the Reporting Services framework will need to implement a framework that generates RDL for reports that the users design using the interfaces of those tools. Ultimately, the RDL definition needs to be conformant with the XML schema for the Report Definition Language, for it to be recognized as a valid report, and for it to be processed by the reporting engine.

This slide (slide 13) lists the main report designing features that are available in Reporting Services. We have grouping and sorting, which allows users to do multiple levels of master groupings and ordinary groupings, but also sort the data within those groupings. It's not necessary that a single report can only display a single data region. For instance, you can have a salesperson summary report that displays the salesperson's information in a form-like interface, using a list data region. And then beneath the form-like interface you can have a chart that displays the sales figures achieved by that salesperson over a number of years. Then beneath that you can have a table that lists all the customers who have been assigned to that salesperson.

In this case, you have a single report that has a list as the first data region, a chart as a second data region, and a table as a third. So you can create reports that have multiple data regions, and each of the data regions are bound to DataSets. So that's the key concept that you need to understand.

We provide support for rendering images, and these are not just image files that are stored on your local file system or image files that are accessible through a URL, but also images that can be retrieved from image data-type columns in databases. So if you store images as binary large objects (BLOBs) in your back-end database, then you can add an image report item to your data region, and bind that image item to a back-end database field that stores the image data. Then at execution time, the image data is retrieved and displayed in the image field.

Data filters is a powerful functionality that allows you to filter the data that is displayed in a report, and to filter the data on the client rather than on the server itself. For instance, if you had a query that returned the data for all the different regions of your company, the sales data for all the different regions of your company to begin with, and you wanted to be able to filter the data down to specific regions, then one way to do that would be to write a parameterized query, which would essentially require that each time you choose a different region value, the query would have to be re-executed on the server to get back the new set of data. But that can, in turn, result in throwing a lot of processing overhead on the SQL Server itself.

Another option that you have is to get back all the data that you need for viewing, to cache the data on the client, and then you define what are called report filters for your report, which will result in the actual filtering of the data to execute on the client rather than in the SQL Server process. So each time you choose a different filter criteria, there is no requirement for the back-end query to be re-executed against the data source and for the data to be returned back to the client. So data filters are a powerful feature that allow you to do client-side filtering of the data that is displayed in the report.

Subreports is an example of another report item. Subreports are mainly used to display one report inside of another report. The key concept in subreports is that when you add a subreport to a report and link it up to another report, each of these reports are completely different reports. So the report that contains a subreport is one report on the Report Server, and the report that the subreport points to is another report. So when these reports are processed, they are actually going to result in two reports being processed: the main report, which contains a subreport; and the subreport, which also points to another report on the server. So the subreport is executed, and the data generated by the subreport can be embedded into the main report.

Drill-down functionality enables you to hide nested levels of data and then to make them visible on demand. So you might have a report that displays multiple levels of groupings, and then that displays a final root-level detailed section. And then you might want to hide all the groupings, except the top-level groupings, to begin with, and then allow users to drill down into the folder levels of grouping on demand. So that's the drill-down functionally.

This whole functionality can be implemented in reports without having to write a single line of code. It's as simple as setting a visibility property for the section that you want to hide, and then setting the toggle item for that particular item to another item, which is used to control the visibility of the item that you have hidden. So you don't have to do any programming to achieve drill-down functionality.

Drill-through functionality is the ability to completely navigate to a different report from your main report. For instance, you might have a report that displays month-long sales summary data for a particular salesperson, and you want to be able to click a month and navigate off to another report that shows how the salesperson achieved the total sales figure for that month — the details of the individual sales orders that he processed. To do that, you might have a different report that displays the detailed level of data, and you can click a link in your main report to navigate through to the second report. So that functionality is known as drill-though. Again, implementing this does not require any coding.

Links and bookmarks: It's not necessarily that you only need to need drill through to another report. You can also drill through to an external hyperlink, which can be an external Web site, or you can define bookmarked sections within your own report, to which you can also navigate by clicking links. So those are the other two features.

Document maps: we spoke about this briefly before. Document maps give you a table of contents type of feature for your report. It's primarily used in reports like catalog listings, where you want to show the different categories and subcategories of products that you sell in your organization in a table of contents on the left pane, and you want users to be able to navigate to a particular category that they are interested in viewing by selecting an item from that document map. So that's what the document map helps you to do.

Report expressions, we spoke about this. Report expressions can be primarily used to control style and formatting dynamically. They can also be used to generate computed values in your reports.

Finally, embedded code and custom .NET assemblies: If at any point in time you feel that the out-of-the-box features are not sufficient to get the data that you need to display in a report, then what you can do is write your own .NET functions. And these functions can either be stored inside the RDL itself or they can be contained in external .NET assemblies. You can place the code that you need to execute your processing, or the data access to generate your reports, in these functions. Then you can reference the functions in report expressions, which will result in those functions being invoked and executed. And then the data returned from those functions can then be plugged into your report for generation purposes.

Let's talk a little bit about the Report Designer (slide 14). The Report Designer is essentially a Visual Studio .NET 2003 Business Intelligence Project system, and it gets plugged into the Visual Studio .NET 2003 installation that you have on the machine. This is a tool that we provide out-of-the-box, and it's used to create and deploy report projects and to visually design and test reports.

When you install the product, the Report Designer is installed in the <drive>:\Program Files\Microsoft SQL Server\80\Tools\Report Designer folder. So the Report Designer folder contains all the assemblies that correspond to this product system.

In this slide (slide 15) we show a few Report Designer screen shots. The main thing is that now, when you launch Visual Studio .NET 2003 and select File, New project, you'll notice that you have a new category of projects, called the Business Intelligence Projects. Inside Business Intelligence Projects you have two options to create a new report project. You can either choose Report Project Wizard, which will result in a wizard interface being launched, through which you can step through multiple screens to set up your report project. Or you can choose an empty report project and manually add items to your report project.

After you have created a report project, move on to the slide on the right, which shows a screen shot of what the Solution Explorer in Visual Studio .NET will look like for a report project. As you can see, the Solution Explorer for a report project contains two main folders, Shared Data Sources and Reports.

You can add what are called shared data sources to your report project. This is essentially nothing but connection information to connect to data sources that your reports must be able to access. They are called shared data sources because a shared data source can be accessed by multiple reports. So you can either define data source and connection information, specific for a report, in the report definition itself, or you can create a distinct entity called a shared data source that contains that information, and that can be referenced by multiple reports that need to use it. Then you have a Reports folder, in which the reports that you create are added to the report project.

This is what the toolbox looks like for the report items. So when you open a report project and then you open a report to design it and add items to it, when you navigate to the toolbox you're going to have a new section called Report Items, which lists all the different controls that you have to design reports. So the controls in this particular toolbox can be classified into two main categories; one is called data regions and the other is data or report items.

The data regions, as we discussed earlier, are essentially controls that repeat rows of data from an underlying DataSet. Examples of data regions include the table, the Matrix, the Chart, and the List. Table, Matrix, Chart, and List are all examples of data regions that you can bind to DataSet and use to repeat rows of data from the underlying DataSet.

The other items are all report items. The Textbox is a report item that you can drag and drop on a data region, and bind to a DataSet field to display the data from that particular field. Lines and Rectangles are typically used for visual decorative purposes. Subreport is a data item that you can use to specify a link to another report that needs to be executed, and whose output has to be included in the main report itself. Image is the data item that you use to bind to image fields in your back-end data source, or that you can use to display static images like company logos on your report.

This slide shows you how to configure the data source properties (slide 16). When you right-click on the Shared Data Source and select New data source, you get dialog boxes that resemble the screen shots shown in this particular slide. As you can see, initially you get a window that shows a General and Credentials tab. In the General tab you can specify the name for the shared data source, and you select the type of the data source. This can be out-of-the-box; it can be SQL Server, Oracle, OLE DB, or ODBC. Then if you create and plug in your own data extensions, you can configure the Designer to list your extensions in the Type drop-down as well.

Then you have Connection string, where you specify the connection string. So you can either type the connection string here, or you can click the Edit button, which would then launch the Data Link Properties dialog box. Here you can define the connection information that will then be used by the data source.

This screen shot (slide 17) shows you an instance of the Report Designer, and this is what you get when you open an RDL file. So anytime you want to edit an RDL file, you double-click it, and that launches an instance of the Report Designer, which shows you three main tabs: a Data tab, a Layout tab, and a Preview tab.

The Data tab is where you go in and define your DataSets. So any DataSet queries that need to be executed to get the data that is displayed in a report must be designed in the Dataset tab. The Layout tab is where you go in and define the layout of the report, and the Preview tab is where you can preview the report to see how it looks at execution time.

This Data tab is very similar to what you're already used to with the Visual Data Tools that you have in Visual Studio .NET. You can write your queries, execute your queries, and test the queries to see how they function, and make sure that the queries are running correctly before you define the layout of the report.

The screen shot (slide 18) is the Layout tab. So what we have here is a simple report that contains a list data region. Inside the list data region we have unusual report items, mainly textbox items and an image item. As you can see, each of the textbox items are either captions or they are bound to themes from the underlying DataSet. The list data region, in turn, is used to repeat records from the underlying DataSet, because it's a data region. Therefore, the layout information will apply to each of the individual records, which will be rendered in the format that is shown in this slide.

You can also see that we are using two image items here, one image item to display a company logo in the page header, and a second image item that's inside the body of the report itself. This report item currently doesn't show any output, because this has been bound to an image data field in the back-end data source that will display the image of a product whose record is being displayed.

This shows (slide 19) a screen shot of the Preview tab. So when you execute the layout that you saw in the previous slide, it will basically look like this at execution time. So as we can see here, our image data item, which we bound to the product photo field, now retrieves the image from the back-end database and displays it. All of the other textbox items that are bound to those specific data fields also reflect the values.

There are two ways to preview reports inside the Report Designer. One is to use the Preview tab, and the other is to launch an application called RSReportHost.exe (slide 20), which is like a preview application that is used by the Report Designer. So when you set your value configuration to the debug mode and choose not to deploy the project to your Report Server, when you click the Start button in the Debug menu of Visual Studio .NET, the product system is going to build a report project, and then it's going to pick up the report that you specified as a start item, and display that report in an instance of the application called RSReportHost.exe.

RSReportHost.exe gives you a more real-time feel of what your report will look like, when you actually deploy it to the Report Server and view it off of a Report Server connection. As you can see, the application has a toolbar that gives you facilities to navigate between pages of the report. You can also switch into Print Preview mode and send the output of the report to a printer. You can vary the zoom settings of the report, and essentially preview a report and get a feel of what it will look like when you deploy it off to a Report Server.

Moving on to the topic of deploying reports to a Report Server (slide 21), this is the process where we finish the design of our reports. We have RDL files on our local system, and we want to deploy these reports to our target Report Server and make them accessible to different users.

Basically, deploying a report refers to the process of publishing report folders, reports within those folders, shared data sources, and any resources that are used by reports in your project. An example of this source would include an image file, which is being used to display a company logo on a report. That would be an example of a resource. Shared data sources basically reflect data sources that are used for multiple reports in your report project. Reports are, of course, individual .rdl files, and report folders basically represent a container for different reports that need to be categorized and stored together on the Report Server.

We have a number of deployment options. The easiest option is to have Report Designer create a report project and then deploy it using the options provided in the Report Designer. What you can also do is manually create report folders and upload reports and data source files to the Report Server using the Report Manager utility. So we'll see how this is done when we look at the screen shots. In addition to that, you can also write your own administrative scripts that call into the Web services API to go and publish reports and report items to a Report Server catalog.

When you use the Report Designer to deploy a report project, it calls into the Reporting Services Web service API to publish report items to a Report Server. So you can do the same thing by writing your own administrative scripts that call into the Web service's API to publish report items to a server.

This slide (slide 22) shows you the different configurations that are applicable to a report project. As you can see, there are two configurations that we are showing here. One is the DebugLocal configuration and the other is a production configuration. Each of these has two main options, the Build option and the Deploy option. The Build option compiles all the reports in your project; it checks the RDL to make sure that the RDL is valid calling to the schema; and it checks to ensure that the reports do not have any errors in them.

If you were to run a project in Visual Studio .NET 2003 with the Deploy check box cleared, it will compile all the reports and launch an instance of RSReportHost.exe to display the report that you have chosen as a startup item. So when the Deploy check box is cleared, the Report Designer is not going to publish the reports to a Report Server. In the case of the Production configuration, you'll notice that the Deploy check box is selected. This not only compiles and checks the report's validity, but it also publishes the reports to a user-specified Report Server.

The next slide (slide 23) shows you the properties of the report project itself. As you can see, in these properties we have a property called TargetServerURL, which is used to specify the Report Server to which a report project must be deployed when a user selects a deploy option. You have a Target folder, and this folder, by default, maps to the name of the report project that you are working on. What this will result in is a folder called Module 2 – Designing Reports, or whatever folder you specify, being created on the Report Server and being used to contain or store all the individual reports that are contained in that report project.

OverwriteDataSources: By default, when you deploy the project it deploys the data sources only the first time, any shared data sources that you might have. Then if you make changes to any of those shares' data sources and need to get those changes back up to the Report Server, you would have to set the OverwriteDataSources property to True to enable it to do that.

The StartItem defines the first report that needs to be launched and displayed when you are executing the report project in debug mode. If you are in debug mode, it's going to launch out of RSReportHost.exe, which we looked at in an earlier slide, and display the first report that is specified as the start item. If you do not select the debug mode and you have the Deploy check box selected, then it will publish all the reports to your Report Server. It would launch an instance of Internet Explorer and use the URL Access feature of Reporting Services to access and display the report that you defined as the start item.

Deploying a report (slide 24) is as simple as right-clicking a project and selecting the Deploy option. Alternatively, you can click the Build menu in the Report Designer and select the Deploy Solution option. So that would result in the project being built and deployed to the user-specified Report Server.

Moving on to the topic of configuring and managing a Report Server, this slide (slide 25) lists the main items, the most commonly executed management tasks. For instance, you can configure report execution and caching settings. You can configure history settings, and then you can modify your data source connections or set properties on data sources. You can use role-based security to determine who has access to individual report items. You can define subscriptions for reports and configure delivery settings for those subscriptions.

You can manage schedules. Schedules basically define when subscriptions have to be processed, when the cache needs to be updated, when snapshots need to be generated, and items like that. You can configure site-wide security settings to control access to your Report Server. To do all these tasks, we provide you with an out-of-the-box ASP.NET GUI application called the Report Manager, which provides a friendly GUI interface to achieve all these different report management functionalities.

One of the key points to note here is that any configuration and management data that you change on a Report Server is ultimately going to be stored in the Report Server catalog database. So the Report Manager ASP.NET application is nothing but a front-end GUI that calls into the Report Server Web service API to store all the configuration settings and management properties that apply to the different report projects.

The Report Manager (slide 26), as stated earlier, is an ASP.NET Web-based user interface, not just for managing a Report Server, but also for viewing reports interactively. By default, it gets installed to the MSSQL\Reporting Services\Report Manager folder. That's the folder where you find all the components of the Report Manager. To access the Report Server on a machine, you would navigate to the URL http://<servername>/Reports, which is the name of the virtual directory that maps to the Report Manager.

This slide (slide 27) shows you what the interface would look like when you navigate out to http://<machinename>/Reports. As you can see, by default they're taken to a home page, and the home page lists all the different report folders that have been published to the Report Server. You will also notice that you have options to manually create new folders, to manually upload reports and report resources, and to manually create new data sources. So if you do not use the Designer, and you had some RDL files on your disk that you wanted to upload into a report folder, you can use the Upload File option. Therefore, if you wanted to create a new data source and wanted to do it directly in the Report Manager itself, you can also do that.

To navigate into a report folder, click one of the links shown on the home page, and that will take you into the folder and list the items in that folder. Items will typically be reports, data sources that are used by those reports, and any resources, like the image files that are used in the rendering of the reports.

This slide (slide 28) shows you what it looks like after you drill into a folder. In the previous slide we had a folder called Module 2 – Designing Reports, and on drilling into that folder, you will now notice that it lists the different report items in that folder. So I have an AdventureWorks2000 data source; I have a Northwind data source, and I have a report called Sales Summary for Territory. It also gives information like the last date on which the report was modified, who modified the report, and other information.

What you can do here is click an individual report to view the report, or you can click a data source definition to view the data source definition information. If you wish to remove items, you can also do that. You can select the check boxes next to those items and click the Delete button, which will then remove the items on the Report Server. If you want to create subfolders underneath the report folder, and/or add other new data sources or upload more files without using the Report Designer, then you can also do that by using the buttons that are provided to you on the toolbar.

When you click a report, by default it's going to pull up the report and render it into your HTML viewer control (slide 29), which will show you what the report looks like when it is pulled off the Report Server. So while navigating to a report, you will get several tabs. You get a View tab, a Properties tab, a History tab, and a Subscriptions tab.

The default tab that you are taken to is the View tab. The View tab instantiates an instance of what is called an HTML viewer control and displays the report in that control. So what you're looking at beneath the different tabs, where you have a toolbar showing the parameter values that the report takes, and which gives you buttons to navigate between pages of the report and to select a particular format to which you want to export a report, that whole interface is known as the HTML viewer, which the Report Manager invokes to display a report inside the Report Manager section.

You can do a bunch of stuff in this particular interface. For instance, you can change the zoom percentage to display the report in a larger size, or in the Select a Format drop-down you can select an external format, such as .pdf, Excel, comma-separated values, and export the report data to the target format. Then you can later open up the exported file using the corresponding data application and view the data in the target format.

That was the View tab, and now we're moving on to the Properties tab (slide 30). The Properties tab is where you come in and define the properties of the report itself. There are a number of properties that you can configure. You can configure parameter properties for reports that use parameters. You can change the data source definitions for individual reports. You can configure the execution property, and you can define snapshots, schedules, and security settings for the report.

Execution determines how a report gets executed. The default setting is what is selected in this screen shot. So the default setting is, "Anytime a user accesses this report, render the report with the most recent data, and do not cache any copies of this report." What this means is, execute the back-end query as specified by the DataSet, get the data, and display it. This needs to be done every time a user comes and uses this report.

You have other options. What you can basically do is instead of saying, "Do not cache the queried copies," you can define a schedule or you can enable caching for the report, in which case what happens is an intermediate format of the report that contains the layout information and the data is cached in the Report Server catalog. Then when users request this report the next time, the report is going to be retrieved from the Report Server cache and rendered to the user.

Going back to the way in which a report is processed, there are two stages. One is executing the query, combining the data output with the layout information. And the second stage is taking the data and layout information and rendering it into the final format.

So what actually gets cached when you enable caching? There is an intermediate format of the report that contains the data and the layout information. The final rendered output is never cached on the server. When you access the cached report, what you are avoiding is the back-end query that needs to be executed against the data source to go pull the data that the report displays. So the cached copy contains the data and it contains the layout information, which after being retrieved from the catalog database is then passed on to a rendering extension, which handles the actual rendering of the report. So that's what meant by caching.

When in different cache settings, you can set additional properties to determine when a cache needs to be expired. You can also define a user-defined schedule, which specifies how the cache needs to be expired and refreshed. So those are some of the options that you have in terms of caching.

The second option talks about an execution snapshot. The execution snapshot is a pretty powerful feature, when you want to compare what the data in a report looks like over a period of time. So let's say, for instance, that you want to see what the data looks like every day at 5:00 P.M. What you can do is go in and define a snapshot schedule, which tells the Report Server, "Every day at 5:00 P.M., generate an instance of this report and store it on the Report Server." Then later on you will be able to go and pull up the individual instances and compare the data that they contain, to see how the data output and data generation pattern of that report has changed over a period time. So that's what is meant by snapshots.

One of the differences between snapshots and caching is that a snapshot is a nonvolatile cache. After you define a snapshot, it's filed in the Report Server catalog, and it doesn't expire unless or until you delete that particular snapshot. Even the option that says you can update a snapshot, that means that a new snapshot is generated at a user-defined time. So it's not like an existing snapshot gets replaced by a new snapshot. Whereas caching, on the other hand, is volatile.

Report Execution Timeout is a value specified at time-out settings, which is in seconds, to determine how long the Report Server should try to process a report before it generates a time-out error. This execution time-out setting does not include the data source time-out. So data sources have their own time-out configuration, which determines the time for which attempts should be made to establish a connection with the data source. And that time-out is not included in the execution time-out. The execution time-out is only specific to the report processing itself, after the connection to the data source has been established.

In this slide (slide 31) we are looking at History. History allows you to store multiple instances of snapshots. On the previous slide, we saw how you can configure snapshot settings for a report, and define settings that dictate when report snapshots need to be automatically generated. So what you want to do is after you have the snapshots, you want to be able to see a history of those snapshots so that you can compare the data across those multiple instances. So that's what History allows you to do.

By default, the option is, "Allow history to be created manually." That means you can go to the History tab seen in the screen shot and click the Create snapshot button to generate a snapshot on demand. So that's the default option. What you can also do is select the option that says, Store all report execution snapshots in history. What that will do is, every time a snapshot is generated for a report, that will automatically be added to your history list. Which means that when you go to the History tab, you will see one entry for each of those processed snapshots. Then you can click each of those individual entries to view the report and see what the data looked like over a period of time.

You can also define schedules, which define how snapshots need to be added to report history. You can define settings that talk about how many snapshot copies need to be stored for a specific report, or whether you want to keep an unlimited number of snapshots on the report history under those configuration settings.

This is the History tab (slide 32). As I said, here you can click the New Snapshot button to create a snapshot on demand, or you can set up a schedule to automatically generate the snapshots, and that will update the history list to reflect those instances.

So far we've looked at report properties. If you were to click a data source, what you would get is the Data Source Properties dialog box. We had a data source called AdventureWorks2000 in our folder. So clicking AdventureWorks2000 will bring up the interface that you are looking at right now (slide 33).

So as you can see, data sources in turn have a Properties tab, and the General tab is where you define all the connection information. So you define the connection string, and then you define how credentials need to be supplied for the data source.

Credentials is an interesting topic. Basically, you can indicate that a user who is running a report needs to supply the credentials, in which case a user will be prompted for the credentials, or you can supply credentials that are stored securely in the Report Server database. So every time the data source is accessed, the stored credentials are used. That is a very safe way of storing it, because we actually use the encrypted connection information that the user specifies before storing it in the Report Server catalog. So the security of your connection information will not be compromised if you use stored credentials. The stored credentials that are specified can either be Windows credentials or SQL Server credentials.

Another option is to use Windows NT® Integrated security. What this means is to identify the credentials of the user who is running this instance of Report Manager, and use his credentials to connect to the data source. The final option is the Credentials are not required option, which is used for data sources that do not require any user credential information to be supplied.

We're moving on to some of the properties that you can configure at the site level (slide 34). This slide shows you the site-wide settings that you can configure for a Reporting Services installation. The very first option, which talks about enabling My Reports, is a feature that is known as My Reports in Reporting Services. That option is used to either enable or disable it.

My Reports gives each user his own independent folder, called My Reports, when he navigates out to the Report Manager. That user in turn can upload his personal reports into that folder, control the properties on those reports, and view those reports. By default, the users will not have My Reports folders assigned to them, where they can go in and upload their own report definitions and view reports that are added to the My Reports folder. But if you wish to provide the facility to your user, then you will have to enable that option at the Report Server level.

You also define site-wide settings in terms of the number of snapshots that you want to retain in your report history. So the default is to keep an unlimited number of snapshots in the report history, but you have to keep in mind that every time a snapshot is generated and stored, and is added onto the history, that's going to add more metadata to your back-end database. So if you want to restrict the number of snapshots that can be stored in Report History, then you can configure that amount to 10 or any other user-defined value.

Report Execution Timeout is, by default, 30 seconds. If you wish to exceed that 30-second time-out, then you can change the value from the default, 1800 milliseconds, or you can even select "Do not time out execution at all." In which case if a report is not processed, then it will continue to hang, and you wouldn't know whether it's hanging because of a particular problem that it's encountering.

Enable report execution logging: Reporting Services also logs a lot of data about the actual execution of reports and the amount of time that it takes for individual reports to be processed and executed. So all the data is stored in the Report Server catalog. And we provide you many useful reports that you can use to access the data from the execution log catalog and look at performance data, which will give you an idea of what reports are executing for the longest time, how long a report takes to get processed, and what the most frequently accessed reports were, and data like that.

The data itself is stored in a raw catalog table, but to make it more easily accessible, we also supply you with a Data Transformation (DTS) package. You can use that to take the particular data from that single execution log table and spread it across multiple tables, so that you can then query to see the execution log data. It's not necessary for you to have to write queries against the tables that were created by the DTS package, because they give you a number of out-of-the-box reports that you can pull up and view execution log data from. By default it's enabled, but if you wish to disable Report Execution Logging, then you can do so, and you can also configure the number of days for which a log entry needs to be retained in the catalog database.

These are some of the system-level roles that are used by Reporting Services (slide 35). Primarily, you have two main roles here, which are the System Administrator and the System User. The tasks that each of these roles can perform can be viewed by clicking the link that corresponds to that particular role.

As you can see, each role in turn can be assigned to a Windows NT group or a Windows NT user, and you can add additional groups and users to a particular system role. Clicking a system role will show you the window that you see at the bottom, and that allows you to see the list of permissions that have been granted to that particular role.

Item-level roles (slide 36) are configured at individual item levels. By items, what we mean is that items are stored in a report folder. Examples include reports, data sources, or any resource files that are used by reports; all these items are examples of report items.

So you can configure security at individual item levels. For instance, you can say a user should be able to access Report Folder A, but he should not be able to access Folder B. Or even within a folder, you can control access to individual report items to determine what users should be able to access and should not be able to access.

By default, we provide four roles out-of-the-box: the Browser role, which is pretty restricted, in terms of all you can do is view folders, navigate the reports, and see the rendered reports. Content Manager is a very powerful role. That's the role whose permissions are shown in the bottom screen shot. Essentially, any user who is a member of the Content Manager can do anything on the Report Server. You can create folders. You can create reports. You can modify properties. You can define subscriptions, and basically do everything that the other users can do.

The Role Assignment is, again, similar to the concept that we showed for the system-wide role definitions. You have roles; you get into the role definition, and you add Windows users or groups to those roles. And the permissions associated with those roles will be granted to the users. These roles are again assigned at the report item level and not at the site-wide level.

Here is an example where we have a report called Sales Summary for Territory (slide 37). If you go to the Properties of that report and navigate to the Security section, you'll notice that by default there is only one role assignment, which gives the built-in administrator the Content Manager permissions on that particular report. So what you can do here is click the New Role Assignment button, add another role for another user, and define that that user should only be given the browser permission; this means that user will be able to navigate and view the report, but he will not be able to modify the properties of the report or delete the report.

To do the new role assignment you click the New Role Assignment button, on the top tab, and that in turn will result in you getting the interface that you see down at the bottom. What you need to do there is go in and type in a user or group name that you want to grant permissions to and then select the roles to which that user needs to be assigned. This in turn will determine what that user can do with this report item.

Subscription and delivery (slide 38), we spoke about this earlier. This is the ability to define a subscription schedule for a report, to have it automatically processed at a user-specified time or at user-specified intervals, and then have the output of the processed report sent out to a specified delivery extension. So you can either request for such processed reports to be delivered by an e-mail, or you can request for these reports to be written out to a file share. So those are the two extensions that we supply out-of-the-box. You can also specify the format in which you want the report to be delivered. So all the other available rendering formats can also be selected when you define a subscription.

There are two main kinds of subscriptions. One is a regular subscription; the other is a data-driven subscription. The regular subscription is where you go to fill in all the subscription data. Let's go on to the next slide.

This slide (slide 39) shows you what the subscription UI looks like. When you click New Subscription, you'll get the screen that you are seeing here. As you can see, we've chosen the Report Server File Share delivery extension. This renders text box items and controls, which you can use to specify settings that must be used when the report is processed and delivered.

For the Report Server File Share, the first thing you need to specify is the name of the file itself. By default, it's going to be the name of the report. Then you can choose whether you want an extension to be added to that file name or not. After specifying the file name, you need to specify a path, which is where you specify your UNC shared location, or where you want the report to be delivered. Then you select the rendered format, which says what format the report needs to be rendered in, and the credentials used to access the file share. So if there is a protected file share that requires NTFS user names and permissions to be able to access it, then you have to specify that user name and password in those text boxes.

You can then define Overwrite options. Like when a file is generated, if there is already a file by that name, then you can overwrite that file. Or you can choose to not overwrite the file if an older version already exists. Or you can choose an option where an auto-increment number is added on as a suffix at the end of the file name. So that's a pretty useful option, when you want to be able to keep track of multiple instances of a subscription that has been processed over a period of time.

In processing options, you define the schedule. The schedule defines how often or when you want the processing to be done. You can either define a one-time subscription, or you can define a recurring subscription that is repeated every week, or every month, or on a specific schedule according to your requirements. If your report uses parameters, then you can supply your values for the report parameters, which will determine what values are needed for that particular subscription instance, as parameter values, which in turn will determine the data that is going to be returned and rendered in the report. This is a regular subscription. What happens in a data-driven subscription is a little different.

As you can see here, you are specifying all the settings. You are specifying the file name. You are specifying the UNC path. You are specifying the rendered format, credentials, overwrite options, and a bunch of configurations for this subscription. Instead, what you can do is store all that configuration data into a back-end SQL Server table and define what's called a data-driven subscription, which automatically fetches the data from the back-end SQL Server table and populates the configuration settings.

By storing that into a database table you can then define individual settings for different users, all of which are stored in the back-end table, and then request Reporting Services to use the information from that particular back-end table to generate data-driven subscriptions automatically, and to process them on defined schedules. So instead of manually supplying all of these configuration settings, you can store those configuration settings into a database, and have Reporting Services pull the configuration settings from the database and use it for the subscriptions.

Schedules (slide 40): Anything that has anything to do with schedules will be processed by the Windows service component of Report Server. So when we spoke initially in the architecture slide, we noticed that are two main server components; one is the Web service and the other is the Windows service. The Windows service is primarily executing on a background thread, and it monitors and waits for events related to background tasks to occur. When those events occur, it processes those tasks.

Examples of background tasks include subscriptions, report history generation (which we spoke about in an earlier slide), and snapshot updates, where a user defines a schedule for report snapshots to be updated continuously, and every time that particular time is reached, an event is generated on the server, which is then processed by the Windows service to generate the required output. Also, the scheduled cached report expiration. So when you define that a report needs to be cached, you can also define schedules on which the cached copies need to be expired and refreshed. So all of these background tasks, which need to execute at user-defined schedules and times, are executed by the Windows service.

Their execution is completely driven based on schedules. So you define schedules for each of these tasks, and when the date and time reaches the date and time that has been defined for a schedule, Reporting Services is going to generate an event, which will then be trapped by the Windows service component and processed to generate the required output.

Schedules can either be item-specific or shared schedules. So you can define shared schedules at the system level, which can then be used in multiple tasks. Or you can have specific a task that defines and uses its own specific schedule.

This is what the schedule definition UI looks like (slide 41). When you select a new schedule or edit a defined shared schedule or an item-specific schedule, you will get an interface like the one on this slide. It allows you to specify the recurring pattern for which the processing needs to be executed. So you can choose to do it on a daily basis and then specify which specific days you want the processing to occur on, or you can do a once-only execution, or you can even use recurring patterns based on the hour, week, and the month. You define a start and end date for the schedule. So by default you'll start on the current date. If you wish the schedule to only be executed for a specified duration of time, then you can define a stop date as well, which would ensure that the schedule does not get processed after the stop date.

After reports are deployed, after you've managed the reports, the next question is, how do you access and view reports (slide 42)? The easiest way to do this, if you want to view reports over a Web-based connection, is you can provide users access to the Report Manager application. Then they can get into the Report Manager application, navigate through report folders, and click individual reports to view those reports in the browser session.

You then use the URL access interface provided by Reporting Services to integrate report rendering into your own custom applications. Again, to integrate report rendering in your custom applications, you have two options: either use a URL access interface, or use the Web service API. For this particular scenario, we recommend using the URL access interface over the Web service API. The primary use case for the Web service API is in report management and configuration tasks. When you want to implement report management functionality in your own applications, that's when you want to use the Web service API.

URL access is very straightforward. You can format and construct a URL that points to a particular Report Server item, and then you can use that URL to access that report item in your own applications. For instance, if you are writing a Windows Forms application, you can put an Internet Explorer Web browser control on the Windows form, and then you can point the Web browser control to a URL that you constructed and request for the report to be retrieved and displayed in the Web browser control.

Similarly, in an ASP.NET application, you might have hyperlinks displayed on your .aspx pages. The target URL properties of those hyperlinks can be set to point to a Report Server URL, which in turn will access a report and render it when the user clicks the hyperlink. So there are multiple things that you can do with URL Access to integrate rendering into your own applications.

Other than that, another option is to export the reports to a desktop format. So you are viewing a report in the Report Manager, and you want to take this for offline viewing. Or you want it in a desktop format like .pdf, Excel, or an image format. In that case you can select the particular format from the drop-down that is provided in the Report Manager, and then you can export the report out to the particular format. Again, because the Report Manager is nothing but a wrapper over the Web service API, you can also do report exporting and all these configuration and management tasks by programming directly against the Web service API.

Printing reports: When you're viewing reports using the RSReportHost.exe application, you can print reports. As far as the Report Manager itself is concerned, there is no server-side printing functionality in this version. Sample documentation contains a sample delivery extension called the Print Delivery Extension, which shows you how to write your own delivery extension if you need to be able to send subscribed reports out to a printer directly. In this particular version we don't have support for server-side printing, so if you want to actually print reports, you'll need to export the report out into a desktop format, use that desktop format viewing application, and then use its printing capabilities. But inside the Report Designer, when you are viewing a report in RSReportHost.exe, you will have toolbar controls that you can use to configure print settings, print the report, and see what the report looks like.

The Reporting Services programming (slide 43) is a really advanced topic, so this mainly encompasses URL access syntax. As you can see, the URL access syntax is really straightforward. What you do is an http:// the name of the Report Server, followed by the virtual root name of the Report Server virtual directory. So the default name is ReportServer; that's the name of the virtual root. Then you pass in the query string parameter, which specifies what report you want to access and what the parameters are that you want to supply to control its data and its formatting.

Following the virtual root name, which is ReportServer, you add a question mark, and then you specify the path info. So if I have a report called EmployeeListing in a folder called Folder1, the path info would be /Folder1/Employeelisting, and that would basically go render that report in the default HTML format and return it back to me. If I want to pass parameters to the report, so the report in turn takes a parameter called EmployeeID, then what I would have to do is specify the path to the report, and then an ampersand, the name of the parameter, EmpID, equals, then the value that I want to pass into the parameter (?[/pathinfo]&[prefix]: EmpID=value]).

There are more advanced syntactical elements. For instance, you can pass device information, setting those parameters. For instance, when you trying to render a report out to, let's say, the CSV format, if you're not happy with using the comma as a delimiter and you want to use your own custom delimiter, like a semicolon, then you can pass in a device information parameter and preface the parameter name using the RC prefix. So you can do an RC:prefix=; and it will pick it up and know what it has to do with the parameter.

If you go into the online documentation and look for a section called Device Information Settings, that will give you all the different information parameters that you can pass for a particular rendering extension. Device information parameters vary by the rendering extension. Depending on what rendering extension you use, each rendering extension has different sets of device information settings. You can use those device information settings to control how the output is actually displayed.

You can also do other things like hide the toolbar when the report is displayed in the HTML format, or hide the controls that are used to solicit parameter values when displaying a report, and other things. So that's good to look at and work with to learn the URL syntax.

The Web services API: This is what almost all the tools call into. The Report Manager calls into it, the command-line tools call into it, Report Designer calls into it. If you write any of your own custom applications or tools, they would also need to call into it. Like any other Web service, to add a Web reference to it, you would open up a .NET project and add a Web reference using the WSDL URL that has been provide on this slide. It's http://server/reportserver/ReportService.asmx?WSDL. So if you plug that into the Web reference dialog box, you'll get a proxy object to access the Web service that will be added into your project. And you can then instantiate an instance of the Reporting Service proxy object and call methods against the proxy object to access the Web service API.

Again, the online documentation contains some conceptual information, which explains the goals and purposes of this API and gives you some samples. It also contains a detailed reference section, which lists all the different types that are defined in that Web service.

Embedded code is the ability to have functions inside your RDL, in your report definition itself. Custom assemblies is the ability to implement functions in external .NET DLLs and to call those functions in your report. Reporting Services also ships WMI provider classes that you can use in WMI code to configure the Report Server and the Report Manager. So we have one WMI class for the Report Server and one WMI class for the Report Manager. There are some samples in the documentation that show how to use that.

As far as extensions go (slide 44), there are four main extensibility points in Reporting Services. You can develop and implement your own data extensions. You can develop and implement your own delivery extensions. You can do your own rendering extensions and security extensions.

Data extensions and delivery extensions currently have samples in the online documentation. So you can take a look at those samples to see what kind of code writing is involved to create your own data extension and deploy it into the Reporting Services framework. We are working on publishing some information on the other two types of extensions. For security extensions, we are specifically working on trying to get a sample available to users that shows how to do forms-based authentication.

For writing any kind of extensions, it's a really advanced topic. Very advanced .NET Framework and programming skills are required for this. Not just that, but you also have to have a total understanding of the Reporting Services architecture and configuration, for you to be able to go and plug these extensions into your Reporting Services framework.

Reporting Services diagnostics (slide 45): We have Setup logging, which generates an MSI log when we execute the Windows Installer package for installing Reporting Services. You also have a bootstrap log file, which does the logging for the pre-MSI phase, where the system is checked for prerequisite components that are installed on the machine.

Execution tracing is basically what I spoke about in an earlier slide on Report Management. If you enable execution tracing at your server level, then data pertaining to the execution of the various reports, when you access the reports, is stored into a back-end catalog database. You can then export the data into a more readable format using a DTS package into a second database. We provide you a number of reports out-of-the-box that you can then run against the database to see how reports are being processed, and then to measure the efficiency of the report processing in your server environment.

Component tracing is fully configurable. What I mean by this is each of the individual components, namely the Windows service, the Web service, and the Report Manager have their own individual tracing features. And you can configure the level of tracing for each of these components independent of the other. So you can have verbose tracing enabled on the Windows servers, and have just informational tracing enabled on the Report Server Web service, and vice versa. So you can do a lot of configuration there.

All the log files that are generated by component tracing reside in the folder that is being shown in this slide. Basically it's MSSQL\Reporting Services\LogFiles. You will see three main categories of log files: one that maps to the Windows service, one that maps to the Web service, and one that maps to the Report Manager Web application.

We also ship Performance Monitor counters out-of-the-box. When you launch Performance Monitor, you'll notice two new Performance Monitor objects, one for the Reporting Server Web service and the other for the Reporting Server Windows service. Each of these will have several counters that you can use to examine the performance of your Reporting Services installation and see how it is responding to user requests.

For instance, in the RS Web service, you have a counter that you can use to track how many times a report is retrieved from a cache, which you can use to test and see whether the caching is actually working, and whether it's being expired and refreshed as required. Similarly, the RS Windows service has counters to track subscriptions, to track snapshot updates, and other functionalities that the Windows service itself executes. So these are all some pretty good diagnostics that you can use to monitor the health of your application and troubleshoot problems that you run into when using the framework.

What we covered today (slide 46) was a broad end-to-end overview of the different components. We showed you some screen shots of some of the visual aspects of these components. We discussed the architecture of Reporting Services overall. We introduced you to the main features, not just in designing, but also in deploying, implementing, and extending Reporting Services. Then we briefly spoke about the process of implementing a reporting solution using the framework.

Having said that, I'd like to now turn it over to the Q&A session.

Otto Cate: Thank you very much for the presentation. Before we jump into the Q&A portion of the Support WebCast today, I'd like to share a couple of quick program notes with our listeners. For more information on future events or to review any of our sessions on-demand, feel free to visit our main Support WebCast site at support.microsoft.com/webcasts/. There you'll find the on-demand streaming media for this particular live session, as well as the PowerPoint® slides. And we also plan to have a full transcript available there within two to three weeks.

At this time, I'd like to address some questions that were submitted to the queue. We do have a limited amount of time, but we'll try to get to as many of these as we possibly can today.

Karthik: This solution has a number of prerequisites that need to be satisfied before it can proceed. You can run into one of a number of different possible issues. The first thing that I would recommend is getting into the online documentation and having a look at the installation prerequisites, and also having a look at all the different installation steps and guidelines.

There is a specific section on troubleshooting common installation problems that also comes along with the product documentation. In fact, there is a Readme.html file that contains a listing of the most commonly known installation problems and configuration issues that you can run into when installing Reporting Services, which is also something that you need to look into and check out before you start the setup. That would be a good first step.

If you are unable to resolve your problem using the information that is provided in the documents and in the ReadMe.html file, then you can contact Microsoft Product Support Services and open up a support case with us, and we'll be glad to help you out with that.

Otto: The next question: Would Reporting Services be able to potentially replace Crystal Reports? Is there any information that we have that might compare Crystal Reports to the Reporting Services?

Karthik: I can't really comment on that. We don't want to comment on any competitor's products at this point in time, so we wouldn't be able to answer that at this point.

Otto: This question might be a little general, but I'll ask it just in case: Because this is a Web service, is security a concern?

Karthik: One of the good things about Reporting Services is that it completely builds off the IIS and the ASP.NET security infrastructures. So the security options that you have available in the IIS infrastructure are also available to the Reporting Services platform, because it's basically an ASP.NET Web service that is hosted on IIS. So by default, it uses Windows Integrated authentication.

If you want to you can enable SSL on a Reporting Services installation, and then require that all access to the Report Manager and to the Report Server be controlled using Secure Sockets Layer, which would add an additional level of security for you. Not just that, but if you feel the need to further enhance the security over and beyond what is provided out-of-the-box, then you can create and plug in your own custom security extensions to implement the kind of security functionality that you are looking for.

That's an area that we are currently working on, creating samples and documentations to show you how you can implement those interfaces and write your own code, to develop your own security extensions that may need to go over and beyond what is supported out-of-the-box with the existing framework. But yes, pretty much you're going to run into the same issues that you run into with any .NET Web service. But by using a combination of SSL and custom security extensions, if you want to go down that path, you can build over and beyond what comes with the framework itself.

Otto: Can you enable or disable rendering extensions on a report-by-report basis?

Karthik: What happens is in your Report Manager configuration file, where you have the rendering extension, this is actually done at the application level. So if your question is whether you can do this for individual reports, that's not possible. Because after you make a change in the configuration file that defines the rendering extensions, that change applies to all the reports using the tool.

Otto: The next question: To have a report delivered to a subscriber by e-mail, do I have to have Exchange running, or could I use my SMTP server?

Karthik: You could also use your SMTP server. What I would recommend here is to check out the section in the online documentation titled "Configuring E-mail Delivery," which has a lot of information on how to go against Exchange and against POP3 servers and SMTP servers. There are specific configuration parameters that you need to set in the configuration file, and you can go against SMTP.

Otto: Does Reporting Services support third-party OLAP engines, like Oracle and DB2 setup?

Karthik: The way Reporting Services currently works against SQL Server Analysis Services is that it uses the OLE DB provider for Analysis Services to execute queries and to execute MDX expressions and pull data back from them. Similarly, if these data sources have OLE DB providers that you can use to query the data, then you would be able to plug into those providers through the OLE DB wrapper data extension that we ship with our product, and use them to write queries against the data source. So as long as you have a valid data extension or a provider that can plug into the framework for these other data sources, you should be able to pull data from them.

Otto: This one might be a little outside the scope, but I'll ask it just in case: It is my understanding that you can use this product freely if you have a SQL Server license. If that's the case, is there an area where you can download the Enterprise version of the Reporting Services?

Karthik: What I would recommend here is that you go to www.microsoft.com/sql/reporting, which is the home page for Reporting Services. There's a document there that details the licensing information and how licensing is going to work for the product. So if take a look at that particular document, you'll get better information about how licensing works. In some cases, your existing license would suffice, but depending upon your deployment scenarios, you might have to acquire additional licenses. So going through that document would be a good first step to give you an idea of how you wish to plan your deployment, and whether based on that deployment you can use your existing licenses or you would need to acquire new licenses.

Otto: On a subscription, basic or data-driven, when the report is run, if no data is returned for the report, can the delivery be halted?

Karthik: If no data is returned for the report, can the delivery be halted? I'll assume that what you mean here is it's not a problem with the Windows service not running, but it's a fact that when the event is generated and the subscription is processed, you want to see whether the data returned with the Select query of this report has data or not. If it does not have data, you do not want the report to be rendered to your target extension.

So the answer to that, at this point, is no. Even if there is an empty DataSet, the report would be generated. So there's no way to control that.

Otto: How does a Web user authenticate to use Report Services?

Karthik: You have a couple of options here. One, if you are generating reports over to an IIS server, which is primarily used for external access to Internet users, then you can configure Basic authentication at the IIS level, and have those users apply the credentials of a user that you would have created for your users who are coming from outside of your organization. Then in your own report project, you would have configured role-based security on each of those individual report items, to grant them only the level of access that they need for those individual reports. Versus users internal to your company, who can log on using their own credentials when prompted. You can do role-based security to grant them more access than what you would grant for Internet users. So that's one of the options that you can use, making Windows Integrated authentication into Basic authentication, and then creating user accounts with restricted permissions for external users.

We are also working on a white paper that goes more into this topic. This talks about deployment scenarios, specifically deploying Reporting Services in an Internet and an extranet environment, and what sort of security issues you need to be prepared for. When that white paper becomes available, you'll have more information. But one way to do it right now is the way I just explained to you.

Otto: Can you dynamically create connections?

Karthik: The connection information will either have to be stored in a data source that has been uploaded to a Report Server, or it would have to be embedded in an RDL if it's a report-specific data source. I'm not sure what you mean by dynamic connection. If you are asking whether you can change the connection string at run time dynamically, when a report is being executed, then the answer to that is no.

Otto: Can the report Web server be deployed on a physically separate server from the database?

Karthik: Yes, it certainly can. In fact, that's the deployment scenario that we are pretty much recommending, which is to have the Web service and the Windows service executing on an entire server, and the actual Report Server catalog database deployed to a SQL Server instance that's running on a different box. That will give you a more scalable architecture, because IIS and SQL are not going to be residing on the same box, and will not end up contending for resources and causing bottlenecks. That's definitely a more scalable architecture, keeping the catalog on an instance of SQL Server that's running on a different box.

Otto: Is Visual Studio .NET the only interface Reporting Services works with?

Karthik: Even with Visual Studio .NET, you have to be very specific about the version, because it has to be Visual Studio .NET 2003 and not even Visual Studio .NET 2002. So it plugs straight into Visual Studio .NET 2003. You don't need any specific edition of Visual Studio .NET 2003. You don't have to have the Enterprise edition or the Enterprise Architect edition. Even the most basic edition of Visual Studio .NET 2003, which costs about $99 (USD), and which you can go buy off the shelf, is sufficient to plug in the Report Designer project system and use.

Otto: You mentioned some white papers earlier. There are a couple of users who are wondering where they might be able to find some white papers that contain testimonials or examples of other companies that may have implemented this.

Karthik: If you go onto www.microsoft.com/sql/reporting, that Web site contains a lot of the kind of information that the user is looking for. That's also the site where any white papers that we create for the product will be posted. So that's the central site to go and look for updated information and documents on Reporting Services. There is a lot of customer information that's available, and if this user wants anything specific, any more information, then he can e-mail us. He can contact us through PSS, and we will be happy to work with our product group and get any testimonials that we have made available.

Otto: Great. Can you set up a report so that the user selects the option? So basically, after we run the report, after it's cached, will it put the cached report in there unless we specifically ask for a refresh?

Karthik: The cache and the render on-demand are mutually exclusive options. You can't have "render using the most refreshed copy" and caching selected at the same time. So when you do have this cache enabled, subsequent access to the report is going to be pulled back from the cache itself. So I don't think you can configure it down to the level that you are looking at.

Otto: The next question: The program goes to an http site. What would it take to get to an https site for greater security?

Karthik: That's very straightforward. When you install Reporting Services, and when you get to the screen where it asks you about information to publish the Report Server virtual directory, there is a little check box down at the bottom that says, "Enable SSL." By default it is selected, but if you clear that selection, then the access is going to be to http. What that essentially requires is the certification to already be configured and installed in IIS. So if you try to select the check box, but you do not have an SSL certificate installed on an IIS installation, it will not allow you to proceed beyond that stage.

If you are in a situation where you've gone ahead with the default http installation and then you want to move into https, then it's a question of altering a Report Server configuration file to enable https. Not just that, but you also have to take care of the IIS configuration aspect, where you need to install the server certificate and enable SSL at the IIS level. So after you enable SSL at the IIS level, it's then a question of modifying a Report Server configuration to enable SSL.

Otto: The next question: What could theoretically be handled from a SQL Server database? Some examples are item-level role security and schedules jobs, for example.

Karthik: Essentially, the way to look at this is that the Reporting Server catalog, which is what gets stored into the back-end SQL Server database, is nothing but a store for all the metadata that is required for the Reporting Services functionality to be exposed to users. The easiest way to understand this is to launch an instance of Query Analyzer, connect to your back-end data server in which you installed the Report Server catalog, and then expand the user tables under the Report Server database in that instance.

If you get into the Report Server database, you get many user tables, which sort of map many of the table names and map one-to-one to the objects that we introduced you to during the course of this presentation. So we'll have tables that show scheduled information, tables that store event information, tables that store security settings and user policies, tables that actually store report items, tables that store catalog information, like what is present on a Report Server, names of folders, reports, and report resources, and tables that store the snapshot data, subscription data — so basically all the metadata that you work with.

As you get more familiar with working through the Report Manager and the Report Designer, you'll get familiar with a number of the concepts and objects that you're going to be dealing with over a period of time. So when you map back to the Report Server database and take a look at the catalog, you'll notice that all those tables pretty much map one-to-one to the entities that you work with, and are used to capture and store metadata associated with those entities. So the best way to understand what's going on here is to get some more hands-on time with the management aspects and the design aspects, and then explore the Report Server database on your own, look at the catalog tables, and see what sort of data is being stored back there.

Otto: Is there a location where I can find any sample projects or code?

Karthik: Currently, for those of you who haven't already done so, if you go up to www.microsoft.com/sql/reporting, there's a link from there where you can download an evaluation edition. The evaluation edition is like a full-feature version. It has all the features that are there in the Enterprise edition. The only thing is it has a time limit set on it, which means that after a certain period of time it's going to expire.

The evaluation edition also comes with books online, the online documentation, which is really excellent because it contains a lot of advanced information about some of the topics that we touched on today. It also ships with a number of samples. So you have sample reports, and then you have sample extensions. You have a sample data extension. You have a sample delivery extension. And you have some sample applications that show you the Web service API. So you get the whole package when you download the evaluation edition.

Looking at those samples will give you a better idea of how to use the programmatic interfaces, how to design reports, and how to apply some of the report designing techniques that we spoke about. So there are a lot of good samples and information that are available in the download itself.

Otto: The next question: Is there any integration with or are there default reports for SharePoint™?

Karthik: SharePoint integration was not a goal for version 1 of this product. If users want to be able to have Web parts that display reports, then they're going to have to use the Content Viewer Web part and set its URL to access a particular report and render it. Beyond that, there are no other features to specifically integrate with SharePoint in version 1.

Otto: The next question: Is there a UI or query tool for Multidimensional Expression (MDX) query?

Karthik: This is a very frequently requested customer feature, and it's on our on our feature list for the next version, which is the Yukon release. But at this point in time, if you are looking for a visual tool to design MDX queries, you're going to have to rely on the MDX sample application that ships with SQL Server Analysis Services, or any other graphical MDX tool that you might be comfortable with using, or which you're already using.

In terms of writing these queries inside the designer itself, we showed you the Visual Query Designer when we walked through a screen shot of the Report Designer. Another query designer that's also available is the generic query designer, and all that gives you is a little text box where you have to manually type in queries, and where you can execute those queries and check the results. The graphical designer cannot be used when accessing Analysis Services data sources, and you're going to have to switch into the generic query designer and then type in queries manually.

One thing that we are seeing a few customers doing is using the MDX sample application to test their MDX queries outside the Reporting Services environment. Then after they have it working the way they want it to be working, they copy and paste into the Report Designer to configure the DataSet. But that, again, has some restrictions associated with it. Because if you want to get down to multiple levels, like you want to have on roles, on columns, and on page expressions, then those types of queries would not run inside MDX, because MDX at the most supports two levels, maybe like on roles and on columns. Then you would run into some limitations. By and large, if you're looking at the more common types of MDX queries, you would be able to use the MDX sample application to test those queries and then plug those queries back into the Report Designer.

Otto: The next question: Is there a lightweight report editor that can be used without Visual Studio 2003? I think you kind of touched on that.

Karthik: I assume that what you're talking about is an editor for the RDL file itself, rather than just looking at a straightforward text file. So the answer to that is at this point there is no standalone utility that allows you to do that.

I think you also had an aside to your question where you spoke about e-mail configuration. There's a whole section in the online documentation that talks about configuring e-mail delivery. It's essentially going to come down to you opening up your configuration file and making some changes to the delivery extension element that maps to the e-mail delivery component. The online documentation of configuring e-mail delivery contains information on what the individual parameters are, how you would specify values for those parameters, and how you would set up event delivery to work in your environment. So I recommend looking at the documentation.

Otto: Can the Reporting Services be used on the extranet? It appears to me that people on the Internet might not be able to see the report.

Karthik: We are going to be releasing a white paper on this particular topic. I don't have much information on that at this point in time, so we should probably wait for the product team to release the white paper on deployment scenarios, where they're going to address scenarios like extranet and Internet deployment, and we'll probably have more definite information when that gets published.

But by and large, you should be able to use the methodology that we discussed earlier, which is to enable Basic authentication on your Report Server virtual directory, and then create accounts in your domain that have restricted access that needs to be exposed to those external users. You can then pass that account information on to those users, and then when they access your site, when they are prompted, they specify the credentials that you supplied them. But you've configured role-based security on the Report Server to ensure that those IDs can only access the items that those guys are meant to access. You can further restrict the permissions by granting browser-only permissions, which means they can only view the report and report items, but they can't actually modify any of the properties or change settings on the reports. So you should be able to use Basic authentication to open up the server to extranet scenarios, and that's something for you to look at.

Otto: It looks like we only have time here for one final question. For the remaining questions, we'll take those offline and follow-up directly offline. Also, for the benefit of the rest of the audience, we'll make sure that these answers make it into the transcript.

Karthik: When we spoke about deploying reports to a Report Server, there are multiple ways in which you can do that. For instance, you can send your customers your report project, have them open it up in an instance of Report Designer, and publish it to the local Report Server. Alternately, you can create your own custom installation script, which calls into the Web service API to publish reports and report items. Then you can send them that script, which they can then run on their servers to deploy the reports over to their Report Server.

So you can set up the installation utility as a GUI application, like a Windows Forms application, that calls into the Web server's API, which offers features like browsing over to your file, collecting particular RDL files, and then publishing those RDL files to a Report Server. Or you can set the whole thing up as a command-line script, which can be executed using the RS.exe command-line tool that ships with the product. There are options that you can do. You can either build off the Designer, or you can write your own management scripts and applications that call into the Web server's API to do the deployment for you.

As far as the e-mail delivery itself is concerned, the delivery extension is going to be primarily invoked by the Window service, because delivery extensions are called to deliver reports that are generated off subscriptions, and subscription processing is going to be handled by the Windows service. So if you were to run into any exceptions or problems with the e-mail delivery extension itself, you can look at the Windows service log file in the folder that was issued on the slide deck on diagnostics and tracing. If you open the Windows service log file, you will see any call stacks or exceptions that were generated when trying to instantiate and use the e-mail delivery extension.

Other than that, you also need to be aware that the e-mail delivery extension is basically a wrapper that sits on top of the CDO object model, which is commonly used to program Exchange-based applications. So one way to isolate problems is to program directly against the CDO API, using the same set of configuration settings. And it will determine whether you can get a particular e-mail delivery to work over your CDO API. If you can get it to work over there, then it's probably something Reporting Services specific.

If you're having a problem at the CDO layer itself, then that would imply that this is probably not a Reporting Services-specific issue, but its more on the CDO API configuration layer, which you would then need to troubleshoot offline. So that can be something like a problem with your mail server, or a problem with your mail server configuration, and issues like that. But by and large, the log files give you a lot of good diagnostic data, and if you want to get really deep diagnostic data, then you can configure robust tracing on each of the components. Or for e-mail delivery, you would set that on the Windows service, and then examine the Windows server log file to see what information you get in terms of call stacks and other data.

Otto: Thank you.

With that, we've run out of time today. I certainly wanted to thank our listeners for coming out and checking out the broadcast. We certainly hope that this information was helpful to you and your business. I wanted to thank Karthik for coming out and giving us a great presentation as well.

I look forward to seeing everyone again in the near future. Thank you, and have a great day.

Karthik: Not in v1.0. We use a third-party chart control licensed from Dundas (www.dundas.com). We have found this to work well for the supported chart reporting scenarios and requirements.

Karthik: Please see http://www.microsoft.com/sql/reporting/howtobuy/faq.asp for licensing information