If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

alternate row shading within a report

Started by blom0344, 13 Mar 2009 06:03:52 PM

Previous topic - Next topic

blom0344

Alternate row shading is one of the most appreciated aspects of reporting. It is surprisingly hard to implement in cognos 8 IN A REPORT.
We are experimenting with mod(running-count([somedataitem]),2) and this works when the resultset returned is ordered in the proper fashion (by somedataitem)
The Cognos server performs the running-count AFTER the set is fetched, unfortunately the set is not always fetched in the correct order.

I am after an EXPLICIT order by , instead of the unreliable GROUP BY , but no matter what I do , Cognos does not add an order by to it's SQL.

Anyone who can help me with this ? Would be forever thankful!!

blom0344

#1
After checking the generated SQL I was a bit stunned to find that the internal ordering is performed by using the primary key (a ID) instead of the name. (Even if the ID is not used or reverenced)  Obviously the order of the ID key has no relation to the ordering of the name in the report. Applying a rank on the name solved this, so instead of:

mod(running-count([name]),2)

mod(rank([name]),2)

worked ALMOST perfectly. Since in my case name is not always unique, some ties occured causing the rank to skip a value. This is the one point where the alternation fails.

update:
In an effort to break the ties I created a new object that is the concatenation of the name and the id. This makes sure no duplicates exist , however still need to enforce that list reports sort by this new dataitem. Back in the old client days, thisused to be a much easier subject to tackle..

JGirl

#2
You can do this with the pre-defined table styles, and its really easy.

1.  Create your list
2.  Select the list item
3.  Go to Table --> Apply Table Style
4.  Choose one of the alternating examples, eg. Rows1

This adds a conditional style to the list for you.  To change the default colors, select the 'List Columns Body Style', and you will see that a conditional style has been added for you.  If you click the ... it brings up a pop-up and you can edit the style options (ie. what colors to use on alternating rows etc)

vaisai

blom0344,

can you please provide the steps you went through when you implemented this? Because i wanted the similar approach in one of my report.

vaisai

blom0344

Quote from: JGirl on 16 Mar 2009 09:47:16 AM
You can do this with the pre-defined table styles, and its really easy.

1.  Create your list
2.  Select the list item
3.  Go to Table --> Apply Table Style
4.  Choose one of the alternating examples, eg. Rows1

This adds a conditional style to the list for you.  To change the default colors, select the 'List Columns Body Style', and you will see that a conditional style has been added for you.  If you click the ... it brings up a pop-up and you can edit the style options (ie. what colors to use on alternating rows etc)

Our basis is still version 8.1 and this feature was added later. However, it uses the same type of logic underneath. It is more or less a handout for designers that fail to write their own conditional logic  ;D.  I just tried your suggestion on a 8.3 testserver, but can't get it to work just  yet. Thanks for the tip, though!!

JGirl

Actually, its a handout for designers that are too lazy to write their own conditional logic ;D

I'm using 8.4 and it works very well.

The conditions added to the report are based on Mod(Row Number,2) = 1 &  Mod(Row Number,2) = 0

blom0344

No matter how I spell row number / row_number, 8.1 does'n't feel up to it. I suppose this is only supported in 8.3/8.4?  Anyway, using rank() does the trick as well..