November 2024 (v24.11)

Formatting a Report at Cell Level

6 Minutes to read

Print
Dark

Light
PDF

Formatting a Report at Cell Level

6 Minutes to read

Print
Dark

Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

The Format functionality contains the following tabs:

Text

STYLE - Apply bold, italic, underline or double underline. Select a font, font size, font color, and background color.
ALIGNMENT & INDENTATION - Select to justify right, center or left. Select right or left indent.
BORDERS - Select border options and border color.

Data

NUMBER FORMAT

Display as - Select to display the cell contents in number, currency, or percentage format.
Show Zero As - Select how you want to display zero values.
Decimal Places - Choose up to 6 decimal places for numbers. Decimal places are displayed in Dynamic Reports based upon whether Report Sets are used or not. When Report Sets are not used in a Dynamic Report, decimal places displayed in quick functions are identical to those in the Dynamic Report. When Report Sets are used, decimal places in quick functions allow for the maximum number of decimal places allowed for a selected range.
Negative No. - Determines the format of the negative number. You can display negative numbers in parentheses, in color, or both. If you choose Absolute as the format, negative numbers will be displayed as positive numbers without any formatting.
Number Scale - Defines a value that the numbers displayed in the Dynamic Report must be multiplied by to get the actual number.
Display Thousand - Determines whether a comma is to be placed to separate thousands.
Preview - Displays the format for negative numbers based on your selections.

Other

SUPPRESS

Empty Rows - Select to exclude empty rows from the report .
Empty Columns - Select to exclude empty rows from the report.

DRILL THROUGH -

Select to drill through Translation and Transaction details. Expand (drill down) and collapse (drill up) report detail.

Transaction and Translation data is not stored in the OLAP Cube from which the Dynamic Reports pull their data, which is why drill-through is required. This means that you cannot run a Dynamic Report to output Transaction or Translation data. For example, lets say you have a travel line item in your report that shows up as a number. You have to drill down to see what transaction data makes up that number. You cannot generate a report to open and display at the transaction level.

Note:

Once you start drilling down, some functionality becomes disabled such as Pivot and Sort.

With Cell Level Formatting, you can control the look and feel of report output down to the cell level. Apply formatting to the entire report, a row/column, a range of cells, or an individual cell. Additionally, you can:

apply formatting when using the insert formula row/column in a report
format a formula exception value
vertically top/center/bottom align headers and numbers in a report

Note:

Header and Footer rows honor the formatting applied from the Header & Footer section

If you opt-in, this functionality applies to all existing and new Dynamic Reports across all browsers. It is available for all reporting areas (Financial, Sales, Workforce).

Cell Level Formatting is enabled for Dynamic Reports that are designed using dimensions or attributes and Report Sets on the Row or Column axes.

Note:

The Cell level formatting options in Dynamic Reports are enabled by default for new and existing tenants.

The default format for a Dynamic Report is:

Column headers are center aligned and bold
Row headers are left aligned and bold
Drill down members are shown with indents
Numbers are right aligned
Font = Arial, size = 8, color = black, fill color = no color

Formatting is applied to all cells within the body of the report and:

Applies to row and column headers, numbers/data values in the report
Applies to merged cells (when multiple dimensions are used on the column axis)
Notes, Insert Formula row/column, Formula Exceptions

Formatting is retained when exporting a Dynamic Report, when it is included as an email attachment, linked report, and when used in a Report Collection or FPP.

When the structure of the report is modified by adding or removing dimensions from the row or column axis, formatting has to be readjusted for accuracy.

How to Apply a Cell Level Formatting to a Dynamic report?

Click Reports.
Open the required report, and click Format (an example is shown below).
The Format pane appears as shown below. There are 3 tabs; Text, Data, and Other. Ensure the Text tab is selected.
Select a cell and apply different styles. The same report shown above has been modified and is shown below.
To select a range of cells as shown below, drag the cursor as you would in Excel.
Change the font type and size. In the report below, font size is increased for Apr-23 and May-23.
Planful supports a range of font types, which are enabled in Static Report Sets. Select the list-box to view all supported font types. Fonts size 8 through 32 px are supported. The default is set to 8 for all existing and new Dynamic Reports across all the lines. Based on the font size the row height automatically adjusts.
Select alignment and indentation options. In the report shown below selected cells are vertically aligned to the bottom of the cell. Select horizontal alignment (left, center or right). Select vertical alignment (top, center or bottom). Select to indent left or right. Indents, horizontal, and vertical alignment also applies headers and data values.
Apply borders to the report. Select a cell or cells, select a border color and click the border type to be applied. In the image below, a yellow border is applied to a cell.

Here is the border color and border type selection pane. There are many options to choose from including, no border, left and right border, and top and bottom border.
Click the Data tab.
For Display As, select to display the cell contents in number, currency, or percentage format. When currency is applied to a cell or cells, the Currency Alignment options displays as shown below.
Select how you want the currency symbol displayed in the cell. There are two formats; Accounting and Currency. With the Accounting format, the currency symbol is displayed in the left-most position within the cell. With the Currencyformat, the currency symbol is displayed directly in front of the number in the cell.When multiple formats are applied to the report, only the most recently applied format gets applied to each cell in the report. When multiple cells with different formatting are selected, format of the first cell is displayed in the pane. Formatting is not supported when Properties are added to the report.
The Currency symbol displayed is based on your location settings.
For Show Zero As, select how you want to display zero values (as dashes, empty, or as 0) for a selected cell, cell range, or the entire report. Selecting 0 applies to all data values in the report including, Excel calculations, insert formulas, and formula exceptions.
Select the number of decimal places for a selected cell, cell range, or report. In the example below, 1 decimal places is selected for a cell. You can select 0 to 6 decimal places.
Select a Thousand Separator option. Display of the thousand separator is driven based on user location settings.
Finally, click the Other tab and select to hide or display empty rows and columns. Don’t forget to click Apply and Save!

Resetting Report Format

Let's say you made several formatting selections, but want to return to the default formatting, click the Reset icon. The Reset icon on the top of the Format pane resets the format values to the defaults.

Tip!

Use the Ctrl+A shortcut key to select the entire report and apply Text and Data formatting. Alternatively, use the Format button available on right click menu of a cell.

Situations When Formatting is Disabled or Can't be Applied

In certain situations, formatting might become disabled or has to be reapplied.

Running Borders and Currency Format

Running borders and data formatting is inherited from the parent member and applied to the drill down rows and columns. The images below depicts this behavior.

Running Border and Currency Format Applied