Calculated Field
The Calculated Field in the Report Builder allows you to specify MySQL statements to be executed when generating the report.
The field itself is a multiple line text area field, which means users can stretch out the box so that all text contained within the field can be immediately exposed. This is of assistance when there are long, complicated calculations against a column.
The following are some examples of statements that can be entered into the Calculated Field of the Report builder.
Sums
Add the values in column 10 and 11:
[10]+[11]
- If the value in either column 10 or 11 is blank(null), using the above statement will result in incorrect calculations.
Sum column 10 and 11, using zero as the value if the either field is blank:
ifnull([10],0)+ifnull([11],0)
Dates
Contents
Report Builder Date Format Option
Under the Calculated Field section of the Report Builder, you can use the following format for a date field:
| Syntax | Output |
| DATE_FORMAT([this], '%W, %M %D') | Thursday, November 5th |
| DATE_FORMAT([this], '%h:%i %p') | 11:45 AM, 02:00 PM |
| Format | Description |
|---|---|
| %a | Abbreviated weekday name |
| %b | Abbreviated month name |
| %c | Month, numeric |
| %D | Day of month with English suffix |
| %d | Day of month, numeric (01-31) |
| %e | Day of month, numeric (1-31) |
| %H | Hour (00-23) |
| %h | Hour (01-12) |
| %I | Hour (01-12) |
| %i | Minutes, numeric (00-59) |
| %k | Hour (0-23) |
| %l | Hour (1-12) |
| %M | Month name |
| %m | Month, numeric (00-12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss AM or PM) |
| %S | Seconds (00-59) |
| %s | Seconds (00-59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %W | Weekday name |
| %Y | Year, four digits |
| %y | Year, two digits |
Conditional Statements
To show different results depending on the value you can use 2 methods:
If
Syntax
- if(conditional statement,value if true,value if false)
Examples
- If the field value is Pending Approval' display the word Submitted, otherwise display the field value:
if([this]='Pending Approval','Submitted',[this])
- If the field value (statusid in this example) is one of 6 different statusids, display the word Submitted', otherwise display the field value:
if([11] in (9681,10134,10028,9685,9683,9901),'Submitted',[this])
Case
Syntax
- CASE value WHEN value 1 THEN result 1 WHEN value 2 THEN result 2 END
Example
CASE [this] WHEN 'Status 1' THEN 'Submission Cutoff is March 5th.' WHEN 'Status 2' THEN 'Thank for your submission' WHEN 'Status 3' THEN 'Congratulations' ELSE 'I have no comment' END
See Also
- Any MYSQL Calculation syntax can be used
- How do I do a count using a sum?
- Disambiguation: Calculated Value Field