Data Grid Properties

From QPR ProcessAnalyzer Wiki
Jump to: navigation, search

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:

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:

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:
DataGridPopup.png
And after clicking "Select country", the Session Context is like in the picture below:
DataGridPopupSessionContext.png

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

Add Data Grid to View

  1. Add a Data Grid Presentation Object to the panel by clicking the Data Grid Presentation Object button on the side toolbar.
  2. This is a good point to save the Panel. Click the Save button on the toolbar on top.
    SavePanel.png
  3. The Presentation Object Properties are used to define what data is used in the Data Grid and how the data is shown. Click the Properties button on the Data Grid Presentation Object to open the data grid properties.
    OpenDataGridProperties.png
  4. First, give a Name and optionally a Description for the data grid. Then click the Query tab.
    DataGridProperties.png

Defining Queries

The query defines the element(s) from which attributes having values are taken. Click Add query to start defining a query, and then select either EnterpriseArchitect / ProcessDesigner, Metrics element, Metrics scorecard, or ProcessAnalyzer to be the initial data source.
AddQuery.png

When querying EnterpriseArchitect / ProcessDesigner data, the first element that appears to the query is the sys:eapdElementType value that is defined in the Session or View/Panel/Presentation object Properties. Click on the Element and Connector types in the Side Pane until you have reached the desired Element Type from which you want to show data.
EAPDQuery.png

When querying Metrics data, with Metrics scorecard, you can select the element residing in the scorecard defined by the sys:metricsScorecard context variable to be the element from which you want the query to take data. With Metrics element, the first element that appears to the query is the element you selected as the sys:metricsElement context variable value. Note that when the value of the context variables are changed, the initial element in the query will change too.

Metrics reference elements are shown with the format "Element name (Scorecard name)". When using a reference element, the data will always be taken from the selected reference element, regardless of what the currently selected scorecard is.

MetricsQuery.png

When querying Metrics data, you can define the Period range, which defines how many periods are shown before and after the currently selected period in the view.
MetricsQueryDefinePeriodRange.png

More information about writing QPR ProcessAnalyzer queries and QPR Web Service queries.

WSquery.png

After reaching the wanted element (EA/PD), defining the period range (Metrics), or defining the analysis parameters (ProcessAnalyzer), click Finalize and give a name for the query result. The attributes from the result set will be selectable on the Mappings tab. If you want to add another query, click the Add query button.
FinalizeQuery.png

Mapping Query Results to the Data Grid

  1. Click the Mapping tab. On the Mapping tab, you select which attributes' values are shown in the table.

    First, expand a Result Set by clicking it under Select attributes.
    MappingExpandResult.png
  2. Click on the Attribute Names on the Result Set list to add the attributes to the table. The whole rectangular area shown on the sheet is called a Mapping. The dotted line shown on the mapping on the sheet indicates where the next attribute will be placed in the table.

    On the Mapping tab, the table will show only placeholders for the attribute values. Real values can be seen on the Presentation tab.
    MappingSelectAttributes.png
  3. A single mapping can have attribute values from only one result set defined on the Query tab. To add another mapping to the table, click a cell that doesn't have a mapping definition in it. To edit an existing mapping, click on a cell on that mapping.
    MappingAddAnother.png
  4. To Move, set the Size and Direction, Hide or Show the Table Headers, or to Delete the mapping, click the Mapping Toolbar buttons shown on top of the mapping.
    MappingToolbar.png
  5. Click the Cogwheel button to open the Cell Properties, and define some Text and the Link URL for the cell.
    MappingCellProperties.png
    The Text in the Cell Properties will overwrite any value coming from a mapping, so that the value won't be shown in the table.

    When the Link URL is defined, the cell will contain a hyperlink to the defined URL, and the link will be shown if you have defined the Text for the cell or if the table contains a value for that cell.

    To link to a QPR UI view, define the view with the "sys:dashboardIdentifier" variable in the URL like this: "http://your.host.here/ui/#/dashboard?sys:dashboardIdentifier=<View identifier of the target view>". Similarly, you can use the sys:dashboard variable, if you haven't defined the View identifier for the target view. You can also define values for the target view's context variables by appending "&variablename=<variablevalue>" to the URL, and utilize context variables as the value in the URL by adding them using the format "{#contextvariablename}". For example:
    http://your.host.here/ui/#/dashboard?sys:dashboardIdentifier=MyFineDashboard&country=Finland&scorecard={#YourScorecardIdentifierVariable}.
    Note: Each variable value you define in the URL like described above must have the value defined, otherwise the link won't work.

    If the QPR Suite Single Sign-on is in use, and you want to link to QPR Portal or QPR ProcessAnalyzer Web Client using the QPR Suite Single Sign-on, append a "&XSESSION={#sys:sessionId}" parameter to the end of the link URL, for example:
    http://your.host.here/QPR2017-1/Portal/QPR.Isapi.dll?your-mea-parameters&XSESSION={#sys:sessionId}

    Cells for which you have defined Cell Properties are marked with a triangle on the top right corner of the cell.

Formatting Values

To format the values in a column or row:

  1. Click a Column/Row Function button and select Format column/row.
  2. Select the Show numeric values as percentages check box to convert the decimal numbers into percentages.
  3. To limit the number of decimals, select the Number of decimals check box and define the number of decimals you wish to use.
  4. Select the Format cell background based on column values check box to make the cells have gradient background colors computed from the position of the value of each cell relative to the maximum and minimum values in the column or row.

To hide a column or row, select Hide column/row from the Column/Row Function buttons menu. A hidden column or row can be shown by selecting Show column/row from the Column/Row Function button menu.