Difference between revisions of "Data Grid Properties"

From Mea Wiki
Jump to navigation Jump to search
(306993)
 
(36 intermediate revisions by 2 users not shown)
Line 1: Line 1:
A data grid is a table presentation object.
+
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:
On the '''JSON settings''' section, you can change variety of Data grid features, such as enable/disable searching, filtering, paging, sorting, grouping, whether the Data grid selection is in row or cell mode, and so on. QPR UI uses Syncfusion component. See the Syncfusion demos what features there are available in the Data grid, and see from the settings reference how the desired settings are enabled:
 
 
* Syncfusion Grid demo: http://js.syncfusion.com/demos/web/#!/bootstrap/grid/defaultfunctionalities
 
* Syncfusion Grid demo: http://js.syncfusion.com/demos/web/#!/bootstrap/grid/defaultfunctionalities
 
* Syncfusion Grid documentation: https://help.syncfusion.com/js/grid/overview
 
* Syncfusion Grid documentation: https://help.syncfusion.com/js/grid/overview
 
* Syncfusion Grid settings reference: https://help.syncfusion.com/api/js/ejgrid
 
* Syncfusion Grid settings reference: https://help.syncfusion.com/api/js/ejgrid
[https://www.w3schools.com/js/js_json_intro.asp See here] for more information about JSON.
+
Check [https://www.w3schools.com/js/js_json_intro.asp 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 JSON Settings===
 
Examples of changing JSON settings:
 
Examples of changing JSON settings:
  
'''Multiple Rows Selection Mode'''
+
{| class="wikitable"
 +
!Feature
 +
!Configuration
 +
|-
 +
||Filtering
 +
||
 +
By default, column filtering is used. It can be disabled as follows:
 +
<pre>
 +
{
 +
  "allowFiltering": false
 +
}
 +
</pre>
 +
|-
 +
||Excel style filtering
 +
||
 +
<pre>
 +
{
 +
  "filterSettings" : {
 +
    "filterType": "excel"
 +
  }
 +
}
 +
</pre>
 +
|-
 +
||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:
 +
<pre>
 +
{
 +
  "toolbarSettings": {
 +
    "showToolbar": false
 +
  }
 +
}
 +
</pre>
 +
|-
 +
||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:
 +
<pre>
 +
{
 +
  "columns": [
 +
    { "type": "string" },
 +
    { "type": "number" },
 +
    { "type": "date" },
 +
    { "type": "datetime" },
 +
    { "type": "boolean" }
 +
  ]
 +
}
 +
</pre>
 +
|-
 +
||Date formatting
 +
||
 +
For ''number'', ''date'' and ''datetime'' columns, the date formatting can be specified for example as follows:
 +
<pre>
 +
{
 +
  "columns": [
 +
    { "type": "number", "format": "{0:n2}" },
 +
    { "type": "date", "format": "{0:d.M.yyyy}" },
 +
    { "type": "datetime", "format": "{0:dd/MM/yyyy HH:mm:ss}" },
 +
  ]
 +
}
 +
</pre>
 +
|-
 +
||Multiple rows selection
 +
||
 
<pre>
 
<pre>
 
{
 
{
Line 19: Line 84:
 
}
 
}
 
</pre>
 
</pre>
 
+
|-
'''Multiple Cells Selection Mode'''
+
||Multiple cells selection
 +
||
 
<pre>
 
<pre>
 
{
 
{
Line 29: Line 95:
 
}
 
}
 
</pre>
 
</pre>
 
+
|-
'''Minimum Column Width'''
+
||Minimum column width
 +
||
 
<pre>
 
<pre>
 
{
 
{
Line 37: Line 104:
 
</pre>
 
</pre>
 
Link to Syncfusion settings reference: https://help.syncfusion.com/api/js/ejgrid#members:minwidth
 
Link to Syncfusion settings reference: https://help.syncfusion.com/api/js/ejgrid#members:minwidth
 
+
|-
'''Specific Column Widths'''
+
||Specific column widths
 
+
||
 
Setting the width to the second, third, and fifth columns named "Case", "Event Type", and "Cost" respectively:
 
Setting the width to the second, third, and fifth columns named "Case", "Event Type", and "Cost" respectively:
 
<pre>
 
<pre>
Line 52: Line 119:
 
}
 
}
 
</pre>
 
</pre>
 
+
|-
'''Resizable Columns'''
+
||Resizable columns
 +
||
 
<pre>
 
<pre>
 
{
 
{
Line 60: Line 128:
 
</pre>
 
</pre>
 
Note that the resized widths cannot be saved, so this setting is for view-time only.
 
Note that the resized widths cannot be saved, so this setting is for view-time only.
 
+
|-
'''Filtering'''
+
||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 [[QPR UI Data Grid Export Service|corresponding export service]]. In this example, context variables are used for the export service URL definitions:
 
<pre>
 
<pre>
{
+
{
   "allowFiltering": true
+
"exportToExcelAction": "http://<YourHostname>/SyncfusionGridExport/excelExport",
 +
"exportToWordAction": "http://<YourHostname>/SyncfusionGridExport/wordExport",
 +
"exportToPdfAction": "http://<YourHostname>/SyncfusionGridExport/pdfExport",
 +
"toolbarSettings": {
 +
   "showToolbar": true,
 +
  "toolbarItems": ["excelExport", "wordExport","pdfExport"]
 +
}
 
}
 
}
 
</pre>
 
</pre>
 +
By default, the exportToExcelAction, exportToWordAction, and exportToPdfAction values point to locations on the same host.
 +
|}
  
'''Search'''<br>
+
=== Datasource in JSON Settings ===
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:
+
It's possible to define the Data Grid to use a certain [[Datasets_in_QPR_UI|dataset]] by using the '''dataSource''' attribute in the JSON settings that contains a valid [[Dataset_Tag_in_QPR_UI|dataset tag]] as the value:
 
<pre>
 
<pre>
 
{
 
{
  "toolbarSettings": {
+
"dataSource": <#dataset identifier="ExampleDataset">
    "showToolbar": false,
 
    "toolbarItems": ["search"]
 
  }
 
 
}
 
}
 
</pre>
 
</pre>
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.
+
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 ===
 
=== Context Variables in JSON Settings ===
Line 134: Line 209:
 
</pre>
 
</pre>
  
== Data grid CSS settings ==
+
== 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 [https://help.syncfusion.com/js/grid/style-and-appearance Syncfusion documentation] and some more [https://www.syncfusion.com/kb/5172/how-to-customize-grid-appearance-using-custom-css here]. General information about CSS is [https://www.w3schools.com/ 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.
 
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 [https://help.syncfusion.com/js/grid/style-and-appearance Syncfusion documentation] and some more [https://www.syncfusion.com/kb/5172/how-to-customize-grid-appearance-using-custom-css here]. General information about CSS is [https://www.w3schools.com/ 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.
  
Line 168: Line 243:
 
* CSS tutorial: https://www.w3schools.com/css/
 
* CSS tutorial: https://www.w3schools.com/css/
 
* CSS settings reference: https://www.w3schools.com/cssref/
 
* 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 [https://www.w3schools.com/js/js_json_intro.asp JSON] configuration strings that are arrays containing the actions and events.
 
 
The '''actions''' have the following properties:
 
* '''identifier''': String. Mandatory. The identifier of the action.
 
* '''paQuery''': String. Optional. The ProcessAnalyzer query to execute. 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". If the behavior property is missing, the value is set as "fixed".
 
 
The '''events''' have the following properties:
 
* '''eventtype''': String. The value defined here specifies which type of event triggers which action. Possible 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 1 ===
 
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.
 
<pre>
 
{
 
"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"
 
}
 
]
 
}
 
</pre>
 
This results in a popup menu like in the picture below:<br>
 
[[File:DataGridPopup.png|800px]]<br>
 
And after clicking "Select country", the Session Context is like in the picture below:<br>
 
[[File:DataGridPopupSessionContext.png|800px]]
 
 
=== Example 2 ===
 
This example contains a cellclicked event to run a QPR ProcessAnalyzer query and to set context variable values:
 
<pre>
 
{
 
"actions": [
 
{
 
  "identifier": "selectCountry",
 
  "paQuery": "AnalysisType=5\nparam_Country={#Country}",
 
  "contextChanges": {
 
    "country": { "value": "{$Country}" },
 
    "city": { "value": "{$City}" },
 
"columnIndex": { "value": "{%columnIndex}" }
 
  }
 
}
 
],
 
"events": [
 
{
 
  "eventType": "cellclicked",
 
  "actionIdentifier": "selectCountry"
 
}
 
]
 
}
 
</pre>
 
 
=== Example 3: Multiple Selections in Row Mode ===
 
The Data grid has the following contents:<br>
 
{| class="wikitable"
 
!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:
 
<pre>
 
{
 
  "selectionType": "multiple",
 
  "selectionSettings" : {
 
    "selectionMode": ["row"]
 
  }
 
}
 
</pre>
 
 
The popup menu has been defined as:
 
<pre>
 
{
 
"actions": [
 
{
 
  "identifier": "setCityProperties",
 
  "contextChanges": {
 
    "places": { "value": "{$Country}-{$City}" },
 
    "capital": { "value": "{$Capital}" }
 
  }
 
}
 
],
 
"events": [
 
{
 
  "eventType": "popupmenuitemselected",
 
  "name": "Set city properties",
 
  "actionIdentifier": "setCityProperties"
 
}
 
]
 
}
 
</pre>
 
 
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
 
 
{{MDBTutorialAddingDataGrid}}
 
  
 
[[Category: QPR UI]]
 
[[Category: QPR UI]]

Latest revision as of 15:00, 15 January 2020

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.

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:

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

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: