8,849
edits
Changes
→Formatting
{{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 calculations statements that can be entered into the '''Calculated Field''' of the [[Report Builder]]builder.
ifnull([10],0)+ifnull([11],0)
===Dates===
{{ReportBuilderDateFormatOption}}
<br />
{{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])- 1
==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 ([[Determining the statusid|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
==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==
* Any [http://www.google.ca/search?hl=en&q=mysql%20calculations&meta= MYSQL Calculation] syntax can be used
* [[How do I do a count using a sum?]]
* Disambiguation: [[Custom Field Type: Special – Calculated Value|Calculated Value Field]]
[[Category:Reports]]
