Using expressions in Sage Intelligence Reporting

2 minute read time.

An expression in Sage Intelligence Reporting is a selection from the available fields of a data source, that either become columns in the Excel report or provide filtering assistance.

Expressions reside in the Container within the Connector and can be modified or created. In the Connector, when you click on a Container, you will see the list of expressions - which can be a data field or other options as listed below.

Sage Intelligence Reporting provides the means to calculate and filter data when the report is run by using these varying expressions.

Sage Intelligence Reporting's Standard Reports have existing, pre-built expressions and, when you have data you would like displayed in a report that is not currently available, you can add expressions to display that data.

You would do this using the Connector Module. If you do not have the Connector module, then your Sage Business Partner can add the expression and provide you the report with the new expression added. 

Here is a list of the available expressions:

Data Field- This is an expression that is directly linked to the database field.

SQL Expression- Returns data from a field or combination of fields based on a SQL expression; the data reflected in this expression is based on the SQL syntax entered into the expression.

Microsoft Excel Formula - This is an Excel formula that will only resolve once it opens in Excel; the data reflected in this expression is based on the Excel syntax entered into the expression.

Pass Through Variable - Pass Through Variables enable the queries (or SQL statements) that are sent to the database systems during report execution to be affected at run time.

Within the Container, each different type of expression is represented by a different icon. The simplest is the data field, however, the other expressions play very important roles.

To display the properties of an expression, select the expression in the object window. The properties of the expression will be displayed in the right properties pane.

You can see in this example that the expression is a data field by looking at the Source Type, and you can see the name of it in the Expression Source field.

The Data Field comes directly from the list of fields in the tables joined within the Container. The Excel Formula field uses Excel formulas to calculate the data fields. The SQL Expression field uses SQL to calculate the data fields.  Pass Through Variables have a few different uses explained in further detail in other documentation, but basically enable multiple sheets within a workbook to access the field as a filter or parameter.