Data Grid Properties

Data Grid is a versatile table presentation object allowing e.g. sorting, filtering, grouping and column reordering. The visual layout of Data Grid can be flexibly changed using CSS.

Data Grid JSON Settings

On the JSON settings field in the Presentation tab, you can change variety of Data Grid features, such as searching, filtering, paging, sorting, grouping, whether the Data grid selection is in row or cell mode, and so on. QPR UI uses Syncfusion component to draw the Data Grid. See the Syncfusion demos to see the available features and check from the settings reference how the desired settings are enabled:

Syncfusion Grid demo: http://js.syncfusion.com/demos/web/#!/bootstrap/grid/defaultfunctionalities
Syncfusion Grid documentation: https://help.syncfusion.com/js/grid/overview
Syncfusion Grid settings reference: https://help.syncfusion.com/api/js/ejgrid

Check https://www.w3schools.com/js/js_json_intro.asp for more information about JSON.

The "Show effective JSON" selection shows the underlying current JSON definition including the definition you have done yourself. You can use the effective JSON as a starting point for your customized JSON settings.

Common Settings

Examples of changing JSON settings:

Feature	Configuration
Filtering	By default, column filtering is used. It can be disabled as follows: { "allowFiltering": false }
Excel style filtering	{ "filterSettings" : { "filterType": "excel" } }
Searching	The search field in the grid is controlled by the "toolbarSettings" block, where setting the "showToolbar" as false will hide the search field in the grid: { "toolbarSettings": { "showToolbar": false } }
Column types	By default, the Data Grid automatically detects the column types from the data in the first row. The automatic detection might not always detect the data type correctly, so it's recommended to explicitly specify data types. Available datatypes are string, number, date, datetime, boolean. Example: { "columns": [ { "type": "string" }, { "type": "number" }, { "type": "date" }, { "type": "datetime" }, { "type": "boolean" } ] }
Date formatting	For number, date and datetime columns, the date formatting can be specified for example as follows: { "columns": [ { "type": "number", "format": "{0:n2}" }, { "type": "date", "format": "{0:d.M.yyyy}" }, { "type": "datetime", "format": "{0:dd/MM/yyyy HH:mm:ss}" }, ] }
Multiple rows selection	{ "selectionType": "multiple", "selectionSettings" : { "selectionMode": ["row"] } }
Multiple Cells Selection	{ "selectionType": "multiple", "selectionSettings" : { "selectionMode": ["cell"] } }
Minimum column width	{ "minWidth": 100 } Link to Syncfusion settings reference: https://help.syncfusion.com/api/js/ejgrid#members:minwidth
Specific column widths	Setting the width to the second, third, and fifth columns named "Case", "Event Type", and "Cost" respectively: { "columns": [ {}, {"field": "Case", "width": 100}, {"field": "Event Type", "width": 200}, {}, {"field": "Cost", "width": 50 } ] }
Resizable columns	{ "allowResizing": true } Note that the resized widths cannot be saved, so this setting is for view-time only.
Excel, Word and PDF exporting	Enabling the export functionalities is controlled in the "toolbarSettings" block. In addition, the exportToExcelAction, exportToWordAction, and exportToPdfAction objects need to contain the URLs for the corresponding export service. In this example, context variables are used for the export service URL definitions: { "exportToExcelAction": "http://<YourHostname>/SyncfusionGridExport/excelExport", "exportToWordAction": "http://<YourHostname>/SyncfusionGridExport/wordExport", "exportToPdfAction": "http://<YourHostname>/SyncfusionGridExport/pdfExport", "toolbarSettings": { "showToolbar": true, "toolbarItems": ["excelExport", "wordExport","pdfExport"] } } By default, the exportToExcelAction, exportToWordAction, and exportToPdfAction values point to locations on the same host. Therefore, if you have routed QPR UI traffic through IIS and you have the Data Grid Export Service installed on the same IIS, you don't necessarily need to define the values here, as the URLs are already pointing to the correct place (e.g. http://<YourHostname>/SyncfusionGridExport/excelExport).

Datasource in JSON Settings

It's possible to define the Data Grid to use a certain dataset by using the dataSource attribute in the JSON settings that contains a valid dataset tag as the value:

{
"dataSource": <#dataset identifier="ExampleDataset">
}

Note that when using the dataSource attribute in the JSON settings, the definitions done on the "Query" and "Mappings" tabs and the "Select Value Settings" and "Value Settings" sections of the "Presentation" tab of the data grid properties are overridden.

Context Variables in JSON Settings

It's possible to use context variables in the JSON settings, and even have all of the JSON settings come from a context variable. The value of the context variable needs to be such that the resulting JSON is valid. For example, JSON settings defined as follows:

{
  "columns": [
    {"width": <#caseColumnWidth>},
    {"width": 200},
    {},
    {"width": 50}
  ]
}

Need to have, for example, the following values for the JSON to be valid:

Context Variable	Context Variable Value
caseColumnWidthValue	100
fieldName	"Cost"

Using Column Templates in JSON Settings

Column templates can be used to define customized columns with HTML. JsRender syntax can be used in the column templates HTML to reference to the source data (more information: https://github.com/BorisMoore/jsrender).

Example use of column templates:

{
  "columns": [
    {"template": "Value is: {{:Column1Name}}"},
    {"template": "<div style=\"color:green;font-weight:bold;\">{{:Column2Name}}</div>"},
    {"template": "{{:Column3Name * 5 + Column4Name}}"},
    {"template": "{{:Column5Name}} and {{:Column6Name}}"},
  ]
}

Notes:

Column names can be referenced by using the column name:
- Column name that has no spaces: "{{:MyVeryFineColumnName}}".
- Column name that has spaces: "{{:#data[\"My Very Fine Column Name With Spaces\"]}}".

Also data grid headers can be customized with headerTemplateID setting, for example:

{
  "columns": [
    {"headerTemplateID": "<div style=\"color:green;font-weight:bold;\">Green bolded header</div>"}
  ]
}

Data Grid CSS Settings

On the CSS settings section, you can define customized styling for the Data grid using CSS. The list of available classes and their purpose is available from Syncfusion documentation and some more here. General information about CSS is available here. In addition to the classes listed, there is also a qc-table-header class that can be used to style the area showing the data grid name.

As an example, use the following CSS to customize the font, text color, font size, font weight and background color of the column headers:

.e-headercell {
  font-family: Lucida Handwriting, sans-serif;
  color: white;
  font-size: 16px;
  font-weight: bold;
  background-color: #080034;
}

The following example shows how to change styling for individual columns. In the numbering, the first column is number 2, the next one on its right is 3 and so on. e-headercell affects the header row and e-rowcell affects the data rows.

.e-headercell:nth-child(2) {
  background-color: green;
}

.e-rowcell:nth-child(2) {
  font-weight: bold;
}

Note the valid CSS syntax:

every class name starts with a dot (.)
CSS settings that are effective for the class are inside the curly brackets as key-value pairs
every CSS settings ends with a semicolon (;)

More information about CSS:

CSS tutorial: https://www.w3schools.com/css/
CSS settings reference: https://www.w3schools.com/cssref/

Data Grid Click and Popup Menu Events

On the Events tab, it's possible to define actions for click and double click events, and for a popup menu that can be opened by right-clicking the data grid or by using a long-press on a mobile device. The definitions are done as JSON configuration strings that are arrays containing the actions and events.

The actions have the following properties:

identifier: String. Mandatory. The identifying name of the action.
paQuery: String. Optional. ProcessAnalyzer query that is executed when the action is triggered. All key-value pairs that the query returns are set as QPR UI context variable values. Note that you need to use \n as linefeed in the string between the wanted ProcessAnalyzer query parameters.
contextChanges: Object. Optional. The context changes object has a property for every context variable it sets. The value of the context variable property is an object with the following properties:
- value: String. Defines from which column and row/cell combination the value(s) to set to the context variable are taken. To refer to a column in the data grid, use the following format: "{$column_name}", for example "{$country}", or to refer to multiple columns: "{$first_column_name}{$second_column_name}", note that it may be a good idea to separate the different columns by having some text between them, e.g. "{$country}-{$city}". Hidden columns can be referenced like this too. Note that this column name reference is case sensitive, so for example "{$country}" and "{$Country}" are not the same. It's also possible to get the index number of the selected column by using "{%columnIndex}" as the value. Then, depending on the Data grid selection mode defined on the Presentation tab, the value(s) are taken from the selected row(s) or cell(s) of the defined column(s). In case multiple rows or cells are selected, the values will be separated by commas.
- prettyValue: String. Value that is displayed as the context variable value. Used like "value" above. For example, if you have a data grid with columns for IDs and a corresponding human intelligible values for the IDs, you can define the context variable value to be fetched from the ID column and the prettyValue for the context variable to be fetched from the corresponding human intelligible values column.
- behavior: String. Either default or fixed. In the fixed behavior, the variable value is set always, whether the variable previously exists or not. In the default behavior, the variable value is set only when the variable doesn't previously exist. If the behavior is not defined, the default value is fixed. Please don't mix this behavior with the context variable behaviors.
- scope: String. Either "global" or "local". With "global", the context variable value is set to the session level. With "local" the context variable value is set on the nearest level (presentation object, panel or view) where the variable has been defined as Local or Optional. If the context variable is not defined on any level, the value will be set to the session level. The default value is "local".

The events have the following properties:

eventtype: String. Specifies which type of event triggers which action. Possible event types are:
- cellclicked: Single click. The events cannot contain more than one cellclicked type event.
- celldoubleclicked: Double click. The events cannot contain more than one celldoubleclicked type event. In case both cellclicked and celldoubleclicked events are defined, both events will be triggered when the data grid is double clicked in the order cellclicked, celldoubleclicked, cellclicked (cellclicked is triggered twice).
- popupmenuitemselected: Default event type. Popup menu selection. The popup menu event type has the following additional property:
  - name: String. Mandatory. The name that is displayed in the popup menu.
actionIdentifier: String. The identifier of the executed action for click, double click, or popup menu selection event.

Once you start entering the JSON, you will se an icon indicating whether the JSON you entered is in valid JSON format or not. In case you have entered invalid JSON, you can't close the Data Grid properties.

The popup menu also works when the multiselection mode is switched on in the datagrid ("selectionType": "multiple"). In that case, if multiple rows or cells have been selected, the stored context variables will have a comma separated list containing a value for each selected cell or row. If the values contains commas (,) it is escaped with \,, so that is's possible to distinguish between commas in the values and commas separating values. Also \ is escaped with \\.

Example: Popup Menu to Run QPR ProcessAnalyzer Query

As an example, the following JSON defines a pop-up menu that runs a ProcessAnalyzer query, sets values for the context variables "country" and "city" from columns named "Country" and "City" and for the "columnIndex" context variable using the order number of the column with fixed behaviors.

{
  "actions": [
    {
      "identifier": "selectCountry",
      "paQuery": "AnalysisType=5\nparam_Country={#Country}",
      "contextChanges": {
        "country": { "value": "{$Country}" },
        "city": { "value": "{$City}" },
        "columnIndex": { "value": "{%columnIndex}" }
      }
    }
  ],
  "events": [
    {
      "eventType": "popupmenuitemselected",
      "name": "Select country",
      "actionIdentifier": "selectCountry"
    }
  ]
}

This results in a popup menu like in the picture below:
800px
And after clicking "Select country", the Session Context is like in the picture below:
800px

Example: Table Cell Clicking to Run QPR ProcessAnalyzer Query

This example contains a cellclicked event to run a QPR ProcessAnalyzer query and to set context variable values:

{
  "actions": [
    {
      "identifier": "selectCountry",
      "paQuery": "AnalysisType=5\nparam_Country={#Country}",
      "contextChanges": {
        "country": { "value": "{$Country}" },
        "city": { "value": "{$City}" },
        "columnIndex": { "value": "{%columnIndex}" }
      }
   }
  ],
  "events": [
    {
      "eventType": "cellclicked",
      "actionIdentifier": "selectCountry"
    }
  ]
}

Example: Actions When Multiple Selections in Row Mode

The Data grid has the following contents:

Country	City	Population	Capital
Finland	Espoo	270000	false
Sweden	Stockholm	935000	true
Norway	Oslo	658000	true

On the Presentation tab, the Data grid has been defined to use multiple row selections:

{
  "selectionType": "multiple",
  "selectionSettings" : {
    "selectionMode": ["row"]
  }
}

The popup menu has been defined as:

{
  "actions": [
    {
      "identifier": "setCityProperties",
      "contextChanges": {
        "places": { "value": "{$Country}-{$City}" },
        "capital": { "value": "{$Capital}" }
      }
    }
  ],
  "events": [
    {
      "eventType": "popupmenuitemselected",
      "name": "Set city properties",
      "actionIdentifier": "setCityProperties"
    }
  ]
}

When viewing the Data grid, a user selects rows "Finland" and "Sweden" and selects the "Set city properties" action from the popup menu. This will result in the context variables having the following values:

places = Finland,Sweden-Espoo,Stockholm
capital = false,true

Template:MDBTutorialAddingDataGrid