Row level security rules reference

Overview

Explains what the feature is or what its benefits are to the user or customer.

Feature

ThoughtSpot allows you to create row level security rules using expressions. If an expression evaluates to "true" for a particular row and group combination, that group will be able to see that row. This reference lists the various operators and functions you can use to create rules.

For information on how to use the row level security functions and operators, see About Rule-Based Row Level Security. There is a special variable called ts_groups, which you can use when creating row level security rules. It fetches a list of the groups that the currently logged in user belongs to. For each row, f the expression in the rule evaluates to 'true' for any one of these groups, that row will be shown to the user.

You can also see this list of operators and examples from within the Rule Builder by selecting Rule Assistant.

Conversion functions

These functions can be used to convert data from one data type to another. Conversion to or from date data types is not supported.

Table 1. Conversion functions for use in rules

Function

Description

Examples

to_bool

Returns the input as a boolean (true or false).

to_bool (0) = false

to_double

Returns the input as a double.

to_double ('3.14') = 3.14

to_integer

Returns the input as an integer.

to_integer ('45') + 1 = 46

to_string

Returns the input as a text string.

to_string (45 + 1) = '46'

to_string (revenue - cost)

Date functions

Table 2. Date functions for use in rules

Function

Description

Examples

add_days

Returns the result of adding the specified number of days to the given date.

add_days (01/30/2015, 5) = 02/04/2015

add_days (invoiced, 30)

date

Returns the date portion of a given date.

date (home visit)

day

Returns the number (1-31) of the day for the given date.

day (01/15/2014) = 15

day (date ordered)

day_number_of_week

Returns the number (1-7) of the day in a week for the given date with 1 being Monday and 7 being Sunday.

day_number_of_week (01/30/2015) = 6

day_number_of_week (shipped)

day_number_of_year

Returns the number (1-366) of the day in a year for the given date.

day_number_of_year (01/30/2015) = 30

day_number_of_year (invoiced)

day_of_week

Returns the day of the week for the given date.

day_of week (01/30/2015) = Friday

day_of_week (serviced)

diff_days

Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact.

diff_days (01/15/2014, 01/17/2014) = -2

diff_days (purchased, shipped)

diff_time

Subtracts the second date from the first date and returns the result in number of seconds.

diff_time (01/01/2014, 01/01/2014) = -86,400

diff_time (clicked, submitted)

hour_of_day

Returns the hour of the day for the given date.

hour_of_day (received)

is_weekend

Returns true if the given date falls on a Saturday or Sunday.

is_weekend (01/31/2015) = true

is_weekend (emailed)

month

Returns the month from the given date.

month (01/15/2014) = January

month (date ordered)

month_number

Returns the number (1-12) of the month for the given date.

month_number (09/20/2014) = 9

month_number (purchased)

now

Returns the current timestamp.

now ()

start_of_month

Returns the epoch for the first day of the month for the given date.

start_of_month (01/31/2015) =1420099200

start_of_month (shipped)

start_of_quarter

Returns the epoch for the first day of the quarter for the given date.

start_of_quarter (09/18/2015) = 1441090800

start_of_quarter (sold)

start_of_week

Returns the epoch for the first day of the week for the given date.

start_of_week (05/30/2015) = 1432450800

start_of_week (paid)

start_of_year

Returns the epoch for the first day of the fiscal year for the given date.

start_of_year (02/15/2015) = 1420099200

start_of_year (joined)

time

Returns the time portion of a given date.

time (3/1/2002 10:32) = 10:32

time (call began)

year

Returns the year from the given date.

year (01/15/2014) = 2014

year (date ordered)

Mixed functions

These functions can be used with text and numeric data types.

Table 3. Mixed functions for use in rules

Function

Description

Examples

!=

Returns true if the first value is not equal to the second value.

3 != 2 = true

ts_groups != public

>

Returns true if the first value is greater than the second value.

3 > 2 = true

>=

Returns true if the first value is greater than or equal to the second value.

3 >= 2 = true

=

Returns true if the first value is equal to the second value.

2 = 2 = true

ts_groups = region

<

Returns true if the first value is less than the second value.

3 < 2 = false

<=

Returns true if the first value is less than or equal to the second value.

3 >= 2 = true

Number functions

Table 4. Number functions for use in rules

Function

Description

Examples

*

Returns the result of multiplying both numbers.

3 * 2 = 6

price * taxrate

+

Returns the result of adding both numbers.

1 + 2 = 3

price + shipping

-

Returns the result of subtracting the second number from the first.

3 - 2 = 1

revenue - tax

/

Returns the result of dividing the first number by the second.

6 / 3 = 2

markup / retail price

^

Returns the first number raised to the power of the second.

3 ^ 2 = 9

width ^ 2

abs

Returns the absolute value.

abs (-10) = 10

abs (profit)

acos

Returns the inverse cosine in degrees.

acos (0.5) = 60

acos (cos-satellite-angle)

asin

Returns the inverse sine (specified in degrees).

asin (0.5) = 30

asin (sin-satellite-angle)

atan

Returns the inverse tangent in degrees.

atan (1) = 45

atan (tan-satellite-angle)

atan2

Returns the inverse tangent in degrees.

atan2 (10, 10) = 45

atan2 (longitude, latitude)

cbrt

Returns the cube root of a number.

cbrt (27) = 3

cbrt (volume)

ceil

Returns the smallest following integer.

ceil (5.9) = 6

ceil (growth rate)

cos

Returns the cosine of an angle (specified in degrees).

cos (63) = 0.45

cos (beam angle)

cube

Returns the cube of a number.

cube (3) = 27

cube (length)

exp

Returns Euler's number (~2.718) raised to a power.

exp (2) = 7.38905609893

exp (growth)

exp2

Returns 2 raised to a power.

exp2 (3) = 8

exp2 (growth)

floor

Returns the largest previous integer.

floor (5.1) = 5

floor (growth rate)

greatest

Returns the larger of the values.

greatest (20, 10) = 20

greatest (q1 revenue, q2 revenue)

least

Returns the smaller of the values.

least (20, 10) = 10

least (q1 revenue, q2 revenue)

ln

Returns the natural logarithm.

ln (7.38905609893) = 2

ln (distance)

log10

Returns the logarithm with base 10.

log10 (100) = 2

log10 (volume)

log2

Returns the logarithm with base 2 (binary logarithm).

log2 (32) = 5

log2 (volume)

mod

Returns the remainder of first number divided by the second number.

mod (8, 3) = 2

mod ( revenue , quantity )

pow

Returns the first number raised to the power of the second number.

pow (5, 2) = 25

pow (width, 2)

random

Returns a random number between 0 and 1.

random ( ) = .457718

random ( )

round

Returns the first number rounded to the second number (the default is 1).

round (35.65, 10) = 40

round (battingavg, 100)

sign

Returns +1 if the number is greater than zero, -1 if less than zero, 0 if zero.