In this section, you can review the main JAQL properties to use in your queries, beginning with the mandatory and commonly used properties, and followed by optional properties that you can add to your query.
|datasource||States the connection (ElastiCube) name against which to execute the query.|
|metadata||Contains an array of JAQL elements. A JAQL element is essentially dimension or measure.|
|type||Defines the metadata element type for dimension measure.|
|dim||The dimension name.|
|level||States the date level in a Date dimension. Available levels are: years, quarters, months, days and timestamp|
The example below returns all members of the product dimension.
The example below returns all years of the date dimension.
The following aggregation method applies a simple aggregation function over a dimension.
Defines the measure aggregation over the dimension defined in the dim property. Available aggregation types are:
The example below returns a single value, which is the sum aggregation on the d1 dimension.
The following methods apply advanced arithmetic operations over dimensions and measures.
|formula||Defines the formula string. For a complete list of supported functions and formula syntax visit Sisense Documentation.|
|context||Defines the context of the dimensions used in the formula.|
The example below calculates the profit, which is defined as the sum of sales minus the sum of cost:
Measures and dimensions can be applied with filters, each with their own limitations. The filter definition must reside inside the filter attribute of the element.
|filter||Defines the element's filter.|
Below is a list of available filters with their syntactic sugars for shorter and easier queries along with their restrictions.
The members filter is an array of strings where each of them represent a member.
Filters dimensions and measures with the equal/not equal to operators.
The following operators related to strings are:
The queries below return all members that contain acer; the like keyword behaves as in SQL.
The queries below return all members that start with acer.
The queries below return all members that end with 2400.
Filters numeric/time dimensions and measures with from and/or to.
If both from and to are defined, the filter includes the range from..to. In addition, fromNotEqual and toNotEqual can be applied instead of from and to.
The Operators are:
The queries below return all members between 6 and 10.
The queries below are defined with value filtering over their measures; they'll both return all members where their measured value is greater or equal to 6.
Exclude any input filter.
The ranking functions can be attached to any dimension, and must be defined with top/bottom (number) and by (measure) attributes.
Both samples below return the top 10 members from d1 that has the best sum d2 ranking. The first sample uses a dimensional filter with an explicit measure definition, while the second uses measure filtering with dynamic dimension assignment.
This example returns rankings 6 to 10 (second 'top 5').
Combines a set of filters with and/or operators to create complex filter criteria.
The sample below returns all products that contain either toshiba or acer.
The sample below returns all customers who purchased Toshiba or Acer.
The sample below returns all customers who purchased more than a total of 1000.
It is also possible to compare the measure with another. The sample below will return all customers who ordered more of Product A than they've from Product B
Filters date-time dimensions with last X days/months/quarters/years.
The query below returns this month.
Filters date-time dimensions with next X days/months/quarters/years.
The query below returns the next month (relative to current month).
Defines the element's sorting rules: asc/desc
Defines the numeric value to select t if a measured value results in null.
Defines whether null values are removed from aggregated and/or calculated values.
Available values are: true or false.
|States the expected query result data type; CSV or JSON.|
offset and count
|Cuts the query result by setting the row offset and row count.|
|Defines the CSV separator that is used when rendering the CSV query result.|
|Defines whether the returned values are returned formatted (Date-time/ numeric) when format is defined for a JAQL element. When true, result values will always be objects with a data/text property pair.|