|This feature uses MySQL syntax|
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.
Add the values in column 10 and 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:
Report Builder Date Format Option
Under the Calculated Field section of the Report Builder, 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 %M %d, %Y will return date in format of September 30, 2012 %a will return the truncated day of the week, e.g. Sun. for Sunday %b will return the truncated name of the month, e.g. Sep for September %c will return the month number in short format, e.g. 9 for September %d will return the numeric day of month (01-31) %e will return the numeric day of month (1-31) %h will return hour (01-12) %i will return minutes (00-59) %j will return day of year (001-366) %k will return hour (0-23) %l will return hour (1-12) %m will return the month number, e.g. 09 for September %p will return AM/PM information, e.g. AM %r will return the full time in 12-hour format, e.g. 12:00:00 AM %s will return seconds (00-59) %u will return the week number (00-53) where Monday is the first day of the week %w day of the week (0=Sunday...6=Saturday) %y will return the truncated year, e.g. 12 for 2012 %D will return the month ordinal, e.g. 30th %H will return hour (00-23) %M will return the month name, e.g. September %T will return the full time in 24-hour format, e.g. 23:00:00 %U will return the week number (00-53) where Sunday is the first day of the week %W will return the day of the week, e.g. Sunday %Y will return the four-digit year, e.g. 2013
Calculate Business Day
To calculate number of business day had passed, you need to do the following:
1. Create a holiday calendar and add all the holiday within that calendar.
2. Use the following syntax within Calculated Field when you build your report
Example SS_FUNC.countbusdays(now(),[this])- 1
To show different results depending on the value you can use 2 methods:
- if(conditional statement,value if true,value if false)
- If the field value is Pending Approval' display the word Submitted, otherwise display the field value:
- If the field value (statusid in this example) is one of 6 different statusids, display the word Submitted', otherwise display the field value:
if( in (9681,10134,10028,9685,9683,9901),'Submitted',[this])
- CASE value WHEN value 1 THEN result 1 WHEN value 2 THEN result 2 END
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
To add a line break between two values in the calculated field, use syntax such as the following:
This will make the column display the value in column 2 of the Report Builder, followed by a line break, followed by the value in column 3.