Data Grid

This page 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.

Set up a Data Grid

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.

   

3. Refer to Data Grid properties described in the next section for details on additional properties.

Data Grid properties

The following sections provides details on how to configure the properties of the Data Grid component.

Basics

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

Server-side filtering

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

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;  
   }

Columns

Through the Columns properties you can:

• Configure properties for individual columns

• Define properties for multiple columns

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.

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.

   

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.

   

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

   

Selection & Routing

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

Action

See Actions for full details.

File export

See Export for full details.

Tooltip

Specify the following tooltip properties.

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.

The following screenshot shows grouping by sym and counterparty.

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.

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.

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

Highlight rules

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

For each rule select either Gradient or monotone Discrete colors.

Custom filters

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

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.

Data Grid Filtering Examples

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

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.

   

2. Define the server-side filter query, as illustrated in the following example, and shown below.

    

    

   Copy

   select distinct Month from TradeData                    

    

   

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.

   

Style

The Style properties are described in the following table.

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.

These are displayed in the Data Grid shown below.

Custom Configuration Layout

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

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.

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.

   

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

• Line charts

• Bar charts

• Pie charts

• Tristate charts

• Discrete charts

• Bullet graphs

• Box plots

Sparkline data format

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.

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.

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.

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

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 maps 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.

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

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

Bar charts

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

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.

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

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.

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

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

Discrete charts

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

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

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

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.

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, …

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

Pie charts

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

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

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

Box plots

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

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

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

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.

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.

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

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:

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

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:

Refer to Style for common Style, Format and Margin settings.

Further Reading

• Navigate Dashboards workspace

• Configure data source connections

• Use view states

• Publish & Export a dashboard