Interacting with Reports

In many cases, your reports can be much more effective when users can view them electronically. Reporting Services offers a number of options for enabling the user to interact with the reports when viewed electronically. You have already seen several examples of drill-down interactivity. This type of interactivity hides detail information until it is needed by the user.

In this section, you learn additional methods for navigating within reports and even moving between reports. You also see how to link a report to other Internet content. Finally, you look at a way for your report to interact with you by always keeping its data current.

So don’t be shy: interact!

The Invoice Front-End Report

Features Highlighted

• Using drill-through navigation to move between reports

• Using the document map to navigate within a report

• Using bookmarks to navigate within a report

• Using links to navigate to Internet content

Business Need   The Galactic Delivery Services accounting department is pleased with the Invoice Report. They would now like a front end to make the Invoice Report easier to use. The front-end report should list all invoices by customer and let the user click an invoice to see the complete Invoice Report. The front end should have each customer start on a new page. In addition, the front end should provide a quick way to navigate to the page for a particular customer, and a way to move from a customer to the page for its parent company. Finally, the front end should include a link to the customer’s website for further information on the customer.

Task Overview

1. Create a New Report, Create a Dataset, and Populate the Report Layout

2. Add the Navigation

Invoice Front-End Report, Task 1: Create a New Report, Create a Dataset, and Populate the Report Layout

1. Reopen the Chapter08 project if it was closed.

2. Create a new report called FrontEnd using the GDSReport template.

3. Create a new dataset called CustomerInvoices that calls the stp_CustomerInvoices stored procedure.

4. Select the Layout tab.

5. Place a table onto the report body.

6. Drag the InvoiceNumber, InvoiceDate, and TotalAmount fields into the details row of the table.

7. Type the letter C for the Format property for the text box containing the TotalAmount field value.

8. Turn off the table header and table footer.

9. Add a group to the table using the CustomerName as the grouping expression. The group should have a group header, but not a group footer. There should be a page break at the start of each new group.

10. Drag the CustomerName field into the leftmost cell in the group header row. Set the FontWeight property to Bold for this text box.

11. Drag the ParentName field into the center cell in the group header row.

Task Notes   We have the layout for the Invoice Front-End Report. However, it is not really a front end because it does not lead anywhere yet. Let’s continue to the good stuff.

Invoice Front-End Report, Task 2: Add the Navigation

1. Right-click the leftmost cell in the details row (the cell containing the invoice number) and select Properties from the Context menu. The Textbox Properties dialog box appears.

2. Select the Navigation tab.

3. Select the Jump to Report option for the Hyperlink action.

4. Select Invoice from the Jump to Report drop-down list.

5. Click Parameters. The Parameters dialog box appears.

6. Select InvoiceNumber from the Parameter Name drop-down list.

7. Select=Fields!InvoiceNumber.Value from the Parameter Value drop-down list.

8. Click OK to exit the Parameters dialog box.

9. Click OK to exit the Textbox Properties dialog box.

10. Right-click the leftmost cell in the group header row (the cell containing the customer name) and select Properties from the Context menu. The Textbox Properties dialog box appears.

11. Select the Navigation tab.

12. Select=Fields!CustomerName.Value from the Document Map Label drop-down list.

13. Select=Fields!CustomerName.Value from the Bookmark ID drop-down list.

14. Click OK to exit the Textbox Properties dialog box.

15. Right-click the center cell in the group header row (the cell containing the parent name) and select Properties from the Context menu. The Textbox Properties dialog box appears.

16. Select the Navigation tab.

17. Select the Jump to Bookmark option for the Hyperlink action.

18. Select=Fields!ParentName.Value from the Jump to Bookmark drop-down list.

19. Click OK to exit the Textbox Properties dialog box.

20. Right-click the rightmost cell in the group header row and select Properties from the Context menu. The Textbox Properties dialog box appears.

21. Type Website Link for Value.

22. Select the Navigation tab.

23. Select the Jump to URL option for the Hyperlink action.

24. Select=Fields!CustomerWebsite.Value from the Jump to URL drop-down list.

25. Click OK to exit the Textbox Properties dialog box.

26. Select the Preview tab.

27. Select Save All from the toolbar.

Task Notes   When you look at the report preview, you notice a new feature to the left of the report. This is the document map, which functions like a table of contents for your report. We created entries in the document map when we placed an expression in the Document Map Label drop-down list in Step 12.

Because you used the customer name as the document map label, when you expand the FrontEnd entry in the document map, you see a list of all the customer names. (FrontEnd is the name of the report. That is why it is the top entry in the document map.) When you click a customer name in the document map, you are taken directly to the page for that customer.

If you are not using the document map, you can hide it by clicking the Document Map button in the report viewer toolbar. The Document Map button is the leftmost button in the toolbar. Clicking this button a second time causes the document map to return.

In addition to creating document map entries for each customer name, we also created bookmarks for each customer name. This was done in Step 13. We are using these bookmarks to link child companies to their parent company. We are creating a Jump to Bookmark using the value of the ParentName field. This was done in Steps 17 and 18.

When a customer has a value in the ParentName field, a Jump to Bookmark link is created on that parent name (the center cell in the group header row). The bookmark link jumps to the page for the customer with the matching name. To try this out, use the document map to jump to the page for Everlast Plastics. Everlast’s parent company is Young & Assoc. Click the link for Young & Assoc., and you will jump to the page for Young & Assoc.

We also created a Jump to URL link for each customer. This link was placed in the cell that reads Website Link and was created in Steps 23 and 24. Clicking this cell is supposed to take you to the website for each customer. However, we are unable to connect to the Inter-galactic-net used by GDS and its customers. Instead, clicking this link opens a browser and takes you to the Osborne website.

Earlier in the process, we created a Jump to Report. This was done in Steps 3 through 7. Clicking an invoice number jumps you to the Invoice Report and passes the invoice number as a parameter. This enables you to see the detail information for the invoice. When you finish looking at the invoice, you can return to the Invoice Front-End Report by clicking the Back button in the report viewer toolbar.

The Transport Monitor Report

Features Highlighted

• Using a chart as the data section of a matrix

• Indicating values over a set maximum on a chart

• Using the autorefresh report property

• Using a multivalued parameter with a WHERE clause

Business Need   The Galactic Delivery Services maintenance department needs a report to assist in monitoring transport operations. Each transport feeds real-time sensor data back to the central database. The maintenance department needs a report to display this information for a selected set of transports. Because the sensor data is updated every minute, the report should refresh every minute. The sensor data should be displayed in a graphical form with a highlight of any values that are above the normal maximums.

Task Overview

1. Create a New Report, Create a Dataset, Populate the Report Layout, and Set Report Properties

Transport Monitor Report, Task 1: Create a New Report, Create a Dataset, Populate the Report Layout, and Set Report Properties

1. Reopen the Chapter08 project if it was closed.

2. Create a new report called TransportMonitor. Do not use the GDSReport template.

3. Create a new dataset called TransportMonitor that calls the stp_TransportMonitor stored procedure.

4. Create a second dataset called TransportList that calls the stp_TransportList stored procedure.

5. Select the Layout tab.

6. Configure the TransportNumber Report Parameter as follows:

   Property	Value
   Prompt	Transports
   Multivalue	Checked
   Available values	From query
   Dataset	TransportList
   Value field	TransportNumber
   Label field	TransportNumber

7. Click OK to exit the Report Parameters dialog box.

8. Place a text box on the report body and set its properties as follows:

   Property	Value
   Font: FontSize	20pt
   Font: FontWeight	Bold
   Location: Left	0in
   Location: Top	0in
   Size: Width	3in
   Size: Height	0.375in
   Value	Transport Monitor

9. Place a matrix onto the report body. Set the properties of the matrix as follows:

   Property	Value
   Location: Left	0in
   Location: Top	0.5in

10. Drag the TransportNumber field from the TransportMonitor dataset into the Rows cell. Set the following properties of the text box created in that cell:

    Property	Value
    BackgroundColor	White
    Font: FontWeight	Bold
    VerticalAlign	Middle

11. Drag the Item field into the Columns cell. Set the following properties of the text box created in that cell:

    Property	Value
    BackgroundColor	White
    Font: FontWeight	Bold
    TextAlign	Center

12. Open the Matrix Properties dialog box and select the Groups tab.

13. Click Edit in the Rows area. The Grouping and Sorting Properties dialog box appears.

14. Check the Grouping Header Should Remain Visible While Scrolling option.

15. Click OK to exit the Grouping and Sorting Properties dialog box.

16. Click Edit in the Columns area. The Grouping and Sorting Properties dialog box appears.

17. Check the Grouping Header Should Remain Visible While Scrolling option.

18. Click OK to exit the Grouping and Sorting Properties dialog box. Click OK to exit the Matrix Properties dialog box.

19. Click the gray rectangle to select the top row of the matrix. Set its Height property to 0.25in.

20. Click the gray rectangle to select the bottom row of the matrix. Set its Height property to 1.625in.

21. Click the gray rectangle to select the left-hand column of the matrix. Set its Width property to 1in.

22. Click the gray rectangle to select the right-hand column of the matrix. Set its Width property to 2.25in.

23. Expand the TransportMonitor dataset in the Datasets window.

24. Place a chart in the Data cell.

25. Drag the Value field and drop it on Drop Data Fields Here. (This may be a little tricky. You might have to drag the fields over the chart to reactivate the drop areas before you can drop the field in the appropriate location.)

26. Drag the Reading field and drop it on Drop Category Fields Here. Drag the ReadingPortion field and drop it on the Drop Series Fields Here.

27. Set the following properties of the chart using the Chart Properties dialog box:

    Property		Value
    On the General tab:
    	Palette	Excel
    	Chart subtype	Stacked column chart
    On the Y Axis tab:
    	Maximum	100
    On the Legend tab:
    	Show legend	Unchecked

28. Click OK to exit the Chart Properties dialog box.

29. Open the Report Properties dialog box.

30. Check Autorefresh and set the autorefresh rate to 60 (seconds).

31. Click OK to exit the Report Properties dialog box.

32. Select the Preview tab.

33. Select several transport numbers from the drop-down list and click View Report. (Autorefresh is not supported in the report preview.) Your report appears similar to Figure 8–21.

    
    Figure 8–21: The Transport Monitor Report preview

34. Select Save All from the toolbar.

Task Notes   A number of interesting things are going on in this report. First, a multivalued parameter is being sent to SQL Server for use in a stored procedure. The stored procedure uses the contents of this multivalued parameter to build a query string on the fly. The SELECT statement in the stored procedure is a bit complicated because it is using some random number generation to simulate the Transport telemetry. Here is a more straightforward version of the content of the stored procedure:

 DECLARE @DynamicQuery    varchar(8000) SET @DynamicQuery = 'SELECT TransportNumber, Item, Reading, Value' SET @DynamicQuery = @DynamicQuery + 'FROM transMonitor' SET @DynamicQuery = @DynamicQuery + 'WHERE TransportNumber IN                                         ('+@TransportNumber+') ' EXEC (@DynamicQuery)

This stored procedure code builds the SELECT statement in the @DynamicQuery variable. It uses the IN operator to look for the content of the TransportNumber field in a list of values. The values must be comma-separated and enclosed in parentheses to be used with the IN operator. The values in the multivalued parameter are being passed to the @TransportNumber stored procedure parameter. Because these values are already comma-separated, all you have to do is place them inside the parentheses to use them with the IN operator.

We placed a chart in the data portion of the matrix data region. Because the data portion of a matrix is an aggregate, the chart has a set of values to use for charting. When the report is rendered, the chart is repeated in each data cell in the matrix. Each chart then acts within the scope of its data cell and charts the data in that scope.

The chart contains two series. The first series is a value up to the maximum normal value for that reading. The second series is the amount of the reading above the maximum normal value. The second series value is 0 if the reading is below or at its maximum normal value. The stacked column chart puts these two series one on top of the other. The result is any readings that are above their maximum normal value have a maroon section at the top of the column. This should be enough to get the attention of any technician monitoring the readings.

Finally, we used autorefresh to meet the business requirements of the report. When the Autorefresh property is set, the report is automatically rerun on the schedule you specify. Unfortunately, autorefresh is only supported in the Report Manager. You can see autorefresh in action if you deploy this report to the Report Manager after reading Chapter 11.