Building a Report from the Ground Up - Part 8: Drill-Through, Hyperlinks, and Bookmarks

Imagine management has come to you, their expert Report writer, and asked for a way to be able to click a Sales Person’s name in the Region Sales report and then be taken to another report that displays all the pertaining information about that individual. Have no fear; passing data between separate reports is easier than you may think.

For this example, I’ll be using the same Region Sales report we used in Part 7.

Also, I’ve created a Sales Person report that simply displays information about a sales person with a parameter that allows the user the select a sales person to be displayed.

Here is the script to create the stored procedure. Don’t forget I’m using the Adventure Works database.

CREATE PROCEDURE sp_Person

      -- Add the parameters for the stored procedure here

      @ContactID int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      SELECT     FirstName + ' ' + LastName as FullName, EmailAddress, Phone

FROM         Person.Contact

Where @ContactID = contactid

And this is what the Sales Person report looks like:

Now back to the Region Sales report. Right-click the FullName field and select Text Box Properties. Select Action and click the radio button next to Go to Report. In the drop down box under Specify Report, select the Sales Person report.

Next, add a parameter by clicking the Add button and select the ContactID parameter in the drop down box under Name. Under Value, select the ContactID field. It Action properties should look like this:

Click OK and then Preview the report. Now on the FullName field, you can click the Sales Person’s name and be taken directly to the Sales Person report and see the individual’s information.

As you can see under the Change Hyperlink Options, you can also link to a URL. If you click the Go to URL radio button, you can link to a web page passing the ContactID through a Query String type format. By clicking the Expression button next to Select URL, you could create an expression similar to the following to link to your page:

="http://www.domain.com?id=" + Fields!ContactID.Value

You can also create a link to a bookmark on your report.  Bookmarks can be added to just about any item. To add a bookmark to an item, select the item, find the Bookmark property in the Property pane on the right, and type in a unique identifier for the item. The identifier for the bookmark must be unique. If 2 bookmarks exist by the same name, the report will just jump to the first one by that name it finds. Then right-click the item you want to link to the bookmark, select Text Box properties, click Action, click the radio button for Go to bookmark, and type in the identifier for the bookmark you want to link to. Click OK and you’re done adding a link to a bookmark!

As you can see, its very simple to pass data between reports by adding drill through functionality, links to URL’s, and bookmarks, but it can add a new level of functionality to your reports, so give it a try.