---
title: "Column Formatting"
canonical: "https://wiki.yellowfinbi.com/space/yfcurrent/2197024/Column%20Formatting"
format: markdown
---
> Macro (anchor)
> Macro (toc)
## Overview
The Column format tab contains a number of sections that you can use to format your report fields. For instance, you could use this feature to display flags in a column report that contains country names.
## How to Apply
1. Create a report as you normally would.
2. While in the Data mode or the Design mode, click on the Column Formatting icon in the header.
3. When the following popup appears, select a field from the left side.
4. Once a field is selected, the column formatting settings will appear in the popup.
5. Simultaneously, you could also bring up this popup by clicking on a column's menu, then selecting Format, and finally clicking on Edit.
6. See the below section to learn about the different types of formatting you could apply to a report column.
## Column Formatting Settings
Each of column formatting setting options is described below.
Display
| **Option** | **Description** |
| --- | --- |
| **Display** | To change the display name of the column from the default value simply update this field. |
| **Format** | Each data type will have a unique set of format options – eg Text, Date or Numeric.
> See [Display Format Types](#) for details on each type. |
| **Description** | Enter or update the description of a calculated field to allow report writers to understand its purpose. This description will appear in a tooltip when hovered over calculated field columns. |
| **Sub Format** | Depending on the format option you have chosen for the column above you will have a separate set of sub format options. Select the appropriate sub format option. |
| **Date Other** | If you select ‘Other’ from the date sub format you will be able to build your own custom date format.
For example to create a Japanese date format which includes characters, eg. 2003?4?2?would be created by adding in: **yyyy?M?d ?** |
| **Decimal Places** | If you have a defined a numeric format you can set the number of decimal places to be defined. This can be used to define cents in a decimal place for $20.00 by adding in:**2**
**Note:** To convert numeric data by doing divide by 1,000 calculations etc you would use the data conversion options in advanced functions which are available on the Report Fields page.
> See [Advanced Functions](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2198199) for more information. |
| **Prefix** | The prefix is used to include additional characters **before** the value that is returned from the data base. This can be used to define currency for $20.00 by adding in: **$** |
| **Suffix** | The suffix is used to include additional characters **after** the value that is returned from the data base. This can be used to define percentage for 30% by adding in: **%** |
| **Rounding** | The rounding format allows you to choose how a decimal value should be rounded.
- **Round Up:** Will round any decimal up eg. 1.1 to 2
- **Round Down:** Will round any decimal down eg. 1.9 to 1
- **Round Half Up:** Rounds 0.5 and above up
- **Round Half Down:** Rounds 0.5 and below down |
| **Thousand Separator** | Turns the defaulted thousand separator for your instance on or off. For example:
1000 to 1,000 |
| **Bracket Negatives** | Displays negative values with or without brackets. |
| **Optional Field** | This feature is new from version 9.17. It allows fields to be added to reports that are not initially visible to the user. The user can use the dynamic column picker to add or remove fields from the report at run time. There are four possible settings:
- Always included - the field will always be shown on the report and cannot be hidden
- On by Default - the field will initially be shown but is can be hidden by the user
- Off by Default - the field will initially not be shown, but can be shown by the user |
| > Macro (anchor)
**Show Field** | To hide the column from the report, select this item. By hiding a column the data presented on the page is not re-grouped which would occur if you removed the field from your report. For Example: |
| **Suppress Duplicates** | The suppression of duplicate option will remove duplicate values from a column and group the values under a single value. |
Sorting
| **Option** | **Description** |
| --- | --- |
| **Direction** | Apply sorting to an individual column. If you wish to use multiple fields to provide a sort order, see [Table Sort](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2204603/Report+Formatting#Table). |
> Macro (html-bobswift)
Data
| **Option** | **Description** |
| --- | --- |
| **Font Style** | Define styling options for the text in this field. This covers the font face, font size, font colour, and font style. |
| **Alignment** | Define the alignment option for text in this field. |
| **Background** | Define a custom background colour for the column. |
| **Column Width** | Define the width of the column. |
| **Maximum Length** | Define the maximum number of characters to be displayed in the cell. |
| **Wrap Text** | Wrap long cell text across multiple rows. |
| **Wrap Text on Character** | Wrap long cell text on the line feed character (\n). This setting will force the header text to wrap at the specified point regardless of whether the full text would fit in the field or not. |
| **Vertical Alignment** | Define the vertical alignment for text in this field - top, middle or bottom. |
Borders
| **Option** | **Description** |
| --- | --- |
| **Position** | Define where borders should be displayed around the edges of the cell. |
| **Colour** | Define the colour of the cell borders. |
| **Width** | Define the thickness of the cell borders. |
Summary
| **Option** | **Description** |
| --- | --- |
| **Total Aggregation** | Define the aggregation to be applied to the column as a total.
**Note:** the calculated total is only available for calculated fields and will create a total based on the same rules as were used for the calculation. For example if you have a ratio of Received / Invoiced the total will equal the Sum (Received) / Sum (Invoiced) |
| **Display Total Value** | Show or hide the total aggregated value of a column. Note that this does not affect subtotals, i.e. if you’ve chosen to hide the total, the subtotals will still be displayed. Works for regular and cross-tab reports. |
| **Move Total Value Location** | When Display Total Value is activated, choose whether to display the table totals at their default location of the end of the table, or activate this option to move the totals to the start of the table. |
| **Display Labels** | Display a text label for the column summary. |
| **Style** | Define custom formatting for the summaries of this column. This covers the typeface, font size, colour and emphasis, and text alignment. |
| **Total Border Style** | Toggle this on to configure a line style to appear as the top-border of a total. This is useful for creating financial style reports that have single and double lines above the totals.
When this is checked on, a drop down list appears that allows a choice between a single solid line, single dashed line , single dotted line or a double solid line. |
| **Background** | Define the background colour for the column summary. |
| **Sub Total** | Display a sub total row for each unique value in the column. |
| **Move Sub Total Value Location** | When Sub Total is activated, choose whether to display the table subtotals at their default location of the end of each section, or activate this option to move the subtotals to the start of each section. |
| **Hide Sub Total on Columns** | Select column fields from this list to hide their subtotal. Works for regular and cross-tab reports.
**Tip:** Remember to disable conditional formatting on subtotal cells if you’re opting to hide the subtotal values. |
> Macro (html-bobswift)
#### Display Format Types
Based on the type of field that the column being formatted is there are various format options. The ones listed below come default with Yellowfin, however as this is customisable there may be additional ones that comes as part of your installation.
| **Format Option** | **Description** |
| --- | --- |
| **Text** | Displays as plain text. |
| **Action button** | Allows you to create an action button linked to a URL. You can pass the value of the returned data into a URL link. Use double hashes **##** to indicate where you want the column value to be placed in the url itself. Click [here](#) for additional settings related to this formatter.
For example, the system will replace the ## in the following link with the column value, and initiate a Google search on it. [http://www.google.com.au/search?hl=en&q=##](http://www.google.com.au/search?hl=en&q=##)
Note: This formatter is similar to the ‘Link to URL’ formatter. |
| **Case Formatter** | Allows you to format text as **Uppercase** or **Lowercase**. |
| **Email** | Creates a hyperlink on the text that will open an email client and pre-populate the sent to address. |
| **Email Salutation** | This field will be used as the email salutation when broadcasting using a report as a recipient list. |
| **Flag Formatter** | If your data contains ISO country codes you can display these as flags of the world instead of text. |
| **HTML** | Formats a field containing HTML tags, either by removing them, or using them, depending on user selection. For example, if you wanted to display an image using a URL the field may look something like this:
`
`. |
| **HTML 5 Video** | Displays a video from a path stored in the field, either a full URL, or a relative path if the video is stored in the Yellowfin ROOT directory. |
| **Image Link Formatter** | When a field contains a URL to an image file, choosing this option displays the image rather than the URL, effectively providing images within reports. |
| **Link To URL** | Allows you to pass the value of the returned data into a URL link.
Use the hashes ## to indicate to Yellowfin where you want the column value to be placed in the URL itself.
For example: Formatting on a column of IP addresses and the URL typed in is:
[http://www.google.com.au/search?hl=en&q=##](http://www.google.com.au/search?hl=en&q=##)
This essentially means that every IP address will be placed into it into it. For example:
[http://www.google.com.au/search?hl=en&q=10.100.32.44](http://www.google.com.au/search?hl=en&q=10.100.32.44)
You can also inject the value from another column into the url. This can be done by referring to the position of the column. For example:
[http://www.google.com.au/search?hl=en&q=%7B%7B2](http://www.google.com.au/search?hl=en&q=%7B%7B2) }}
Where {{2}} refers to the second column in the report. You can also reference the visible display name of the column, for example:
{{Athlete ID}} |
| **Link To Application URL** | This formatter works in a similar way to Link to URL, however it allows data to be formatted as links that point to a preconfigured end-point on a per Client org basis.
This is set up by adding a custom parameter named “APPLICATION_URL”, and assigning a URL. This can be set up for each Client.
You can read about setting up Custom Parameters [here](https://wiki.yellowfinbi.com/space/yfcurrent/2199341/Custom+Parameters).
This formatter is useful for Saas implementations where links might point back to an application page, but where the url for each configured customer is different, eg. client.application/page.
As per the Link to URL formatter, you can use hashes ## to indicate to Yellowfin where you want the column value to be placed in the URL itself.
For example: Formatting on a column of IP addresses and the URL typed in is:
[http://www.google.com.au/search?hl=en&q=##](http://www.google.com.au/search?hl=en&q=)
This essentially means that every IP address will be placed into it. For example:
[http://www.google.com.au/search?hl=en&q=10.100.32.44](http://www.google.com.au/search?hl=en&q=10.100.32.44)
You can also inject the value from another column into the url. This can be done by referring to the position of the column. For example:
[http://www.google.com.au/search?hl=en&q=%7B%7B2](http://www.google.com.au/search?hl=en&q=%7B%7B2) }}
Where {{2}} refers to the second column in the report. You can also reference the visible display name of the column, for example:
{{Athlete ID}} |
| **Reference Code** | Converts the text in the cell to the value of an internal lookup table. E.g. AU to Australia. See [Reference Codes](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2201784) for more information. |
| **Separated Value Aggregator** | This is a formatter for text fields that contain separated values, such as a comma separated list. Eg. Apple, Orange, Banana.. The formatter will reformat the list of items into ", +n" format. Eg. Apple, +2 |
| **Sparkline Formatter** | Allows you to create a single sparkline column or column chart within the report table. Click [here](#) for additional settings related to this formatter.
**Tip:** You may use this in conjunction with the Sparkline Array advanced function, or any text field that contains comma separated numeric values. For example, you may create a calculated field with multiple values gathered from different metrics or dimensions to create a sparkline chart, as long as they are comma separated. |
| **Raw Formatter** | Displayed the data as it would have been returned from the database – no additional formatting applied. |
| **URL Hyperlink** | Creates a hyperlink on the text and will open web page on click. Assumes the text is a legitimate URL. |
| **YouTube Formatter** | This displays a YouTube video, based on the ID being stored in the field. |
| **Date** |
| **Date** | Displays value as a date – multiple date options exist. |
| **Time** | Displays value as a time field – multiple date options exist. |
| **Timestamp** | Displayed full date and time value |
| **Date Part Formatter** | Takes a date field and formats the display to show part of that date. |
| **Numeric** |
| **Numeric** | Displays value as a decimal – allows you to set the decimal places to be used. |
| **Percentage Bar** | Converts a percentage value less than or equal to 100 into a bar. |
> Macro (anchor)
### Action button formatter settings
Below are descriptions of all settings used to configure action buttons in reports.
| **Option** | **Description** |
| --- | --- |
| **URL** | Define the URL to use, including ## to be replaced by the field value. You can also reference other columns of data in the table by using the **{{1}}** syntax, where ‘1’ is the position of the column appearing in the report table. Note that the position numbering starts from‘1’ rather than ‘0’. |
| **URL type** | Specify whether the URL points to something external (Remote) or internal (Local) in the system. |
| **Apply URL Encoding** | Enable this option to apply URL encoding used for special characters (such as %20 for a space) to the field values. Note that this applies to the entire URL, so this may break URLs if they contain symbols because symbols would inadvertently be replaced with encoded values too.
Disable this option if you wish to leave field values in their original format. This is recommended for URLs that contain symbols so that they remain as symbols instead. |
| **New window** | Allows you to open the URL in a new browser window when enabled. |
| **Button display text** | Enter the text to be displayed on the action button. |
| **Disable button on click** | Specify whether the button should be disabled or not when clicked. |
| **Use data values to disable button** | The button will become disabled by specified data values in a given column. |
| **Status field** | Enter the column number from disabled values will be sourced. |
| **Disabled values** | No action button will be displayed for these values, a completed status button will be displayed instead. |
| **Inactive display** | Choose how the button should be displayed for inactive status. You can choose to display a blank cell, a success icon, or an inactive button. |
> Macro (anchor)
### Sparkline formatter settings
Below are descriptions of all settings for the Sparkline formatter. Only one Sparkline chart per table is currently supported.
| **Option** | **Description** |
| --- | --- |
| **Width** | Specify the maximum width of the sparkline chart. |
| **Height** | Specify the maximum height of the sparkline chart. |
| **Sparkline type** | Select whether the chart should be displayed as a sparkline or a column. |
| **Includes scaling value** | Enables scaling on the chart. Scaling enables the chart to scale lines by observing values of all rows. If left unscaled, a sparkline with smaller values, such as 10, 21, 35, and so on, might look similar to a sparkline with drastically different values that have similar value differences, such as 100, 210, 350, and so on.
If enabled, the first data-point will be treated as a scaling value, and will not be rendered. |
---
## Column Drop Down Menu
If you wish to select a column to format from the table you can do so by clicking the menu drop down in the column title.
| **Option** | **Description** |
| --- | --- |
| **Aggregation** | Allows the user to apply [Aggregations](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2200234) to the field. |
| **Sort** | Allows the user to apply sorting to the individual field.
- None: removes any sorting applied to the field.
- Ascending: sort the data in ascending order – A to Z or 1 to 9.
- Descending: sort the data in ascending order – Z to A or 9 to 1. |
| **Advanced Function** | Allows the user to apply an [Advanced Function](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2198199) to the field. |
| **Format** | Opens the Column Formatting menu with this field selected to allow the report writer to apply formatting options. |
| **Clear Formatting** | Allows the report writer to clear all formatting options applied to this field. |
| **Conditional Formatting** | Allows the user to open the [Conditional Formatting](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2199746) menu for this field in order to apply alerts. |
| **Group Data** | Allows the user to create groups of values based on the data in the field.
e.g. age (1-18 = Youth, 19-36 = Gen Y etc) |
| **Totals** | Allows the user to apply a summary aggregation to the field. |
| **Hide Field** | Allows the user to hide the field from display. |
## Column Drag & Drop Options
Most of the formatting options available to you are accessed through the format menus. However, once your report has been generated you can use some drag and drop formatting options to change the layout of your report.
**Note:** the drag and drop formatting are only available whilst a report is in DRAFT mode. If the report is ACTIVE you will not see these options.
### Column Order
You can change the order that columns will appear in two ways. The first option is to drag and drop a column to a new position directly from the report itself. This can only be done in the Data tab when the report is being newly built or is in draft mode.
1. To move a column, place your cursor over the column title and click and hold the mouse button. Start to drag the column to its new position - a + symbol will appear indicating that drag and drop is enabled.
2. Now drag your column into the desired location. You will see an outline of the column to indicate what position it will be moved to.
3. Drop the column and the page will be refreshed with your column in the new location.
You can also change column order from the Column Formatting popup. This can only be done in the Data tab when the report is being newly built or is in draft mode.
1. Open the Column Formatting popup by clicking on the Column Formatting icon in the toolbar.
2. Currently selected columns will appear in the Report Fields section on the left hand side. If the report is a cross-tab, then the columns will be in separate sections reflecting whether the field is used as a Column, Row or Measure. If the report is not a cross-tab report, then all the fields will appear in a single section.
3. Fields can be dragged and dropped to a new position. Note that fields cannot be moved between sections. So if a field is in the Column section, it cannot be moved to the Row section from this popup.
4. Close the popup by clicking the x in the top right corner in order for the column order changes to take effect.
> Macro (inline-media-image)
### Column Width
You can resize a column as seen on a report by placing you cursor over the right hand column border of the column you wish to resize.
1. Click and hold the cursor. The cursor will be represented as a horizontal line and the column outline will be highlighted.
2. Drag your column to the desired width and let the cursor go. The report will refresh and your column will be resized.
