Overview #
This page is intended to provide a quick overview of JavaScript expressions, specifically in the context of the “calculated fields” functionality available in Apsona’s singlestep and multistep reports. This overview is just for getting you started. Much more extensive resources available on the web include You don’t know JS (a free ebook), the Mozilla Developer Network and Eloquent JavaScript.
Calculated values are supported in Apsona’s singlestep reports as well as multistep reports. A calculated value is simply a JavaScript expression which uses the columns of the report as variable names, and standard JavaScript operators and methods.
Variable syntax #
For singlestep reports, when using the value of a column in a report, the column name must surrounded by {!
and }
symbols. Note also that the column name must match exactly, including letter case, e.g., the column reference {!Total Amount}
would be incorrect when referring to a column named Total amount
because of case mismatch. Similarly, the column reference {!TotalAmount}
would be incorrect because the lack of the single space.
With multistep reports, to insert a variable referring to the value of an earlier step, you use the step name of the variable, followed by a period and then column name, surrounded by {!
and }
symbols. For example, if you have a query step named Opportunities
containing a field named Amount
, you use the notation {!Opportunities.Amount}
. You can use the “Insert reference to field” dropdown in the field editor as a quick way to insert these variables into your formula.Thus, a calculation step with a field containing the formula {!Opportunities.Amount} * 0.075
calculates a 7.5% commission on the amount. The effect of adding a calculation step is to add new columns to the report in which each row contains the result of applying the formulas of the step to the other columns in the row.
Data types #
Each variable used in a calculation formula denotes a value from an earlier query step in the report. So, the variable will have the data type of the column of that value. In the above example, the variable {!Opportunities.Amount}
is of type currency (a numeric value). So it makes sense to multiply it by 0.075. By contrast, of you had a variable {!Opportunity.Close Date}
containing the close date of the Opportunity, its value would have the data type Date, so you can use any JavaScript Date operations on that value. For example, you can write a formula {!Opportunity.Close Date}.getFullYear()
that uses the getFullYear
method of JavaScript’s Date object to obtain the year of that date.
Examples #
JavaScript expressions are very powerful and flexible. You can find detailed documentation about them in many places on the web, in particular at the JavaScript area of the Mozilla Developer site. Here are a few examples.
Conditionals #
These examples show how to use JavaScript’s conditional ternary operator to calculate conditional (ifthenelse) values in a formula.
Example 1: Numeric range #
Given the opportunity amount field Oppties.Amount
, the formula{!Oppties.Amount} * ({!Oppties.Amount} <= 10000 ? 0.075 : 0.085)
calculates a commission of 7.5% on the amount if the amount is $10,000 or less, and 8.5% if the amount is over $10,000.
Example 2: Number buckets #
Here is a more complex example involving conditionals. Given a particular day of month, we want to generate the correct suffix for that day. For example:
 1, 21 and 31 should produce
1st
,21st
and31st
respectively  2 and 22 should produce
2nd
and22nd
respectively  4 and 15 should produce
4th
and15th
respectively.
Assuming that you have a report field named Day
that contains the day of month, the calculation below will produce the desired result:
{!Day} + ( Math.floor ({!Day}/10) == 1 ? "th" : {!Day} % 10 == 1 ? "st" : {!Day} % 10 == 2 ? "nd" : {!Day} % 10 == 3 ? "rd" : "th" )
Example 3: "Empty or null" tests #
You can test whether a particular string value is empty or null using the JavaScript "truthiness" test, like this:
{!Salutation} ? {!Salutation} + ". " + {!Name} : {!Name}
In this example, we want to produce a period after the Salutation if it is not empty or null, and completely omit it otherwise. If the Salutation
is not empty, the condition is treated as true, so the result produced is something like
Mr. John Smith
But if the Salutation
field is null or the empty string, the condition is treated as false, so the result produced is something like
John Smith
The point here is that within the conditional part (to the left of the ? symbol), just the occurrence of the field name is enough to signify whether it is null or empty. This is because of the notion of "truthiness" in JavaScript.
JavaScript functions #
You can calculate the age of an Opportunity – the number of days from created date to close date – using the following formula:{!Oppties.Close Date}.daysAfter ({!Oppties.Created Date})
This formula uses the daysAfter
method that is not part of native JavaScript, but is provided by Apsona. The daysAfter
method is really just a convenient shortcut. You would get the same result using plain JavaScript's getTime
method, which returns the number of milliseconds since Jan 1, 1970, and using the fact that there are 86,400 seconds in a day:Math.floor (({!Oppties.Close Date}.getTime()  {!Oppties.Created Date}.getTime()) / 86.4E6)
String splitting #
JavaScript includes capabilities for easily splitting strings into pieces using regular expressions. For example, consider this common situation. Suppose you have a data field named Street Address
in your report containing the street address part of a full Contact address, but this field actually contains multiple lines, as is often the case with international addresses. Suppose now that you want to produce each of the lines in that field in a separate field. You can achieve this goal using the regularexpressionbased splitting that JavaScript supports. For example, to create a field named Street1
that contains the first line of a multiline street address field named Street Address
, you can use the following calculation:
{!Street Address}.split(/\n/)[0]
This formula splits the string Street Address
into parts returned in an array, and then extracts the first part by referring to the first element (element 0) of the array using the subscript notation. Similarly, the formula {!Street Address}.split(/\n/)[1]
gives us the second line in the multiline address.
Bucketing #
You can get really fancy with writing inline JavaScript functions. For example, given a previous step named Account
that has a column named Billing State
containing a state abbreviation such as CA
or NV
, the following formula returns the full name of the state for known abbreviations, and Unknown
for an unknown abbreviation:
(function (abbrev) { var nameMap = {"CA": "California", "NV": "Nevada", "OR": "Oregon"}; return abbrev in nameMap ? nameMap[abbrev] : "Unknown"; }) ({!Account.Billing State});
Another example involves bucketing a numeric value, such as the age of a person. If your buckets are uniformly distributed (e.g., 20year intervals), you can use the following calculation:
(function (ageValue) { var buckets = ["19 or younger", "2039", "4059", "60+"]; /* Set up the bucket names */ var index = ageValue % 20; /* Find the bucket in which the given age range belongs */ if (index <= 0) { /* Fix it up if it's out of range */ index = 0; } else if (index >= 3) { index = 3; } return buckets[index]; /* Return the correct bucket name. */ }) ({!Contact.Age});
But if your buckets are adhoc, you might want to use conditionals like this:
(function (ageValue) { return ageValue < 18 ? "Under 18" : ageValue <= 44 ? "1844" : ageValue <= 64 ? "4564" : "65+"; }) ({!Contact.Age});
String operations #
These next examples will show you how to do some basic operations with strings.
Simple concatenation #
Maybe you'd like to produce URLs to each Contact record in your report. You know that the URL for the Contact records looks something like:
https://myDomainName.my.salesforce.com/contactID
Basic string concatenation in Javascript is done with a "+" sign. So assuming you already have a field for the Contact ID in your report, your calculation would look like this:"https://myDomainName.my.salesforce.com/" + {!Contact ID}
where you'd need to fill in myDomainName
with your actual Salesforce domain name.
Fancier concatenation #
When you need to take a bunch of different values and string them all into one string, the JavaScript filter and the join functions come in handy. For example, suppose you have four fields in your report, named (rather unimaginatively) field1
, field1
, field1
and field4
. Now you need a single calculated string that contains the four values concatenated together separated by commas, but any of the fields that is empty must be omitted. For example, if the four field values are Boston
code>, empty, New York
and null, you want your result to look like Boston, New York
. So you combine two little tricks:
 the JavaScript
filter
function that works with an array and removes unneeded values, and  The
join
function makes a string out of an array, with the array fields separated by commas.
So here is the finished expression:[ {!field1}, {!field2}, {!field3}, {!field4} ].filter (function (aValue) { return aValue != null && aValue != ""; }).join (", ")
The expression produces an array with four values. The array is run through the filter function which returns true only for those values that are not empty or null. The resulting array is then run through the join function which produces a single string with your inputs separated by commas, as required.The Mozilla website offers full details about the filter function and the join function.
Constraints #
Some constraints to note:
 A calculated field's formulas can only use fields from earlier steps, not from later ones. Additionally, if your multistep report is split into more than one block because of linkages, the formula can only use fields available in the block in which the formula appears.
 Formulas may not use fields from other calculation steps, only from query steps.
 Calculation steps cannot be moved up or down, unlike query steps.
Examples #
Below are a few examples.
Calculation code  Result type  Notes 

{!Total} >= 0 ? "Positive" : "Negative" 
String  The result is string Positive if the value of the Total column is zero or more, and the string Negative otherwise. This is an example of a ternary or conditional expression 
{!Total} + 335 
Number or Currency  The result of adding 335 to the column named Total . If the Total column is empty, the result will be NaN (the special JavaScript value called Not a Number) 
(isNaN ({!Total}) ? 0 : {!Total}) + 335 
Number or Currency  The result is zero if the Total column is empty, and adds 335 to that column otherwise. This example builds on the one above, and uses the builtin isNaN function to ensure that the result is always a number regardless of whether or not the Total column is empty. 
({!Total}  0) + 335 
Number or Currency  The result is the same as in the above example, but the technique is different: it uses JavaScript's shortcut 'or' operator. 
{!CloseDate}.getFullYear() < 2000 ?
"Last millennium" : "This millennium" 
String  Assuming that the CloseDate column is of type Date , this example uses the Date object's getFullYear method to obtain the year of the close date. So its result is a string that indicates the millennium of the close date. 
Apsona enhancements #
Apsona includes several methods that enhance the native JavaScript functionality, described below. These functions are available only in the Apsona pages, not natively in JavaScript.
Date methods #
These methods are available on Date values, i.e., you can only use them on a column whose value is known to be of type Date.
Function  Result type  Example 

addDays (numberOfDays) 
Date  {!CloseDate}.addDays (15) 
Produces a new Date whose value is the given date plus the given number of days. The number of days can be positive or negative. Note that the result includes the time part as well. For example, if you add 5 days to March 12, 2016 1:20 pm your result will be March 17, 2016 1:20 pm . This method correctly accounts for leap years, transitions across months and years, and the like. 

closestPreviousWeekday (wkDayNo) 
Date  {!CloseDate}.closestPreviousWeekday (3) 
The wkDayNo parameter must be a number between 0 and 6 (indicating Sunday through Saturday). In this example, the result is the previous Wednesday closest to the CloseDate column. 

daysAfter (aDate) 
Date  {!CloseDate}.daysAfter ({!StartDate}) 
Produces the number of days between the two dates. If the aDate parameter is before the specified date, the result is positive, otherwise negative. This example produces the number of days between the CloseDate and StartDate columns. 

endOfDay() 
Date  {!CloseDate}.endOfDay() 
Produces the value of 11:59:59 pm on the given date. For example, for the date March 12, 2016 1:20 pm the result will be March 12, 2016 11:59:59 pm . Note the pair of parentheses after startOfDay  that's required by JavaScript syntax. 

format (formatString) 
String  {!CloseDate}.format ("yyyyMMdd") 
The value of the CloseDate column, formatted like 20160713 . The format codes in the formatString parameter indicate the layout of the result. Note that format codes are casesensitiveHere are the format codes that Apsona recognizes: 

yyyy 
Four digit year, e.g., 2017  
M 
One or twodigit month, e.g., 1 for January and 10 for October  
MM 
Twodigit month with a leading zero if necessary, e.g., 01 for January and 10 for October  
MMM 
Threeletter month name, e.g., Feb for February 

MMMM 
Full month name, e.g., October 

d 
One or twodigit date  
dd 
Twodigit date with a leading zero if necessary  
EE 
Full weekday name, e.g., Monday 

E 
Threeletter weekday name, e.g., Mon 

HH 
Twodigit hour of day, in 24hour format  
hh 
Twodigit hour of day, in 12hour format  
h 
Hour of day in 12hour format, with leading zero if needed  
mm 
Twodigit minute of hour, with leading zero if needed  
ss 
Twodigit seconds, with leading zero if needed  
a 
AM/PM indicator  
startOfDay() 
Date  {!CloseDate}.startOfDay() 
Removes the time part of the date, if any, and produces the value of midnight. For example, for the date March 12, 2016 1:20 pm the result will be March 12, 2016 12:00 am . Note the pair of parentheses after startOfDay  that's required by JavaScript syntax. 

weekOfYear() 
Integer  {!CloseDate}.weekOfYear() 
Returns the week number of the year of the specified date, assuming that the first week is week 0. In the example at left, if CloseDate is January 9, the value produced is 1 (since the first week is treated as week 0). If CloseDate is May 1, the produced value is 17, and If CloseDate is Dec 31, the produced value is 52. 
Number methods #
These methods are available on Number values, i.e., you can only use them on a column whose value is known to be a number or currency.
Function  Result type  Example 

formatWithCommas () 
String  {!Quantity}.formatWithCommas () 
Produces the number with commas in the thousands positions. For example, for the code (1300207).formatWithCommas() will produce 1,300,207 . 

formatAsCurrency (currencySymbol) 
String  {!TotalAmount}.formatAsCurrency ("$") 
Produces the number formatted as a currency value, with commas in the thousands positions. For example, the code (23457.89).formatAsCurrency() will produce $23,457.89 . 
Multipick methods #
These methods are available on multipick values, i.e., you can only use them on a column whose value is known to be a multiselect picklist field.
Function  Result type  Example 

choices () 
Number  {!Interests}.choices () 
Produces an array containing the values selected in the multiselect field.  
allowedChoices () 
Number  {!Interests}.allowedChoices () 
Produces an array containing all of the allowed values for the multiselect field.  
contains (choiceString) 
Boolean (yes/no)  {!Interests}.contains ("Bowling") 
Produces a boolean (yes/no) indicating whether the given value (in this example, Bowling ) is part of the multiselect field's (in this example, Interests ) value. 
As an example, suppose you have a field named {!Interests} that produces a multiselect picklist value, and you want to display its contents in Englishlike syntax. You could then use this calculation:
{!Interests} == null ? "" : {!Interests}.choices().length <= 1 ? {!Interests} : {!Interests}.choices().slice(0, {!Interests}.choices().length1).join (", ") + " and " + {!Interests}.choices()[{!Interests}.choices().length1]
This logic is set up so that if the selection contains only one value, it produces that value; otherwise, if there are n selected values, it produces the first n1 values commaseparated, then an "and" and finally the last value, as expected in English, e.g., Red
or Red and Blue
or Red, Blue and Green
 notice the placement of the commas and the word "and" in the right places.
Row functions #
As noted above, if you create an expression that involves column references, it will only use the values of the referenced columns in the current row. But sometimes you would want to use data values from elsewhere in the report, e.g, a running sum, or a column total. The functions documented below are meant to serve such needs.
Function  Result type  Example 

columnTotal (columnName) 
Number  columnTotal ('Commission $') 
Produces the total value of the specified column across all the rows of the report. Obviously this makes sense only for columns with numerical values such as number or currency.  
prevValue (columnName, rowOffset) 
prevValue ('Posted Date', 1) 

Produces the value in the cell in the specified column, in the previous row offset from the current row by the specified amount. In the above example, we are obtaining the value in the column labeled Posted Date , in the row immediately before the current row. The data type of the produced value (number, date, etc.) is the same as the data type of the column specified. 

rowCount () 
Number  rowCount () 
Produces the number of rows in the report.  
rowNo () 
Number  rowNo () 
Produces the row number of the current row. Row numbers start at 0, not 1.  
runningSum (columnName) 
Number  runningSum ('Commission $') 
Produces the running sum of the specified column, up to and including the current row. This function can be applied to any numerical columns, such as numbers or currencies.  
value (columnName, rowIndex) 
value ('Posted Date', 4) 

Produces the value in the cell in the specified column and row. In the above example, we are obtaining the value in the column labeled Posted Date , in row 4 (i.e., the fifth row, since rows are indexed from 0). Note that when calling this function, the column name must be enclosed in either single or double quotes. The data type of the produced value (number, date, etc.) is the same as the data type of the column specified. 