How to build a Clickthrough link in Report Builder 2.0

As I mentioned in a previous post, due to resource constraints in this release, Report Builder 2.0 is not a strict superset of RB 2005/1.0. One of the features missing from RB 2.0 is automatic creation of Clickthrough links. You can still get them, of course, by creating the basics of your report in RB 1.0, then saving and reopening in RB 2.0. It is also possible to build them manually.

Here's how:

1. Determine what the target entity should be. This is the model entity the Clickthrough link should drill to. For example, if the value being displayed is # Sales Orders, you'll want to drill to the Sales Orders entity.

2. Get the target entity ID. (This is the worst part) Unfortunately, this value is not accessible from within RB 2.0. You can see it in the property grid in Model Designer, or you can get someone who has the RS Script Utility to use the attached script to get a list of all the entity IDs in the model. Keep the list around for reference.

3. Determine whether to show a single-instance or multiple-instance report. If the path from the value’s context to the target entity involves at least one “one-to-many” relationship, then the drill action can return multiple instances of the target entity, so you’ll need to show a multiple-instance report. Remember that the path from a report subtotal to the target entity is always one-to-many. Only use a single-instance report if there are no one-to-many relationships along the path. For example, if the value being displayed is # Sales Orders per Sales Territory, the value’s context is Sales Territory, and the path from that context to the target entity is Sales Territory -> Sales Person -> Sales Orders. Both of those relationships are one-to-many, so this Clickthrough action should use a multiple-instance report.

4. Create a "Go to report" action. Open the property dialog for the Text Box, Placeholder, Chart Series, or other report element that has Action properties, and go to the Action property page. Select the “Go to report” option and use the following settings:

Setting

Value

    Report Path

=DataSources!MyDataSource.DataSourceReference

Parameters

Value

    rs:EntityID

(Target entity ID)

    rs:DrillType

“Detail” or “List” for single- or multiple-instance, respectively

    rs:Command

“Drillthrough”

    DrillthroughSourceQuery

=Datasets!MyDataset.RewrittenCommandText

    DrillthroughContext

=CreateDrillthroughContext()

A few tips:

· Clickthrough links will not work correctly in a “details” scope, i.e. a group with no grouping expression. If you want to use them in a scope that is currently a details scope, you’ll have to change it to a non-details scope by adding one or more grouping expressions that uniquely identify each row of data.

· If the value you are displaying is an expression that involves multiple field references, set the target entity as the entity at the end of the longest common path between them.

· Read up on how creating customized Clickthrough reports can help maximize the value of every Clickthrough link you create.

 

GetModelEntityIDs.zip