SQL Server Reporting Services

Database reporting solutions are really applications with a user interface, query components, and a data source. Generally speaking, these fit into the client/server application model. Until recently, enterprise-level reporting products were only offered by third-party companies such as Crystal and Business Objects. A number of specialized reporting products, such as Brio and Hyperion, are also available for multi-dimensional, decision-support databases. Microsoft has offered desktop reporting capabilities in Access and Excel, but it wasn't until 2003 that Microsoft released a serious, enterprise-ready reporting extension for SQL Server 2000 called SQL Server Reporting Services. It was originally intended to ship with SQL Server 2005 (which it does) but was completed ahead of the rest of the SQL Server 2005 components. Reporting Services is for serious reporting but it's pretty easy to use.

A brief tour of SQL Server Reporting Services is provided here. You'll see how to use some of the query techniques you've learned to support report features. This will be an opportunity to apply some of the techniques and practices you've learned earlier in this book. I'll use Reporting Services to show you how to create and use parameterized queries.

Reporting Services Architecture

Reporting Services is really quite different from other products for a number of significant reasons. The reporting engine runs as a Windows service on a computer configured as a web server. The core component is an XML web service sitting on top of ASP.NET and the .NET Common Language Runtime (CLR). This is a highly scalable and extensible architecture, meaning that features and additional capabilities can be added and that it can be expanded to more capable hardware and to multiple servers to handle increased workload. Reports can be integrated into a variety of application types and viewers, but it is most commonly used from a web browser. Out of the box, Reporting Services can render reports to different formats including variations of the following:

• HTML

• Adobe PDF

• Excel

• TIFF bitmap

• XML

• CSV text

Reports can be viewed on demand or saved to a file. User can subscribe to reports, resulting in the report being sent by email or to a file share at scheduled intervals.

In addition to these standard features, Reporting Services is programmable. Application developers can add additional capabilities for report rendering, data access, security, and delivery. They can also programmatically manage the report server and render reports, embedding report content into custom applications. In a nutshell, Reporting Services has an enormous feature set and with a little custom programming, can be made to do most anything imaginable.

To acquaint you with the Reporting Services design environment, I will walk you through the steps to create a simple report with basic features.

Because our focus is using SQL queries to drive reports, you will create two queries that utilize a parameter to filter report data. Before getting started, here is a quick disclaimer. The book you are reading is not a book on Reporting Services or Visual Studio, so I will not provide an in-depth explanation of all the features you're about to see. Because Visual Studio and Reporting Services are large, complex products, I can't guarantee that I can cover every detail in this short tutorial to get you completely up-to-speed on designing reports with Reporting Services.

Note

The Wrox book, Professional SQL Server Reporting Services (Wiley Publishing, Inc.), provides an excellent explanation of SQL Server Reporting Services. The first edition is based on SQL Server 2000, and the second edition includes SQL Server 2005. If you would like to have a comprehensive guide to Reporting Services and report design, I recommend that you pick up this book.

The Report Designer

The Report Designer is an ad-on design environment that integrates into the Microsoft Development Environment. This means that it can be used in Visual Studio 2003 and 2005. The Report Designer is opened when you create or open a report project. Aside from some minor cosmetic differences, your experience creating simple reports should be similar whether you use Visual Studio 2003 with SQL Server 2000 Reporting Services or Visual Studio 2005 for SQL Server 2005 Reporting Services.

To begin, open Visual Studio and create a new project. Depending on the installation options, different project types may be available. Select Business Intelligence from the Project Types list. For you to see the mechanics of report design, I don't want to use the report project or report wizards. Select the Report Project icon from the Templates list on the left. I originally created this demonstration using the Report Designer for SQL Server 2000 and then re-created it for SQL Server 2005. I've included screen captures from both. As you work through these screens, note that there are only subtle, cosmetic differences between those in the Visual Studio 2003 Report Designer (used with Reporting Services for SQL Server 2000) and the Visual Studio 2005 Report Designer, for SQL Server 2005. The 2005 New Project dialog is pictured in Figure 14-5.

Figure 14-5:

The Name and Location values can be set as you deem appropriate. A report project places data-source and report definition files into a single folder, and this folder can be stored pretty much anywhere you like. Unless you have another location for your projects, just append a folder name to the default path in the Location box and then enter a project name in the Name box.

Along the left and right edges of the Visual Studio window are a number of dockable utility windows. Each of these windows can be "pinned" in place or moved. The default behavior is for each of these windows to auto show and hide as the mouse is moved over the icon and then off of the window. Although this nifty feature effectively conserves screen real estate, it can be a bit annoying when the window you are working with suddenly goes away. To show the Solution Explorer (if it's not already pinned out), just float the mouse pointer over the Solution Explorer icon. To pin down a window so it doesn't perform this disappearing act, click the little pushpin icon so it becomes oriented vertically rather than horizontally. Depending on whether or not windows are pinned, the window icons will either be along the edge of an auto-hidden window, displayed on either side of the design environment, or along the top of each window if it is shown.

Visual Studio 2005 has added window-docking aids that show up when you drag a dockable window around. This new feature allows you to see exactly where a window will be docked before you let go of it.

The Solution Explorer window is shown in Figure 14-6. Begin by adding a new shared data source. Right-click the Shared Data Sources folder and then choose Add New Data Source from the pop-up window.

Figure 14-6:

The concept of a data source is quite simple. A shared data source is actually a small XML file with an RDS extension, containing the connection information. Enter or select the name of your SQL Server in the first box. If you are using Reporting Services with SQL Server 2000, select the AdventureWorks2000 database, as shown in Figure 14-7. If you are using Reporting Services with SQL Server 2005, select the AdventureWorks database.

Figure 14-7:

That's all there is to it. After entering the server name, verifying your authentication options (you can leave this alone if your server is configured for Windows authentication), and selecting the database name, just click the OK button.

The next step is to add a new report. From the Solution Explorer, right-click the Reports folder. From the pop-up menu, select Add and then Add New Item. . . , as shown in Figure 14-8.

Figure 14-8:

There are two options for creating new reports, which include using the report wizard or building the report from scratch. You won't learn as much by using the wizard so I'm not going to have you use this method. You may want to go back and use the report wizard on your own. This will automate the process for creating a few styles of canned reports.

In the Add New Item dialog, select the Report icon on the right and give the new report file a name, as shown in Figure 14-9. This report will list products grouped by category and subcategory. Name the report Products by Category. Click the OK button when this is done.

Figure 14-9:

Now step back and take a look at the Report Designer screen. Again, if you are not seeing the same thing as that shown in Figure 14-10, namely the utility windows along the left and right; just float the mouse pointer over the corresponding icons to make these windows appear.

Figure 14-10:

The Report Designer has three tabs along the top, which represent three distinct design surfaces. The first tab is the data set designer, used to create and work with queries. In just a bit, you'll see how to use the report Layout and Preview tabs. Again, there are only a few cosmetic differences between the Visual Studio 2003 and 2005 versions.

Report Queries

In Reporting Services, a query is called a dataset (not to be confused with an ADO.NET dataset, used in .NET programming). To create a new dataset or query, drop down the Dataset list and select <New Dataset. . . >. This opens a dialog box for setting up the dataset, shown in Figure 14-11.

Figure 14-11:

Replace the default dataset name with something meaningful. I typically like to use a name that describes the data. This may be the same as, or similar to, the name of the report. Note that the shared data source is selected for you. For the command type, I will typically either choose Text if I plan to create an ad-hoc SQL query or Stored Procedure if I will use an existing SQL Server stored procedure. You can type a SQL expression directly into the Query string box or leave it blank for now. Either way, the query can be designed or modified after closing this dialog. After changing the dataset name to Products_And_Categories and making no other changes, click the OK button.

The default query view is the generic query builder. This is a simple interface with few features. You can simply type a SQL expression into this window. The generic designer doesn't offer much help. You can switch to the graphical query designer view using the third icon from the left. This button is in the "pushed" position while in the generic query view and in the "up" position when using the graphic query designer, as shown in Figure 14-12.

Figure 14-12:

Because you're already familiar with the graphic query designer, I'm going to give you only high-level instructions. Begin creating the query by adding these three tables to the diagram pane: ProductCategory, ProductSubCategory, and Product. If you are using SQL Server 2005, these three tables are all in the Production schema. When you add these three tables, inner joins are added because of the referential constraints defined in the database, as depicted in Figure 14-13.

Figure 14-13:

Add columns to the SELECT statement by checking them in the diagram pane in the following order:

1. ProductCategory.Name

2. ProductSubCategory.Name

3. Product.Name

4. Product.Color

5. Product.ListPrice

The query should look like that shown in Figure 14-14 in the graphical query designer.

Figure 14-14:

Creating a Parameterized Query

This query is completely unconstrained and will return all records. Unless you have a controlled set of low-value data, you will typically want to limit the result set. I'd like to build some logic into my query so that if a category value is selected and passed into the query only product records matching the selection will be returned. I also want some way of indicating that I want to ignore the category and return all rows. A few different ways exist to approach this common requirement. I want to filter on the key value for categories, the CategoryID column. Because this is an Int data type, I want to limit all of the values I work with to numeric types. Whenever possible, I try to avoid the use of Null values to perform business logic. I need to have a special value that doesn't occur in the result set to indicate that I want to return all rows. Rather than mixing data types or using a Null value, I'll designate –1 as my magic number for all records.

Using the graphic query design tools, modify the query so the CategoryID column has two conditions: the column value must either match the @CategoryID parameter or the @CategoryID parameter is equal to –1. Now that you have mastered Transact-SQL, you might feel like using this technique is beneath your abilities. However, I want you to see how the query designer behaves (just in case you forget something and need to go back to it some day). Note the placement of these two conditions before you move the cursor to another cell. Figure 14-15 shows the graphical query designer environment completed to this point.

Figure 14-15:

Now click another cell in the column designer grid. Observe both the column design grid and the SQL pane, as shown in Figure 14-16.

Figure 14-16:

The designer moved the second parameter reference into the Columns area along with the corresponding equation. This is a very simple example but keep this in mind when you are building more complex parameterized queries. This is a common scenario in the reporting world. Imagine a report query with five or six parameters, each with similar logic. You could have dozens of possible combinations of literal values and "all record" indicators. At some point, I find that the graphical query designer begins to work against me, and will often mess up my logic expressions. In such cases, I typically use the graphical tools to get to this point, switch back to the generic designer, and then write the rest of the WHERE clause by hand. Remember to make explicit use of parentheses to clarify and control the grouping of operations when nesting and combining AND and OR logic.

The main query is done. To test this query and view results, click the Execute button on the toolbar (the dark red exclamation mark). You will be prompted for the parameter value. Enter a low, positive integer value to see products for a specific category or enter –1 to see all product records, regardless of the category, as shown in Figure 14-17.

Figure 14-17:

Creating a Parameter Lookup List

To test the product query and filter by a category, you used to either have to know the category primary key values or take a wild guess until you found the one you were looking for. Entering –1 for all records isn't particularly intuitive either. To make this a better experience for users, it helps to provide a lookup list of product categories for selection. This list will include an item to select all products as well.

Begin by creating a new dataset. Drop down the dataset list on the data design tab and select <New Dataset. . . Just like before, the Dataset dialog opens. Just change the name to Category_List, as shown in Figure 14-18, and then click OK.

Figure 14-18:

After the Dataset dialog is closed, the designer window is cleared and takes you to the generic query designer. This will be a simple query, so I'll just type the script directly into the SQL pane. Here's my query:

 SELECT     - 1 AS ProductCategoryID, ‘(All Categories)’ UNION SELECT     ProductCategoryID, Name FROM       Production.ProductCategory ORDER BY Name  

If you are using Reporting Services for SQL Server 2005, the query designer displays a special grid for the UNION query, showing the literal values for the first expression and then the table selection for the second SELECT expression, as shown in Figure 14-19.

Figure 14-19:

The results for the query are displayed in the lower pane of the designer. Note that the first row displays the special value, –1, to indicate that product records are not to be filtered by category.

The tools for SQL Server 2000 don't handle the UNION query quite as gracefully. The first thing the query designer does is show a warning dialog that, if you didn't read carefully, might appear to be an error. This dialog simply explains that the graphical query designer isn't equipped to display the design of a UNION query (see Figure 14-20).

Figure 14-20:

This is fine. Just click the Yes button to make the warning go away and keep your changes. The designer window will show the results but makes no attempt to represent the query in any other form except SQL text.

Designing the Report Layout

Think of the report layout as a canvas representing the body of the report. There are three dockable utility windows on the left side of the Report Designer in Visual Studio. These include the Toolbox, which contains report items (similar to controls in application development projects), the Dataset window, and the Server Explorer. The Dataset window, pictured in Figured 14-21, differs between Reporting Services for SQL Server 2000 and 2005. As you see in the Visual Studio 2005 designer, multiple datasets are displayed on a tree view. In Visual Studio 2003 (the designer for Reporting Services 2000), this window presents a drop-down list and iconized list of fields for only one dataset at a time.

Figure 14-21:

Switch tabs or hover over the Toolbox icon to show the Toolbox window shown in Figure 14-22.

Figure 14-22:

In design, standard report items are placed on the body and these items may be bound to the fields in a dataset. These are known as scalar report items because an instance represents a single value. Report items include the following:

• Textbox

• Rectangle

• Line

• Image

There is another class of report items called data range items. These items render repeated sections for every row returned from a dataset query. Scalar report items are placed into cells or sections of data range items. These include the following:

• Table

• List

• Matrix

Additionally, subreports and charts are special-purpose and although they are often categorized as data range items, they have unique behaviors.

Reports are rendered from top to bottom, and items placed on the report body will be rendered only once unless they are placed within a data range object. For this reason, if a text box in the body of the report references a dataset field, the expression must use an aggregate function to resolve to a scalar value. To keep things simple, examples will work with just a few report items. Tables are the most common data range item used in basic reporting and text boxes are the item of choice for scalar values.

Start by resizing the report body. Use the mouse pointer to find the right edge of the report body. The pointer will change to an east-west pointer icon (that is the official name). Use this to drag the edge and resize the body to a width of 8 inches. If your machine is configured to a different locale than mine, your scale may be in centimeters. In that case, make the report body 20cm wide.

Next, place a text box in the upper-left corner of the report body by either dragging and dropping the text box from the Toolbox window or by selecting the text box item and then drawing it on the report body. This text box will not be bound to data but will display the name of the report. Type =Globals!ReportName into the text box.

For more help with expressions, you can also right-click the item and then select Expression. . . to open the Expression Builder window. This is one area of the designer that has changed between the 2000 and 2005 versions. The expression builder in the Reporting Services 2005 designer has several enhancements over the 2000 version shown in Figure 14-23. For our purposes, this doesn't matter because we're only adding a very simple expression.

Figure 14-23:

Set the font properties for the text using the properties window or the Report Formatting toolbar.

So far, your report should look like that shown in Figure 14-24.

Figure 14-24:

This report will display a list of products in rows and columns, grouped by category and subcategory. A columnar report calls for a table data range item. Drag a table from the Toolbox and drop it onto the report body just below the text box. Your report designer should look similar to Figure 14-25.

Figure 14-25:

I need five columns and a table has three by default. The easiest method to add columns is to right-click a column header and then use the menu options to add columns to the right or left of the current column. Before you do that, resize the existing columns to make room. The table designer behaves much like Excel. In the column header, grab the border separating two columns or the right edge of the rightmost column in the table. Add two additional columns. It's very common while designing a table to make it too wide. This pushes the report body out to fit. This is no problem. Just resize the columns and then the report body accordingly.

Adding Fields

This is the easy part. Show the fields list in the Dataset window and drag and drop fields into cells in the detail section of the table. Figure 14-26 shows the mouse pointer just prior to dropping the first field.

Figure 14-26:

When using this technique, the designer not only sets the value of the text boxes in the detail row to the right field expression, but it also sets the text in the header row text boxes. As you see in Figure 14-27, fields with mixed-case names (such as SubCategory) are converted to title case. Every cell in a table contains a text box unless you replace it with a different report item. Different types of values should be formatted appropriately. I would like the currency value for the ListPrice field to be right-aligned and formatted as currency. Select the last column in the table by clicking the column header and then use the Report Formatting toolbar to right-align all of the cells in this column. I've also made the headers bold. The easiest method to set the properties for all cells in a row (in this case, the header text) is to use the row selector. Use this method to select the entire header row and then click the Bold button on the Report Formatting toolbar. To see the row and column selectors, you must first click on any cell in the table.

Figure 14-27:

Setting Format Properties

To set the format for the ListPrice text box, select the text box and then right-click to open the Textbox Properties dialog. In Figure 14-28, you can see the table with the ListPrice cell selected prior to opening the Properties dialog.

Figure 14-28:

All of an item's properties may be set using the properties utility window as well. In the Textbox Properties dialog, shown in Figure 14-29, indicate that this item is to be formatted as currency by selecting the Currency format from the Standard format list box. Click the OK button to return to the designer.

Figure 14-29:

Configuring Report Parameters

The last step in completing the first iteration of the working report is to configure the CategoryID parameter I defined in the main dataset. By default, the user would have to type the CategoryID value into a text box in the report parameter bar. I'd like to make things a little easier for my user by listing the available product categories by name, along with the option to view products for all categories. You'll recall that I created a dataset for this purpose using a UNION expression.

With the Report Designer in layout view, drop down the Report menu on the Report Designer toolbar and select Report Parameters. . . , as shown in Figure 14-30. The Report Parameters dialog opens and you will see that the CategoryID query parameter is now listed as a report parameter.

Figure 14-30:

You defined a parameter in the query by prefixing the name with the @ symbol. This query parameter is now a report parameter. Just to clarify, a parameter defined in a query is known as a query parameter. Parameters can also be defined within the report, separate from query parameters. All query parameters are part of the report parameters collection.

Note

Not all report parameters are necessarily query parameters. I won't go into this any more in this book, but you can do a lot of very creative things with report parameters that go way beyond the scope of simple, query-based filtering.

In the Report Parameters dialog, change the Prompt for this parameter to a friendly label. This is the text displayed in the parameter bar for the drop-down list containing the parameter values. I want to feed the Available Values list from a query. Choose this option and then select the dataset name, Category_List, from the Dataset drop-down. I defined two columns, the CategoryID and the Name. I want the CategoryID value to feed the actual parameter value so this become the Value Field selection, and I want my user to see only the Name field value in the list, so this becomes my Label Field selection.

Click the OK button when your selections look like that shown in Figure 14-31.

Figure 14-31:

The report should be ready for testing. Switch to the Preview tab. Because the parameter wasn't configured with a default value, the report won't render until a parameter value is selected. At the top of the report preview pane the parameter drop-down list is displayed next to the label you defined. Drop down the list and select the first item, labeled (All Categories), as shown in Figure 14-32, and then click the View Report button on the right.

Figure 14-32:

As the report begins to render, an animated icon is displayed. After a few seconds, you should see the first page of the report, as shown in Figure 14-33.

Figure 14-33:

Notice the page count displayed just below the parameter list. If you navigate through the pages, you will see products for all of the categories. Now select one category from the list and click the View Report button again. This time, only products for the selected parameter are displayed, and the page count should be considerably less than before. The report should look similar to Figure 14-34.

Figure 14-34:

It's not an elegant report by any means, and you can dress it up a bit by adding a shaded background to the header row. Switch back to the Layout tab, as shown in Figure 14-35, and select the header row as you did when you set the text to bold.

Figure 14-35:

In the Properties window, find the BackgroundColor property and click the down arrow button to open the color selection list. From the list, select the color Silver, as demonstrated in Figure 14-36.

Figure 14-36:

Adding Groups

Now you can begin grouping the data in the table. If you've worked with other reporting products, you may be accustomed to the idea of the report having banded sections used to group and sort the data. The same pattern applies here but this is a feature of the table rather than the report. Suppose you want to create two groups for category and for subcategory. Groups are created top-down, in order of their hierarchy. Begin by using the right mouse button to click the detail row selector. The pop-up menu displays several options that apply to the table row. Select Insert Group to open the Grouping and Sorting Properties dialog, as shown in Figure 14-37.

Figure 14-37:

Like report items, a group is given a name. Drop-down the list in the first row of the Group on: expression list box, shown in Figure 14-38. You will see a list of field expressions. From this list, select =Fields!Category.Value and then click the OK button.

Figure 14-38:

As you see, this adds two additional rows to the table that will be repeated for each instance of grouped values. As in Figure 14-39, you'll use the group header and footer rows to show the product category and subcategory headings and subtotals.

Figure 14-39:

With the category group in place, also add another group for the product subcategory. Groups must be added in hierarchal order, from top to bottom. Just like you did before with the category group, right-click the detail row selector and add another group, as shown in Figure 14-40.

Figure 14-40:

Select the SubCategory field expression, as shown in Figure 14-41, and then click the OK button.

Figure 14-41:

As you can see in Figure 14-42, when you click the OK button, a group header and footer appears in the table.

Figure 14-42:

You can drag and drop the existing text boxes to different cells within the table. Do this with the Category and SubCategory fields. In the same column, place the Category text box in the group one header row and place the SubCategory text box in the group two header row, within the second column.

Make some additional room for these values. Because the category and subcategory fields don't have to share space on the same row with any other fields, you can set a cell to span adjacent cells to the right. This works much the same way as Excel and HTML tables. Begin with the Category field and drag across the other cells in the row to select them as a group. Right-click the selection and choose Merge Cells from the menu, as shown in Figure 14-43.

Figure 14-43:

Repeat these steps with the SubCategory field, starting with the second cell in the group two header row. Group-select the remainder of the row, right-click and choose Merge Cells from the menu. Change the font size and weight for each of these headings. Go ahead and experiment with these properties to adjust the headings to your own liking. Report design is a little bit of an artistic endeavor. You'll need to try out ideas and test them by switching to the Preview tab.

I won't lead you through the rest of the process step by step. By now you should have a good feel for the basics of report design. In Figure 14-44, you can see the final design. In the group footers for the SubCategory and Category fields, I have added summaries using the Avg aggregate function with the ListPrice field. You'll also notice that I have labeled the SubCategory group footer (Group 2) with the expression:

Figure 14-44:

 =Fields!SubCategory.Value & " average price:" 

This is a Visual Basic expression used to prefix the group footer for the SubCategory field value.

The Category group footer is similar:

 =Fields!Category.Value & " average price:" 

I've merged the cells on these rows to make room for this text. I did not merge the last column to make room for the ListPrice summary. Both of the group footers contain the same expression in the ListPrice column.

 =Avg(Fields!ListPrice.Value) 

Now it's finally time for some cosmetic touch-ups: Note the leading space in front of the footer label text. This is accomplished by using the padding properties of these cells. Select one of these cells (before or after it has been merged) and use the properties window to select the Padding properties group. Use the plus sign to expand the group and increase the Left Padding property to about 15 points (15pt.).

Now modify the borders to for the text boxes and table row. Group-select the cells in the table and then use the properties window to view the BorderStyle properties. Expand this group to reveal the Left, Right, Top, and Bottom properties. Set the Bottom border to Solid. You can also experiment with any of these properties to get different results.

To add a page footer section to the report, select this option from the Report menu. I've added a gray line and two text boxes to the page footer. The text boxes will display the date and time the report was executed and the page number (Page X of Y) at the bottom of each page.

My finished report design looks like that shown in Figure 14-44.

The final product, when rendered to the Preview pane, looks like that shown in Figure 14-45.

Figure 14-45:

Deploying the report to the report server is actually very easy. Before this can be done from the Report Designer, Visual Studio needs to know where your report server is located. If you have a standard installation of Reporting Services on your local computer, do the following.

Using the Solution Explorer window on the right side of Visual Studio, right-click the project icon. This opens the project Property Pages dialog. Make sure that the TargetServerURL property is valid. The path shown in Figure 14-46 is correct for a default development workstation configuration.

Figure 14-46:

In the Solution Explorer, right-click the report icon and choose Deploy from the menu. Assuming that there are no errors in the report design, the report will be deployed to your local server ready for use.

Viewing Report with the Report Manager

When Reporting Services is installed a shortcut is created in the SQL Server group for the Report Manager page. This shortcut can be found under Start All Programs Microsoft SQL Server Reporting Services Report Manager. This opens a page located at http://localhost/Reports, as shown in Figure 14-47.

Figure 14-47:

Report Data Caching

Possibly one of the most compelling features of Reporting Services is its ability to cache report data so that subsequent requests don't require the database to be re-queried. There are a number of ways that reports can be cached and that parameters can be used in combination to refresh cached data and filter cached results.

Report snapshots are a form of cached reports that are completely static. Snapshots are lightweight and simple. This may be an appropriate option for common reports that are produced at regular intervals and usually don't contain parameterized options. Snapshots can be placed into history so that one snapshot doesn't overwrite a previously cached rendering of the same report. Each snapshot is marked with a date and time stamp, and a specific number are typically held in history before they are overwritten. Snapshots are typically generated on a predefined schedule and users don't need to wait while queries run against live data.

Cached instance reports are more flexible than snapshots but take a little planning and design effort. When a report is configured for instance caching, each unique combination of query parameters causes a separate cached copy of the report data to be stored in the report server database. Further, non-query report parameters may be used to filter the data stored in the cache. A cached instance may be configured to "live" for a specific period of time or to expire on a regular schedule. Because the cache is populated when a report is requested, the first user who views the report must wait for the query to run. Subsequent users or requests run against the cache until it expires.

Report Application Integration

Reporting Services can be integrated into applications in several ways. These range from a simple hyper-link to fully embedded reports within custom and commercial applications. Using a hyperlink to open a report is uncomplicated. The following URL opens a report in a web browser window:

http://localhost/ReportServer?/FarmAnimal_Reports/Farm Animal  Sales_BW&rs:Command=Render

Parameters can either be left to be filled with default values, be provided by users, or may be provided in the URL request:

http://localhost/ReportServer?/FarmAnimal_Reports/Farm Animal  Sales_BW&rs:Command=Render&DateFrom='1/1/2005'&DateTo='3/5/2005'&GroupBy=AnimalName  &Animals='Cow','Horse','Chicken','Llama'

This URL opens a browser with the report displayed below a parameter bar, pre-filled with the parameter values supplied in the URL string, as shown in Figure 14-48.

Figure 14-48:

This approach is simple and provides a great deal of functionality. However, this may not be an ideal interface for all reporting solutions. A more customized approach uses an ASP.NET Web Form, such as that shown in Figure 14-49. Sophisticated web controls may be used to prompt users for parameter values.

Figure 14-49:

Parameter values are gathered from these controls and then concatenated into a URL like the previous example. A hyperlink control uses this URL to target an HTML frame on the Web Form. After using the custom parameter interface to choose a user's selection criteria, the link renders the report to the in-line frame embedded in the web page. As far as the user is concerned, this is simply a feature of a web browser-based custom business application (see Figure 14-50).

Figure 14-50: