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)
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 Expression | Output |
---|---|
$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.
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 Expression | Output |
---|---|
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.
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
Expression | Output |
---|---|
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.
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.