## 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)
``````

### 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)
``````

### 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
``````

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)
``````

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
``````

If you are using string variables for groups, use quotation marks to represent string values.

``````region=="Northwest"
gender=="F"
``````