Microsoft SQL Server 2008 Business Intelligence Development and Maintenance: Developing SSRS Reports

Lesson 3: Working with Advanced Report Object Properties

Estimated lesson time: 25 minutes

Designing effective reports involves much more than adding new elements or enhancing the look and feel of the existing ones. A critical part of report development is creating an interactive user experience in which users can see the general summarized picture of the data and drill through the data to discover details. This lesson covers how to configure advanced object properties to build interactive reports.

Toggling Object Visibility

A primary concept in interactive reports is that they include groups, columns, rows, tables, or matrix elements that are hidden when the user first sees the report. The user can then click an element of the report to display hidden items only as they are needed. Interactive reports help users stay focused on the global perspective of the report and still have the ability to look into the details.

Some common reasons you might want to use the toggle visibility feature are as follows:

1. To hide columns or rows with details in table and matrix report items

2. To completely hide a table or matrix item

3. To hide other report items

Hiding Groups

To hide groups in table and matrix items, follow these steps:

1. In the Report Designer, on the Design tab, select the table or matrix report item (that is, row or column group) from the Row Groups or Column Groups pane.

2. Right-click the table or matrix item, and then select Group Properties.

3. In the Group Properties dialog box, click the Visibility pane.

4. Change the display options by first changing the When The Report Is Initially Run option to Show Or Hide Based On An Expression.

5. Select the Display Can Be Toggled By This Report Item check box.

6. Select the report item that will toggle the visibility. Typically, this is a text box that shows the label of the parent group.

7. Click OK to accept the configuration.

Hiding a Whole Tablix

Sometimes you will want to hide not columns and rows but the whole table or matrix. The following steps will hide a table or matrix:

1. Select the table or matrix report item, right-click, and then select Tablix Properties.

2. In the Tablix Properties dialog box, click the Visibility pane.

3. Change the display options by first changing the When The Report Is Initially Run option to Show Or Hide Based On An Expression.

4. Select the Display Can Be Toggled By This Report Item check box.

5. Select the report item that will toggle the visibility.

6. Click OK to accept the configuration.

Hiding a Static Row, Column, or Text Box of a Tablix

If you need to hide items such as a specific row, column, or even text box, you need to select the item and change its Visibility property. For a row or column of a Tablix, click the Tablix to select it and display handles on the left and top borders. Right-click the row or column handle you want to hide, and then select either Row Visibility or Column Visibility. To hide an individual text box (or cell of a Tablix), right-click it and then select Text Box Properties. Then use the Visibility pane to change the Visibility property.

Hiding Other Report Items

Other report items can also be hidden initially and toggled automatically when users select another item. To hide other report items such as images, charts, and so on, follow these steps:

1. Select the report item you want to hide.

2. In the Item Properties window, use the Visibility pane to change the settings.

Defining Report Actions

Actions are another common feature of interactive reports. Actions are responses to user clicks on a report item. Actions let users navigate reports in the same way they navigate the Internet. SSRS supports three types of actions:

• Go To Report This action lets you configure a set of interlaced reports. Reports can use parameters to indicate what users want to navigate to. Go To Report is frequently used to drill through to details of summary information.

  NOTE: Report Parameters

  In Chapter 11, you will learn more about parameters and how reports use them to filter displayed information.

• Go To Bookmark This action lets users navigate quickly within complex reports. Users can click one item to rapidly move to another section of the same report.

• Go To URL This action lets developers integrate the report infrastructure with the Internet, an intranet, or an extranet. Reports can use hyperlinks to integrate with the rest of the Web infrastructure.

To implement one of these actions, take the following steps:

1. Select the item in which you want to configure the action.

2. In the Properties window, select the Action property.

3. Click the ellipsis button and then select the type of action you want to configure.

4. Add the expression required to set the URL.

Adding Bookmarks

Bookmarks provide a customized table of contents or customized internal navigation links in the report. Add bookmarks for locations you want to direct users to—for example, to a chart that graphically explains values in a Tablix.

To add a bookmark, follow these steps:

1. Select the text box, image, chart, or other report item on the Design tab.

2. The properties for the selected item will appear in the Properties window. If this window is closed, open it by pressing F4 or by selecting the Properties Window option on the View menu.

3. In the Bookmark box, type a string that is the label for this bookmark. Alternatively, use the drop-down list to select a value of a field of the bound dataset or to select and edit a custom expression used as the bookmark value.

Practice: Modifying Advanced Report Object Properties

In this practice, you will modify the report you created in this chapter’s previous practices to initially hide some of the detailed information from users. Users can then interact with the report and expand only those areas they want to focus on. You will then configure a hyperlink action in the report.

EXERCISE 1: Hide Columns

In this exercise, you hide columns so that the user will first browse only summarized yearly information.

1. Open BIDS.

2. Open the TK 448 Ch10 SSRS Purchasing project you created in the previous practices for this chapter. You can open the project from the main menu by selecting File, Recent Projects or File, Open Project/Solution.

3. In Solution Explorer, double-click the PurchasingSummary report. The report will open in the Report Designer Design tab.

4. Add a calculated field to your dataset, as follows. You will calculate order year from the OrderMonth field.

   1. In the Report Data window, right-click DataSet1, and then click Add Calculated Field.

   2. In the Dataset Properties dialog box, the Fields pane should be selected, with all the existing fields and boxes for creating a new field showing.

   3. Type OrderYear as the name of the calculated field, and then click the Expression button and add the following expression for this field:

      =Fields!OrderMonth.Value\100

   IMPORTANT: Use the Integer Division Symbol

   The preceding expression uses the \ symbol and not the regular division symbol /. The \ symbol is for integer division. Order month comes from a query as an integer with six numbers, in YYYYMM format, such as 200407 for July 2004. Performing an integer division by 100 on this number gives 2004 as the result.

   Figure 10-4 shows the Expression dialog box, with the expression added.

   

   Figure 10-4 The Expression dialog box, showing how to build the OrderYear expression by using integer division

5. Click OK to close the Expression dialog box, and then click OK again to close the Dataset Properties dialog box.

6. In the matrix, click the OrderMonth column to select it. Make sure that only this column is selected, not the complete matrix. Right-click the OrderMonth column, select Add Group, and then select Parent Group. Figure 10-5 shows the correct selections.

7. In the Tablix Group dialog box, select the OrderYear field in the Group By drop-down list, and then click OK.

8. Click the new text box that has the OrderYear field in it to select it. In the Properties window, change the TextAlign property to Center.

9. Click the OrderMonth column header to see the column groups in the Column Groups pane at the bottom right of the Design window. In the Column Groups pane, right-click Matrix1_OrderMonth, click Add Total in the Textbox pop-up menu, and then click After on the submenu.

10. In the Column Groups pane at the bottom right of the Design window, right-click Matrix1_OrderMonth, and then select Group Properties.

11. In the Group Properties dialog box, select the Visibility pane, and then change the When The Report Is Initially Run option to Hide. Now the monthly columns will not be displayed when the user sees the report.

12. Click OK to accept the new visibility settings.

13. Click the Preview tab to review the report. Notice that the user will be able to see only yearly sales and will not see the monthly details.

    

    Figure 10-5 Adding a parent group to OrderMonth

EXERCISE 2: Unhide Columns

In this exercise, you change the report item properties so that the user can display month columns for a particular year. The idea is that the user will first browse yearly summarized information and, if more detailed information is needed, the user can expand any year to display the monthly details.

1. Switch to the Design tab.

2. In the Column Groups pane at the bottom right of the Design tab, right-click Matrix1_OrderMonth, and then select Group Properties.

3. In the Group Properties dialog box, click the Visibility pane. Select the Display Can Be Toggled By This Report Item check box. From the drop-down list under the check box, select Group1.

4. Click OK to accept the new visibility.

5. Click the Preview tab to review the report. Notice that the user will now be able to expand the yearly sales into monthly details.

6. Save the solution.

EXERCISE 3: Create a Hyperlink Action

In this last exercise, you enhance the usefulness of the report by adding a hyperlink action to the product name column so that users can browse the product home page in the Adventure Works portal.

1. Select the ProductSubcategory text box, which is the leftmost cell that is the last row in the matrix report item.

2. In the Properties window, make sure that the ProductSubCategory text box is selected. Change the Color property to DarkBlue (the sixth color in the first row of the color palette). Also change TextDecoration property to Underline so that users can see that the subcategory name value is a hyperlink.

3. In the Action property, click the ellipsis (...) button to open the Text Box Properties dialog box. Below Change Hyperlink Options, select the Go To URL option. Use the following expression to configure the URL:

   ="http://AdventureWorksIntranet/Products/"+Fields!Name.Value+".aspx"

4. The expression configures the hyperlink action to navigate to an imaginary Web site.

5. Click OK to configure the action, and then click OK again to close the Text Box Properties dialog box.

6. Click the Preview tab to preview the report. Try to click a product subcategory link. Internet Explorer should open, but because the URL points to an imaginary Web site, it should not open the URL.

7. Save the solution and close BIDS.