Skip to content

Data Grid#

This section describes the Data Grid component and how it can be set up and configured to help you interact with data in a structured grid format.

A Data Grid

This section provides information on the following:

Set up#

To set up a Data Grid component, click and drag the component into the workspace and configure the following:

  1. Click to populate Data Source or select a Data Source from the Basics properties to open the Data dialog.
  2. Click New and proceed with configuring the Data Source.

    ChartGL Data Source

  3. Next define the Basics properties.

Basics#

Open the Basics properties on the right and configure the properties described in the following table.

Basics Properties

Property Description
Name Enter a name for the component.
Data Source Define the query or analytic to be used to retrieve data. See Data Sources for further details.
Filtering Select one of the filtering options:
Quick Search This is a global search of all data columns. For example, for selected text, number, date, or time. Quick Search
Column Filters This searches on the selected column. Additional filter options are available with Custom Filters.
Custom Filters
Advanced Column Filters This searches on the selected column with support for boolean operators and other options. Advanced Custom Filters

The following options are supported:
Option Description
Wildcard (*) Wild card searches return everything.
AND Searching for `J AND M` returns a Jim but not a John or Mary.
OR Searching for `J OR M` returns a Jim, John, and Mary.
NOT (-) Searching for `* -Jim -John` returns all bar Jim and Mary

Advanced Column Filtering is not available for columns using Custom Filters.

Advanced Column Filtering must be selected if server-side filtering is employed.
Disabled No filters.
Filter Visible Check this to display the Filter field. Users can use Hide Filter to remove this field.
Custom Filters
Show Paging Control / Show Paging State When these two options are checked, you can see pagination controls, which are used for large data sets.
Paging Control
Enable Grouping When checked, users can group data together. Subtotals can be applied with Summary Row For Groupings.
Auto Collapse Grouping When checked, the dashboard is loaded with Data Grid groupings in collapsed form. Grid groupings are configured in Grouping Columns.
Keep NonExistent Columns Non-existent columns are employed when working with many queries. If a Data Source is changed, the prior column configurations from the original is kept if this control is checked.

Dynamic queries
Dynamic queries which don’t have fixed column definitions can set Keep NonExistent Columns to retain configuration options such as column formatting. In this scenario, the * wildcard would be used in the Data Field Name. Hybrid wildcard usage is also supported; for example Data\* returns all columns with Data.
Custom Layout When checked, the Data Grid has a button to summon the Configuration dialog.

Custom Layout Configuration

The Configuration dialog lets users change the display of columns in the Data Grid.
Custom Layout Configuration

All columns are available to users
Hidden columns are visible in the Available Columns list. This may not be desirable if a hidden column stores a calculation or highlight rule operator. In such instances it may be necessary to disable Custom Layout
User configuration is saved on exiting the dashboard and is available on next login.

Retain Relative Column Widths on Reload
Check this to continue with relative width on display columns (relative widths are applied only when columns fit allocated horizontal space, that is when the horizontal scrollbar is not visible).
Edit Mode Select one of the options; Enabled, Disabled or Instant.

Enabled - A user can edit data by clicking the Edit button in the top-right corner, making each cell editable.
Edit Mode Enabled

Disabled - When this is set users cannot edit cell contents.

Instant - When this is set boolean values in the table are rendered as checkboxes that users can toggle to instantly edit cell contents.
Edit Mode Instant
In both cases, an update query must be used.
Enable Insert/Delete This is only used with an update query.
Sort Column Defines the column to sort data when the Data Grid is loaded.
Sort Order Defines the direction to sort data when the Data Grid is loaded.
KDB Filter String Used in server-side filtering.
Filter on Enter When checked, the user must press ENTER to return a filter result. Default behavior is to smart filter on typing.
Scroll Row Stores a scroll value required for PDF Creation.
Experimental Mode Improved data handling efficiency and performance with high volume data
Virtual Scroll The options are:
Disabled - This is the default and means all data is loaded to the dashboard from the Data Source.
Uncached - When enabled, viewed data is loaded as required. For streaming or frequently changing data and update queries this setting is recommended,
Cached This enables backend data management.
Expand When Creating Pdf When checked, Data Grids are printed on multiple pages

Data Grids must be placed inside [Tab Control](./datasources.md). If a header is present, it is printed on each page. Grouping is not supported.

Footer Data Source This Data Source property can be used to provide customized values in the footer section of the Data Grid, as shown in the following example.
Footer Data Source
Each column name returned by this data source becomes an additional option to choose from in the dropdown list of available footers.
Only the first row of values resulting from this data source are presented in the footer section of the Data Grid.
Frozen Column Count You can freeze one or more columns in the Data Grid. This allows the user to scroll horizontally through the Data Grid, while keeping some important columns visible at all times (such as ID or command column).
This property defines the number of columns that are frozen starting at the first column to the left in the grid.

Server-side filtering#

Server-side filtering is not used in KX Insights Platform Views

To use server-side filtering, set the value of Filtering to Advanced Column Filter in the Basics properties.

Screenshot

The following steps outline an example of setting up server-side filtering.

  1. Create the following three view state parameters and group them in the view state parameter menu.
    • Sort Column - This can be either string or symbol.
    • Sort Order - This can be either string or symbol.
    • KDB Filter String- This must be of type string.
  2. Define a data source to be filtered. The following code snippet uses dfxQuote as the target data set; replace as appropriate:
   {[sortCol;sortOrder;filterString]
    res:.dfilt.apply[dfxQuote; filterString];
     if[all not null (sortOrder; sortCol);
     sortOp:(`descending`ascending!(xdesc; xasc))sortOrder;
     res:sortOp[sortCol;res]
     ];
     :res;  
   }

Server-side filtering setup

Columns#

Through the Columns properties you can:

Column#

For each individual column in your Data Grid there is a section defining how its data is formatted. These properties are defined in the following table.

Column Properties

Property Description
User Defined When checked, the selected column remains in the Data Grid if the selected column variable is removed from the query. This is effectively a lock on the column.
Note that the User Defined column shows as an empty column in the Data Grid if the User Defined column variable is missing in the query.
Data Field Name A column or [dynamic column](datasources.md#dynamic-columns) from the Data Source.
Dynamic column syntax
use \* or regex (for example; CPUCore\*, FXpair\*EUR, /symbol|sym/i )
Display Name Columns with Display Names: Symbol, Counter Party, %, and Start Date Display Name
Display Name is set to wildcard when Data Field Name uses the wildcard. Hybrid names using a mix of name label and wildcard are also supported; for example, _Data Field Name_ set to 'Data\*' with _Display Name_ of 'Result \*' returns columns including 'Data' with 'Result ' in the _Display Name_.
Header Tooltip Provides a rollover text description for the column. This is useful if the existing column title is clipped on browser resize.
Width (relative) Column width relative to other columns. For example; columns with a relative width of 20, 10, 5, 1 scale to column widths of 55% (that is 20/(20+10+5+1)), 28%, 14% and 3%. Columns resize with the browser window.
Min Width (px) The minimum width for a column in pixels
When using Width (relative) and Min Width (px) together Width (relative) uses a default value based on the data Type in the column; in the case of a Data Grid the default Width (relative) and Min Width(px) are the same. There are two priority rules for Width (relative) and Min Width (px):
1. If the screen is resized so that a scroll bar appears, Width (relative) is disabled and only Min Width(px) used. If a further resize removes the scrollbar, Width (relative) is applied.
2. If the Width (relative) is set to zero, only Min Width (px) is applied.
If Width (relative) (irrespective of scrollbar) narrows Data Grid column widths to less than Min Width(px), then Min Width (px) is used.

When using Width (relative) or Min Width(px) it may be helpful to use the bulk parameter adjuster; click Bulk Adjust CTRL + Select the columns to change, then adjust the required parameter(s) to have the value applied to all selected columns Multi select group
Text Align Horizontal alignment of values in their cells.
Sortable When checked, clicking on a column header toggles between ascending and descending sorts.
Column sort
Format Set the format for the data in the column. The options are:
- General
- Number
- Formatted Number
- Smart Number
- Date
- Time
- DateTime
- Percentage
- Boolean
- Sparkline
Precision The number of decimal places show for numeric data.
Hide Trailing Zeroes When checked, this suppresses trailing zeroes for numeric data.
Currency Symbol Set the currency symbol. The following example shows columns prefixed with currency symbols.
Currency symbol
Date Format/Time Format Where Format is Date, Time, or DateTime the data is formatted with these two settings.
Date Format, Time Format, and a formatted DateTime column
Negative Color Set the color for negative values in the column.
Highlight Changes When this is enabled, higher than previous value changes are highlighted in green and lower values in red. The length of time for the highlight is set by Highlight Change Duration
Read more about Highlight rules here.
Highlight Min Value/Highlight Max Value/Range Highlight These checkboxes are used to set highlighting colors to be used for minimum and maximum values as well as ranges, as shown here.
Minimum value color

Maximum value color

Range color
Invert Range Color Flip range color usage; dark shade for high values, light shade for low values
Percentage Color A color, used for mini-bars when _Format_ is Percentage
Percentage highlight
Note that Min Value Color, Max Value Color, Range Color, and Percentage Color can be View state parameters
Read Only When checked, the column cannot be edited
Selectable Checked by default. Column cells cannot be selected when unchecked
Template Apply HTML customizations with Handlebar helpers; for example: {{column name}}
where **{{column name}}** is the reference name for the column from the data source.
Read more about Templates here.
Sparkline Options Configuration for Sparklines.
Hidden When checked the data column is hidden. If Full Excel Export is enabled, all hidden columns are downloaded as part of the export. Hidden columns are also available under Custom Layout.
Footer This property allows the user to specify what values are displayed in the footer section of the Data Grid. The options are; None, Average, Count, Sum, and Weighted Average.
Footer dropdown default
If at least one column has a Footer property set to a value other than None, the footer section appears as the last row at the bottom of the grid display. Footer values inherit the format options of their column.
Data Grid Sum
When a Footer data source is defined, the columns returned by this data source are appended as additional options to the Footer dropdown list.
Footer dropdown columns
To create labels in the footer section, text can be used as a footer value even if the Column format is different.
The Footer property is set to None if a custom footer is selected and the Footer Data Source changes to exclude the selected column.
Footer Weight Applies a weighting to the current column by selected data source variable. For this to be enabled, Footer must be set to Weighted Average.
Header Applies a column grouped header. Auto size applied on grouped header when user resize columns. Column reorder restricted within its group.
Column grouped header
Filter Use this to save the current filter value for this column as a view state for use elsewhere.
Column grouped header

Multi Column Editor#

In addition to the individual column settings, you can define settings for all columns in the Multi Column Editor.

  1. Click the edit column properties icon as shown below.

    Screenshot

  2. Select columns on the left using Ctrl-click on columns.

  3. Select properties (settings) on the right to be applied across the selected columns.

    Screenshot

  4. Columns can be removed from a Data Grid by clicking the trashcan icon

    Screenshot

Selection & Routing#

The Selection & Routing properties are used to define what happens when areas of the Data Grid are selected.

Screenshot

Selection Mode Once a selection mode is set, the user can select and highlight a specific region in the grid for bulk actions like copy and paste. Select one of the following:
Option Description
Area Allows for selecting a contiguous rectangular area of cells
When Area is selected the user can select cells and rows using the following keyboard shortcuts.
Shortcut Description
Shift + End Select to the end of a column from a cell.
Shift + Home Select to the top of column from a cell.
Select + Page Up Select from a cell to each page up instance.
Shift + Page Down Select from a cell to each page down instance.
Select + Click Select a cell to click-select-cell.
Drag-select-rows Select multiple rows.
Single Row Enables the selection of only one row at a time.

Multi Row
Permits the selection of multiple rows simultaneously.

Cell
Allows for selecting an individual cell within the grid.
Row ID Column For a given selected row, this is used to specify the column used in the selected row. This is defined from the Data Sources
Selected Row This property sets the value in the specified column ID for a selected row. It provides precise control over row selection, allowing actions to be applied to specific cells within the grid. This is assigned by a view state parameter when Cell is selected.
Selected Column This property sets the value of the column header value for any selected cell. This is assigned by a View State parameter when Cell is selected.
Selected Cell Value This property sets the value of selected cell. While the selected row always sets the value based on column ID, this property is independent of column id. This is assigned by a view state parameter when Cell is selected.
Follow Selected Value When checked, row selection highlight retains the view on screen during a scroll event
Default Fallback on Deselect When checked, the default View State Parameter values are used (if set) in the absence of a row select.
Checkbox Alignment Position of data row selection check boxes; select either none, left or right
Select on checkbox only Toggle whether the whole row can be selected or just the checkbox.

Action#

See Actions for full details.

File export#

See Export for full details.

Tooltip#

Specify the following tooltip properties.

Property Description
Position Set the tooltip position to either none, right, left, or cursor.
Template See Templates for details.
Use Formatted Cell Value When this is checked the tooltip uses the formatted value from column template otherwise it uses the raw cell value.

Grouping columns#

The Grouping Columns properties are used to group Data Grid rows by their values in that column. Specifying further grouping columns repeats this within each group.

Screenshot

The following screenshot shows grouping by sym and counterparty.

Screenshot

Dashboard users can also create groupings on the fly by dragging a column header into the space directly above the column headers, as shown here.

Screenshot

Summary Row for Groupings#

Grouping Columns reorganize the Data Grid layout. Summary Row for Groupings works with the grouping columns by applying simple mathematical calculations to the groups. The following example shows Leverage calculated for each group.

Screenshot

The Summary Row for Groupings properties are described in the following table.

Screenshot

Property Description
Aggregate Function The mathematical function to apply to the group. The following functions are supported. AVG, SUM, MIN, MAX or WAVG. When you select WAVG (weighted average), an additional Column property is displayed.
Column The column on which the calculations are made.
Label The text label to use for the calculation.
Color The font color for the grouping.

Highlight rules#

Highlight rules use colors to emphasize changes in data grid values.

Screenshot

For each rule select either Gradient or monotone Discrete colors.

Screenshot

Property Description
Name The name of the rule.
Target The column on which the rule acts. This can be any column in the Data Grid. It does not have to be the column using the operator.
Condition Source See Highlight Rules for details.
Condition Operator See Highlight Rules for details.
Condition Value See Highlight Rules for details.
Color The text color when the rule is true.
Background Color The background cell color when the rule is true.
Border Color The cell border color when the rule is true.
Icon The icon to appear when the rule is true.
Icon Color The color for the icon (if used) when the rule is true.

Custom filters#

Custom Filters introduce client-side advanced filters for columns covering text, numeric and datetime formats.

Screenshot

Custom Filters are available only when Filtering is set to Column Filters or Advanced Column Filters.

Filtering is only applied to viewable data. Paging limits the results to the current page.

Specify a custom filter for each columns by setting the properties in the following table.

Screenshot

Description
Name Select a column to use for filtering. When you click this field, a dialog opens.
Type Set the type of filtering to be used. Select one of the values described below.
Selection Filter dropdown. Checks the available data options to display.
Server Selection See Server Selection for details.
Number Support for filter options: `<`, `>`, `>=`, `<=`, `=`, `==`, `!=` (exclude)
DateTime Select for Date or Time using standard operators: `<`, `>`, `>=`, `<=`, `=`
Disabled No filtering
Datasource This is used if *Type** is set to **Server Selection**.

Data Grid Filtering Examples#

The following tabs contain examples of different types of column filters.

Symbol starts with EUR or USD
Screenshot
Symbol starts with EUR, without EURAUD
Screenshot
Symbol starts with EUR, without EURAUD and EURCAD
Screenshot
Symbol ends with CAD
Screenshot
Selected numbers
Screenshot
Selecting Four symbols
Screenshot
Range between 10000 and 20000000
Screenshot
Range between 25 and 75
Screenshot
Range outside 25 and 50
Screenshot
Return 25 and 50 only
Screenshot

Screenshot

Filtering variables with !=

When the exclusion function != is used, all values, including column cells with null data, are returned except for the value defined by !=

Server selection filtering#

Server selection filtering allows users to dynamically filter and interact with large datasets directly from the server, ensuring that only relevant data is retrieved and visualized in the dashboard. This approach is especially useful in scenarios involving real-time data analytics, financial data monitoring, and other data-intensive applications.

Server selection must be used when data is not fully loaded to the client. For example, when using server-side paging or Server-side Filtering via the KDB Filter String.

Server selection uses a data source to return all member items of the filtered column.

To define a server-side custom filter:

  1. Add a custom filter and set Type to <Server Selection.

    Screenshot

  2. Define the server-side filter query, as illustrated in the following example select distinct Month from TradeData.

    Screenshot

  3. View the results and you can see that the filter options in the column header use the server-side filter query you just defined. In the following screenshot, we can see the distinct months being shown.

    Screenshot

Style#

The Style properties are described in the following table.

Screenshot

Property Description
Even Row Background
Odd Row Background
Set the background colors of the even and odd rows
Selected Row Background When Selection Mode is set to Single Row or Double Row selection routing properties this property is used to set the background for the selected row.
Row Height This sets the height of each of the rows. Here we see two examples with row height set to 30 and 20.Screenshot
Header Row Height This sets the height of the header row.
Header Text Transformation This specifies the text transformation for column headers. The options are; none, uppercase, lowercase or capitalize.
Header Font Weight The font weight for column headers.
Font Family The font family to be used for column headers and rows.
Font size The font size to be used for rows (css format). For example, 18px, 0.8em, 80%, larger

See Style for details about common style settings.

Advanced CSS#

See Advanced CSS for details about using advanced CSS.

Header Groups#

Header Groups are a set of custom column names that can be defined by the user and added to the column header.

In the following example the columns Address Info, Property Info and Price Info are added to a Header Group.

Header Group

These are displayed in the Data Grid shown below.

Header Group

Custom Configuration Layout#

The Custom Configuration Layout properties are described in the following table.

Custom Configuration Layout

Property Description
Ignore Custom Layout Viewstate When checked, if there is a viewstate defined in the column properties then it ignores this value when applying user defined custom layouts.
Include Hidden Columns When checked, any hidden columns, (that is any columns with the Hidden property checked in Column properties) are included in the list of columns that can be added when applying user defined custom layouts. Custom layouts are applied by clicking Configuration in preview mode.

Sparklines#

Sparklines are small, lightweight charts used to show trends or variations in data. They are useful, when working with data grids, to enhance your data visualization. Composite sparklines provide a way of combining sparklines. The following type of Sparklines are supported; line, bar, stacked bar, pie, tristate, discrete charts, bullet graphs, and box plots.

The following screenshot shows a Data Grid component with its columns format set to Sparklines of various types.

Screenshot

To display Sparklines:

  1. In the Basics properties ensure the Data Source format is correct.

  2. In the Column properties:

    • Ensure the Data Field Name points to the correct data in the data source.
    • Set Format to Sparkline
    • Modify the Sparkline Options using the JSON editor, as displayed below.

    Screenshot

    The common sparkline options section describes the options used by each of the chart types with chart specific options described in the following sections:

Sparkline data format#

The data, for Data Grids using Sparklines, must be formatted correctly. The examples displayed in this and the following sections use a data source called Data, shown in the following screenshot.

Sparklines data source example

You can copy the following kdb+/q query to create this data source query and then create the sample sparklines shown in the following sections.

    ([] 
        Single:{ ((60?30) - 5)}each 1_til 101;
    Composite: {((20?10);(20?10))}each 1_til 101;
    XandY: {((1;rand 10);(2;rand 10);(3;rand 10);(4;rand 10);(5;rand 10);(6;rand 10);(7;rand 10);(8;rand 10);(9;rand 10);(10;rand 10);(11;rand 10);(12;rand 10)) }each 1_til 101;
    Stacked: {((3?10);(3?10);(3?10);(3?10);(3?10);(3?10);(3?10);(3?10);(3?10))}each 1_til 101;
    Pie:{(3?20)}each 1_til 101;
    Tristate:{(1- 60?3)}each 1_til 101
    ``)
  • For Line, Bar, Discrete charts, Bullet graphs, and Box plots, the data must be a sequence of numbers separated by semicolons. For example; 10; 8; 9; 3; 5; 8; 5. In our example these chart types use Single as their Data Field Name.
  • For Tristate charts, the data must be a sequence of numbers separated by semicolons, with three values. For example 1, -1, 0 representing win, lose or draw. For example; 1; 1; 0; 1; -1; -1; 1; -1; 0; 0; 1; 1. In our example this chart type uses Tristate as its Data Field Name.
  • For Pie charts, the data is sets of sequences of numbers. Each set (representing an individual pie chart) is made up of numbers separated by semicolons. For example; (1; 1; 2)(20; 50; 80). In our example this chart type uses Pie as its Data Field Name.
  • For stacked Bar charts, values for each data series in the chart can be separated by colons if passed by HTML, or as an array of arrays. For example, to draw series one with values of 1,2,3,4, and series 2 with values of 4,3,2,1 would be passed as ((1; 4); (2; 3); (3; 2); (4; 1))
  • For Composite sparklines, the data must be sets of number sequences each set (per sparkline) in square brackets []. For example; [3, 7, 1, 9, 5, 2, 8, 4, 0, 6, ...], [9, 1, 4, 7, 3, 2, 6, 0, 8, 5, ...]. In our example, this chart type uses Composite as its Data Field Name.

For example:

  • The Single query, { ((60?30) - 5)}each 1_til 101;, calculates the result of subtracting 5 from a random number (between 0 and 29) for each value from 1 to 101, and provides the result as a sequence to display in any column that references it in their Data Field Name.
  • When a column's Format is set to Sparkline, this sequence of data is displayed in the chart type specified in Sparkline Options.

    The following screenshot shows that for the Column Line:

    • The Data Field Name is set to Single
    • The Format is set to Sparkline
    • The Sparkline Options are set up with type=Line.

    This results in the data from the query being displayed as Line charts.

    Sparklines data source example

Common sparkline options#

These options can be set for most of the supported chart types.

These options are specified in the JSON editor accessed by clicking Sparkline Options in column properties. The JSON editor auto-completes names and default values when you enter the initial letters of the name.

Sparkline auto-complete

Option Description Default value
type The following types of chart are supported:
- Line charts
- Bar charts
- Pie charts
- Tristate charts
- Discrete charts
- Bullet graphs
- Box plots
The common properties for each of these types are described in this table with additional properties described in the sections that follow.

Spark Types
"line"
width The width of the chart. This can be any valid CSS width: 1.5em, 20px, and so on. You have to specify a unit for the number or it won't work. This option does nothing for bar and tristate chars (see barWidth). "auto"
height The line height of the containing tag. "auto"
lineColor This is used by Line and Discrete charts to specify the color of the line drawn as a CSS values string. "#00f"
fillColor Specify the color used to fill the area under the graph as a CSS value. Set to false to disable fill. "#cdf"
chartRangeMin Specify the minimum value to use for the range of Y values of the chart. This defaults to the minimum value supplied. null
chartRangeMax Specify the maximum value to use for the range of Y values of the chart. This defaults to the maximum value supplied. null
composite Setting this to true ensures that any existing chart attached to the tag remains, and a new chart is drawn over it. If an existing chart is detected, the width and height settings are ignored. To ensure the same values align on both charts, you must lock the axis on both charts using chartRangeMin and chartRangeMax. false
enableTagOptions Set to true if you want options to be specified as attributes on each tag to be transformed into a sparkline, as well as passed to the sparkline() function. See also tagOptionPrefix. false
tagOptionPrefix The string that each option passed as an attribute on a tag must begin with. "spark"
tagValuesAttribute The name of the tag attribute to fetch values from. "values"
disableHiddenCheck Set this to true to disable checking for hidden sparklines. This can be beneficial if you know you’ll never attempt to draw a sparkline into a hidden parent element, as it avoids a browser reflow for the test, increasing rendering performance. false

The following screenshot shows the JSON editor with these common options and their default values.

Sparkline common options

Click Apply to save the Sparkline Options.

Range maps#

Several parameters, such as colorMap, in Line charts and tooltipValueLookups, in interactive sparklines, accept a range map as a parameter. As the name suggest, it maps ranges of numbers to values. For example:

{
    1: "red",
    "2:9": "yellow",
    "10:": "red"
}

This would map 1 to red, values of 2 through 9 (inclusive) to yellow and values of 10 and higher to "red"

Line charts#

A line chart provides a visual representation of data using connected points along a straight line.

Line

In addition to the common options the following options can be applied when the type is set to line.

Option Description Default value
defaultPixelsPerValue The number of pixels of width for each value in the chart. 3
spotColor The CSS color of the final value marker. Set to false or an empty string to hide it. "#f80"
minSpotColor The CSS color of the marker displayed for the minimum value. Set this to false or an empty string to hide it. "#f80"
maxSpotColor The CSS color of the marker displayed for the maximum value. Set this to false or an empty string to hide it. "#f80"
spotRadius The radius of all spot markers, in pixels. 1.5
valueSpots Specifies which points to draw spots on, and with which color. Accepts a range. For example, to render green spots on all values less than 50 and red on values higher use {":49": "green", "50:": "red"}
highlightSpotColor Specifies a color for the spot that appears on a value when moused over. Set to null to disable. "#f5f"
highlightLineColor Specifies a color for the vertical line that appears through a value when moused over. Set this to null to disable. "#f22"
lineWidth The width of the line in pixels. 1
normalRangeMin, normalRangeMax Specify the threshold values between which to draw a bar to denote the normal or expected range of values. For example, the green bar might denote a normal operating temperature range. "null"
drawNormalOnTop Setting this option to true causes the normal range to be drawn over the top of the fill area. When set to false the normal range is drawn behind the fill area of the chart. false
xvalues By default the values supplied to line charts are presumed to be y values mapping on to sequential (integer) X values. If you need to specify both the X and Y values for your chart, use an array of arrays: ((1; 3); (2; 4); (5; 3))
You can also specify a value of null to omit values from the chart completely.
chartRangeClip When set to true the Y values supplied to plot are clipped to fall between chartRangeMin and chartRangeMax. By default chartRangeMin/Max just ensures that the chart spans at least that range of values, but does not constrain it.
chartRangeMinX Specifies the minimum X value of the chart null
chartRangeMaxX Specifies the maximum X value of the chart null

The following screenshot shows the JSON editor with these line specific properties and their default values.

Sparkline Line Specific Options

Bar charts#

A bar chart provides a visual representation of data using rectangular bars.

Bar

In addition to the common options the following options can be applied when the type is set to bar.

Values can be omitted by using the null value instead of a number.

Option Description Default value
barColor The CSS color used for positive values. "#3366cc"
negBarColor The CSS color used for negative values. "#f44"
zeroColor The CSS color used for values equal to zero. null
nullColor The CSS color used for values equal to null. When this is set to null, null values are omitted entirely, but setting this adds a thin marker for the entry. This can be useful if your chart has minimal data points. Try setting it to a light gray or something equally unobtrusive. null
barWidth The width of each bar, in pixels (integer). 4
barSpacing The space between each bar, in pixels (integer). 1
zeroAxis This centers the Y-axis at zero if true. true
colorMap A range map to map specific values to selected colors. For example if you want all values of -2 to appear yellow, use colorMap: { "-2": "#ff0" }. You may also pass an array of values here instead of a mapping to specify a color for each individual bar. For example if your chart has three values 1, 3, 1 you can set colorMap=["red", "green", "blue"]
stackedBarColor An array of colors to use for stacked bar charts. The first series uses the first value in the array, the second series uses the second, and so on. "#3366cc"
"#dc3912"
"#ff9900"
"#109618"
"#66aa00"
"#dd4477"
"#0099c6"
"#990099"

The following screenshot shows the JSON editor with these bar chart specific properties and their default values.

Sparkline Bar Specific Options

To create stacked bar charts, values for each data series in the chart can be separated by colons if passed by HTML, or as an array of arrays.

For example, to draw series one with values of 1,2,3,4, and series 2 with values of 4,3,2,1: ((1; 4); (2; 3); (3; 2); (4; 1))

Tristate charts#

Tristate charts are useful to show win-lose-draw information, such as the SF Giants recent game results below. You can also use the colorMap option to use different colors for different values, or for arbitrary positions in the chart.

Sparkline Tristate

In addition to the common options the following options can be applied when the type is set to tristate.

Option Description Default value
posBarColor The CSS color for positive (win) values. "#6f6"
negBarColor The CSS color for negative (lose) values. "#f44"
zeroBarColor The CSS color for zero (draw) values. "#999"
barWidth The width of each bar, in pixels (integer). 4
barSpacing The space between each bar, in pixels (integer). 1
colorMap A range map to map specific values to selected colors. For example if you want all values of -2 to appear yellow, use colorMap: { "-2": "#ff0" }.

You may also pass an array of values here instead of a mapping to specifiy a color for each individual bar. For example if your chart has three values 1, 3, 1 you can set colorMap ["red", "green", "blue"].

The following screenshot shows the JSON editor with these tristate chart specific options and their default values.

Sparkline Tristate Specific Options

Discrete charts#

Discrete charts provide a separated thin vertical line for each value.

Sparkline Discrete

In addition to the common options the following options can be applied when the type is set to discrete.

Option Description Default value
lineHeight The height of each line in pixels. "auto" = 30% of the graph height
thresholdValue Values less than this value are drawn using thresholdColor instead of lineColor 0
thresholdColor The color to use in combination with thresholdvalue. null

The following screenshot shows the JSON editor with these discrete chart specific options and their default values.

Sparkline Discrete Specific Options

Bullet graphs#

A bullet graph is a variation of a bar graph. Inspired by the traditional thermometer charts and progress bars found in many dashboards, the bullet graph serves as a replacement for dashboard gauges and meters.

Sparkline Bullet

In addition to the common options the following options can be applied when the type is set to bullet.

Supplied values must be in this order: target, performance, range 1, range 2, range 3, …

Option Description Default value
targetColor The CSS color of the vertical target marker. "#f33"
targetWidth The width of the target marker in pixels (integer). 3
performanceColor The CSS color of the performance measure horizontal bar #f33
rangeColors The colors to use for each qualitative range background color. This must be a JavaScript array. For example ["red","green", "#22f"]. "#d3dafe"
"#a8b6ff"
"#7f94ff"

The following screenshot shows the JSON editor with these bullet graph specific options and their default values.

Sparkline Bullet Specific Options

Pie charts#

A pie chart is a circular statistical graphic divided into slices to illustrate numerical proportion.

When using pie charts as sparklines, these little pie charts tend only to be useful only with 2 or 3 values at most.

Sparkline Pie

In addition to the common options the following options can be applied when the type is set to pie.

Option Description Default value
sliceColors An array of CSS colors to use for pie slices. "#3366cc"
"#dc3912"
"#ff9900"
"#109618"
"#66aa00"
"#dd4477"
"#0099c6"
"#990099"
offset Angle in degrees to offset the first slice. Try -90 or +90. 0
borderWidth Width of the border to draw around the whole chart, in pixels. 0
borderColor CSS color to use to draw the pie border. "#000"

The following screenshot shows the JSON editor with these pie chart specific options and their default values.

Sparkline Pie Specific Options

Box plots#

A box plot is a method for demonstrating graphically the locality, spread and skewness groups of numerical data through their quartiles.

Sparkline Box

In addition to the common options the following options can be applied when the type is set to box.

Option Description Default value
raw False - When set to false the values supplied are used to calculate the box data points for you. This means that you can pass an arbitrarily long list of values to the sparkline function, and the corresponding box plot is calculated from those values. This is the behavior you want most of the time.

True - If you have thousands of values to deal with you may want to pre-compute the points needed for the box plot. In that case, set this to True and pass in the computed values. If showing outliers, supplied values of: low_outlier, low_whisker, q1, median, q3, high_whisker, high_outlier. Omit the outliers and set showOutliers to false to omit outlier display.
false
showOutliers Outliers are values that are numerically distant from the rest of the data. When set to true outliers (values > 1.5 × the IQR) are marked with circles and the whiskers are placed at Q1 and Q3 instead of the least and greatest value. true
outlierIQR This sets the inter-quartile range multiplier used to calculate values that qualify as an outlier. 1.5
boxLineColor The CSS line color used to outline the box. "#000"
boxFillColor The CSS fill color used for the box. "#cdf"
whiskerColor The CSS color used to draw the whiskers. "#000"
outlierLineColor The CSS color used to draw the outlier circles. "#333"
outlierFillColor The CSS color used to fill the outlier circles. "#fff"
spotRadius The radius in pixels to draw the outlier circles. 1.5
medianColor The CSS color used to draw the median line. "#f00"
target If set to a value, then a small crosshair is drawn at that point to represent a target value. null
targetColor The CSS color used to draw the target crosshair. null
minValue If minvalue and maxvalue are set then the scale of the plot is fixed. By default minValue and maxValue are deduced from the values supplied. ****
maxValue See minValue. ****

The following screenshot shows the JSON editor with these box plots specific options and their default values.

Sparkline Box Specific Options

Composite sparklines#

A composite sparkline combines multiple individual sparklines into a single chart. This helps you to compare trends or variations across different data sets.

Screenshot

The screenshot above uses the following JSON to display the data in a bar and a line chart.

[{
    "type": "bar",
    "composite": false, "height": "29px",
    "width": "100%"
},
{
    "type": "line",
    "composite": true,
    "fillColor": "rgba(0,255,0, 0.2)",
    "lineColor": "#00ff00",
    "height": "29px",
    "width": "100%"
}]

Interactive sparklines#

Interactive sparklines in the form of tooltips, mouseover highlighting, and click interaction are supported for all types of sparkline.

Sparkline Interactive

In Preview mode, the example above maps numerical values to months. So a value of 4-4 is displayed as Apr 4.

Sparkline Interactive Preview

If you’re happy with the default look and feel of the tooltips, you can skip this entire section.

The following options, used to control sparkline interactions, can be specified in the JSON editor for Sparkline Option when the Format is set to Sparkline in Column properties:

Option Description Default value
disableInteraction Set to true to disable all sparkline interactivity. false
disableTooltips Set to true to disable mouseover tooltips. false
disableHighlight Set to true to disable the highlighting of individual values when mousing over a sparkline. false
highlightLighten Controls the amount to lighten or darken a value when moused over. A value of 1.5 lightens by 50%, 0.5 darkens by 50%. 1.4
highlightlineColor If specified, values that are moused over are changed to this color instead of being lightened. "#f22"
tooltipClassname Specifies a CSS class name to apply to tooltips to override the default built-in style. "jqstooltip"
tooltipOffsetX Specifies how many pixels away from the mouse pointer to render the tooltip on the X axis. 10
tooltipOffsetY Specifies how many pixels away from the mouse pointer to render the tooltip on the Y axis. 12
tooltipChartTitle If specified then the tooltip uses the string specified by this setting as a title.
tooltipFormat A format string or spformat object (or an array thereof for multiple entries) to control the format of the tooltip. See formatting tooltips for more details. " {{prefix}}{{y}}{{suffix}}"
tooltipPrefix A string to prepend to each field displayed in a tooltip.
tooltipSuffix A string to append to each field displayed in a tooltip.
tooltipSkipNull If this is set to true null values don't have a tooltip displayed. true
tooltipValueLookups An object or range map to map field values to tooltip strings. For example you may want to map -1, 0 and 1 to the strings Lost, Draw, Won. See formatting tooltips for more details.
tooltipFormatFieldlist An array of values specifying which fields to display in a tooltip and in what order. Currently only useful for Box plots.
tooltipFormatFieldlistKey Specifies which key holds the field name to reference above. For Box plots this should be field. ****
numberFormatter When set to true, it passes a JavaScript function to control how numbers are formatted in tooltips. The callback is passed a number to format and must return a string. When set to false numbers are formatted to Western conventions. false
numberDigitGroupSep Specify the character to use for group separator in numbers 1,234 for L10n purposes. ","
numberDecimalMark The character to use for the decimal point in numbers for L10n purposes. "."
numberDigitGroupCount The number of digits between the group separator in numbers for L10n purposes. 3

The following screenshot shows the JSON editor with these interactive sparkline specific options and their default values.

Interactive Tooltip

Formatting tooltips#

The tooltipFormat and tooltipValueLookups interactive sparkline options provide the main methods of formatting the text displayed for each value in a tooltip.

The tooltipFormat is applied whenever the mouse is moved over a value. Various fields enclosed between double curly braces in the format string are substituted depending on the type of sparkline in use. For example, the default format string for line charts is:

{{prefix}}{{y}}{{suffix}}
  • color is derived from the color of the line being drawn.
  • prefix and suffix and set by setting the tooltipPrefix and tooltipSuffix items.
  • y represents the Y value of the point under the mouse
  • x can also be used if useful.

The supported fields for the different types of sparklines include:

Sparkline Supported fields
All types prefix, suffix as set by tooltipPrefix and tooltipSuffix options
Line color, fillColor, x, y, isNull (true if a null value)
Bar value (the value under the pointer), color, isNull
Tristate value (the value under the pointer), color, isNull
Discrete value, isNull
Pie value, percent (number between 0 and 100), color (of the moused-over slice)`
Bullet value, fieldkey, isNull
Box field and value. Field may be one of lq(lower quartile), med (median), uq (upper quartile), lo (left outlier), ro (right outlier), lw (left whisker) rw (right whisker)

Further Reading#