Some days ago I had the requirement to show alternate row colors in a break.

In fact that I was missing a SCN-Entry about this – there we go:

(I am using BO 4.0)

Requirement:

/wp-content/uploads/2013/11/req_320848.png

Solution:

1. I create a variable RunningSwitcher with following definition:

=RunningCount(LineNumber();([Project]))

This gives me a number starting at 1 for each line of one project:

/wp-content/uploads/2013/11/running_switcher_320974.png

2. next I created a variable ProjectSwitcher with following definition:

=If(LineNumber()=1;1;If([RunningSwitcher]<>1;Previous(Self);Previous(Self)+1)) 

This variable gives me the same number for each project starting by 1:

/wp-content/uploads/2013/11/switcher_color_320990.png

3. a variable which checks if the ProjectSwitcher is even or odd to get one number for each even row and another for each odd row:

=Mod([ProjectSwitcher];2) 

breakColor.png

4 & last step: I created a conditional formatting on the last variable which just set a gray background color if the row number is 0:

conditional.PNG

That’s it!

As a result I got this:

result.PNG

Hope that helps,

Andreas

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

  1. Sampath Dheekonda

    Hi Andreas,

    It’s nice trick to get the alternate row color based on the dimension values.

    I had same issue and resolved by using below variables and wants to share

    Variable 1:

    Curr Value : =[Location]

    Variable 2:

    Prev Value: =If(IsNull(Previous([Location]));[Location];Previous([Location]))

    Variable 3: (to distinquish the dimension values alternatively)

    Alternate Color: =If([Curr Value]=[Prev Value];(0+Previous(Self));If((Previous(Self))=1;0;1))

    Applied Aleter on based on the Alternate Color variable and achieved the required result.

    Hope it is useful.

    (0) 
  2. Neil Mitchell-Goodson

    I am a fan of brevity and don’t like to create lots of functions where I don’t need to. Therefore I do something like this:

    =if(mod(runningcount([Project]) forall([Bidder];[Quote])+1;2)=0;1;0)

    Starting from the outside in, the runningcount should ignore the bidder and quote columns to just give us the same as step #2 above. We then add 1 to this and check (using the mod function) if this number is divisible by 2. If it is, it’s an even row and we return a 0. If it’s not, we return a 1. This will then give you an output the same as step#3, leaving you free to perform the conditional formatting via an alerter.

    Nice and concise 🙂

    (0) 
  3. Vidas Jucius

    This is nice feature indeed. I accomplished this functionality creating simple variable with formula:

    =Mod(RunningCount([Some dimension]) In Break;2)

    Afterwards used this variable in creating conditional formatting rule. Hope this helps.

    (0) 
  4. Zahid Yener

    Why don’t you guys use Odd or Even formula for this problem.

    That’s how i did it.

    1. I created the variable below:

    /wp-content/uploads/2015/09/pic_1_785110.png

    2. I created the Conditional Formating below:

    /wp-content/uploads/2015/09/pic_2_785129.png

    Result:

    /wp-content/uploads/2015/09/pic_3_785130.png

    (0) 

Leave a Reply