Report Workshop 2.1 - expressions

[Sergey Tkachenko]

New versions of TRichView and Report Workshop have been released.

While this TRichView update includes only minor fixes and tweaks, Report Workshop 2.1 has a major new feature: expressions.

Trial versions can be downloaded here: http://www.trichview.com/download/

This update allows using expressions:
- to insert expression results in documents
- in $if commands
- in visualizers (diagrams that can be displayed on background of table cells)

functions.png (42.75 KiB) Viewed 36654 times

Previous version:
https://www.trichview.com/forums/viewto ... f=6&t=9810

[Sergey Tkachenko]

Expressions

You can find detailed information about expressions in our manual: https://www.trichview.com/help-report/expressions.htm

Expressions can be calculated on integer, floating point, Boolean, string, and date-time operands.
There are no strict type-checking, operands are converted to other types when necessary.
For example, the result of

Code: Select all

"score:" + 10

is "score:10".

Operators

The following binary operators are supported:
- math: + - * /
- text: +
- logical: & |
- comparison: = (or ==) <> (or !=) < > <= >=

Note: operator precedence is not like in Delphi:
- precedence of logical operators is lower than precedence of math/text operators
- precedence of relational (comparison) operators is lower than precedence of logical operators
So:

Code: Select all

expression:
a<b | c<b+1
equivalent expression:
(a<b) | (c<(b+1))

You can use brackets to change the order of calculation.

There are the following unary operators:
- math: + -
- logical: !

+ can be used to convert value to a number, so the result of

Code: Select all

+"1.23"

is 1.23.

[Sergey Tkachenko]

Operands

The following operands are supported:
- variables (e.g. %myvariable)
- data fields (e.g. Sales, or MyTable:Sales)
- cross-tab header fields (e.g. #Field)
- numeric constants (e.g. 123, or 1.23, or 1.23e+4)
- text constants (e.g. "Hello world!")
- functions

Variables, data fields and cross-tab header fields may be enclosed in single quotes or square brackets, e.g. 'Total sales', [My field], 'Len'. It is necessary if they have space characters in names, or if they have the same names as functions (see below).
Note: while numeric constants must use the dot character as a decimal separator, strings can use both the dot and the comma characters, e.g. both "1.23" and "1,23" can be converted to 1.23.

Functions

I implemented more than 50 functions for expressions. When possible, I used SQL-like names rather than Delphi-like names.

Functions:
- basic math: Div, Mod, Round, Ceiling, Floor, Trunc, Abs, Sign
- trigonometric: Sin,Cos, Tan, Cotan, Radians, Degrees, Pi
- log and exp: Log, Log10, Ln, Exp, Power, Sqrt
- min/max: MinVal, MaxVal
- text: Upper, Lower, Trim, LTrim, RTrim, Substring, Left, Right, Repeat, Len
- date and time: Now, CurDate, CurTime, MakeDate, DateFromParts, MakeTime, GetDay, GetMonth, GetYear, GetHour, GetMinutes, GetSeconds
- logical: False, True, If
- conversion: ToNumber, ToText
- data: RecNo.

Aggregate functions (Min, Max, Sum, Count, Average, Var.P, Var.S, StdDev.P, StdDev.S) are also available. But there is a difference in syntax: they accept a text parameter, so field names must be enclosed in double quotes, e.g. Sum("Sales"). For consistency, I added a possibility to use double quotes when using aggregate functions outside of expressions as well.

As you can see, the functions that return the greater/lesser of two values are named MinVal and MaxVal. I could not name them Min and Max because these names are already used by aggregate functions.

Logical If function is especially useful in hypertext targets, hints and visualizers, because commands (including {$if}) are not available there. Example:

Code: Select all

If(Len(Name)<100, "Hello "+Name, "Welcome to our planet!")

RecNo returns an index of the currently processed record, RecNo(0) returns the index of record of the most nested data query.

Difference from similar Delphi functions:
- in RW, Round does not use bankers' rounding, it always rounds 5 up;
- in RW, in Round (unlike Delphi's RoundTo), the positive second parameter moves the rounding position to the right, i.e Round(12.345,1)=12.3, Round(12.345,-1)=10;
- in RW, the parameters of Log are (Number,Base) (instead of Delphi's LogN(Base,Number)

[Sergey Tkachenko]

Using expressions

Expressions can be used in the places listed below.

1) In conditions of $If command:

Code: Select all

{$IF Count=0}Not started yet.{$ELSE}{Count}{$ENDIF}

You can use space characters:

Code: Select all

{$IF Lower(%Reply) = "say something"}No comments{$ENDIF}

2) To insert an expression result in text. In this case, the field must start from "=" character:

Code: Select all

{=Sales/2}

If an expression includes space characters (in the expression itself, or in text strings, or in field names), the expression (including "=") must be enclosed in single quotes. In this case, single quotes in the expression itself must be doubled:

Code: Select all

{'=if(''Total sales''>0, "Hurray!", "It''s not my fault")'}

In this case, it makes sense using [] instead of single quotes in expressions (to improve readability):

Code: Select all

{'=if([Total sales]>0, "Hurray!", "It''s not my fault")'}

Like other field types, expressions can use type and format:

Code: Select all

{=Sales/2 int "roman"}

3) In visualizers (in ReportEditor, demos, menu "Report | Report Table Cell", tab "Visualization", add a background color scale or a background diagram, and enter "expression to calculate").
The syntax is like in (2), but without curly brackets:

Code: Select all

=MaxVal(Credit,Debit)

You can use type conversion (int, bool, float), but not format.

[Sergey Tkachenko]

Compatibility issue: RVReportGetErrorString function has a new parameter. ReportEditor demos were changed to update the calls to this function.

A helper panel of ReportEditor demos (that allows inserting fields and functions in reports) is improved:
1) The "functions" page allows inserting all available functions, not only aggregate functions
2) If the caret is already in a field, it does not enclose the inserted value in curly brackets.

[Sergey Tkachenko]

This update added several messages (mostly expression parsing and evaluation errors).
The only up-to-date translation is Russian. In other languages, new strings are added in English. They will be translated in future updates.

Ukrainian translation is updated (by Sergey Kovbasa), but it does not include new expression messages yet. Ukrainian translation of RichViewActions is updated as well.