Rank/Top Count using a Transformer Cube as Data source

[RobsWalker68]

Hi All,

I have a problem that I'm stuck on and hopefully somebody can give me some guidance or show me the direction to try.

I have attached an Excel spread sheet showing a simplified view of the report layout and the data source is a Transformer Cube and the Cognos BI Version is 10.2.0

Issue: For the attached layout I need to create a conditional style that will highlight the top 3 intersections, the intersections being Sessions (Rows) and Day of Week (Columns), so that it is top 3 of any session on any day.  Day of Week is also contextually filtered by the member of 'Food'

I was thinking of using Rank and placing it on the inner row edge and then using the conditional style to highlight those intersection between 1 and 3 and setting the row box type to non.  However I have never used Rank or topCount in this way and I'm unclear what the syntax should be as I need it to generate a figure for the conditional style instead of just restricting the rows to top x if that makes sense.

Many thanks

Rob

[Lynn]

Personally I'd rank breakfast higher, especially when haggis is on the menu  :D

I had a similar problem recently when trying to highlight a measure based on a quartile calculation. Take a look at the attached report spec from one of the sample cubes. It has a context filter for order method with product/time on rows/columns. I nested revenue beneath time rather than just having it as the default measure at the crosstab corner. Then I included a rank calculated item next to revenue:

Code Select Expand


rank ( currentMeasure within set [children(Time)], [children(Products)] )


Then apply a conditional style to the crosstab intersection for revenue:

Code Select Expand


[Query1].[rank] in (1, 2, 3 )


What I can't manage is hiding the rank column. Box type none messes up the headings. Removing it makes the highlighting stop working although I messed around with node properties. In my quartile situation I was only computing it based on rows, so I was able to put the calculated column stacked on the far right with boxtype none and no issues. In your example the computation is based on both row and column intersections, so I can't figure another way to do it.

Perhaps others might have ideas on how to solve it or play around with formatting to come up with something the users are ok with.

Hope this helps!!

[RobsWalker68]

Many thanks Lynn I will give it ago.

Me and Julie will treat you to a fried Haggis Supper next time you are in toon  ;D

Cheers

[Lynn]

Many thanks Lynn I will give it ago.

Me and Julie will treat you to a fried Haggis Supper next time you are in toon  ;D

Cheers

Yay!! I will search for the perfect wine to pair with innards and oats  ;D

[RobsWalker68]

That's an easy one.  To top off such a culinary delight you need a can of Barrs IRN-Bru  ;D

[RobsWalker68]

Lynn,

Tried the solution you posted and it worked a treat and having it on the row made it some what easier.

Cheers

[Lynn]

Great!! Did you manage to hide the rank or did you leave it in the layout?

[RobsWalker68]

As it was a nested row, I was able to set the node and fact cells to box type none without any issues.  ;D

[Lynn]

Oh, of course!! Wonderful. Glad it worked out.