Here's a rundown of syntax in Report Builder. Click a topic on the left for a more detailed reference.
To compute the mean or average of q1:
avg(q1)
To compute a count of the valid (non-missing) values in q1:
validcount(q1)
To compute the percent of records where q1 equals 1:
pctin(q1,1)
The percent of records where q1 equals 1, 2 or 3:
Usage is pctin(expr,value(s))
pctin(q1,1,2,3)
The percent of records where q1 is within the range 8 to 10: (Make sure you use pctinrange rather than just pctin). Usage is pctinrange(expr,min,mix).
pctinrange(q1,8,10)
Leave either the min or max null for an unbounded range. The percent of records where q1 is 8 or more:
pctinrange(q1,8,null)
The percent of records where q1 is less than or equal to 10:
pctinrange(q1,null,10)
A count (frequency) of records where q1 is 1:
countin(q1,1)
See also: avg, pctin, pctinrange, countin, countinrange, median, sum, min, max, validcount
Missing values are excluded from computations. So if you want to compute percent of all respondents rather than just those answering, you’ll have to convert the missing values to an actual value, such as zero.
Rather than using q1, use ifnull(q1,0) to treat all q1’s missing values as zero. Then you can plug that into a formula such as pctin:
pctin(ifnull(q1,0),1)
See also: ifnull
Rather than using q1 you would use filter(q1,techuser==1) to include only records where techuser equals 1. Where techuser doesn’t equal one, the above expression will return a missing value (null).
pctin(filter(q1,techuser==1),1)
See also: filter, nulliftrue, nulliffalse
The percent of records where q1 equals 1 and q2 equals 2:
pctin(q1==1 and q2==2,true)
The percent of records where <whatever you want to test> is true:
pctin(<any expression that returns a boolean>, true)
The percent of records where q1 doesn't equal 1:
pctin(q1!=1,true)
or:
pctin(q1==1,false)
Net Promoter Score is percent top box minus percent bottom box, multiplied by 100.
Top box:
pctinrange(q1,8,10)
Bottom box:
pctinrange(q1,0,4)
NPS:
(pctinrange(q1,8,10) - pctinrange(q1,0,4)) * 100
Syntax for a group is just an expression that returns true or false. True means the record belongs to the group, false means it doesn't. Example group expressions:
segment==1
country==1
segment==1 and country==1
segment==1 and (country==1 or country==2 or country==3)
age > 50
age >= 50 and age <= 60
See also: and, or, Equivalency
You may have noticed the following syntax being using for the Total group. This syntax always returns true, so the group includes every record.
1==1
Total could also be written this way:
true
The function in(expr,value(s)) helps condense syntax. The following syntax:
country==1 or country==2 or country==3 or country==4 or country==5
... could be written as:
in(country,1,2,3,4,5)
See also: in
As shown above, the double equals sign == is the test for equivalency. To express doesn't equal use either <> or !=. For example:
country doesn't equal 1:
country!=1
country<>1
country doesn't equal 1, 2, or 3:
country!=1 and country!=2 and country!=3
(country==1 or country==2 or country==3) == false
in(country,1,2,3) == false
See also: Equivalency
If you are using string variables for groups, use quotation marks to represent string values.
region=="Northwest"
gender=="F"
Please leave a comment if I should add something here.
Many of the possible functions are listed below: