InfoSol We're passionate about inspired business intelligence
Related Articles
While the built in alternate row shading is nice to shade every other row or a set a pattern of rows, what if you wanted to shade rows based on a variable number of rows in a break? We can accomplish this by using a variable and a conditional formatting rule.
To begin, add at least 2 dimensions to a table or cross table block. I used a cross table above with a case count in the body. Next, we need to add a break on the 1st column in the block. Highlight the data in the 1st column (in this example it is Case Substatus) and click Break on the Analysis/Display menu.
After applying the break, we can manage the break to change the way the duplicate values appear and remove the break footer. Use the Break drop down and select Manage Breaks.
De-select the Break footer and select Merge from the Duplicate values drop down.
Now we will create a variable that will determine odd/even values for the distinct break values. This will be used later in a conditional formatting rule.
In this example, we are creating a variable named Break Highlight with the following formula.
=Even(RunningSum(If(RunningCount([Case Sub Status];([Case Sub Status])) > 1; 0 ; 1)))
To understand how this works, I recommend creating the full formula by developing each of these individual pieces of logic one at a time, starting with the innermost logic. Display the variable in the block to see the actual values as you add logic.
- =RunningCount([Case Sub Status];([Case Sub Status]))
This function produces a running count of case substatus values and uses a reset dim of case substatus, which means the count will reset with each new case substatus value. Remember that we are not displaying duplicate break values, but they are still there, as evidenced by the running count below.
- =If(RunningCount([Case Sub Status];([Case Sub Status])) > 1; 0 ; 1)
We have wrapped an If statement around the RunningCount function. This will display a value of 0 if the running count within the case substatus break value is greater than 1. Otherwise, a 1 will be displayed for the first occurrence of the break value or if there is only 1 row for the break.
- =RunningSum(If(RunningCount([Case Sub Status];([Case Sub Status])) > 1; 0 ; 1))
Working our way outward with the formula, we have added a RunningSum function around the If statement. Since each row in the break value will either have a 1 or a 0, the RunningSum function will calculate a distinct count of break values.
- =Even(RunningSum(If(RunningCount([Case Sub Status];([Case Sub Status])) > 1; 0 ; 1)))
The last step in creating this variable is to use the Even function, which determines if a number is odd or even. This will result in a value of 0 when the number is odd and a 1 when it is even.
Next, we will create a Conditional Formatting Rule that uses the Break Highlight variable to control the alternate break value highlighting. Use the New Rule option on the Analysis/Conditional menu.
Name the formatting rule Highlight Alternate Break. Select the drop down on the Condition option and select Formula Editor.
Select the Break Highlight variable that was created previously to use this variable in the Formula Editor. After changing to the Formula Editor, we can use the “fx” on the right side to select the variable from a list of available objects.
Now we need to format the cell when the Break Highlight variable is true. Since this is a Boolean value, it is true when the value is 1 and false when it is 0. Click on the Format button next to “Cell contents”, then select Background and change the color to whatever color is desired for the shading of the alternate break values. Click OK to complete the definition of the rule.
The final formatting rule should look similar to below.
With the formatting rule defined, we can finish this up by applying the rule to columns in the block. To apply the rule, select the data in each column, one at a time, and use the drop down arrow next to Formatting Rules on the Analysis/Conditional menu. Click on the formatting rule Highlight Alternate Break to apply the rule.
Once we are satisfied that the highlighting is working correctly, we can remove Break Highlight from the block.
