Difference between revisions of "Calculated Field"
From SmartWiki
| Line 1: | Line 1: | ||
| − | The following are examples of | + | The '''Calculated Field''' in '''Reporting''' allows you to specify MYSQL statements to be executed when generating the report. |
| + | |||
| + | The following are some examples of statements that can be entered into the '''Calculated Field''' of the [[Report]] builder. | ||
| Line 11: | Line 13: | ||
ifnull([10],0)+ifnull([11],0) | ifnull([10],0)+ifnull([11],0) | ||
| + | ===Dates=== | ||
| + | |||
| + | Under Calculated Field section, you can use the following format for a date field | ||
| + | |||
| + | DATE_FORMAT([this], '%W, %M %D') -- Thursday, November 5th | ||
| + | |||
| + | DATE_FORMAT([this], '%h:%i %p') -- 11:45 AM - 02:00 PM | ||
| + | |||
| + | {| border="1" | ||
| + | ! 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 | ||
| + | |} | ||
| + | ==See Also== | ||
| + | * Any [http://www.google.ca/search?hl=en&q=mysql%20calculations&meta= MYSQL Calculation] syntax can be used | ||
[[Category:Reports]] | [[Category:Reports]] | ||
Revision as of 15:06, 6 October 2010
The Calculated Field in Reporting allows you to specify MYSQL statements to be executed when generating the report.
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
Under Calculated Field section, you can use the following format for a date field
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 |
See Also
- Any MYSQL Calculation syntax can be used
