- Getting Started
- Administration Guide
- User Guide
- Developer Guide
Syntax
This article describes the basic structure of analytical expressions in Wyn Enterprise.
Name Requirements
Typically, a data model is composed of multiple data tables and data columns in a database that is stored in a database server. All the tables in that database must have a unique name and also all the columns in a particular table should also have a unique name.
The name of the entities in a data model is not case-sensitive. Example: FACTONLINESALES and FactOnlineSales will represent the same table.
Calculated columns and measures added to an existing data model must also belong to a specific table. The original column/calculated column/measure name in the table must also be unique.
Add Tables and Columns Reference
When creating analytical expressions, you add a reference to the tables and columns on which you want to perform the calculations. The basic format to add a column reference in an expression is to enclose the table name within single quotes, followed by the name of the column in square brackets. For example,
'FactOnlineSales'[SalesAmount]In some cases, like, using the table manipulation function to create a new column, you have to use the "unqualified" format which is the column name in square brackets. For example, when you are referencing a scalar value from the same row of the current table, you can use the unqualified column name format [SalesAmount].
Tables
As already mentioned above, table names should be unique in a database. So whenever a column used, is from a different table than the current table, then the table name is required.
Table names must be enclosed within single quotes if they contain spaces, any non-English alphanumeric characters, or other special characters.
Calculated Columns / Measures
Names must always be in brackets.
Names can contain spaces.
Each name must be unique within an entity.
Listed below in the table are examples of some object names
Object | Object Name | Comment |
|---|---|---|
Table Name | RetailDataset | If there is no space or any special character in the table name, then the name does not need to be enclosed in single quotation marks. |
Table Name | ' Retail Dataset' | If there are spaces, tabs, or other special characters in the table name, then enclose the name in single quotation marks. |
Fully qualified column name | RetailDataset[Quantity] | The table name precedes the column name, and the column name is enclosed in square brackets. |
Unqualified column name | [Quantity] | The unqualified name is just the column name, in square brackets. Contexts where you can use the unqualified name include formulas in a calculated column within the same table, or in an aggregation function that is scanning over the same table. |
Fully qualified column in a table with spaces | 'Retail Dataset'[Quantity] | The table name contains spaces, so it must be enclosed in a single quotation mark. |
Other restrictions
The syntax applicable for each function and the kind of operations it can perform varies a lot. However, in general, rules applicable to all the functions and expressions are listed below:
Formulas and expressions in WAX cannot insert or modify individual values in the tables.
By using WAX, you can create only calculated columns and measures. Calculated rows are not created.
You can nest functions any number of times while defining calculated columns.
WAX has many functions that return a table. Normally, you use the values returned by these functions as input to other functions, which require a table as input.
Data types
In WAX, there is no need to convert, cast, or otherwise specify the data type of a column or value that you use in a formula. WAX automatically identifies the data types for the following:
Data types in the referenced columns
Data types of the values that you type in
For these data types, WAX performs implicit conversions where ever required to complete the specified operation.
Listed below are some of the limitations to the values that can be converted:
WAX will return an error in case a column or a value has a data type that is not compatible with the current operation.
In WAX there are no functions that let you explicitly convert, change, or cast the data type of existing data that you have imported into a data model.
Define Functions
Analytical expressions support a varied range of inbuilt functions. These functions can be used for performing complex calculations such as aggregation functions, date and time functions, filter functions, and so on. A function consists of a name and argument(s). The name of the function must be followed by a parenthesis in which the parameters are passed.
SUMX(ALL('FactOnlineSales'), 'FactOnlineSales'[SalesAmount])Use Parameters
Wyn Enterprise supports parameters in WAX expressions. This gives users the flexibility to change the output results based on the parameter values. When creating an expression, click Parameters to add a dashboard parameter.
Note: Only single-valued parameters can be used in WAX expressions.
To include user-related information in the WAX expressions, bind parameters to user-context or organization context values.
Specifying Parameters
Following is the list of parameters supported in WAX.
Parameter | Description |
|---|---|
Expression | Any WAX expression that returns a single (scalar) value. The expression is evaluated multiple times for each row or context. |
Value | Any WAX expression that returns a single (scalar) value. The expression is evaluated exactly once before all the other operations. |
Table | Any WAX expression that returns the data of the table. It could be a table or a table manipulation function. |
TableName | Returns the name of the table. It cannot be an expression. |
ColumnName | Returns the name of the column. It cannot be an expression. |
Name | A string constant that provides the name of the new object. |
Order | An enumeration that determines the sort order. |