Data queries

The visual data query editor is a supervisor (or admin) tool that allows you to create and edit data queries, which can be displayed in grids or used to populate various DDN with data. Queries let you predefine the dynamic structure of data returned from the database, which can be used in grids or charts. The query contains subordinate column definitions that determine how the data will be displayed.

The editor can be found in FrontStage administration. You need the EditQuery role.

Note

If the user has assigned the role EditQuery at leats on AllowRead level, new button will appear within data grids, for opening the editor, located in lower bar

The running editor displays a list of all existing queries. It is also filled with the result of the data query. Its ID is in the QueryAdminQueryId configuration parameter.

In the upper right corner there are buttons for interacting with the records:

• Create - Displays a dialog for creating a query header.

• Copy - Creates a copy of the query marked in the list.

• Delete - Deletes the marked records in the list.

• Reset selected perf. counter - Reset all counters at once. To use this button, you need the DiagAdmin role with at least the AllowWrite level.

• Export - Exports tagged records in JSON format. The file can be used for updating the record and importing it again.

  Note

  You can perform the import using the “General.Model.Db” utility by running it in Powershell (run it locally from the utility’s folder) and entering the command: dotnet general.model.db.dll addall .\ExportFile.json

• Usage list - Option to assign specific queries to application functions. The query will then populate the functionality with its data.

• Usage Matrix - Functions like List, but different display.

• Save

The Detail tab

The tab allows you to define a query using SQL syntax and set its parameters within the system.

Available parameters:

• ID - System generated GUID.

• Name - You have already filled it in, editable. It is intended for administration; the user sees it together with the query group name when selecting a query in the grid.

• Description - Further specification or note. Intended only for the administrator.

• GDPR log level.

• Query group - For categorizing and grouping similar queries.

• Sorting

  • The name of the query column by which the records will be sorted. Must correspond to the Target column name value for a specific existing column on the same page.

  • You can enable sorting by multiple columns, names separated by a comma, e.g., “SpecificName, DisplayName”.

  • You can force the sorting direction: “DESC” for descending, “ASC” for ascending. E.g.: “TimeUtc DESC”.

  • This sorting will be used by the grid when the user has not selected any column for sorting yet

• Use for special types of queries with a different condition construction when special conditions are needed beyond the default query (e.g., conditions with OR or tolerating NULL values, or it may be needed for selection queries), primarily used for the “ReactClient” application, where queries used only AND filters. In “TeamsClient”, more complex conditions can be built using the OR construct, “multiple choice”, etc. The construction of a query from the grid is done by taking the original query written in the DataQuery definition, creating another parenthesis around it, and recreating the SELECT (original_query) WHERE X,Y,Z ORDER BY A,B,C (CTE query). If this parameter is active, it is assumed that the WHERE X,Y,Z part is already contained in original_query. To do this, you need to know the variable names in the form generated by the DataQuery mechanism. These start with the @ character and are filled in by the DataQuery mechanism based on the object being processed. Example for the query “All outgoing calls”:

  (@TimeUtc IS NULL OR (@TimeUtcFrom<=TimeUtc)) AND
  (@TimeUtc IS NULL OR (@TimeUtcTo>=TimeUtc)) AND
  (@CallerNumber IS NULL OR (CallerNumber LIKE @CallerNumber)) AND
  (@ProjectId IS NULL OR (@ProjectId=ProjectId)) AND
  (@LanguageId IS NULL OR (@LanguageId=LanguageId)) AND
  (@WorkplaceId IS NULL OR (@WorkplaceId=WorkplaceId)) AND
  (@CallDuration IS NULL OR (@CallDurationFrom<=CallDuration)) AND
  (@CallDuration IS NULL OR (@CallDurationTo>=CallDuration)) AND
  (@OutboundListName IS NULL OR (OutboundListName LIKE @OutboundListName)) AND
  (@AgentId IS NULL OR (@AgentId=AgentId)) AND
  (@trial IS NULL OR (@trialFrom<=trial)) AND
  (@trial IS NULL OR (@trialTo>=trial)) AND
  (@CallTime IS NULL OR (@CallTimeFrom<=CallTime)) AND
  (@CallTime IS NULL OR (@CallTimeTo>=CallTime)) AND
  (@CallResult IS NULL OR (CallResult IN (@CallResult_0))) AND
  (@CallPhase IS NULL OR (CallPhase IN (@CallPhase_0)))
  

• Query type - Specifies how the system will evaluate the query.

  • DQ - Common data query (executed against the database). Usually serves as a data source for the grids.

  • Snapshot - Executed once and then the data is returned for the time set in Snapshot interval without change, to all users equally. Assumes non-parametric queries, i.e., should not use @Me, @MyTeam and similar variables, as their value changes depending on who calls the query first. The processing of this type of query is always the same, i.e., it doesn’t matter whether it is called by a “ReactClient”, “synchronous service”, etc.

  • Timeline - Called repeatedly, always after the time set in Snapshot interval. Assumes non-parametric queries, i.e. should not use @Me, @MyTeam and similar variables, as their value changes depending on who calls the query first. Individual calls are layered into a series (after the interval, the query is called again, and the first row of data is taken and added to the series). Queries of this type are usually plotted as a graph. Additional parameters appear when this option is selected:

    • Snapshot interval - Time [s] of the query periodical execution.

    • Timeline - Length of individual execution series. Selected interval will be presented by the query, captured data already out of the interval will be deleted from the series (if you choose the value “None” = NULL, the type “Timeline” will be evaluated as standard “DQ”).

    

  • Cache - The query is called against the database and then returns the same data from cached memory (to all users) for a set periode. It is used for specific parametric queries (e.g., bar queries). It is specifically used in the processing of “synchronous service”, for the needs of “BulletinBoard”, for the application “DesktopClient”: the data query should have the column AgentId, because the data is taken as one query from the database, but only the row for a specific agent is always sent to the “DesktopClient” (if the column AgentId is omitted, it will be evaluated by other applications as a regular data query). When this option is selected, additional parameters appear:

    • Cache interval - Time [s] of the cached data expiration.

• Main DB table - You can open the detail of the specified object by clicking on the row in the grid. This setting tells you what type of object it is and where in the database to look for a match (search by Id or EntityId columns). This parameter affects only TeamsClient (opening the object in the “Mainstage” tab). It has no effect on ReactClient.

• Query - SQL query text.

  Example query:

  SELECT M.*, P.DisplayName AS ProjectName, G.DisplayName AS  GatewayName, A.DisplayName AS AgentName, L.DisplayName AS LanguageName,
  CAST(CASE WHEN M.Eml IS NULL THEN 0 ELSE 1 END AS bit) AS HasEml
  FROM Message AS M
  LEFT JOIN Project AS P ON M.ProjectId=P.ProjectId
  LEFT JOIN Gateway AS G ON M.GatewayId=G.GatewayId
  LEFT JOIN Agent AS A ON M.AgentId=A.AgentId
  LEFT JOIN Language AS L ON M.LanguageId=L.LanguageId
  

  Processing - The entire query is wrapped in a paging query:

  SELECT * FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY QuerySortExpression ) AS CTE_ROW_NUMBER, *
   FROM ( QueryText )
  AS CTE_DataQuery
  WHERE QueryFilter )
  AS CTE_FilterQuery
  WHERE CTE_ROW_NUMBER BETWEEN Start AND End
  ORDER BY CTE_ROW_NUMBER
  

  Where:

  • QueryText is the original query with the sorting clause added

  • Start and End are the record range requested by the grid’s paging mechanism

  • QuerySortExpression is replaced by the current sorting expression

  • QueryFilter is replaced by the current filtering expression

  You can also use variants that differ by the initial SELECT expression:

  • Get record count: SELECT COUNT(*) FROM

  • Find possible values in a column (value palette): SELECT DISTINCT *ColumnName* FROM

The subfolder contains a Preview button that you can use to view and debug the query continuously.

Note

The permission to create and modify the query text is controlled by the DataQuery role.

Query parameters

A query can contain multiple parameters, all starting with the @ character. Before sending the query to the database, they are automatically filled with values. You can use them in the SQL query text, entered in the Detail subtab, parameter Query.

We divide them into two groups:

• Column parameters - Depending on the column type, the column filter generates one or more parameters that can be used in the query. You set the column type for each column individually in the Columns subtab. Parameters for each column type are described in Column models.

• Global parameters - These are always available and are generated by the grid itself, regardless of filters.

Global parameters

Parameter	Description
@MeAgentId	The agent’s GUID who is viewing the grid page. Usage: all queries like “My emails”. Data type: uniqueidentifier.
@MeTeamName	The name of the agent’s team who is viewing the grid page. Usage: all queries like “Our team’s emails”. Data type: nvarchar.
@GdprMaster	Flag indicating that the user viewing the grid has GDPR Master permission. Data type: bit.
@GdprArchiver	Flag indicating that the user viewing the grid has GDPR Archiver permission. Data type: bit.
@Now	Current local time. Data type: datetime.
@NowUtc	Current UTC time. Data type: datetime.
@Utc2Local	Current offset of local time to UTC time in seconds. Data type: integer. Example: On 31.1.2016, this variable in Prague time zone UTC+01 will have the value 3600. On 31.7.2016, in Prague UTC+01 summer time, it will have the value 7200.
@LastHour	Local time one hour ago. Data type: datetime. Usage: ongoing results for the past hour.
@LastHourUtc	UTC time one hour ago. Data type: datetime. Usage: ongoing results for the past hour.
@LastDay	Local time 24 hours ago. Data type: datetime. Usage: ongoing results for the past day.
@LastWeek	Time one week ago (24*7 hours). Data type: datetime. Usage: ongoing results for the past week.
@LastMonth	Time one month ago (same date or last date in previous month). Data type: datetime. Usage: ongoing results for the past week. Example: On 31.3.2016, this variable will have the value 29.2.2016.
@Today	Time 00:00:00 of today’s date. Data type: datetime. Usage: results for today. Example: On 31.3.2016, this variable will have the value 31.3.2016 00:00:00
@TodayUtc	Time 00:00:00 of today’s local date, converted to UTC. Data type: datetime. Usage: results for today. Example: On 31.3.2016, in Prague time zone UTC+01, this variable will have the value 30.3.2016 23:00:00
@ThisWeek	Time 00:00:00 of the first day of the week (according to Culture), usually Monday. Data type: datetime. Usage: results for this week
@ThisMonth	Time 00:00:00 of the first day of the month. Data type: datetime. Usage: results for this month.
@FilterTimeFrom	If a period selector is attached, this is the start time. If not, it is NULL. Data type: datetime.
@FilterTimeTo	If a period selector is attached, this is the end time. If not, it is NULL. Data type: datetime.
@WeekDays	If a period selector is attached, this is a string of seven 0 and 1 characters, indicating for each day of the week whether it is selected (1) or not (0). Data type: nvarchar. Example: “1111100” – represents weekdays. Usage – the following condition evaluates whether the value in the TimeLocal column is on a selected day or not: SUBSTRING( @WeekDays, DATEPART(dw, TimeLocal),1)=’1’
@DayHours	If a period selector is attached, this is a string of 24 0 and 1 characters, indicating for each hour of the day whether it is selected (1) or not (0). Data type: nvarchar. Example: “000000111111000000000000” – represents the morning hours Usage – the following condition evaluates whether the value in the TimeLocal column is in a selected hour of the day or not: SUBSTRING( @DayHours, DATEPART(hh, TimeLocal),1)=’1’
@RecordId	In queries triggered in context (typically entity editors), this parameter contains the record ID relevant to the context. Another option is to specify this parameter in the grid window URL.

Columns tab

List and settings of columns for grid needs. You can create one at a time using Add column or all at once using the Generate columns function. It fills in the missing columns according to the indication in the TargetColumns database table and tries to guess their types according to the data type in the query.

Note

The query returns all columns in the SELECT section, but the grid can only use and display columns that are predefined in this section. One grid column can draw data from multiple SQL query columns.

You can edit the detail for each column:

• - Ordering of columns in lists and displayed grid. Set order using the drag & drop.

• Column width - Default grid column width in pixels. The user can change it in their settings. It can be empty, then the default value 80 is used.

• Name - Displayed name in the grid. To localize it, you need to write it as a variable starting with “$” - e.g., “$Cmn_State”. Variables can be found or created in the LiteralLookup database table.

• Target column name - Must correspond with the name of the specific column written in the SQL query on the Detail page, this will link it.

• Model - Specifies the column type in the grid, how data is sourced, and how it is displayed. Based on the selected model, additional column settings will appear see Values available according to the selected model.

Column models

Model	Display	Parameters for value	Filter	@Parameters
Text	Plain value, left-aligned	TargetColumn, TargetFormat	Textbox, dovolující zapsat hledaný text. Používá „SQL LIKE“ % konvenci a navíc tyto případy: Přesný výraz: „abc“ Začnající: „abc*“ Obsahující: „*abc“ Začínající na „x“ a mající ještě 2 znaky: „x??“ SQL LIKE konvence	Single parameter by TargetColumn, containing SQL LIKE syntax. Data type nvarchar.
Fulltext	Plain value, left-aligned	TargetColumn, TargetFormat	Textbox, dovolující zapsat hledaný text. Používá „CONTAINS“ %syntaxi, která dovoluje vyhledat výraz, začátek výrazu a případně operátory mezi nimi: Přesný výraz: „abc“ Začnající: „abc*“ Dva výrazy: „abc AND def“ CONTAINS konvence	The expression must be considered in the query itself in the WHERE section using syntax: @Parameter= '##FullText##'. This macro element is replaced by the appropriate CONTAINS predicate, with the column list taken from the Name field.
Select	Plain value, left-aligned	TargetColumn, TargetFormat or Converter. If a converter is used, it translates values to “readable” form according to its type.	Dropdown menu allowing you to select a specific value.	Single parameter by TargetColumn, containing the exact value, without conversion. Data type nvarchar.
Options	Plain value, left-aligned	TargetColumn - displayed column, optionally converted using TargetFormat pairs - choices separated by semicolons. One choice can be just a value or a comma-separated pair (first item is the literal from the query, second is the displayed name) Example 1 - pairs: DayInWeek,Weekly;NotDayInWeek,Weekly (reverse);DayInYear,Yearly;SingleDay,One-time Example 2 – just values: WF;SQL;PAUSE	Dropdown menu allowing you to select a specific value.	Single parameter by TargetColumn, containing the exact value, without conversion except for specified pairs. Data type nvarchar.
Hyperlink	Value displayed as a link, left-aligned	TargetColumn, TargetFormat. (Parameters for link: UrlColumn, UrlFormat)	Textbox allowing you to enter search text. Uses * and SQL LIKE convention.	Single parameter by TargetColumn, containing SQL LIKE syntax. Data type nvarchar.
Image	Value displayed as an image, optionally clickable	TargetColumn, TargetFormat. The result must be an image URL. (Parameters for link: UrlColumn, UrlFormat. Link is optional)	Dropdown menu allowing you to select a specific value.	Single parameter by TargetColumn, containing the exact value, without conversion. Data type nvarchar.
ForeignKey	Plain value or clickable link, left-aligned	TargetColumn, TargetFormat (Parameters for link: UrlColumn, UrlFormat. Link is optional)	Dropdown menu allowing you to select a specific value.	Single parameter by GuidColumn, containing the exact value. Data type uniqueidentifier.
DateTimeFromTo	Plain value, right-aligned	TargetColumn, TargetFormat	Textbox allowing you to enter a date or time. If two values are entered separated by a slash, it is a from-to interval. Example1: “4.4” – April 4th from 00:00 to 23:59 (the second dot is not written for a date without a year) Example2: “1.8/12.9” – August 1st to September 12th Example3: “1.1.2000 10:30/1.6.2000 18:25” Example4: “10:30/18:00” – today from 10:30 to 18:00	Two parameters based on TargetColumn, one with the “From” suffix and the other “To”. If the second parameter is just a date, the time is set to cover the whole day (from 0:00:00 to 23:59:59.99). Data type datetime.
WeekDay	Plain value, right-aligned	TargetColumn, TargetFormat	Dropdown allowing you to select a day of the week.	A single parameter named according to TargetColumn indicating the day number in .NET convention (Sunday = 0, Saturday = 6). Data type int.
DayTime	Plain value, right-aligned	TargetColumn, TargetFormat	Dropdown allowing you to select an hour of the day.	A single parameter named according to TargetColumn indicating the hour of the day (0 to 23). Data type int.
Duration	Plain value, right-aligned. The source must be an integer column convertible to int/uint/short/ushort .NET types.	TargetColumn, TargetFormat, Converter Possible converters: DurationMSS – number of minutes (even large numbers), seconds: “4323:23” DurationHMMSS – hours minutes seconds: “76:24:23” DurationDHHMMSS – days hours minutes seconds: “3.11:24:23” Duration or empty – if the duration is up to one day, HMMSS format is used; if over one day, DHHMMSS format is used. TimeSpan – respects TargetFormat formatting for .NET type TimeSpan	Field for entering duration. If one time is entered, it is used as both the upper and lower limit; if two times separated by a slash are entered, it is a from-to interval. If one time and a slash before, it is the upper limit; if the slash is after, it is the lower limit. Example1: “2:00” – exactly two minutes Example2: “0:30/1:00” – from 30 seconds to one minute Example3: “5:00/” – over 5 minutes. Example4: “/1:00” – up to 1 minute.	Two parameters based on TargetColumn, one with the “From” suffix, the other “To”. Data type int.
Integer	Plain value, right-aligned. The source must be an integer column convertible to int/uint/short/ushort .NET types.	TargetColumn, TargetFormat	Field for entering a value. If one number is entered, it is used as both the upper and lower limit; if two numbers separated by a slash are entered, it is a from-to interval. If one number and a slash before, it is the upper limit; if the slash is after, it is the lower limit. Example1: “15” – exactly fifteen Example2: “50/120” – 50 to 120 (inclusive) Example3: “300/” – over 300. Example4: “/2” – up to two.	Two parameters based on TargetColumn, one with the From suffix and the other To. Data type int.
Bool	Checkbox. The source must be a column of type bit. Example of getting a bit value in SQL: CAST(CASE WHEN M.Eml IS NULL THEN 0 ELSE 1 END AS bit) AS HasEml	TargetColumn	Three-state checkbox (checked, unchecked, gray/indeterminate)	A single parameter based on TargetColumn. Data type bit.
Color	Color of the entire row. The source must be a column of type bit. Colors are applied according to the column order (Rank); the first color that returns a value of 1 (true) in the row is used. Example of getting a bit value in SQL: CAST(CASE WHEN M.Eml IS NULL THEN 0 ELSE 1 END AS bit) AS HasEml	TargetColumn, Color. Example color: #1255E4	None	None
Bold	Bold font for the entire row. The source must be a column of type bit. There should be only one such column in the definition. Value 1 (true) means highlight. Example of getting a bit value in SQL: CAST(CASE WHEN M.Eml IS NULL THEN 0 ELSE 1 END AS bit) AS HasEml	TargetColumn	None	None

Values available according to the selected model

• Target column format

  • Available for Text, Time, etc. models.

  • Formatting example: {0:dd.MM. HH:mm}

  • Formatting is evaluated preferably for ReactClient - lists “History” and “Related communications”. If the format is not specified, the so-called “friendly date-time” is used so that you will see something like “Today 9:51:16”.

  • FrontStage v7 and higher - this formatting is suppressed by default; the underlying data is formatted by the application on “frontend”

• URL column name - If the column model allows, this is the name of the column used to get the data for the link to the page that should be displayed when you click the item in the column.

• URL column format

  • .NET format string applied to the value of the URL column.

  • Example: ~/Pages/Message/DisplayForm.aspx?Id={0}

  • Vlnovka na začátku bude nahražena výchozí URL k aplikaci. Lze použít i absolutní URL, mířící mimo web aplikace.

• Converter - Some column types can display values stored in the database as special strings (enums). Selecting a converter allows them to be shown in a translated form. The converter then contains the table name and column name. Another use is for converting values beyond .NET formatting, e.g., displaying an integer as a duration (the name starts with “Duration”).

• Sort expression - The item should contain the names of the query columns, separated by commas (optionally with a DESC indicator). This sorting is used by the grid when the user selects this column for sorting (by clicking the column header). If empty, the column cannot be sorted. Example: Priority DESC, TimeUtc DESC.

• Sort expression DESC - Expression for sorting in reverse order. The item should contain the names of the query columns, separated by commas (optionally with a DESC indicator). This sorting is used by the grid when the user selects this column for sorting (by clicking the column header). If empty, the regular sort expression is used, but “DESC” is added at the end.

• Disable filter - Checking this will hide the filter for the column.

• Color - Default color. Used for coloring grid rows or as the series color in a chart. The color is specified in web notation #FF7733. Web utility for generating colors here.

Performance tab

When working with tables, data queries return the required information from the database. Some tables are used and refreshed more frequently, some less frequently. Some tables return data briskly; some may return it with a long delay. When the data rendering starts to slow down significantly, this page can be used to see which tables are used most frequently, which ones display the requested data with the greatest delay, and which ones show errors when displaying the data.

This page allows you to optimize your data display by identifying problematic data queries and taking required action.

After performing the necessary analyses, you can optimize your data display in several ways:

• Edit query

• Increase the interval for automatic data refreshing

• Set the portlet to display data in the grid only after selecting one of the filters

On the start page in the query list (with the default settings), the columns with performance information are as follows:

• Type – Query type

  • Common data query – Always executed against the database

  • Timeline – Executed regularly; only the first line is taken and added to the set

• (ms) – An average delay time in milliseconds before data was displayed (Duration/Count)

• Data(N) – The number of times the table data is displayed; the tooltip displays the cumulative duration

• Data(E) – error count – The value indicates the number of hard errors and timeouts in total; tooltip shows only hard errors (e.g. a syntax error in query, failed to connect to database, etc.)

Important

To view this information, you must have the DiagAdmin role with at least the AllowRead level.

Next   Previous