The Expression function allows you to create a manual field by building a calculation, based on standard database variables (Fields). You would use an expression when you want to create a value that isn’t available within the database. For E.g you may want to subtract a Cost Price from a Sales Price.  

The Sage 200 Report Designer has an expression editor which can make the building of the expression easier.  

The expressions and the expression editor can be used for ‘Advanced filters’, Sort fields, Criteria Fields, Drill Down fields and to Group Data. 

Add an Expression 

  • Select Toolbox > Expression 

  • Select the position you want the new field to go, on the report 
  • The Expression editor window opens

On the right hand side of the screen you have a Functions pane and a Fields pane.  

  • The Functions Pane contains the different functions you can use such as + (Adding), – (Subtracting) functions or operators such as < (less than) or > (greater than).  
  • The Fields Pane contains the different database Tables and Fields available to this report. The number of different functions gives an indication of how detailed the Expression can be. 
  • You can enter your expression by manually typing it. Alternatively you can Double click or drag Fields and Functions from the relevant pane.
    • To add a variable, find and select the required table and then variable within the Fields pane. Left click and drag the variable to the left-hand pane. 
    • To add a function to the expression, open the relevant folder within the Functions pane. Select the relevant function then left click and drag the function to the left-hand pane.
    • To add a numeric or text, type the required information into the left-hand pane. A text value must be surrounded by quotation marks, for example, “Abbey Retail Ltd”. A numeric value does not need to be surrounded, for example, 100.00.

To confirm the Expression, click OK

  • You are returned to the Design screen 
  • Preview the report to see the outcome

Expressions Errors

If an expression is entered in a way that does not make sense, then you will not be able to save it. The expression editor will highlight the area that has a problem, as below:

Advertisements