Designer FAQ

We cannot tell you exactly what expressions can be translated because this depends not only on your database vendor, but also on the extract version, the driver you are using and the settings you made. But we can provide you with a few guidelines and a way to find out whether your record selection formula will be converted into SQL or not.

Things to avoid in the record selection formula:

• Use of variables
• Loops
• If and switch statements - try to use IIF instead of IF where possible
• Any kind of programming - try to build one expression.

How to find out if the record selection formula was converted into SQL and passed to the database, e.g.:

Record Selection Formula: if ( {Customers.CustomerID} = “ALFKI” ) then true else false

Logging output after report execution:

[CC,INFO] Warning, could not optimize SF to run on the database.
	Reason: if then else rewrite disabled, please use IIF(a, b, c) instead.
[CC,INFO] SELECT Customers.CustomerID,Customers.City FROM dbo.Customers Customers
[CC,INFO] Data fetched. Records:91 Discarded:90

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 91 records got fetched and 90 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 ( {Customers.CustomerID} = "ALFKI",true,false)

Logging output after report execution:

[CC,INFO] SELECT Customers.CustomerID FROM dbo.Customers Customers
	WHERE ( ( Customers.CustomerID='ALFKI' AND (1=1) OR NOT Customers.CustomerID='ALFKI' AND (1=0) ) )
[CC,INFO] Data fetched. Records:1 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: ({Customers.CustomerID} = “ALFKI” would be sufficient) it will execute faster since far less data have to be transferred.

To use the i-net Designer in English on a machine with another locale (e.g. Chinese or German) you only need to add the following command line parameters: -Duser.language=en -Duser.country=us, e.g.:

java -Duser.language=en -Duser.country=us -jar Designer.jar