Data Query

Option 1: Data Definition

Data Definition can be selected using Schema Picker. All Data Definitions are available for selection. See example of using this type of Data Source.

• Priority (since v12.0.1): a numeric value defines which Data Query will be displayed to the user if the Data Query is not explicitly defined, for instance, in the Object Picker control Data Query to Display property, Objects Picker control, or Object Link control. By default is 0. 
  If there are several Data Queries based on the same Data Definition,  the Data Query with the highest priority is displayed. The higher the number, the higher the priority.
  Other properties that define which Data Query is displayed to the specific user of the system:
  • Default: the highest priority has a Data Query that is marked as default. If the audience settings are restricted and the default Data Query cannot be shown, the priority is checked.
  • Priority: the Data Query with the highest priority will be displayed to the user. 
  • Display Name: if all Data Queries for the Data Definition have a restricted audience and the user is not allowed to see any of these Data Queries (both Default or checked by Priority), the Display Name of the Data Definition is shown to the user instead.
• Primary Filter (since v12.0.1): an optional condition that is applied to the Data Definition and thus allows showing to the users only those entries that correspond to the specified filter. An ASQL filter that will be applied with the "AND" operator to all the data requests relying on this Data Query. 
  

The following system Data Queries have a default Primary Filter:

• Announcements_Portal: shows active announcements that are relevant for the current user based on the user's Organizational Unit or Role.
• Changes_Portal: shows Change Requests that are relevant for the current user where he/she is the initiator or is in attached users of the Change Request.
• Changes_Portal_Approvals: shows pending or processing Change Approvals where the current user is the responsible user or member of the responsible role.
• Changes_Portal_Overview: shows Change Requests that are relevant for the current user where he/she is the initiator or is in attached users of the Change Request and a decider or delegated user of the Cost Center.
• Changes_Portal_MyChangeRequests: shows Change Requests for the current user where he/she is the initiator or is in attached users of the Change Request, is responsible, or is in the responsible role.
• Tickets: shows Tickets, Incidents, and Service Requests for the current user where he/she is the initiator or is in attached users.
• Portal My Hardware: shows Hardware that belongs to the current user.

For more details, see also Data Query Primary Filter Configuration Example page. 

Option 2: Web Service

DataQuery can be based on the Web Service Method, which returns an array of Objects and supports OData interface for filtering data. See example.

Option 3: SQL Query

In case the "Data Definition" option does not cover the use case, the user can access data in the database directly using an SQL Query.

• SQL Query: valid SQL query should be set here. Users can validate the query using the "LOAD COLUMNS" Button, which will also generate the columns in the section below.
• Allow Cache: if checked the results of the query will be cached on the server. Users can also define how long it will be cached in the field "Cache Interval".
• Culture Sensitive: if checked then cache will be deleted after changing the culture.
• Cache Interval: how long the query result is cached on the server.
• Such parameters  are available in SQL Query:
  • @LCID_long - Long language code for the current user session. Ex. 1031
  • @LCID_short - Short language code for the current user session. Ex. 9
  • @CurrentUserId: SPSUserClassBase.ID of current user 

See example.

Data

 

Expression	The field contains the expression that defines the data taken from Data Source. The way to set the expression depends on the Data Source: If the Data Source is Data Definition then ASQL expression should be used. In this case, the title "Expression [(...)]" contains a hint, for example "Expression (Subject)" or "Expression (Pickup)". Example of simple Expressions: "Expression (StringType)" = Subject. If the Data Source is WEB Service then Java Script expression should be set using contract from the service operation. You can see the contract details when you click on the service operation in Data Query. If the Data Source is SQL Query then the only way to create the column is to use the LOAD COLUMNS button. Expression cannot be changed. All complex logic should be implemented at the level SQL Query.
Name	Technical name of the column. The name should be unique inside of the Data Query.
Position	Defines the order of columns in the Grid, based on the the the Data Query.
Title Type	Specifies the source of the column Title. "No Title", column has no title. "Take From Expression" means that the title will be automatically detected from the related Column Expression, and refers a Schema attribute which has localized Display String. In case, the title is not been identified from Expression, the form be invalid. "Individual Display String" allows you to set any string, the value from Data Source will be pre-selected and can be edited.
Title	title of the column in visualizations of the Data Query, for example, in the grid. It is set depending on option "Title Type", see above.
Alignment	self-explanatory
Width	in visualizations of the Data Query, for example, in the grid, in px.
Display	The property specifies the way the column be displayed in Grid "Visible", the column is visible on grid by default  "Invisible by default", the column is hidden in grid, but column visibility can be changed in Grid Settings on the right side (see example below). "Hidden", the column is not present in Grid, but the System returns column data to Client, and this data can be used for internal operations, e.g. filtering of the available Actions "None", column is not present in Grid. The data for column is not retrieved. The main purpose to use such column for search in Advanced Search
Visibility	Allows automatically show/hide the column depending on the kind of the client device, where the Grid is displayed.  "All devices", the column is displayed everywhere "Except Mobile", on all platforms, except mobile "For Desktop Only", column is visible only on Desktops "Mobile Only", the column visible only on Mobile platform
Format	The Format property setups custom format for the data. Can be applied to data of types DateTime, Double, Float,. Date   Formats for versions 10.0.0 and later: 'YYYY' or 'Y': 4 digit representation of year (1970 1971 ... 2029 2030) 'YY': 2 digit representation of year (70 71 ... 29 30) 'MMMM': Month in year (January-December) 'MMM': Month in year (Jan-Dec) 'MM': Month in year, padded (01-12) 'M': Month in year (1-12) 'DD': Day of month, padded (01-31) 'D': Day of month (1-31) 'DDD': Day of year (1 2 ... 364 365) 'DDDD': Day of year (001 002 ... 364 365) 'dddd': Day in Week,(Sunday Monday ... Friday Saturday) 'ddd': Day in Week, (Sun Mon ... Fri Sat) 'dd': Day in Week, (Su Mo ... Fr Sa) 'd': Day in Week, (0 1 ... 5 6) 'do': Day in Week, (0th 1st ... 5th 6th) 'HH': Hour in day, padded (00-23) 'H': Hour in day (0-23) 'hh': Hour in AM/PM, padded (01-12) 'h': Hour in AM/PM, (1-12) 'mm': Minute in hour, padded (00-59) 'm': Minute in hour (0-59) 'ss': Second in minute, padded (00-59) 's': Second in minute (0-59) 'SSS': Millisecond in second, padded (000-999) 'a': am/pm marker 'A': AM/PM marker 'z': timezone (EST CST ... MST PST) 'Z': timezone (-07:00 -06:00 ... +06:00 +07:00) 'ZZ': timezone (-0700 -0600 ... +0600 +0700) 'ww': Week of year, padded (00-53). Week 01 is the week with the first Thursday of the year 'w': Week of year (0-53). Week 1 is the week with the first Thursday of the year 'medium': equivalent to 'MMM d, y h:mm:ss a' for en_US locale (e.g. Sep 3, 2010 12:05:08 PM) 'short': equivalent to 'MM/DD/YYYY h:mm a' for en_US locale (e.g. 9/3/10 12:05 PM) 'fullDate': equivalent to 'EEEE, MMMM d, y' for en_US locale (e.g. Friday, September 3, 2010) 'longDate': equivalent to 'MMMM d, y' for en_US locale (e.g. September 3, 2010) 'mediumDate': equivalent to 'MMM d, y' for en_US locale (e.g. Sep 3, 2010) 'shortDate': equivalent to 'MM/DD/YYYY' for en_US locale (e.g. 9/3/10) 'mediumTime': equivalent to 'h:mm:ss a' for en_US locale (e.g. 12:05:08 PM) 'shortTime': equivalent to 'h:mm a' for en_US locale (e.g. 12:05 PM) 'timeAgo':  example "7 days ago",  "a minute ago" 'timeAgoShort':  relative time, example "-7d",  "1min"   For versions earlier than 10.0.0 there are the following differences in the date format: lowercase 'yy/'yyyy' instead of uppercase 'YY'/'YYYY' for years 'y' - 1 digit representation of year lowercase 'd'/'dd' instead of uppercase 'D'/'DD' for day of month 'E'/'EE'/'EEE'/'EEEE' instead of 'd'/'dd'/'ddd'/'dddd' for day of week Double, Float, Number  * supported from 9.0.5 Number Format String 10000 '#,#.####' 10,000.0000 10000.23 '#,#' 10,000 -10000 '#,#.#' -10,000.0 10000.1234 '#.###' 10000.123 100.1234 '#####' 00100 10 '###.##' 10.00 10000.1234 '#[.]#####' 10000.12340 0.23 '#.#####' 0.23000 0.23 '#.#[####]' 0.23

 

Search & Sorting

The Search and sorting options are configured differently for different Data Sources. For Data Definition and WEB Service Data Sources, there are more possibilities. For SQL Query Data Source, the complex logic should be implemented at the level of the SQL Query.

For Data Definition and WEB Service Data Sources

• Use in Keyword search: if checked then this column will be used in the keyword search, if not the only advanced search is possible. Involving of too many columns in keyword search can cause the slower work of the search, so this field should be used carefully.
  If Data Query has no column explicitly marked as "Use In Keyword Search", then the System considers the Data Query as misconfigured, and automatically picks the Column for Keyword search 
• Search Display Type: "Yes" - the column is available in advanced search, "No" - the column is not available in advanced search.
• Automatically defined: the condition for the column will be automatically present for the newly created filter, run a standard advanced search.

• Search Expression: expression defining the value to be used in the search. This field should be set similarly to the "Expression" in the Data section of the column depending on Data Source (Data Definition or WEB Operation). 
• Sorting: the options below are self-explanatory.

• Sorting Expression: expression defining the value to be used in sorting. This field should be set similarly to the "Expression" in the Data section of the column depending on Data Source (Data Definition or WEB Operation). 

 

For SQL Query Data Source, the corresponding sub-form is more primitive. The fields are filled as described for the previous case.

Graphics

The formatting of the columns in the grid is configured here. There is a possibility to create conditional formatting and add images. See also examples of the possible configuration of the format in the grid.

Configuration of image

• Display Image: it is possible to show in the column instead of the usual text the

"Image":  

or "Image together with the Text":   .

• Image Script: Javascript expression that returns the reference to the image. An image can be represented by the name of the available SVG icon, or just a relative or full URL to the image. You can operate with two variables in Script to specify the logic, $item - represents the whole data in grid row, and $value - value of the column where the Image script is defined. See the Graphics Configuration section of this page for more information.

All the available images are here https://material.io/icons/

Configuration of colors

• Use Color For:

• Color Script: Javascript expression that configures the color of the Cell Text / Cell Background / Row Text / Row Background. The expression can return either the code of the color, e.g. #FF0000, or the whole definition of the CSS style, like 'font-weight:bold'. As well as for the Image Script, it is possible to use variables $item and $value to specify the logic. 
  See the examples for more information depending on the value set in "Use Color For". If several options Cell Text / Cell Background / Row Text / Row Background needed, use trick with invisible column.

• Column Background Color: is selected from the color picker. It has a higher priority than the color configuration above.

After the new Data Query is created you can specify users and user groups that are eligible to access the data of the Data Query with the Set Audience action. For more information, see also How to Set Audience in SolutionBuilder.

Localization 

The Data Query dialog does not support the localization of the Columns in the "Localization Mode". The localizing of the Columns can be fulfilled in the "Administration / User Interface / Localization" area. To show only the Data Query Columns in the grid, filter the Localization string Context to "PDRDataQueryClassColumn : Title"