With the record selection formula it is possible to reduce the number of records, received from database server. The formula will be translated into SQL syntax and inserted into the Where clause, to run the selection on server side. The return type of the formula must be Boolean.

i-net Clear Reports tries to translate the formula into SQL syntax. That succeeds in nearly all cases, but not in every case. The translation to SQL depends on the database vendor and of the used functions in the formula. If the resulting SQL statement is runnable on database is also dependent on the used JDBC driver. If the translation failed or the statement was not unable on database, the request will be started again, but now without the Where clause. In this case, i-net Clear Reports filters out the records by itself.

We can provide you with a few guidelines and a way to find out whether your record selection formula will be converted into SQL syntax or not.

Things to avoid in the Record Selection Formula:

• Use of variables
• Loops
• switch statements - try to use IIF-function or IF-control structure where possible
• Any kind of programming - try to build one expression.
• convert functions with format pattern

To find out if the record selection formula was converted into SQL and passed to the database, you have to make a look into the log file.

The following abstract sample will demonstrate how to do this:

select {Order_Details.Quantity} \  case 5 : true\  case 8 : true\  default: false

[CC,INFO] Warning, could not optimize SF to run on the database. Reason: Not Excecutable on Database
[CC,INFO] SELECT Orders.OrderID,Order_Details.Quantity FROM dbo."Order Details" Order_Details
          FULL OUTER JOIN dbo.Orders Orders ON Order_Details.OrderID=Orders.OrderID
[CC,INFO] Data fetched. Records:2155 Discarded:2038

Obviously you will get a warning in the log. Instead of converting the record selection formula into an SQL expression (where clause) that is executable on the database i-net Clear Reports fetches all rows from the database and does the filtering itself. So 2155 records got fetched and 2038 of these got discarded right away. The better solution is to only fetch this one remaining record, so we better change the record selection formula to:

iif({Order_Details.Quantity}=5 or {Order_Details.Quantity}=8, true,false)

[CC,INFO] SELECT Orders.OrderID FROM dbo."Order Details" Order_Details FULL OUTER JOIN dbo.Orders Orders ON
Order_Details.OrderID=Orders.OrderID WHERE ((((Order_Details.Quantity = 5) or (Order_Details.Quantity
= 8)) AND (1=1)) OR ( NOT ((Order_Details.Quantity = 5) or (Order_Details.Quantity = 8)) AND (1=0)))\
[CC,INFO] Data fetched. Records:117 Discarded:0

As this record selection formula was executed on the database there was no need to discard any of the fetched records. Even the record selection formula is not optimal:

({Order_Details.Quantity}=5 or {Order_Details.Quantity}=8

would be sufficient) it will execute faster since far less data have to be transferred.

Parameter fields can be used in the record selection formula to filter the records dynamically depending on user input or selection. If a parameter field is used in the record selection filter and if the parameter field is left blank then often, all records should be returned. This is just like as if the parameter field did not exist in the record selection formula. You can achieve this with the following formula:

{Database Field} = {?Parameter Field} or IsNull({?Parameter Field})

• If the record selection formula is not executable on database side, you have a higher risk to get an out of memory error, if the fetched data is very large.
• i-net Clear Reports also tries to optimize the statement as good as possible. If an used function in the formula can be directly evaluated, the function will be replaced by its result.

• all Summary functions (aggregate functions)
• all financial functions
• all date ranges
• all array functions
• all print state functions
• all additional functions except DatetimeToTime and DatetimeToDate
• String functions: Join, Filter, Split, NumericText, StrReverse, ToWords, ToText(with more than one parameter), Replace(with more than three parameter)
• Date/Time functions: DateSerial, IsDate, IsTime, IsDatetime, TimeSerial, DateDiff(with firstDayOfWeek parameter), DatePart(with firstDayOfWeek parameter), Monthname(with abbreviate parameter)
• Programming shortcuts: Switch, Choose