3. Preview the Report. The completed report is

Matrix column groups cannot have document map labels. Only row groups can have a label. To add a list group to a document map: 1. In Layout view, right-click the list and then select Properties. 2. On the General tab, click Edit Details Group. 3. On the General tab, for the Document Map Label field, type or select a label or an expression that evaluates to a label. The label or the value of the expression appears in the document map. Using ToolTips A ToolTip appears in a report when the user pauses a mouse pointer over a control on an HTML report. This is helpful in providing users some information about something on a report or help in determining the action the user should take on a report. Figure 15-10: Preview the Product Category Information report with a document map for navigating to pages of Category detail. 286 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 Clan Web Hosting services

3. Preview the Report. The completed report is

3. Preview the Report. The completed report is shown in Figure 15-10. This report has a page break after each product category. You can click on one of the product categories listed in the document map to the left of the report and the report will go directly to the page corresponding to the category selected in the document map. You will note that I selected the Components SubCategory from the document map to navigate to that section of the report, which is on the second page of the report. To add other report items to a document map: 1. In Layout view, right-click the report item that you want to add to the document map and then select Properties. 2. On the Navigation tab, for the Document Map Label field, type or select a label or an expression that evaluates to a label. The label or the value of the expression appears in the document map. To add a table or matrix group to a document map: 1. In Layout view, click the table of matrix so that column and row handles appear above and next to the table or matrix. 2. Right-click the corner handle of the table or matrix, and then select Properties. 3. On the Groups tab, select the group to edit, and then click Edit. 4. On the General tab, for Document map label, type or select a label or an expression that evaluates to a label. The label or the value of the expression appears in the document map. Figure 15-9: The Document Map Label field to define a document map for a report. Chapter 15: More About Interactive Reporting 285

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

3. Preview the report and test the link.

To add items to a document map, you associate a document map label with each item. You can use a document map in a report to provide users with a way to navigate to certain areas of the report. When you view an HTML, Excel, or PDF report, a document map appears along the side of the report. Clicking items in the document map refreshes the report and displays the area of the report that corresponds to the item in the document map. To create a document map, you add document map labels to report items. If any report items have a label, a document map is automatically generated when a user views the report in HTML Viewer. To add a textbox to a document map, follow these steps: 1. In the Layout view, right-click the textbox that you want to add to the document map and then click Properties. The Textbox Properties dialog box appears. 2. Click the Navigation tab. 3. In the Document Map Label field, type or select a label or an expression that evaluates to a label. The label or the value of the expression appears in the document map. A good example of when to use the document maps is when you have multiple page reports where you need to navigate directly to a page selected from a list. To do this, follow these steps: 1. Define a document map for the report grouping. The report grouping is set in a cell or textbox in a data region. Rightclick on the cell or textbox on which the report is grouped (and on which there is a page break set after the group) and select the Properties menu item. The Textbox Properties dialog box appears, as shown in Figure 15-9. 2. Enter the expression for the items to appear in the document map. In the Document Map Label textbox, enter the expression for the field values you would like to see in the document map that link to specific sections of the report. In this case, for a report with groups defined for Category, I have defined the document map label to be: =Fields!Category.Value 284 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

3. Preview the report and test the link.

3. Preview the report and test the link. When you click on the source textbox, you should be able to jump to the report at the bookmark ID indicated in the Jump To command on the bookmark link. Adding hyperlinks You can also enable a report to link to a URL that can refer to a file, another report or Web page, or content on the Internet. A hyperlink can be a static URL or an expression that evaluates to a URL. If you have a field in a database that contains URLs, the expression can contain that field, resulting in a dynamic list of hyperlinks in the report. You can add hyperlinks only to textboxes and images. To set hyperlinks in a report, proceed with Step 2 in the previous section on jumping to bookmarks. In this case, all you need to specify is the Jump to URL for the link when the user clicks on that control in your report. You can hyperlink to the Google Web site by entering the URL http://www.google.com in the combo box under the Jump to URL radio button. Using Document Maps Another way a user can interact with a report is through a document map. In the HTML Viewer, a document map appears as a table of contents next to the report. Users can click an item in the table, and the browser jumps to that item in the report. Figure 15-8: Navigation tab on the Textbox Properties dialog box showing how to set a Jump to Bookmark expression. Chapter 15: More About Interactive Reporting 283

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

Figure 15-7: Preview of the report when each

Using Navigation Techniques You can also add links to a report to assist in navigating to specific sections of a report quickly. When you add a link to a textbox, image, or chart element, you can specify that the link is a bookmark or a URL. The bookmark link provides a link to a bookmark that is set within the current report. The URL link provides a way to link to a Web page on the Internet from within a report. I show you some examples and how to use this capability. You can set these up in the Navigation tab of the Advanced Textbox Properties dialog box. Adding bookmark links A bookmark link is a link that a user clicks to move to another area or page in a report. You set up bookmarks by setting a bookmark on a report item to which users can jump, and by adding a bookmark link on the item you want the users to click to jump to the item with the bookmark. You can set bookmarks on any report item, but you can add bookmark links only to textboxes and images. The process to define a bookmark and a link to the bookmark is as follows: 1. Set the bookmark ID for the target to which users can jump. To do this, select a control on the report that requires a bookmark ID so that you can link to this. This will usually be a textbox or a cell within a report. a. To set the Bookmark ID, right-click on the target textbox or data region cell and select the Properties menu item. This will show the Textbox Properties dialog box (like that shown in Figure 15-8, for example). b. Go to the Navigation tab to set the bookmark. In the bookmark ID textbox, enter the string that is the bookmark ID for that target. In this example, I have set the bookmark ID to an expression: =Fields!Categoryvalue. I want to navigate to the page of the appropriate Category value. 2. Set the Jump To property from which you can jump to a bookmark. For the source textbox or image control, follow the steps noted in the previous step to get to the Navigation tab in the Textbox Properties dialog box. Then click on the Jump to Bookmark radio button and enter the bookmark ID to which this bookmark link will navigate to on the report. This is shown in Figure 15-8. 282 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 Clan Web Hosting services

Right-click the cell on the Detail row in

1. Build a report with two textboxes and two subreports. The report I created is shown in Figure 15-6. I have two textboxes labeled Category Sales Analysis and Show Sales Trend. The subreports are actually the reports described in the textbox the SalesTrend report is a chart showing a sales trend and the Matrix Product Report shows the subcategory sales detail. 2. Set up the SubReports to display when the respective textboxes are toggled. To do this, right-click on each subreport and select the Properties menu item. This displays the Subreport Properties dialog box. Click the Visibility tab and indicate that this subreport is initially Hidden and then visibility can be toggled by the respective textbox. Do this for each subreport. 3. Preview your report. Initially you should see a report where the textboxes are visible with a toggle to indicate that they can be expanded. Then click on each textbox. If set up properly, each textbox toggle should show one of the subreports. When both have been toggled on, the report should look like that shown in Figure 15-7. Figure 15-5: Preview of the Employee Roster with dynamic visibility on parent in a parent-child relationship. 280 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

Right-click the cell on the Detail row in

Right-click the cell on the Detail row in Level column and select the Expression menu item. Then in the Edit Expression dialog box, type the following expression: =Level( RecursiveGroup ) This Level function returns the current level of depth in a recursive hierarchy. The highest level is 0. 5. Indent the first column based on level. Right-click the first column cell and select Properties menu item to display the Textbox Properties dialog box. Click the Format tab. In the Left spacing textbox, enter the expression: =2 + (Level ( RecursiveGroup ) * 20) & pt This will left indent by a multiple of 20 points for each level of the employee. 6. Bold the Highest Level Employee. This will add to the readability of the report. In the Textbox Properties dialog box, click the Font tab. Then for the Weight specification, enter the expression: =iif (Level ( RecursiveGroup ) = 0, Bold , Normal ) Then click OK. 7. Add the Employee Count field. Enter the following expression for this cell in the report: =Count(Fields!EmployeeKey.Value, RecursiveGroup ,Recursive) 8. Hide lower levels and toggle the visibility of the lower levels. To do this, right-click the detail row and select Edit Group in the list that appears. In the Details Grouping dialog box that appears, click the Visibility tab. Under initial visibility, click expression and enter the following expression: =iif(Level() = 0, False, True) Then check the Visibility Can Be Toggled By Another Report item check box and select Employee as the toggle field. 9. Preview the report. Your final report should look something like the one shown in Figure 15-5. Dynamically visible data regions Another way to utilize the visibility options in Reporting Services is to have a textbox toggle the visibility of an entire data region. You can do this by using SubReports within a report while having the textbox be the toggle to showing or hiding the information. Follow these steps: Chapter 15: More About Interactive Reporting 279

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

Note: To set the Visible properties for cells

2. Create a simple report layout of employee information. Define a report with the column headings of Employee Name, Title, Level, and Employee Count. You can drag the Title field from the query into the Title field. You need to define a new calculated field called Employee (or the Employee Name column), which is set to the expression: =Fields!FirstName.Value + + Fields!LastName.Value. 3. Edit the Details Group by clicking inside the table and then clicking the Detail row icon to select the row. Then set the grouping properties as follows: a. With the details row selected, bring up the Properties window and click on the Grouping property. b. In the Details Grouping dialog box that appears, enter RecursiveGroup as the name. c. In the Group On section of the General tab, select the =Fields! EmployeeKey.value from the Expression drop-down list. This will group the detail data by employee key. d. In the Parent Group drop-down list, select the =Fields!Parent EmployeeKey value. This enables you to leverage the parentchild relationships in the data. 4. Display the level number of each employee. Figure 15-4: Query specifications for the Employee Roster report. 278 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

Note: To set the Visible properties for cells

Note: To set the Visible properties for cells in the data region, you can alternatively use the Textbox Properties dialog box, shown in Figure 15-3. To do this, right-click on the cell (such as the SubCategory header SalesAmount cell). This will show the Textbox Properties dialog box (like that shown in Figure 5-3, for example). Click the Visibility tab to select Hidden or Visible and a ToggleItem if appropriate. This is equivalent to setting these Visible property components in the Properties window. You cannot set the Visibility properties for the entire Detail and SubCategory rows in this manner you must use the Properties window. The Row/Column settings also supersede the individual cell properties within the Row/Column. Drilling down a recursive hierarchy Consider the example of drilling down on a report of managers and showing all the employees reporting to the manager. This is a little different from the previous example, because this employee hierarchy is usually a parent-child relationship in the database. This means that for every employee record in the database, there is an attribute that identifies the manager for that employee. Other types of parent-child relationships that you usually see in databases include departments and business units, cost centers and departments, and territories to regions. How do you tackle this? Follow these steps: 1. Prepare a query from the DimEmployee table. The Data window for the query is shown in Figure 15-4. You just need the fields EmployeeKey, ParentEmployeeKey, FirstName, LastName, and Title. Figure 15-3: Advanced Textbox Properties dialog box for the Sub- Category header cell Sales- Amount. Chapter 15: More About Interactive Reporting 277

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