Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Formatting a Report

Specify the Number Magnitude

By default, Adaptive numbers throughout your OfficeConnect report are rounded to a magnitude of thousands. If your raw number is $1,222,333,444.5555, the rounded number will be $1,222,333.44 to mean $1,222.333.44 thousands. If you specify a magnitude of millions, this same number would be rounded to $1,222.33 to be read as 1,222.33 millions. Specifying the magnitude of the numbers in your report can make the numbers easier to read and understand.

To change the rounding magnitude of numbers in your book:

  1. Click Book Properties from the Book group in the OfficeConnect tab.

  2. On the Format tab, click in the Round To box in the Rounding section.
    The drop-down lists your magnitude choices:

    SNAGHTML11ea7b9

  3. Click the rounding magnitude you want to apply throughout your OfficeConnect workbook.
    The Sample box shows the result of raw data versus rounded data according to your choice.
    If you don’t want to apply any rounding to the values in the workbook, click No Rounding.

  4. Click OK.

  1. Click Refresh from the Design group in the OfficeConnect tab.
    If you have multiple worksheets in your workbook, click Refresh > All Sheets. The rounding magnitude setting applies to the entire workbook. If you have multiple worksheets, it’s best to refresh all worksheets at the same time.
    The Adaptive numbers in your report are rounded to the magnitude you specified.

The Rounding setting in the Book Properties dialog box establishes the rounding magnitude for the workbook. If required, you can specify exceptions to the general magnitude setting by using the Selection Properties. For example, create exceptions if you want all the numbers in your report to be rounded except for certain types of numbers like Earnings Per Share.

To set an exception to the workbook rounding magnitude:

  1. Select the row, column, or cell in which you want to make an exception to the workbook rounding magnitude setting.

  2.  Click Selection Properties from the OfficeConnect tab > Design group.
    The Selection Properties dialog box appears, as shown in Figure 51.

  3. Select the Suppress Rounding (Do Not Round Amounts)  check box.

  4.  Click OK.
    The numbers in the selected area expand to the original un-rounded value:

If necessary, add a label to the un-rounded numbers to clarify that these values are exceptions to the report’s general rounding magnitude setting.

If you want to cancel the exception, select the row, column, or cell, and then click OfficeConnect > Design > Selection Properties. Clear Suppress Rounding and click OK. The selected values are rounded like the rest of the workbook.

Note:  OfficeConnect automatically widen columns to accommodate large numbers. On the OfficeConnect tab, in the Book group, click Book Properties. On the Format tab, under Column Display, clear the Autofit columns when refresh data box if you do not want columns to resize. Click OK. Note that columns do not narrow with shorter data, but you can do that manually if necessary.

Suppress Zero Rows or Columns

If you’ve applied elements or a filter that results in certain rows or columns showing all zeros, you can hide those zero rows or columns to make your report cleaner and easier to read. You can suppress all zero rows or columns in the workbook by using the Book Properties command. Alternatively, you can suppress only those rows or columns you select by using the Selection Properties command. Either way, you can control whether rows or columns with no data are shown or hidden by using the Suppress Zero command.

To hide zero rows or columns throughout the workbook:

  1. Click Book Properties from the Book group in the OfficeConnect tab.
  2. On the Format tab, select whether you want to suppress rows or columns with no data:



    Under Row Display, select the check box labeled Suppress rows with all zeros. (The check box might already be selected, as this is the OfficeConnect default.) Under Column Display, select the check box labeled Suppress columns with all zeros. You can select both if you want.
  3. Click OK.

  4. Click Refresh from the Design group in the OfficeConnect tab.

  5. To suppress zeros, click Suppress Zero on the OfficeConnect tab.
    When Suppress Zero is enabled, it’s highlighted in orange.
    Any rows or columns that have all zeros are suppressed. If any data becomes available for a suppressed row or column, it shows in the report again.

  6. To show any suppressed rows or columns, on the OfficeConnect tab, in the Show group, click Suppress Zero to turn it off.
    When the Suppress Zero command is off, it’s no longer highlighted in orange.

To suppress zero rows or columns for selected areas:

  1. Select the rows or columns that you want to suppress as long as they show all zeros.
  2. Click Selection Properties from the {{oc}} tab > Design group, .
    The Selection Properties dialog box appears, providing choices depending on whether you selected a row or column.
  3. If you selected a row, click in the Suppress row with all zeroes box, and choose Suppress row with all Zeros from the drop-down.
    If you selected a column, click in the Suppress column with all zeroes box and choose Suppress column with all zeroes in the drop-down:

  4.  Click OK.

  5. Click Refresh from the OfficeConnect tab > Design group.

  6. Click Suppress Zero from the OfficeConnect tab > Show group  to turn it on.
    When the Suppress Zero command is on, it’s highlighted in orange.
    If the rows or columns you selected to suppress have all zeros, they are now suppressed.

  7. To show any suppressed rows or columns, click Suppress Zero from the OfficeConnect tab> Show group to turn it off.
    When the Suppress Zero command is off, it’s no longer highlighted in orange.

    If you want to show rows or columns with all zeros, make sure that the Suppress Zero command is off so you can see areas that would otherwise be suppressed. Select the rows or columns, and then click OfficeConnect > Design > Selection Properties. Click in the Suppress row/column with all zeroes box, and then click Suppress row/column with all zeroes in the dropdown menu or Use Settings From Book, and then click OK. Refresh the report.

You can use the book properties and selection properties in conjunction with each other if you want. For example, you can set the book property to hide all columns with all zeros, but use the selection properties to specify exceptions and show certain selected columns with all zeros.

Note:  The Suppress Zero command operates only on rows and columns to which you’ve applied Adaptive design elements. If you’ve used Excel functionality to add rows or columns that are filled with zeros, for example, a Total Revenue row, those areas will still show.

 

  • Was this article helpful?