Changes

Jump to: navigation, search

Calculated Field

95 bytes removed, 20:04, 11 November 2014
→‎Formatting
{{MySQL Syntax}}
The ''Calculated Field'' in the [[Report Builder]] allows you to specify MySQL statements to be executed when generating the report.
===Dates===
Under Calculated Field section, you can use the following format for a date field{{ReportBuilderDateFormatOption}}<br />
DATE_FORMAT{{MYSQLDATETIMEFORMATS}} ===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 '''Syntax'''SS_FUNC.countbusdays(date1,date2) '''Example'''SS_FUNC.countbusdays(now(),[this], '%W, %M %D') -- Thursday, November 5th 1
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
|}
==Conditional Statements==
THEN 'Thank for your submission' WHEN 'Status 3' THEN 'Congratulations' ELSE 'I have no comment' END
==Formatting==
 
To add a line break between two values in the calculated field, use syntax such as the following:
 
<nowiki> concat([2],"<br>",[3])</nowiki>
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.
==See Also==
Arthur Lathrop
8,849
edits
Retrieved from ‘https://wiki.smartsimple.com/wiki/Special:MobileDiff/19247...23328’

Navigation menu