- Metric set element format
- Placeholder keywords
- Format properties
- See also
This article shows you how to format text values that are displayed in data visualizations. Text formatting can be done at the metric set level or by using placeholder keywords and format strings in supported data visualization properties.
You can format measure values appearing in a data visualization (e.g., table column) by opening the Metric Set / Data Analysis Panel.
Click the measure's Edit button on the right. Locate the Format field and enter a valid .NET format string.
|Format Type||Format String||Displayed 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|
2.1. Formatting currency
When using the format string C (currency), Dundas BI relies on the user's culture setting to decide which currency symbol to use and where to place it.
You can override the automatic symbol selection by indicating a specific symbol in the Symbol field.
The symbol placement remains automatically defined based on the culture of the user. In order to format a value with a specific symbol placement, which is independent of the culture, use other format strings (such as N or 0.00) and specify the placement using the Symbol Placement field.
Numeric values can be further formatted with thousands, millions, billions, or trillions units of measure according to the magnitude of the values. 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.
Conditional numeric formatting applies to tooltips, table cells, data point labels, and data labels (assuming visualization properties have not been changed to override the formatting from the metric set). These formatting settings are not applicable to chart axes or gauge scales. Gauge scales have their own specific properties for conditional numeric formatting.
If the Format Large Values Separately option is checked, numeric values can be formatted into the different units as values vary in magnitude. When this option is unchecked, the numeric values will all use the same unit to allow for better comparison. In this case, the smallest common unit will be used.
The value displayed for unknown values can be customised to a text value. When configuring measures, the dialogue entry is Empty Value Text. The default value is blank. When configuring hierarchy Members, it is Unknown Member Caption, the default value is "Unknown".
3. Placeholder keywords
Various text and tooltip properties of data visualizations accept placeholder keywords that dynamically resolve into corresponding measure or hierarchy values. The Properties panel provides a menu with suggestions for the relevant keywords, which you can then customize by controlling the formatting.
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.
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, the keywords [Measure Name] and [Measure Value] are available for all measures when the metric set is transposed, for those visualizations that display multiple rows and columns and support transposed metric sets (such as tables and charts).
Example numeric formats supported:
|Format Type||Format 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|
Example date and time formats supported:
|Format Type||Format String||Displayed Text|
|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|
|Year||y, yy, yyyy|
|Month||M, MM, MMM, MMMM|
|Day||d, dd, ddd, dddd|
|12-hour clock||h, hh|
|24-hour clock||H, HH|
|Fractional seconds||f, ff, fff|
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 instead of the hierarchy, go to the properties for the chart series. In the Tooltip Text property, construct a keyword by appending ' 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.
Switch to View mode and check the tooltip for a data point.
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 supports regular text, as well as hierarchy keywords, such as [Product], to refer to a hierarchy placed on Columns in the metric set.
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.
|[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.|
3.5. Visualization-specific keywords
The following keywords are supported for all label text, tooltip text, or legend text properties of the relevant visualization.
|[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.
|[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.
|[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).