Formatting text

Contents[Hide]

1. Overview

This article shows you how to format numbers and dates or times displayed in your metric set. Formats can be set in the metric set, or by using placeholder keywords in a data visualization's properties.

Related video: Using The Data Analysis Panel

2. Metric set element format

You can format a measure displaying numeric values by changing the metric set in the Data Analysis Panel.

Click the green measure tile for your measure to edit its settings, then locate the Format field and enter a format.

Enter a format
Enter a format

Some common examples of formats are listed below. (Dundas BI uses .NET formats, including standard numeric format strings and custom numeric format strings.)

Format TypeFormatDisplayed Text
Numeric with 5 decimal places N5 80,487,704.17919
Currency with 2 decimal places C2 $80,487,704.18
Percent (multiplied by 100) with 0 decimal places P0 8,048,770,418%
Exponential/scientific with 2 decimal places E2 8.05E+007
Custom numeric (1 non-zero decimal place) 0,0.# 80,487,704.2
Custom numeric (thousands) #,0,K 80,488K
Custom numeric (millions with 1 decimal place) #,0,,.0M 80.5M

Tip
You can also set the format for a measure in the data cube. See the Process Result article for details.

2.1. Formatting currency

When using the 'C' currency format, the user's culture determines which currency symbol to use and where to place it.

Automatic currency formatting
Automatic currency formatting

Important
This automatic behavior does not change the numeric value. Unless you are have values stored or converted into the currency corresponding with the user's culture and have configured it to display automatically, users will see the same numeric value with a different currency symbol unless Symbol is set.

Normally, your values are in a specific currency and you should enter the corresponding Symbol. It will be placed according to the user's culture, but will display the same symbol to all users.

Change the currency symbol
Change the currency symbol

If you prefer to format a value with a specific symbol placement that does not change based on culture, use other format strings such 'N' or '0.00', and specify the placement using the Symbol Placement setting.

Specify the symbol placement on other string formats
Specify the symbol placement on other string formats

2.2. Formatting magnitude units

Numeric values can be further formatted with thousand, million, billion, or trillion units of measure automatically according to the magnitude of the values as they change. For example, to display a value larger than one million with an 'M' symbol following an appropriately divided value, enter 0M into the Millions Format field. The '0' is a placeholder for the corresponding number of millions.

Specify the format for magnitude units
Specify the format for magnitude units

Note
Chart axes and gauge scales do not display measure values directly but their own reference values. They have their own versions of these properties that can be set in the Properties window.

If the Format Large Values Separately option is checked, numbers in different rows or columns can be formatted into different magnitude units as they vary. When this option is unchecked, all numbers displayed at one time use the same unit for easier comparison.

Values formatted using common unit of thousands
Values formatted using common unit of thousands

Click Add range format override to indicate a different format for a specific range of values. For example, to reduce the number of decimal places for larger numbers.

Range format override
Range format override

2.3. Formatting unknown values

The value displayed for unknown values in a hierarchy can be customized. To change the default caption of "Unknown" (in English), set the Unknown Member Caption.

Changing the Unknown value text to custom value
Changing the Unknown value text to custom value

When using a missing data rule on a measure with an Output Rule of Empty, there is an Empty Value Text setting that can display text in empty cells instead of leaving them blank.

3. Placeholder keywords

Various text and tooltip properties of data visualizations accept placeholder keywords that resolve into corresponding measure or hierarchy values for each data point, row, column or cell. When you click into one of these properties in the Properties window, a popup will appear with suggestions for the relevant keywords.

You can optionally edit these keywords to customize the formatting to be different than the format set up in the metric set or time dimension.

3.1. Measure and hierarchy keywords

The figure below shows the use of a placeholder keyword to display a LineTotal measure value atop each data point, where each value is formatted using the 'N0' format string.

Using a placeholder keyword in a Text property for data label settings
Using a placeholder keyword in a Text property for data label settings

The keyword consists of square brackets enclosing the name of a measure or hierarchy plus an optional format string preceded by a colon. For example: [LineTotal:N0].

Placeholder keywords support most standard date and time format strings, custom date and time format strings, and standard numeric format strings. (Custom numeric format strings are not supported in visualization keywords.)

Examples of numeric formats you can use:

Format TypeFormat String
Numeric with 2 decimal places N2
Currency with no decimal places C0
Percent (multiplied by 100) with 1 decimal place P1
Whole numbers, 3 digits D3

Examples of date and time formats:

Format TypeFormat StringDisplayed Text
Short date d Jun/12/2015
Long date D Friday, June 5, 2015
Short time t 8:31 AM
Long time T 8:31:05 AM
Long date, short time f Friday, June 5, 2015 8:31 AM
Long date, long time F Friday, June 5, 2015 8:31:05 AM
Month/day M June 05
Month/year Y 2015 June
Year  y, yy, yyyy 5, 05, 2005
Month M, MM, MMM, MMMM 4, 04, Apr, April
Day d, dd, ddd, dddd 9, 09, Wed, Wednesday
12-hour clock h, hh 9, 09
24-hour clock H, HH 9, 09 & 21, 21
Minutes m, mm 6, 06
Seconds s, ss  8, 08
AM/PM t, tt a, AM
Fractional seconds  f, ff, fff 6, 61, 611

3.2. Hierarchy Level keyword

By default, a tooltip for a chart series data point shows the hierarchy caption (for example, Product) and corresponding member caption (for example, Road Bikes).

If you want to display the caption of the hierarchy level, go to the visualization property, such as the Tooltip Text for a chart series. Enter a keyword that appends ' Level' to the name of the hierarchy, like this: [<name of your hierarchy> Level]

For example, use the keyword [Product Level] to display the caption of a Product hierarchy level, or [Date Level] to display the caption of a Date hierarchy level.

Using the Level keyword
Using the Level keyword

Click View in the toolbar to hover over and check the tooltip for a data point.

Tooltip displays the hierarchy level
Tooltip displays the hierarchy level

3.3. Table keywords

You can use the Header Text property to change the text displayed in the column header, and the Custom Text property to change the text displayed in the cells of the column. These properties support regular text as well as keywords. The Header Text property supports keywords referring only to hierarchies placed on Columns in the metric set.

When the metric set is transposed, the keywords [Measure Name] and [Measure Value] can be used for whichever measure is displayed in each row.

3.4. Chart keywords

The following chart-specific keywords are supported for all label text, tooltip text, or legend text properties of the relevant chart.

KeywordChartDescription
[Total] Stacked and 100% stacked charts, Pie charts In any stacked chart type series, display the stacked total up to and including that data point (this technically includes Pie charts, where each segment stacks angularly from the start angle around the circle).
[Percent] 100% stacked charts, Pie charts In any 100% stacked chart type series, including Pie charts, display the percent of the data point out of its stack total.
[Member] Pie chart, Waterfall chart, or any chart where the property Hide Total Values is unchecked. The hierarchy member caption in any chart displaying totals.
[Measure Name][Measure Value] Charts displaying a transposed metric set The value corresponding with whichever measure is displayed by each data point.

3.5. Visualization-specific keywords

The following keywords are supported for all label text, tooltip text, or legend text properties of the relevant visualization.

KeywordVisualizationDescription
[Flow] Sankey diagrams (nodes) The total quantity flowing in or out of the node.
[Member] Treemaps, Relationship diagrams, Chord diagrams (nodes) The hierarchy member caption.
[Level] Treemaps, Relationship diagrams, Chord diagrams (nodes) The hierarchy level caption.
[Size] Treemaps The value of the measure used to determine the node size.
[SizeMeasure] Treemaps The name of the measure used to determine the node size.
[Source] Sankey diagrams (links), Chord diagrams (links) The source member caption.
[SourceValue] Chord diagrams (links) The source member value.
[Target] Sankey diagrams (links), Chord diagrams (links) The target member caption.
[TargetValue] Chord diagrams (links) The target member value.
[Value] Chord diagrams (nodes) The hierarchy member value.

3.6. Dynamic element keywords

The following keywords are used in the tooltip text properties of a chart series or table column when using a Dynamic Element filter.

KeywordDescription
[Dynamic Measure Caption] The caption of the current dynamic measure (e.g., OrderQty or Line Total).
[Dynamic Measure] The numerical value of the current dynamic measure.
[Dynamic Hierarchy Level] The caption of the current dynamic hierarchy level (e.g., Product Subcategory)
[Dynamic Hierarchy] The caption of the current dynamic hierarchy level member (e.g., Bib-Shorts)

3.7. Size and color rule keywords

The following keywords are supported for the legend text property of size and color rules (i.e. Range Size Rule, Auto Size Rule, Range Color Rule, and Auto Color Rule) in any visualization.

KeywordDescription
[From] The starting value in the range.
[To] The ending value in the range.

4. Format properties

Dedicated format properties in data visualizations (such as on chart axes and gauge scale label styles) support the exact same format strings as measure/hierarchy keywords (and have the same restrictions).

Using a date/time format string for an axis label Format property
Using a date/time format string for an axis label Format property

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri