Calculated Fields are plugged directly into the reports SQL query. As an easy example, if you create a Calculated Field whose value is simply 'A', you will see ('A') plugged into the SELECT statement (you can see this demonstrated in screenshot below).
In terms of building your own Calculated Field's, there are two options: Simple and Freehand SQL.
In Simple Builder, Yellowfin can intelligently determine when a user is using fields from different tables. For example, if we create a report with 'SUM Invoiced Amount' (which is from the 'ATHLETEFACT' table), then create a Calculated Field and add 'Athlete Region', Yellowfin knows this is from the 'TRAVELAGENCY' table, so it automatically brings in the Join for that table:
In Freehand SQL builder, Yellowfin loses this ability to know what table this field is from - all it does is trust the user. This is what the query would look like:
And as such, the report goes from this:
to this:
Because Yellowfin now only has ‘ATHLETEFACT’ contained in its Joins, and not ‘TRAVELAGENCY’.
This can be addressed in a few ways.
You can set the field to Mandatory in the View:
You can check for the table via Access Filters; or, you can drag in another field that contains the ‘TRAVELAGENCY’ table, so that it’s added back to the Joins. For example, if we brought in the original Simple Builder Calculated Field example, we’d then see both results instead of the "user lacks privilege...' message, as:
The primary benefit of using Freehand SQL is that users can utilize database functions. Yelowfin does very little adjustment to SQL queries based on the database the query’s being sent to (one of these few exceptions is using SELECT DISTINCT TOP with SQL Server).