Switch to: V10V9V8V7V6V5

Report Editor - Expression Help

The control “Expression” is designed to output a value defined by an expression. The expression may consist of data (from fields), functions, macros and operators. The output of the control will be a text field, containing the result of the expression evaluation.

It is possible to write the following types of expressions:

  • JavaScript – executed by JavaScript engine embedded into VReports library
  • SQL – executed by the SQL engine
  • Text With Macros – they work like templates for the generated strings where each macro is replaced with the value of a field, SQL expression, parameter. (Deprecated since 9.6)

Prior to version 9.6, there were two types of expressions: simple expressions (started with '=' character) and text expressions with macros ($F, $P). Since version 9.6 they are deprecated and will be removed in the future versions. These expressions continue to work and can be used for compatibility with older versions of ADKs and Valentina Server installations, but for new projects, it is recommended to replace simple expressions with SQL expressions, text expressions with macros – with JavaScript expressions

JavaScript Expressions

JavaScript expressions are more powerful and flexible, allowing the user to use JavaScript functions (built-in or user-defined), properties and methods of the report object, data from fields, parameters, etc.

Any valid JavaScript expression can be used here, the same Valentina Report scripts JavaScript API is accessible for it.

A set of global functions can be used in expressions:

  • $E( 'sql_expression' ) | Executes sql_expression using the SQL engine and returns its result.
  • $F( 'field_name' ) – Returns the value of the specifield field_name field for the current record.
  • $P( 'param_name' ) – Returns the value of the specified param_name parameter.

If expression requires even more complex logic it can be defined using the anonymous function along with its call:

(function( num )
{
  switch( num )
  {
    case 1: return 'one';
    case 2: return 'two';
    default: return '...';
  }
})( $F( 'field_name' ) )

In a case, this function will be used in different expressions, it can be added as a method of the report object in pre_build script.

A useful feature of the JavaScript language – template literals, also known as “template strings”, they are string literals allowing embedded expressions. You can use multi-line strings and string interpolation features with them. The syntax is:

`string text`
 
`string text line 1
 string text line 2`
 
`string text ${expression} string text`

You can use the template literals in your JavaScript expressions in VReport as it allows avoiding writing extra concatenations with other expressions or text.

EXAMPLES

JavaScript ExpressionOutput
$F('f1')+$F('f2') 8
Math.pow( $F('f1') ) 27
(new Date).getDay() 5
'Value of field f1 is: ' + $F('f1') Value of field f1 is: 3
`Value of field f2 is: ${ $F('f2') }` Value of field f2 is: 5
$F('f1') + ' + ' + $F('f2') + ' = ' + ( $F('f1') + $F('f2') ) )3 + 5 = 7
`${ $F('f1') } + ${ $F('f2') } = ${ $F('f1') + $F('f2') }` 3 + 5 = 7
$P('pTime') + ' = ' + $F('value') + ' ' + $P('units') x = 5 ms
`${ $P('pTime') } = ${ $F('value') } ${ $P('units') }` x = 5 ms
Math.PI * Math.pow( $F('f2'), 2 ) 78.539816
`Report was created at ${ report.generationHour } hours and ${ report.generationMinute } minutes.` Report was created at 16 hours and 22 minutes

Pay attention, that such controls as the Date, Date & Time, Page Number, Record Number, Time, Total Pages, Total Records are based on the “Expression Field” and performs specialized function.

Setting the Expression

You can set the expression for the control in a special dialog by making double click on the control and select JavaScript in the expression language select list at the top of the dialog, if it is not selected.

Report Editor - JavaScript Expression Dialog

This dialog will help you to add values to expression, such as:

  • Fields – the value from the field of source cursor of the report.
  • Parameters – the value of parameter defined for report.
  • Templates – frequently used JavaScript code templates, such as returning the page number, total pages, etc.

SQL Expressions

Any valid SQL expression can be defined here. Additionally, you can use $P macro, which embeds a value of specified parameter into resulting query in the following format:

$P( param[=DEFAULT] )

NOTE: The default value of parameter macro is optional, but is useful during designing and previewing a report. Actual values can be set later on switch to preview mode or in application code using SetParameterValue() method from appropriate Reports ADK.

EXAMPLES

SQL ExpressionOutput
f1+f2 8
pow( f1, 3 ) 27.000000
dayname( now() ) Friday
pi() * pow( f2, 2 ) 78.539816
f1+$P( param1=2 ) 8

SQL expressions are useful if it is necessary to execute some function available (built-in or user-defined) in the SQL engine.

Setting the Expression

You can set the expression for the control in a special dialog by making double click on the control and select SQL in the expression language select list at the top of the dialog, by default, it is set to JavaScript for a new expression.

Report Editor - SQL Expression Dialog

This dialog will help you to add values to expression, such as:

  • Fields – the value from the field of source cursor of the report.
  • Parameters – the value of parameter defined for report.
  • SQL Functions – the value of the function available in the SQL engine.

Text Expressions With Macros

(Deprecated since 9.6)

Expressions with macros are different, they work like templates for the generated strings.

There are four forms of macros:

  • $F( field ) embeds a value of specified field into resulting string;
  • $E( expr ) embeds a result of subexpression calculation;
  • $P( param[=default] ) embeds a value of specified parameter into resulting string;
  • $( var ) embeds a value of specified variable, such as date, time, page number, etc.

NOTE: The default value of parameter macro is optional, but is useful during designing and previewing a report. Actual values can be set later on switch to preview mode or in application code using SetParameterValue() method from appropriate Reports ADK.

Use of macros allows to avoid writing extra concatenations with other expressions or text.

EXAMPLES

ExpressionOutput
Value of field f1 is: $F(f1) Value of field f1 is: 3
Value of field f2 is: $F(f2) Value of field f2 is: 5
$F( f1 ) + $F( f2 ) = $E( f1 + f2 ) 3 + 5 = 7
$P( pTime ) = $F( value ) $P( units=ms ) x = 5 ms
=f1+f2 8
=pow( f1, 3 ) 27.000000
=dayname( now() ) Friday
=pi() * pow( f2, 2 ) 78.539816
Report was created at $(hour) hours and $(minute) minutes.Report was created at 16 hours and 22 minute

Setting the Expression

You can set the expression for the control in a special dialog by making double click on the control and select Text With Macros in the expression language select list at the top of the dialog, if it is not selected.

Report Editor - Text With Macros Expression Dialog

This dialog will help you to add values to expression, such as:

  • Fields – the value from the field of source cursor of the report.
  • Parameters – the value of parameter defined for report.
  • Macros – the list of macros for a page number, total pages, etc.

Formatting the Output Text

The control “Expression Field” is a form of text control, so it is amenable formatting. You can change the font, the color or the background color of the control using Properties Inspector.

Pay attention to the ability to change format of the data for some data types.