Using MOD and ROW to format hyperblocks

This topic describes how to use the MOD and ROW functions to assign different formats to alternate lines of a hyperblock-based report. It refers to sample report RP007 in the Best Practices sample database.

See Connecting to the sample database.

Report RP007 has two, overlapping hyperblocks, created from the Region and Period dimensions of the Analysis cube of the Best Practices sample database. The Region hyperblock is formatted to display the darker grey background, with a white bottom border. A conditional format in the hyperblock uses the MOD and ROW spreadsheet functions to apply the lighter grey background to alternate cells.

The ROW function returns the number of a row specified by a cell reference. Its syntax is =ROW().

The MOD function divides one number by another and returns the remainder. Its syntax is=MOD(number, divisor)

If you add =n to the MOD function, it returns TRUE or FALSE. For example, the formula =MOD(15,2)=1 returns TRUE. But =MOD(14,2) returns FALSE. That is, 15 divided by 2 leaves a remainder of 1. But 14 divided by 2 leaves no remainder.

The conditional format uses the ROW function to return the row number, and the MOD function to return the result of dividing that row number by 2. For each alternate row, the remainder of that division will be 0 or 1.

The formula for the conditional format is MOD(ROW(),2)=1. That is, the ROW function is used as the number argument of the MOD function. If the result of dividing the row number by two is a remainder of 1, the conditional format is applied.

To create a hyperblock-based report with alternating row colors:

  1. Open the report which contains the hyperblock to format.
  2. In Design Mode, select Format > Style
    The Edit Styles dialog is displayed.
  3. In the Style name field, specify a name for the style to apply to alternate rows of the hyperblock.
  4. Clear the Number format check box.
  5. Click Add.
  6. Click Change.
    The Format Cells dialog is displayed.
  7. Click the Pattern tab.
  8. From the Primary color palette, select the color to apply to alternate rows of the hyperblock and click OK.
  9. Click OK to close the Edit Styles dialog.
    In Design mode, the new style is applied to the currently selected cell.
  10. Select the Standard style from the drop-down list on the Formats tool bar.
  11. Click inside the hyperblock that you want to format and drag to select all the cells of the hyperblock.
  12. Right-click and select Format Cells.
    The Format Cells dialog is displayed.
  13. On the Pattern tab, select a color from the Primary color palette.
    Select a different color from that selected in Step 8.
  14. Optionally, on the Border tab, specify a bottom border.
    For example, specify a white border.
  15. Click OK.
    Note: The hyperblock cells remain selected. Do not click outside the hyperblock because this will deselect them. If they are deselected, reselect the cells as described at Step 11.
  16. Right-click in the hyperblock and select Conditional Formatting.
    The Conditional Formatting dialog is displayed.
  17. In the Condition section, click the arrow on the Cell value is button and select Formula is.
  18. Specify this formula: MOD(ROW(),2)=1 and click Add.
    Formulas in the Conditional Formatting dialog must not be preceded by the equals sign.
  19. Click the arrow on the Format button and select the style which you created at Step 1.
  20. Click OK.
    In View Mode, the colors you selected at Steps 8 and 13 are applied to alternate rows of the hyperblock.
Parent topic: Hyperblocks