The drilldown techniques in Cognos are wonderful, click on a column or a row in a table, and you can drill down, and down, and down. But drilling up doesn’t always work well, and sometimes you just want to go back directly to the top level. Sadly Cognos doesn’t provide a quick and easy breadcrumb solution, but with the advanced drill options it’s possibly to make one so let’s dig into drilldown breadcrumbs in Cognos Analytics.

Drilldown Breadcrumbs in Cognos Analytics

First, this only works in non-interactive mode, but is backwards compatible to at least 8.4. Interactive mode doesn’t recognize the advanced options, which are required to use this.

Second, this is a dimensional solution. Relational packages need not apply.

Let’s start with a very basic crosstab. Revenue by Region by Product Line:

In the query, let’s create a new data item called “Region Breadcrumbs”

[sourcecode]set(ancestor(item([Region],0),5)
,ancestor(item([Region],0),4)
,ancestor(item([Region],0),3)
,ancestor(item([Region],0),2)
,ancestor(item([Region],0),1)

)[/sourcecode]

This is a great example of Cognos not having the complete MDX function set. We have descendants, why not ASCendants?! But enough whinging.

Next bit is important, in the properties of the query set “Define member sets” to Yes. Drag in Region, and nest Region Breadcrumbs:

Enable “Drill-up and drill-down” in the report properties. The breadcrumbs will very elegently handle the navigation back to the top level, so we can disable the drill up for the region.

In the advanced drill behavior properties, disable the drill ups for both the Region and the Region Breadcrumbs.

In the advanced tab set Drill-down behavior for Region to Replace Expression and Region Breadcrumbs to Preserve.

Create a new crosstab right above the original one. This will be the structure of the breadcrumbs.

We’re not done, there’s still a bit to get it to look nice, but let’s see what happens when we run it now.

It’s working, but there are a couple of issues that present. It looks a bit ugly and more importantly the only way to drill on the breadcrumbs is right clicking. Let’s see if we can find a way to enable the drill behavior on the upper levels.

It turns out that enabling the drill link is a really simple matter. Inspecting the HTML of the node with an active link shows that the span has the class “dl”, and the non-active nodes don’t have “dl” in the span. So let’s be tricky here.

Create a local class called “dl”. Just the name needs to be “dl”, and you don’t need to set anything else.

Unlock the report and select the text item in the crosstab node. Add the class to the item. That’s it. Next time we run it, the links are active!

Next we need to make it look nice. I’ll quickly run through the changes I normally make to make it look nice.

Crosstab corner, rows, fact cells are display:none
Crosstab column members have white background, italics, borders: none
A layout expression is inserted after the text item in the crosstab columns:
case when IsLastInnerMostCrosstabColumnNodeMember () then ” else ‘ >> ‘ end

Let’s take a look at it in action now.

As usual, the report xml is attached below. Drilldown Breadcrumbs in Cognos Analytics (42 downloads)

Conclusion

I hope you found this article on Drilldown Breadcrumbs in Cognos Analytics useful. Explore the rest of our blog for more Cognos tips and tricks.

Make sure to sign up for our newsletter the PMsquare Journal to get more data and analytics news, updates, and insights delivered direct to your inbox.

Subscribe

Next Steps

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