Syntax Reference

0     0     Edited Aug 31, 2015

Here's a rundown of syntax in Report Builder. Click a topic on the left for a more detailed reference.

Measure Syntax

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


Re-basing (compute from ‘all respondents’ rather than ‘answered question’)

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


Filtering (e.g., reducing the base from ‘answered question’ to something else)

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


Percentages from multiple variables

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)


Computing Net Promoter Score (NPS)

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




Groups Syntax

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.




Additional Reference

Many of the possible functions are listed below:



Leave a Comment

Please sign in to leave a comment.