2100 Clearwater Dr. Suite 330, Oak Brook IL 60523

1-(708) 575-2090 contact@pmsquare.com Visit our other locations: australia singapore philippines

Drilldown Breadcrumbs in Cognos Analytics

Drilldown Breadcrumbs in Cognos Analytics

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:

 

Drilldown Breadcrumbs in Cognos Analytics

 

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!

 

Drilldown Breadcrumbs in Cognos Analytics

 

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.

 

Drilldown Breadcrumbs in Cognos Analytics

As usual, the report xml is attached below. Drilldown Breadcrumbs in Cognos Analytics (27 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.  If you haven’t already, be sure to subscribe to the PMsquare Journal for more technical articles and updates delivered directly to your inbox.

 

SUBSCRIBE

 

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, please reach out to at:

United States Australia Singapore, Philipines, Thailand
PMsquare LLC Cornerstone PMsquare | A Cornerstone Company
P: 708-575-2092
E: Chris Loechel
P:+61 1300 840 048
E: Piers Wilson
P:+65 6635 1700
E: Carsten Brandt

Blog post shared courtesy of PMsquare LLC

Leave a Reply

Your email address will not be published. Required fields are marked *