Cell Formatting
- Enoch Poston
- Kevin Meek
The cell formatting menu allows you to customize how data values will show, add custom borders, and add formatting that will only show if a condition is met.
Â
Number
If a cell has a numeric, date, or time value, then you can use Number formatting to choose how the value should appear on the report. For example, you could add a dollar sign ($) to monetary values and separate each three digits to make values easier to read.
Numeric values with currency styled formatting
The following options for Number formatting are available:
Â
General
Format the data using the default settings for your environment. This is the default option. The application will assume the data type based on the value.
Â
Number
Format the data as a number, currency, or percentage.
Optional: Choose how the number displays:
In the Decimal Places field, enter a number for how many decimal places to display. Then, in the field to the right, enter a symbol to use as the decimal mark.
To show a delimiter every three digits, select Use 1000 Separator. Then, in the field to the right, enter a symbol to use as the delimiter.
To show a currency symbol before the number, select Use Currency Symbol. Then, in the field to the right, enter the symbol to show.
To show a percent sign (%) after the number, select Append Percent Sign.
To show no value if the number is 0, select Blank When Zero.
To show a minus sign (-) in front of negative numbers, select Show Negative Symbol.
To show parentheses ( ) around negative numbers, select Show Parenthesis.
To show negative numbers in a different color, enter a color code in the Color field or use the color picker to choose a color.
Â
Date
Format the data as a date, time, or date and time.
Optional: Choose which date and time components to display, and how to show them. Either select one of the patterns from the Date/Time Format list, or enter a custom pattern using the following variables:
Variable | Description | "Sept-2-1907 5:08:04 PM" |
d | day of the month, from 1 to 31 | 2 |
dd | day of the month, from 01 to 31 | 02 |
ddd | day of the week, abbreviated name | Mon |
dddd | day of the week, full name | Monday |
M | month, from 1 to 12 | 9 |
MM | month, from 01 to 12 | 09 |
MMM | month, abbreviated name | Sept |
MMMM | month, full name | September |
y | year of the century, from 0 to 99 | 7 |
yy | year of the century, from 00 to 99 | 07 |
yyyy | year, from 0001 to 9999 | 1907 |
h | hour using a 12 hour clock, from 1 to 12 | 5 |
hh | hour using a 12 hour clock, from 01 to 12 | 05 |
H | hour using a 24 hour clock, from 0 to 23 | 17 |
HH | hour using a 24 hour clock, from 00 to 23 | 17 |
m | minute, from 0 to 59 | 8 |
mm | minute, from 00 to 59 | 08 |
s | second, from 0 to 59 | 4 |
ss | second, from 00 to 59 | 04 |
t | A/P | P |
tt | AM/PM | PM |
Â
Text
Do not apply any formatting to the data, and show it exactly as it appears in the database.
Â
Border
Alter the width and color of the cell borders. To set a color for a cell border, enter a color code or select a color from the picker. To set the width of the border, enter a pixel value, or use the arrows to make the border thicker or thinner.
To set all the cell borders to the same color and width, select Make Borders Uniform.
Tip:Â If gridlines are enabled for the Report Viewer, then cell borders will show in addition to the gridlines.
Choosing border colors and widths
Â
Conditional
A conditional format allows you to format a cell according to its output data. The cell and text styles can depend on its data value, and you can even conditionally hide rows or entire sections. This can be useful for highlighting certain values in a data set, such as outliers from a trend.
Conditional formatting uses a formula to set the condition. The formula must evaluate to True or False. If True, the formatting will be applied, and otherwise it will not. Conditional formulas are often based on data in the cell, but they can also be based on other cells, data fields, or other information about the report.
Example of a formula that evaluates to True or False
To set or modify the format of a cell based on a conditional formula:
Click  Add to create a new condition.
From the Action list, select an action to occur if the condition is met.
Optional: If applicable, select an attribute for the action from the Attribute list.
Click the formula  icon and enter a formula for the condition. The formula must evaluate to True or False.
To use the value of the current cell in the formula, use the functionÂ
CellValue(). Click  Cell Value to insertÂCellValue() into the formula.
A cell can have multiple conditional formats, each of which is a separate row in the Conditional page. If two or more overlap, the lower condition takes precedence. Click the Move Row Up  and Move Row Down  icons to reorder the precedence of the conditions.
A cell with multiple conditional formats