When authoring reports in which you have one or more Tree Prompt controls most often you do not present the hierarchy path for specific data sets.  This information is not always pertinent and sometimes superfluous.  However, offering a hierarchy-based selection of a specific data set for the purpose of constraining data used in a report is at times critical for data validation.  In this article I’ll share a practical guide to add breadcrumb trails in Cognos Report Studio.  This procedure shows the full hierarchical path of a selection made from a Tree Prompt control, without including it in your main report objects (list, crosstab, graph, visualization).  We can accomplish this using a series of Singletons and queries; we will construct the Breadcrumb trail.

Before we get started we need to make sure we start from the same point in our environment. 

Assumptions

• The package that includes OLAP data source (DMR, Cube, etc.)

• We have an existing Report Studio report, with at least 1 Tree Prompt (single member selection)

• The number of levels of the hierarchy used for the Tree Prompt has been pre-determined

• Example report is written using the Go Data Warehouse (analysis) package

Ok, it’s time to get started.  Below is an outline for making that bread crumb trail for effective reference, and data validation.

Create a Breadcrumb Trail

1. Navigate to the Query window using the Query Explorer

2. Add a query, and name it “Q_Breadcrumb_Selection”

3. Add a data item to the query and name it “Breadcrumb – Selection”

4. The expression for the data item should be in the format:
   caption(hierarchy -> ?parameter?)
   eg: caption([Sales].[Retailers].[Retailers]->?pGeo?)

5. For each higher level of the hierarchy, add another query and two data items.

   • Create a data item named selected_member with the same expression as above except you will remove the caption ( ) portion.

   • Use the table below for suggested the query and data names for the higher levels:

Naming Convention

You can, of course, use any naming convention that you prefer.  Although only suggestions, the names above work well since they accurately describe their role and purpose.  The expressions however need to be input as indicated.

1. Once you have all created all of the Queries that you need, add a Block to the report where you want to render the Breadcrumb trail.

2. In the Source window in the upper-left corner of the Report Studio window, click the Data Items tab.

3. Drag in the individual Breadcrumb data items one at a time. Each drag will create a Singleton with the data item contained inside. Repeat until you have all the data items in place, ordered from higher to lower levels, and left to right.

When they have all been inserted into the block, it should resemble something like this:

Now, when the report is executed, you will now see your breadcrumb trail:

Conclusion

Creating your own hierarchy path enables users drill down without getting lost in the data.  I hope this article about how to add breadcrumb trails in Cognos assists you in your development efforts. By adding a breadcrumb trail, users can view the full path of where they are in regard to hierarchy and dimension which means more trusted and reliable data. Subscribe to our e-newsletter for more technical articles and updates delivered directly to your inbox.

Next Steps

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.