The Cell Format Window allows you to customize how data values will display, add custom borders, and add formatting that will only show if a condition is met. It is broken down into three tabs: Number, Border and Conditional.
Click on the below links for details:
Number
If a cell has a numeric, date, or time value, 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.
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. Formatting may be automatically applied based on system configuration.
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, check the Use 1000 Separator checkbox. Then, in the field to the right, enter a symbol to use as the delimiter.
To show a currency symbol before the number, check the Use Currency Symbol. Then, in the field to the right, enter the symbol to show.
To show a percentage sign (%) after the number, check the Append Percent Sign (%) checkbox.
To show no value if the number is 0, check the Blank When Zero checkbox.
To show a minus sign (-) in front of negative numbers, check the Show Negative Symbol checkbox.
To show parentheses ( ) around negative numbers, check the Show Parenthesis checkbox.
To show negative numbers in a different colour, enter a hexadecimal colour code in the Color field or use the colour picker to choose a colour.
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 | Result for sample date of "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 century, from 0 to 99 | 7 |
yy | year of 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
Does not apply any formatting to the data and shows it exactly as it appears in the database.
Border
Alter the width and colour of the cell borders. To set a colour for a cell border, enter a colour code or select a colour 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 colour and width, check the Make Borders Fixed checkbox.
Tip: If gridlines are enabled for the Report Viewer, then cell borders will show in addition to the gridlines.
Conditional
A conditional format allows you to format a cell according to its output data. The cell and text styles can depend on the 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 be evaluated as True or False. If True, the formatting will be applied 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.
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. If applicable, select an attribute for the action from the Attribute list.ActionAttributeForeground ColorEnter a formula, or hexadecimal colour code, or select a colour using the colour picker, which will change the foreground (text) colour of the chosen cell.Background ColorEnter a formula, hexadecimal color code, or select a color using the color picker, which will change the background color of the chosen cell.Font FamilySelect a font to change the text to.Font SizeEnter a font size, in ems, to change the font size to.Boldno attributeItalicno attributeUnderlineno attributeHorizontal AlignmentChoose from Left, Right, Center, or Justify to which the text in the cell will change.Vertical AlignmentChoose from the Top, Middle or Bottom to which the text in the cell will change.Suppress Rowno attributeSuppress Sectionno attributePage Breakno attributeTip
Hexadecimal colour values may be upper or lowercase and may contain the # prefix character, but it is not required.
Click the Formula Editor icon to enter a formula for the condition. The formula must be evaluated as True or False. The Action will be applied to the report when the formula evaluates to True.
To use the value of the current cell in the formula, use the function
CellValue(). Click Cell Value to insertCellValue()it into the formula.
A cell can have multiple conditional formats, each of which is a separate row in the Conditional tab. 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.
Using Formulas as Conditional Formatting Colors
In addition to selecting a static colour with the colour selector, a formula may be used to change the colours of a cell when using conditional formatting.
A formula which returns a value convertible to a hexadecimal color code can be entered as the Attribute when the Action is Foreground Color or Background Color. When using this option, conditional formatting colour properties can be determined at runtime instead of report design time.
One application of this feature is to read colour values from a data source and use that colour data to apply formatting to a cell.
Clicking on the Color Selector's Set by Formula button will open the standard Formula Editor. All formula elements available in conditional formulas are available, including CellValue(), references to other cells, application parameters and Data Objects.
Aggregate functions are not available in attribute formulas.
If the formula returns null or an empty string, the conditional formatting will not be applied. If the formula returns a value that is not convertible to a hexadecimal colour code, an error message will be displayed in the cell.
Examples
This example uses conditional and logical functions to combine multiple conditions into one clause.
If(And(CellValue()>=1000, CellValue()<10000), "#FFFFFF", If(CellValue()>=10000, "#ECECEC", "#00000"))
This example uses an application parameter named ConditionalColor to select a colour to apply:
=@ConditionalColor@
This example simply refers to a Data Field that contains a hexadecimal colour value to apply:
{Products.ProductColor}
A few examples of valid hexadecimal colour codes:
#FFF
#c0c0c0
ff4c00
F90
Next Article: Sections in Advanced Reports