4. Toggle the visibility of the hidden rows.

5. Show the SubCategory totals when the detail is hidden. Because you still want to be able to see the SubCategory totals when the detail rows are hidden, copy the subcategory totals from the footer row to the header row and then use the same toggle that displays the detail rows to hide the totals in the header row so that the totals are hidden when you drill down. Proceed as follows: a. Click on the SalesAmount cell in the SubCategory footer row and hold down the Ctrl key as you click the CumSales, Units, and Margin % cells. With all these columns highlighted, right-click and then select Copy from the list that appears. b. Right-click the SalesAmount cell in the Subcategory header row and click Paste. This paste does not copy the Visible property settings, so you need to set these next. c. For each of the copied cells, in the Properties window, expand the Visibility property and set the ToggleItem property to SubCategory. 6. Preview the final report. Your report should look something like the one shown in Figure 15-2. Figure 15-2: Preview of the Product Profitability report showing a drill-down with Hidden and ToggleItem properties set. 276 Part V: Developing Advanced Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

4. Toggle the visibility of the hidden rows.

4. Toggle the visibility of the hidden rows. This is the maneuver that makes the drill-down effect really work. The second component of the Visibility property is ToggleItem. This property enables you to interactively toggle the Hidden state of the specified report items. When you designate a report item as a ToggleItem, it gets a small plus sign next to it when the report renders. Clicking that plus sign toggles the visibility of any linked report item. Proceed as follows: a. Right-click to the left of the Detail row to select the entire row and then click Properties. b. In the Properties window, expand the Visibility property, and in the ToggleItem property drop-down list, click SubCategory. This enables you to click on the SubCategory and show the detail product rows underneath. c. Right-click to the left of the SubCategory footer row to select the entire row and then click Properties. d. In the Properties window, expand the Visibility property and in the ToggleItem property drop-down list, click SubCategory. As a result of clicking on SubCategory, you will see detail rows and the subcategory footer showing subtotals as well. Figure 15-1: Layout of the Product Profitability report with Customer parameter showing all product levels of detail. Chapter 15: More About Interactive Reporting 275

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Chapter 15 More About Interactive Reporting In This

A drill-down report is an interactive report that allows you to expand and collapse sections of the report to uncover more detail as desired. The technique is useful in delivering information where you want to highlight the general trends but, at the same time, allow investigation into more detail within the same report. In the rest of this chapter, I show you this concept. Hiding items in a report Each item in a report has a set of properties that controls the visibility of the item. You can set items to be visible or invisible (but not translucent). These properties are at your disposal for hiding items on a report, conditionally hiding data based on other data in the report. You can conditionally hide data by clicking a control that toggles items between visible and hidden. For example, you can create a drill-down report that shows summary data when the report is first loaded and shows detail rows when users click a particular. The secret of this technique is to use the two components of the visible property namely, Hidden and ToggleItem. Drilling down on reports Suppose you want to show a report with the same columns as the Product Profitability report in Chapter 7, but for the rows, you want to show all detail from product category, subcategory, and actual product sold. Furthermore, you want to filter on Reseller (which you can call Customer for our purposes) to see the product sales for a specific customer. Finally, to create a drill thru effect, show detail to the Product SubCategory level initially and provide the user the options to drill down in the same report to see product detail. Follow these steps: 1. Design the report with all levels of detail desired. Begin with the most detailed level report. This is shown in Figure 15-1. In this example, I added an expression to change the color of the BackgroundColor of the Margin % column based on its values. 2. Hide the Product Detail and the SubCategory footers by right-clicking to the left of the Detail row and Subcategory footer lines and selecting Properties from the menu that appears. Then expand the Visibility property and set the Hidden property to True. 3. Preview the report. Notice that because detail and SubCategory is hidden, they will not show on the report. Instead, the SubCategory headers show as well as the subtotals.= 274 Part V: Developing Advanced Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 15 More About Interactive Reporting In This

Chapter 15 More About Interactive Reporting In This Chapter Dynamic visibility in reports Drill-down reports Using navigation techniques Using document maps Using ToolTips Interactive sorting In Chapter 7, I examine some of the capabilities of Reporting Services that enable users to control the report when the report is executed. This means one report can serve many needs. It also provides for interactivity with the user in reporting. By interactive, I m referring to the fact that the user can click on something in the report as he or she is viewing it and the report responds with an immediate action. This chapter deals with more interactive features of reports. In this chapter, I review how you can use the visibility to show or hide report items at runtime. I also describe some capabilities of navigating different types of hierarchies and using visibility to provide some interesting interactive effects. I also cover the use of ToolTips to provide cues to the user and document maps to assist in navigating large reports another interactive aspect of reporting. Using Visibility Options If you ve read the book The Invisible Man, you may have marveled (as I did) at what you could accomplish if you were invisible. You could influence events by being present but invisible. In Reporting Services, you can control the visible properties of almost any control in a report. Specifically, this feature can help you in creating drill-down reports.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Part V Developing Advanced Reports

In this part . . . In this part, I suggest techniques for you to arrange data in a report to ensure that your report consumer gets the point! You start to master the use of interactive components that the end user can use when the report is presented. Drilling down, navigating seamlessly to other reports, and constructing natural intuitive reports that even executives can understand create some exciting opportunities for reports you can easily build. To make your choices more plentiful, there are even ways to query OLAP databases that allow extremely fast response time in reporting from potentially huge underlying databases. Microsoft Reporting Services 2005 has so much capability to advance your reporting capability.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Using snapshots When you request a report, Reporting

Using snapshots When you request a report, Reporting Services merely retrieves and renders the snapshot. You then see the data and layout that were current for the report at the time the snapshot was created. As I mention in Chapter 10, using a snapshot approach helps to improve the overall performance of your reporting environment in a number of situations: Providing multiple people with access to the same data at the same time. For example, finance, marketing, and sales want the monthly financial reports when they are produced. You can specify that financial reports be set up as snapshots that execute each month. Preventing arbitrary report execution. For example, an invoice report requested during business hours slows overall system performance. In order to alleviate the system load and avoid inconsistent results, you can execute the invoice report as a snapshot every evening. Controlling long-running reports with queries that take a long time. For example, the weekly sales report runs a long time and each salesman wants to know results the first thing Monday morning. You can specify that the weekly sales report run every Sunday evening so that it s ready to go on Monday morning. Using filters for greater flexibility. Changing the filter values filters only the current snapshot of a particular report. Caching in on performance Caching is a performance-enhancement technique. To enhance report server performance, you can preload the cache. To preload the cache with a collection of parameterized report instances, you create a data-driven subscription that uses the Null Delivery Provider. Preloading the cache is achieved through a specialized rendering extension called the null rendering extension. When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination. This feature is especially useful if you want to cache multiple instances of a parameterized report in which different parameter values are used to produce different report instances. Note that you can only specify query-based parameters on the report. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure or drive through a subscription definition. Chapter 14: Optimizing Report Performance 269

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

size is determined by the row set that

Specify item-level security to allow you to control access to the report. By default, users can open any report that they can view in the folder hierarchy. Even if you configure a report to run as a snapshot, users who can view the report item in a folder can open the report. To restrict access to the report, edit item security by replacing the default role assignments with new ones that allow access to just those users who need to create the subscription or manage the report. Leverage the query and execution timeout settings for long-running reports that process during the business day. You want to ensure that the report does not unduly hamper overall system performance during a normal business day of report processing. Rendering reports Before you configure report distribution, you need to know which rendering clients can accommodate large documents. The recommended format is Adobe Acrobat Reader (PDF), but you can choose from any format that supports pagination. You can specify the format when you define how the report is distributed. Distributing reports You can distribute reports via subscription an important consideration for working with large reports. Through a subscription definition, you control how the report is distributed and rendered. You can use either a standard subscription or a data-driven subscription to deliver the report. You can also configure your subscriptions to be rendered as a PDF in a file share delivery. Use a desktop application to work with the report after it is generated. Set permissions on the file share to determine who can view the report. Note that after the report is on the file share, it is no longer controlled or secured by Reporting Services. Using parameters and filters Because the full set of data is retrieved and then filtered on the report server, the report may not perform as well as a report that filters data at the source using query parameters. I note in Chapter 7 that for any report a query must be executed and the dataset regenerated each time you change a parameter that modifies the SQL query. It is possible to get better reporting performance by pulling a larger set of data into the dataset and then filtering the rows that will appear in the report. 268 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

size is determined by the row set that

size is determined by the row set that comes back from the query. To estimate the size of a report after it is processed, review the row count returned from the query. If it is many thousands or hundreds of thousands of rows, I provide you with some recommendations in the list that follows. For reports that contain volatile data, report size can change dramatically from one report run to the next. Be sure to monitor the data source to determine how data volatility affects your report and whether you need to follow the steps I prescribe for large reports. Here are some general recommendations when you re considering how to configure and run large reports: Design the report to support pagination. The report server sends a report one page at a time. If the report includes pagination, you can control how much data is streamed to the browser. Configure the report to run as a report execution snapshot. Use this option if you can t add page breaks. Do not set a time-out value for report execution. Use a schedule to determine when the report data is refreshed. Never run a large report on demand because it almost never succeeds. Configuring a report to run as a report execution snapshot prevents it from running on demand. The HTML rendering format used to initially render a report opens the report in a browser, and most browsers cannot accommodate very large documents. For example, a report that contains 5,000 rows of data almost certainly cannot be viewed in a browser in a single page. Consider distributing a report as a file share on a file directory. If the report is very large, it will hang the browser when a user opens the report in Report Manager. Configure the report to use a shared data source if you want flexibility in determining whether the report is processed. One advantage to using a shared data source is that you can disable it so that it cannot be used to get data for the report. Disabling the data source prevents report processing. Use stored credentials for the data source connection for security and to enable data-driven subscriptions for the report. Disable report history (optional) if you want to conserve disk space. The recommendations for report distribution offered later in this chapter (see the Distributing reports section) provide an alternative to storing a large report in report history. To disable report history, clear all the check boxes on the History properties page. Configure the report to use item-level security. Limit access to users who define the subscription and manage the report. Chapter 14: Optimizing Report Performance 267

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services