Advanced report design

Expression evaluator

The expression evaluator is a powerful tool included in Report Manager engine. It allows to evaluate complex expressions accessing dataset fields, convert field types, interactuate with the report to know the available free space in the current page, the current data group...

The Report Manager expression evaluator is like a scripting language but without iteration operators.

There are lot of report component properties that are expressions: print condition, skip page condition,...

To design advanced reports you should understand how the evaluator interactuates with the engine:

Evaluator syntax

The evaluator syntax is similar to pascal syntax, with a few exceptions:

Suported data types

Data type Description Samples
Integer Integer types, up to 64 bits of precision 123 2345 -24345 0
Double Double precision types 12.34 -12.34345 -1.3e-10
Currency Currency types, with a low range but without lossing precision (four decimals allowed) 12.4534
String String types (ansi), place two ' to represent one ' 'Sample' 'It''s a sample' 'A sample with a line feed at end'+#10
WideString String types of multiple bytes, usually from fields, but also in expressions, the expression evaluator can work with WideStrings, but literals enclosed with ' sign can not contain WideStrig values, if you need a widestring literal use the format: 'this is the infinite widechar'+#8734 ADATA.AWIDESTRINGFIELD
Boolean Boolean data type False True (A AND B)
TDatetime Date and time datatype, this is a double precision number where each unit is a day, so 1/24 is an hour, you can operate as a double so you can sum days, hours... Now-1/24 Date+30
Null Null value no value assigned NULL
Binary Althoug there is no operator suport to binary values, you can evaluate fields to pass content to report components, for example images or large texts ADATASET.ABINARYFIELD

 

Evaluator operators

This table is ordered by operator precedence

Operator Type Description
( ) Parenthesis Expressions enclosed by parenthesis are always evaluated first. Parenethesis are also used to pass paraeters to functions
IIF(condition,expr1,expr2) Conditional This is a special operator, if condition evaluates to true, the evaluates expr1 else evaluates expr2
- + NOT Unary When this symbols operate to only one operator (NOT always does this)
* / Arithmetic Multiply and divide operators
+ - Arithmetic Sum and Dif operators
>= <= > < Comparison Greater or equal, minor or equal, greater and minor.
<> >< Comparison The two operators have the same meaning: not equal
= == Comparison The two operators have the same meaning: equal
; Separator Sentence or expression separator, the result will be the last evaluated expression
AND Logical The AND operator works with two boolean expressions
OR Logical The OR operator works with two boolean expressions
:= Assignment This is the operator to assign values to variables, always place the variable in the left side

Evaluator standard functions and constants

Function or constant Description
True Boolean constant
False Boolean constant
Today Returns the current date
Time Returns the current time
Now Returns the current date and time
Null Null value constant
function EvalText (expr:string):variant Evaluates a expression, you can have expressions inside database fields and evaluate them using this useful function
function Uppercase (s:string) :string Returns the string in upper case
function Lowercase (s:string) :string Returns the string in lower case
function FloatToDateTime (n:Double):TDateTime Converts a double value to a TDatetime value
function Sin (ang:Double):double Returns the sinus of the angle in radians
function Round (num:double,r:double):double Round the num number to r scale, so you can round to 5 cents assigning 0.05 to r parameter
function Int (num:double):integer Converts a double to an integer unsing the standard Int function, that is the smaller more closer integer value
function Str (num:variant):string Converts the parameter to string, the parameter can be any value that can be represented by a string (numbers, dates, times...)
function Val (s:string):double Converts a string to a number
function Trim (s:string):string Removes leading and trailing spaces from the string
function Left (s:string; count:integer):string Returns first count characters of the string,
function Right (s:string; count:integer) :string Retutns the right part of a string
function Substr(cadena:string; index:integer; count:integer) :string Returns a substring begining at index and copying count characters
function Pos (substr:string,str:string):integer Search a substrin inside a string and returns the index wher is found or 0 if not found
function Sqrt (num:double):double Returns the square of the number
function HourMinSec(h:Double; idenH:string; idenM:string; idenS:string) :string Returns a formated string in format hh:mm:ss but separators are get from identifiers. Use the most flexible function FormatStr instead
function Mod (d1:integer,d2:integer) :integer Returns the moduleof the integer division
function Monthname (d:datetime) :string Returns the monthname of a date, you can use FormatStr also
function Month (d:datetime) :integer Returns the month number of a given date
function Year (d:datetime) :integer Returns the year of a given date
function Day (d:datetime) :integer Returns the day of a given date
function Formatstr (format:string,v:variant):string Powerful formating function, accepts all data types, and formats acording to the data type, for example for a DateTime value you can use the format string: 'dd/mm/yyyy hh:nn:ss', for a number: '###,###0.00', where the , says the use of thousand separator, the 0 are required format positions and # are not required format positions, this function is used in display format property for expressions
FormatNum(mask:String; number:double)

Formats a number with a mask, this function is used in display format property for expressions. The mask is similar to the one used in FormatStr but allow the use of repeatable thousand separator amd other options:

A hidden decimal separator can be specified (indicates the position for the separation of integer and decimal part) as :, also you can include this pairs of chars indicating:

LX Where X is the character used to fill needed numers on the left, the default is 0

TX Where X is the character used as thousand separator.

Some samples:

Mask Sample Result Description
##,##,##,##,##0.00 123123456.789 1,23,23,456.79 Indian formatting
T'000,000,000,000.00 123123456.789 000123'123'456.79 Changing thousand separator, see thousand separator is not used when padding length
LI000000000000.00 1231234.789 IIIII1231234.79 Left padding with a character (usually a blank space)


function NumToText (n:double,f:boolean):string Returns the natural language representation of a number, f says if female words required (' una peseta'-'un euro'), for example for 1200 the result will be 'one thousand and two hundred'. The language is selected from the active report language. The implementation for this function is: English, Spanish, Catalan.
function ReplaceStr(S,OldPattern, NewPattern:Sring):String Search OldPattern inside S replacing it if found with NewPattern
function FileExists (s:String):boolean Return true if the file exists
function ChtToChs (expr:string):String Translates traditional Chinesse to simplified chinesse (available only on Win32)
function ChtToChs (expr:string):String Translates simplified chinesse to traditional chinesse (available only on Win32)
function IsInteger (avalue:string):boolean Returns true if the string is a valid integer
function IsNumeric (avalue:string):boolean Returns true if the string is a valid number
function IsValidDateTime(avalue:string):boolean Returns true if the string is a valid date/time
function CheckExpression(expression,message):boolean If the expression is false raises an exception with the provided message
function StringToBin:binary Convers a string to a binary type. An expression returning a binary type can be used in the image expression for example.
function Decode64(value:string):string Decodes a base64 string to the original string representation, you can convert the result to binary if the original string represens an image for example.

Evaluator functions and variables available while executing a report

Function or constant Description
function GraphicClear (Gr:string):Boolean Clears the values of a Chart report component, you can place this expression in beforeprint property of a section for example. The parameter is the identifier property of the Chart component
function GraphicNew (Gr:string; V:Single; C:Boolean; Etiq:string; Caption:string) :Boolean Inserts a new value in the Chart, the first parameter is the identifier of the Chart component. V is the value to insert, C says if there is a serie change, Etiq is the text of the value and Caption is the text of the serie
function GraphicBounds (Gr:string; autol,autoh:boolean; low,high:double;log:boolean; logbase:double; inverted:boolean):Boolean;
 Establish upper and lower bound for a chart, the axis can be inverted and also a logaritthmic scale can be used. Set autol (automatic lower bound) to false and set a value to the low parameter, idem for autoh (automatic higher bound)
function GraphicSerieColor(Gr:string, Color:Integer):Boolean;
 Set the current chart serie color
function GraphicColor(Gr:string, Color:Integer):Boolean;
 Set the color for the last value added to the chart
Identifer Any identifier property assigned to a report component usually (TRpExpressions and TRpCharts) will create a variable in the expression evaluator, so you can calculate totals and then use the calculated value in other report components
Page Returns the physical page number
PageNum Returns the logical page number, you can alter this variable or initialize it setting Init.PageNum property in a group section
Free_space , Free_space_inch, Free_space_cms Returns the available space in the page, in twips (1440 twips = 1 inches), inches or centimeters
function Eof (alias:string):Boolean Returns true if the alias is at the end of the recordset
Parameters All the report parameters are available in the expression evaluator
Draw functions See draw functions page for details
PAGECOUNT

This is a special expression, can not be combined or included inside another expression, when two pass report option is enabled, the engine will sustitute any PAGECOUNT expression with the integer page count value of the report, but you can still use the display format property to customize the final output. PAGECOUNT can not be used inside expressions because:

  • Before the report processing is ended, the engine does not know total number of pages.
  • ANY expression can change section sizes or any other components conditioning the length (in pages) of a report.
FREE_SPACE_TWIPS, FREE_SPACE_INCH, FREE_SPACE_CMS Returns the available free space (vertical) in current page, in the selected unit.
CURRENTGROUP Returns the actual processing group, it returns 0 if the engine is printing details (so the next data section will be a detail), it returns a negative number if the engine is printing group headers (the number indicates what group header is processing) and a positive number when processing group footers.
FIRSTSECTION Returns true if evaluated inside the first non repeatable data section printed in the current page, that is if a page header or a group header with page repeat is printed they don't count for the first section printed.

PAGEHEIGHT, PAGEWIDTH

 Returns the page witdh and page height in twips ((1440 twips = 1 inches)
function GetValueFromSQL (connectionname:String; sql:String):Variant
 This function uses the sql parameter to execute, and open a sql query using a connection name, it will return the first column value of the first row, or NULL if the opened dataset is empty.
M.LANGUAGE Allow the change of the report language at runtime
PARAMINFO(paramname:string; index:integer)

Allow obtaining information about a report parameter, useful for multiple selection or list values

Index Meaning
0 Value as string
1 Selected values on multiple selection
2 Selected indexes on multiple selection
3 Selected items on multiple selection
4 Full list of items
5 Full list of values
6 Datasets linked to the parameter
7 Parameter description
8 Parameter hint
9 Parameter error message
10 Parameter validation expression

Building expressions

When you are editing a report, in the expression properties appears a button, clicking this button a expresion builder helper is shown.

There you can find information about available functions, variables and database fields, and you can syntax check expressions.

Using the expression evaluator in Delphi/Kylix/Builder

The report evaluator is included in components package. You can use it to evaluate expressions, functions, accessing to database fields etc..

All you need is to drop a TRpEvaluator component, the use is very simple:

procedure TForm1.Button1Click(Sender: TObject);
begin
RpEvaluator1.Expression:='2+2';
RpEvaluator1.Evaluate;
ShowMessage(String(RpEvaluator1.EvalResult));
end;

If you need access to dataset fields drop a TRpAlias, edit the list to add a alias and assign a dataset to the alias, then assign the TRpAlias to the RpAlias property of TRpEvaluator, now you can include dataset fields in expressions as ALIAS.FIELDNAME.