Sscalculation is a variable syntax used for Web Page Views, Store Value system variables, and Read Only system variables. It uses MariaDB syntax and works similarly to sslogic, but calculates a result from a formula instead of evaluating a condition.
Basic Sscalculation Syntax
Sscalculation works as follows:
<!--@sscalculation('@field@'*0.05)-->This will return the computed value of '@field@'*0.05 and replaces the sscalculation tag.
This allows calculations to run directly on web page views without using JavaScript, which also resolves issues with creating PDF files from web page views that contain JavaScript.
The sscalculation method uses MySQL syntax. Any MySQL function such as concat(...), format(...), round(...), can be used.
Use Sscalculation in a List
To obtain a sum of the values in a custom field for all transactions associated with an organization or contact using sscalculation:
<!--@sscalculation( [#(?object=transaction)$add this$+#]0)-->
When finding a sum, do not forget the trailing zero at the end of the statement.
Example
If there are transactions with values of 10, 15, and 20, the statement between the square brackets translates as follows:
[#(?object=transaction)$add this$+#] becomes 10+15+20+
The trailing zero ensures the evaluated statement does not end with a plus sign, which would cause the calculation to fail.
To pull information from Level 1 while working within an sscalculation tag at Level 2, use the parent keyword.
<!--@sscalculation(date_format("@parent.fullstartdate@","%Y"))-->@datetime(parent.startdate)@ or @parent.fullstartdate@.To sum a value on a Company Role field (Custom Field ID 123456) formatted with no decimal places for all companies with the role of Funding Agency and a value stored on Level 1 (Custom Field ID 545454):
<!--@sscalculation(format(( [#(?object=company::criteria=rolename='Funding Agency') $?opc_123456.value$ + #] @#545454.value#@),0))-->
To find the average of all values (Custom Field ID 1755555) rounded to the nearest whole number on a particular Level 2 Type (typeid=54321), excluding a particular status (statusid is not 10001):
<!--@sscalculation(format( ( [#(?object=activity::criteria=typeid='54321' and status.statusid not in ('10001')) $1755555$ + #] 0 )/@level2.count(*):typeid='54321' and status.statusid not in ('10001')@,0))-->To find the difference in days between two dates:
<!--@sscalculation(datediff('@datetime(currentdate)@','@Last Contact Date@'))-->To add a number of months to a date equal to the number selected in another field:
<!--@sscalculation(DATE_ADD('@Anticipated Project End Date@', INTERVAL @system.Grants-FinalDisbursementDelay@ MONTH))-->To subtract a number of months from a date, use a negative number for the INTERVAL. For example, -6 months below:
<!--@sscalculation(DATE_ADD('@Anticipated Project End Date@', INTERVAL -6 MONTH))-->To calculate the difference in months between two date values, use one of the following syntax options:
<!--@sscalculation(PERIOD_DIFF(DATE_FORMAT('@Anticipated Project End Date@','%Y%m'),DATE_FORMAT('@Anticipated Project Start Date@','%Y%m')))--><!--@sscalculation(TIMESTAMPDIFF( MONTH,'@Start Date.value@','@End Date.value@') )-->
Use Sscalculation to Change Date Formats of Data in Custom Fields
To parse out date/time information from a custom field and display it in a different format, use the following syntax on Level 2, referencing the field fieldname on Level 1:
<!--@sscalculation(date_format("@parent.fieldname@","%M %d, %Y"))-->Use the following arguments to format the date/time information:
| Format | Description |
|---|---|
| %M %d, %Y | Returns date in format of September 30, 2012 |
| %a | Returns the truncated day of the week, e.g. Sun. for Sunday |
| %b | Returns the truncated name of the month, e.g. Sep for September |
| %c | Returns the month number in short format, e.g. 9 for September |
| %d | Returns the numeric day of month (01-31) |
| %e | Returns the numeric day of month (1-31) |
| %h | Returns hour (01-12) |
| %i | Returns minutes (00-59) |
| %j | Returns day of year (001-366) |
| %k | Returns hour (0-23) |
| %l | Returns hour (1-12) |
| %m | Returns the month number, e.g. 09 for September |
| %p | Returns AM/PM information, e.g. AM |
| %r | Returns the full time in 12-hour format, e.g. 12:00:00 AM |
| %s | Returns seconds (00-59) |
| %u | Returns the week number (00-53) where Monday is the first day of the week |
| %w | Returns day of the week (0=Sunday...6=Saturday) |
| %y | Returns the truncated year, e.g. 12 for 2012 |
| %D | Returns the month ordinal, e.g. 30th |
| %H | Returns hour (00-23) |
| %M | Returns the month name, e.g. September |
| %T | Returns the full time in 24-hour format, e.g. 23:00:00 |
| %U | Returns the week number (00-53) where Sunday is the first day of the week |
| %W | Returns the day of the week, e.g. Sunday |
| %Y | Returns the four-digit year, e.g. 2013 |
It is not necessary to use sscalculation if the custom field being referenced is on the same level as the field referencing it. For example, referencing a Level 2 custom field in a web page view field on Level 2 does not require sscalculation. In this case, use the syntax outlined in Web Page View Field Variables Reference.
Use Sscalculation to Replace Text
In the following example, the variable @Select Many Checkbox Fields@ returns a semicolon-separated list of selected values from the field:
Option 1;Option 2;Option 3
To replace the semicolons with a comma followed by a space, use the following syntax:
<!--@sscalculation(REPLACE("@Select Many Checkbox Field@",";",", "))-->This returns:
Option 1, Option 2, Option 3