Adding a custom field allows you to calculate a new data field from existing data without affecting the original data. So, adding custom fields using SQL expressions makes the dataset design more efficient. SQL expression editor uses the primitive column to create the SQL expression on the related data source SQL. Primitive fields come from the data source.

To Add a Sql Expression Field

Consider a dataset, that contains information related to the CustomerName, SaleAmount, and SaleDateTime. We can use this dataset to get the data where the SaleAmount is greater than a particular amount using the below analytical expression.

SaleAmount>300

Follow the below steps to add a sql expression field with the above expression

1. On the Fields tab, click the More option corresponding to the field and select the Add Sql Expression Field option from the list.

   
   

   
   

   Note: On the Fields tab, you will see the More option in the Detail mode.

   Sql Expression Field dialog box is displayed.

   
   

2. In the Sql Expression Field dialog box, enter the name and expression for the new field.

   
   

   
   

3. Click the Save button or Save and Create Another button as required.

   
   

   The new sql expression field now appears under the Field.

   
   

   
   

   
   

   If you preview the dataset, then the Sql Expression Field will be visible and where the condition mentioned in the sql expression( SaleAmount>300) is satisfied, it will display True, else it will display as False.

   
   

   
   

   Note: In expressions, you can use the @ symbol to refer to query parameters in the dataset. If you need to use a query parameter that has not been defined before, write it in an expression, such as "@P1", and then click the "Parameters" button to add the parameters.

   So now the dataset supports storing the WAX expression and you can use this in the dashboard designer.

   Note: You can create WAX expression in Cached Dataset Designer only and not in other types of dataset designer.