Row Limit Step Size

Maximum number of rows returned on a query. Set to 0 to return all rows. Set greater than 0 to enable Incremental Loading for Advanced Reports and ExpressView. The value determines how many rows are returned for each user-initiated data query.

This setting does not have an affect for ExpressView unless Admin Console > General > Feature/UI Settings > ExpressView Settings > Fields Enabled in Data Fields Tree is set to Direct Joins Only.

Aggregate and Group in Database

If True, aggregate and grouping calculations will be done in the database when possible. This will provide a performance boost for reports with group sections.

Before enabling this, ensure that all One-To-Many Joins in the configuration are correctly identified and set as One-To-Many in theJoin Configuration. If these joins are not properly identified, reports which utilize them will return incorrect aggregate data. See Database Aggregation for more information.

If True, formula filters and sorts will be converted to SQL when possible. The converted SQL statements will be placed in the WHERE (for formula filters) and ORDER BY (for formula sorts) clauses when querying data for the report. If set to False, all formula filtering and sorting will occur in-memory.

For more information, please see the Converting Formula Sorts to SQL section below.

Due to innate difference between Exago’s formula engine and SQL database engines, there may be discrepancies in data returned between two methods. Additionally, because the converted SQL formulas may contain arbitrary input, the connection string must be read-only access.

The function translations below use a JSON object instead of an array of strings. This feature requires v2021.2+.

And

The and translation has no additional properties. It emits the same SQL regardless of database in use.

Example

"and": {  }                                         

Or

The or translation has no additional properties. It emits the same SQL regardless of database in use.

Example

"or" : {  }                       

Switch

The switch translation has no additional properties.

Example

"switch" : {  }           

DateAdd

The DateAdd translation may implement 1 or more of the following properties:

• year — the SQL to add years to the date
• month — the SQL to add months to the date
• day — the SQL to add days to the date
• week — the SQL to add weeks to the date
• hour — the SQL to add hours to the date
• minute — the SQL to add minutes to the date
• second — the SQL to add seconds to the date
• quarter — the SQL to add quarters to the date
• default — the SQL when a non-constant is passed for the interval argument of the function

Example

"dateadd": {
    "year": "DATE_ADD({2}, INTERVAL {1} YEAR)",
    "month": "DATE_ADD({2}, INTERVAL {1} MONTH)",
    "day": "DATE_ADD({2}, INTERVAL {1} DAY)",
    "week": "DATE_ADD({2}, INTERVAL ({1}*7) DAY)",
    "hour": "DATE_ADD({2}, INTERVAL {1} HOUR)",
    "minute": "DATE_ADD({2}, INTERVAL {1} MINUTE)",
    "second": "DATE_ADD({2}, INTERVAL {1} SECOND)",
    "quarter": "DATE_ADD({2}, INTERVAL ({1} * 3) MONTH)",
    "default": "CASE {0} WHEN 'yyyy' THEN DATE_ADD({2}, INTERVAL {1} YEAR) WHEN 'm' THEN DATE_ADD({2}, INTERVAL {1} MONTH) WHEN 'y' THEN DATE_ADD({2}, INTERVAL {1} DAY) WHEN 'd' THEN DATE_ADD({2}, INTERVAL {1} DAY) WHEN 'w' THEN DATE_ADD({2}, INTERVAL ({1}*7) DAY) WHEN 'h' THEN DATE_ADD({2}, INTERVAL {1} HOUR) WHEN 'n' THEN DATE_ADD({2}, INTERVAL {1} MINUTE) WHEN 's' THEN DATE_ADD({2}, INTERVAL {1} SECOND) WHEN 'ww' THEN DATE_ADD({2}, INTERVAL ({1}*7) DAY) WHEN 'q' THEN DATE_ADD({2}, INTERVAL ({1} * 3) MONTH) END"
}

DateDiff

The DateDiff translation may implement 1 or more of the following properties:

• year — the SQL to calculate the difference in years
• month — the SQL to calculate the difference in months
• day — the SQL to calculate the difference in days
• week — the SQL to calculate the difference in weeks
• hour — the SQL to calculate the difference in hours
• minute — the SQL to calculate the difference in minutes<.li>
• second — the SQL to calculate the difference in seconds
• quarter — the SQL to calculate the difference in quarters
• default — the SQL when a non-constant is passed for the interval argument of the function

Example

"datediff": {
    "year": "TIMESTAMPDIFF(YEAR, CAST({1} AS DATE), CAST({2} AS DATE))",
    "month": "TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE))",
    "day": "TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))",
    "week": "FLOOR(TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))/7)",
    "hour": "TIMESTAMPDIFF(HOUR, {1}, {2})",
    "minute": "TIMESTAMPDIFF(MINUTE, {1}, {2})",
    "second": "TIMESTAMPDIFF(SECOND, {1}, {2})",
    "quarter": "FLOOR(TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE))/3)",
    "default": "CASE {0} WHEN 'yyyy' THEN TIMESTAMPDIFF(YEAR, CAST({1} AS DATE), CAST({2} AS DATE)) WHEN 'm' THEN TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE)) WHEN 'd' THEN TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE)) WHEN 'w' THEN FLOOR(TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))/7) WHEN 'h' THEN TIMESTAMPDIFF(HOUR, {1}, {2}) WHEN 'n' THEN TIMESTAMPDIFF(MINUTE, {1}, {2}) WHEN 's' THEN TIMESTAMPDIFF(SECOND, {1}, {2}) WHEN 'ww' THEN FLOOR(TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))/7) WHEN 'q' THEN FLOOR(TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE))/3) END"
}

Concatenate

The Concatenate translation implements these properties

• mode — determines if the translation will use a function or an operator. Provide either one of these values:
  • infix-operator — use an operator (for example a || b || c) for concatenation
  • function-variable-args to use a function (for example CONCAT(a, b, c)) for concatenation. When using function-variable-args, specify the name of the function with the functionname property.
• functionname — the name of the database function to use for concatenation when mode is function-variable-args.

Example

"concatenate": {
	"mode": "function-variable-args",
	"functionname": "CONCAT"
},

For example, in v2021.2.0+, the entire Formula Dictionary for MySQL is:

"FormulaDictionary" : {
	"and": { },
	"if": [ "(CASE WHEN {0} THEN {1} ELSE {2} END)" ],
	"switch": { },
	"or": { },
	"len": [ "CHAR_LENGTH({0})" ],
	"null": [ "NULL" ],
	"dbnull": [ "NULL" ],
	"date": [ "STR_TO_DATE('{0}/{1}/{2}', '%Y/%m/%d')" ],
	"dateadd": {
		"year": "DATE_ADD({2}, INTERVAL {1} YEAR)",
		"month": "DATE_ADD({2}, INTERVAL {1} MONTH)",
		"day": "DATE_ADD({2}, INTERVAL {1} DAY)",
		"week": "DATE_ADD({2}, INTERVAL ({1}*7) DAY)",
		"hour": "DATE_ADD({2}, INTERVAL {1} HOUR)",
		"minute": "DATE_ADD({2}, INTERVAL {1} MINUTE)",
		"second": "DATE_ADD({2}, INTERVAL {1} SECOND)",
		"quarter": "DATE_ADD({2}, INTERVAL ({1} * 3) MONTH)",
		"default": "CASE {0} WHEN 'yyyy' THEN DATE_ADD({2}, INTERVAL {1} YEAR) WHEN 'm' THEN DATE_ADD({2}, INTERVAL {1} MONTH) WHEN 'y' THEN DATE_ADD({2}, INTERVAL {1} DAY) WHEN 'd' THEN DATE_ADD({2}, INTERVAL {1} DAY) WHEN 'w' THEN DATE_ADD({2}, INTERVAL ({1}*7) DAY) WHEN 'h' THEN DATE_ADD({2}, INTERVAL {1} HOUR) WHEN 'n' THEN DATE_ADD({2}, INTERVAL {1} MINUTE) WHEN 's' THEN DATE_ADD({2}, INTERVAL {1} SECOND) WHEN 'ww' THEN DATE_ADD({2}, INTERVAL ({1}*7) DAY) WHEN 'q' THEN DATE_ADD({2}, INTERVAL ({1} * 3) MONTH) END"
	},
	"datediff": {
		"year": "TIMESTAMPDIFF(YEAR, CAST({1} AS DATE), CAST({2} AS DATE))",
		"month": "TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE))",
		"day": "TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))",
		"week": "FLOOR(TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))/7)",
		"hour": "TIMESTAMPDIFF(HOUR, {1}, {2})",
		"minute": "TIMESTAMPDIFF(MINUTE, {1}, {2})",
		"second": "TIMESTAMPDIFF(SECOND, {1}, {2})",
		"quarter": "FLOOR(TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE))/3)",
		"default": "CASE {0} WHEN 'yyyy' THEN TIMESTAMPDIFF(YEAR, CAST({1} AS DATE), CAST({2} AS DATE)) WHEN 'm' THEN TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE)) WHEN 'd' THEN TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE)) WHEN 'w' THEN FLOOR(TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))/7) WHEN 'h' THEN TIMESTAMPDIFF(HOUR, {1}, {2}) WHEN 'n' THEN TIMESTAMPDIFF(MINUTE, {1}, {2}) WHEN 's' THEN TIMESTAMPDIFF(SECOND, {1}, {2}) WHEN 'ww' THEN FLOOR(TIMESTAMPDIFF(DAY, CAST({1} AS DATE), CAST({2} AS DATE))/7) WHEN 'q' THEN FLOOR(TIMESTAMPDIFF(MONTH, CAST({1} AS DATE), CAST({2} AS DATE))/3) END"
	},
	"day": [ "DAY({0})" ],
	"dayofyear": [ "DAYOFYEAR({0})" ],
}