Data Query API Code Samples

Selection of code samples that demonstrate the use of the Data Query API. Copy and edit these code samples to suit your needs.

When using the code samples, remember to replace:

{vanity_name} with your tenant name
{api_key} with your API key
{security_token} with a valid security token

These code samples may not include all available parameters and request body fields for each endpoint. For the endpoint's full request schema, see "Data Query" in API Reference.

Basic aggregate query

Headcount query

This sample request retrieves 1 month of Headcount data up to January 1, 2021. It does not use any group bys or filters.

POST /v1/data/query/aggregate

curl
Python
Node.js

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "employeeCount"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01"
        }
    }
}'

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import requests
import json

url = 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate'
headers = {
    'apikey': '{api_key}',
    'Cookie': 'VisierASIDToken={security_token}',
    'Content-Type': 'application/json'
}
data = {
    "query": {
        "source": {"metric": "employeeCount"},
        "timeIntervals": {"fromDateTime": "2021-01-01"}
    }
}

response = requests.post(url, headers=headers, data=json.dumps(data))
print(response.json())

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
const fetch = require('node-fetch');

const data = JSON.stringify({
    query: {
        source: { metric: 'employeeCount' },
        timeIntervals: { fromDateTime: '2021-01-01' }
    }
});

fetch('https://{vanity_name}.api.visier.io/v1/data/query/aggregate', {
    method: 'POST',
    headers: {
        'apikey': '{api_key}',
        'Cookie': 'VisierASIDToken={security_token}',
        'Content-Type': 'application/json'
    },
    body: data
})
.then(response => response.json())
.then(json => console.log(json))
.catch(error => console.error('Error:', error));

POST /v1/data/query/sql

curl
Python
Node.js

Copy

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT employeeCount(), Visier_Time, level(Function, \"Function\") FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 1, period(1, Month)) AND Permanent IN (\"True\")"
}'

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import requests
import json

url = 'https://{vanity_name}.api.visier.io/v1/data/query/sql'
headers = {
    'apikey': '{api_key}',
    'Cookie': 'VisierASIDToken={security_token}',
    'Content-Type': 'application/json'
}
data = {
    "query": 'SELECT employeeCount(), Visier_Time, level(Function, "Function") FROM Employee WHERE Visier_Time IN periods(date("2021-01-01"), 1, period(1, Month)) AND Permanent IN ("True")'
}

response = requests.post(url, headers=headers, data=json.dumps(data))
print(response.json())

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
const fetch = require('node-fetch');
const data = JSON.stringify({
query: 'SELECT employeeCount(), Visier_Time, level(Function, "Function") FROM Employee WHERE Visier_Time IN periods(date("2021-01-01"), 1, period(1, Month)) AND Permanent IN ("True")'});
fetch('https://{vanity_name}.api.visier.io/v1/data/query/sql', {
    method: 'POST',
    headers: {
        'apikey': '{api_key}',
        'Cookie': 'VisierASIDToken={security_token}',
        'Content-Type': 'application/json'
    },
    body: data
})
.then(response => response.json())
.then(json => console.log(json))
.catch(error => console.error('Error:', error));

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "cells": [
        {
            "value": "4071",
            "support": "4071",
            "coordinates": [
                0,
                0
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "DateInRange",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z - [0]"
                    ]
                }
            ]
        }
    ]
}

Aggregate query with filters

Headcount query filtered by Permanent employees

This sample request retrieves 1 month of data for Headcount filtered by Permanent employees up to January 1, 2021. The data is grouped by Function.

POST /v1/data/query/aggregate

curl
Python
Node.js

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "employeeCount"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01"
        },
        "filters": [{
            "memberSet": {
                "dimension": {
                    "name": "Permanent",
                    "qualifyingPath": "Employee"
                },
                "values": {
                    "included": [{
                        "path": ["True"]
                    }]
                }
            }
        }],
        "axes": [
            {
                "dimensionLevelSelection": {
                    "dimension": {
                        "name": "Function",
                        "qualifyingPath": "Employee"
                    },
                    "levelIds": [
                        "Function"
                    ]
                }
            }
        ]
    }
}'

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import requests
import json

url = 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate'
headers = {
    'apikey': '{api_key}',
    'Cookie': 'VisierASIDToken={security_token}',
    'Content-Type': 'application/json'
}
data = {
    "query": {
        "source": {"metric": "employeeCount"},
        "timeIntervals": {"fromDateTime": "2021-01-01"},
        "filters": [{
            "memberSet": {
                "dimension": {
                    "name": "Permanent",
                    "qualifyingPath": "Employee"
                },
                "values": {
                    "included": [{"path
                    : ["True"]}]
                }
            }
        }],
        "axes": [{
            "dimensionLevelSelection": {
                "dimension": {
                    "name": "Function",
                    "qualifyingPath": "Employee"
                },
                "levelIds": ["Function"]
            }
        }]
    }
}

response = requests.post(url, headers=headers, data=json.dumps(data))
print(response.json())

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
const fetch = require('node-fetch');

const data = JSON.stringify({
    query: {
        source: { metric: 'employeeCount' },
        timeIntervals: { fromDateTime: '2021-01-01' },
        filters: [{
            memberSet: {
                dimension: {
                    name: 'Permanent',
                    qualifyingPath: 'Employee'
                },
                values: {
                    included: [{ path: ['True'] }]
                }
            }
        }],
        axes: [{
            dimensionLevelSelection: {
                dimension: {
                    name: 'Function',
                    qualifyingPath: 'Employee'
                },
                levelIds: ['Function']
            }
        }]
    }
});

fetch('https://{vanity_name}.api.visier.io/v1/data/query/aggregate', {
    method: 'POST',
    headers: {
        'apikey': '{api_key}',
        'Cookie': 'VisierASIDToken={security_token}',
        'Content-Type': 'application/json'
    },
    body: data
})
.then(response => response.json())
.then(json => console.log(json))
.catch(error => console.error('Error:', error));

POST /v1/data/query/sql

curl
Python
Node.js

Copy

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT employeeCount(), Visier_Time, level(Function, \"Function\") FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 1, period(1, Month)) AND Permanent IN (\"True\")"
}'

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import requests
import json

url = 'https://{vanity_name}.api.visier.io/v1/data/query/sql'
headers = {
    'apikey': '{api_key}',
    'Cookie': 'VisierASIDToken={security_token}',
    'Content-Type': 'application/json'
}
data = {
    "query": 'SELECT employeeCount(), Visier_Time, level(Function, "Function") FROM Employee WHERE Visier_Time IN periods(date("2021-01-01"), 1, period(1, Month)) AND Permanent IN ("True")'
}

response = requests.post(url, headers=headers, data=json.dumps(data))
print(response.json())

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
const fetch = require('node-fetch');

const data = JSON.stringify({
    query: 'SELECT employeeCount(), Visier_Time, level(Function, "Function") FROM Employee WHERE Visier_Time IN periods(date("2021-01-01"), 1, period(1, Month)) AND Permanent IN ("True")'
});

fetch('https://{vanity_name}.api.visier.io/v1/data/query/sql', {
    method: 'POST',
    headers: {
        'apikey': '{api_key}',
        'Cookie': 'VisierASIDToken={security_token}',
        'Content-Type': 'application/json'
    },
    body: data
})
.then(response => response.json())
.then(json => console.log(json))
.catch(error => console.error('Error:', error));

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "cells": [
        {
            "value": "1420",
            "support": "1420",
            "coordinates": [
                0,
                0
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "DateInRange",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z - [0]"
                    ]
                }
            ]
        }
    ]
}

Aggregate queries with trailing time

Employee Exit Rate query with 2 months trailing time

This sample request retrieves 2 periods of Exit Rate. Each periods has 3 months trailing time up to January 1, 2021. It does not use any group bys or filters.

POST /v1/data/query/aggregate

curl
Python
Node.js

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "Exit_Model.Rate.Exit"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01",
            "intervalCount": 2,
            "trailingPeriodType": "MONTH",
            "trailingPeriodCount": 3
        }
    }
}'

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import requests
import json

url = 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate'
headers = {
    'apikey': '{api_key}',
    'Cookie': 'VisierASIDToken={security_token}',
    'Content-Type': 'application/json'
}
data = {
    "query": {
        "source": {"metric": "Exit_Model.Rate.Exit"},
        "timeIntervals": {
            "fromDateTime": "2021-01-01",
            "intervalCount": 2,
            "trailingPeriodType": "MONTH",
            "trailingPeriodCount": 3
        }
    }
}

response = requests.post(url, headers=headers, data=json.dumps(data))
print(response.json())

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
const fetch = require('node-fetch');

const data = JSON.stringify({
    query: {
        source: { metric: 'Exit_Model.Rate.Exit' },
        timeIntervals: {
            fromDateTime: '2021-01-01',
            intervalCount: 2,
            trailingPeriodType: 'MONTH',
            trailingPeriodCount: 3
        }
    }
});

fetch('https://{vanity_name}.api.visier.io/v1/data/query/aggregate', {
    method: 'POST',
    headers: {
        'apikey': '{api_key}',
        'Cookie': 'VisierASIDToken={security_token}',
        'Content-Type': 'application/json'
    },
    body: data
})
.then(response => response.json())
.then(json => console.log(json))
.catch(error => console.error('Error:', error));

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
{
    "cells": [
        {
            "value": "0.15906009941256213",
            "support": "528",
            "coordinates": [
                0,
                0
            ]
        },
        {
            "value": "0.1480026822492576",
            "support": "515",
            "coordinates": [
                0,
                1
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "TimeInInterval",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2020-09-01T00:00:00.000Z/2020-12-01T00:00:00.000Z - [0]"
                    ]
                },
                {
                    "path": [
                        "2020-10-01T00:00:00.000Z/2021-01-01T00:00:00.000Z - [1]"
                    ]
                }
            ]
        }
    ]
}

Employee Exit Rate query with 6 months forward trailing time

This sample request retrieves 2 periods of Exit Rate. Each period has 6 months trailing time starting from January 1, 2021. It does not use any group bys or filters.

POST /v1/data/query/aggregate

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
cURL sample request with JSON bodycurl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "Exit_Model.Rate.Exit"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01",
            "intervalCount": 2,
            "direction": "FORWARD",
            "trailingPeriodType": "MONTH",
            "trailingPeriodCount": 6
        }
    }
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
{
    "cells": [
        {
            "value": "0.22863198050227826",
            "support": "899",
            "coordinates": [
                0,
                0
            ]
        },
        {
            "value": "0.2181540484928194",
            "support": "857",
            "coordinates": [
                0,
                1
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "TimeInInterval",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z/2021-07-01T00:00:00.000Z - [0]"
                    ]
                },
                {
                    "path": [
                        "2021-02-01T00:00:00.000Z/2021-08-01T00:00:00.000Z - [1]"
                    ]
                }
            ]
        }
    ]
}

Headcount query filtered by Women employees

This sample request retrieves 1 month of data for Headcount filtered by Women employees up to January 1, 2021. It does not use a group by.

POST /v1/data/query/aggregate

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "employeeCount"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01"
        },
        "filters": [{
            "selectionConcept": {
                "name": "isFemale",
                "qualifyingPath": "Employee"
            }
        }]
    }
}'

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT employeeCount(), Visier_Time FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 1, period(1, Month)) AND isFemale = TRUE"
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "cells": [
        {
            "value": "1420",
            "support": "1420",
            "coordinates": [
                0,
                0
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "DateInRange",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z - [0]"
                    ]
                }
            ]
        }
    ]
}

Headcount query filtered by Direct Manager

This sample request retrieves 1 months of data for Headcount filtered by Direct Manager up to January 1, 2021. It does not use any group bys.

POST /v1/data/query/aggregate

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "employeeCount"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01"
        },
        "filters": [{
            "memberSet": {
                "dimension": {
                    "name": "Permanent",
                    "qualifyingPath": "Employee.Direct_Manager"
                },
                "values": {
                    "included": [{
                        "path": ["True"]
                    }]
                }
            }
        }]
    }
}'

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT employeeCount(), Visier_Time FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 1, period(1, Month)) AND Direct_Manager.Permanent IN (\"True\")"
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "cells": [
        {
            "value": "3282",
            "support": "3282",
            "coordinates": [
                0,
                0
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "DateInRange",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z - [0]"
                    ]
                }
            ]
        }
    ]
}

Aggregate query for multiple periods

Headcount query for 4 time periods

This sample request retrieves 4 quarters of data for Headcount up to January 1, 2021. It does not use any group bys or filters.

POST /v1/data/query/aggregate

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "employeeCount"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01",
            "intervalPeriodType": "QUARTER",
            "intervalCount": 4
        }
    }
}`

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT employeeCount(), Visier_Time FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 4, period(1, Quarter))"
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
{
    "cells": [
        {
            "value": "3829",
            "support": "3829",
            "coordinates": [
                0,
                0
            ]
        },
        {
            "value": "3736",
            "support": "3736",
            "coordinates": [
                0,
                1
            ]
        },
        {
            "value": "3620",
            "support": "3620",
            "coordinates": [
                0,
                2
            ]
        },
        {
            "value": "4071",
            "support": "4071",
            "coordinates": [
                0,
                3
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "DateInRange",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2020-04-01T00:00:00.000Z - [0]"
                    ]
                },
                {
                    "path": [
                        "2020-07-01T00:00:00.000Z - [1]"
                    ]
                },
                {
                    "path": [
                        "2020-10-01T00:00:00.000Z - [2]"
                    ]
                },
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z - [3]"
                    ]
                }
            ]
        }
    ]
}

Aggregate query with groupings

Headcount query grouped by Job Function and Pay Level

This sample request retrieves 1 months of data for Headcount grouped by Job Function and Pay Level up to January 1, 2021.

POST /v1/data/query/aggregate

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "employeeCount"
        },
        "timeIntervals": {
            "fromDateTime": "2021-01-01"
        },
        "axes": [{
            "dimensionLevelSelection": {
                "dimension": {
                    "name": "Function",
                    "qualifyingPath": "Employee"
                },
                "levelIds": [
                    "Function"
                ]
            }
        }, {
            "dimensionLevelSelection": {
                "dimension": {
                    "name": "Pay_Level",
                    "qualifyingPath": "Employee"
                },
                "levelIds": [
                    "Pay_Level"
                ]
            }
        }]
    }
}'

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT employeeCount(), Visier_Time, level(Function, \"Function\"), level(Pay_Level, \"Pay_Level\") FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 1, period(1, Month))"
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "cells": [
        {
            "value": "4071",
            "support": "4071",
            "coordinates": [
                0,
                0
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "DateInRange",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2021-01-01T00:00:00.000Z - [0]"
                    ]
                }
            ]
        }
    ]
}

Aggregate query with parameters

Days Worked Over Maximum Consecutive Limit query with the Maximum Consecutive Working Days Limit parameter set to 1

This sample request retrieves 4 intervals each containing 3 months of data for Days Worked Over Maximum Consecutive Limit up to January 1, 2021 with the Maximum Consecutive Working Days Limit parameter set to 1. Some metrics use parameters (dynamic values) in their formulas that allow you to change how the metric is calculated. In this example, you can change the parameter to set the maximum number of consecutive days an employee is able to work. Working streaks that exceed this limit during the period will be included in the calculation. It does not use any group bys or filters.

POST /v1/data/query/aggregate

Copy

JSON sample request body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/aggregate' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "query": {
        "source": {
            "metric": "daysWorkedOverMaxConsecutiveLimit"
        },
        "parameterValues": [{
            "numericValue": {
                "parameterId": "daysWorkedOverMaxConsecutiveLimit.Max_Consecutive_Working_Days_Limit_Parameter",
                "value": 1
            }
        }],
        "timeIntervals": {
            "fromDateTime": "2021-01-01",
            "intervalPeriodCount": 3,
            "intervalCount": 4
        }
    }
}'

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT daysWorkedOverMaxConsecutiveLimit(Max_Consecutive_Working_Days_Limit_Parameter = 1), Visier_Time FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 4, period(3, Month))"
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
{
    "cells": [
        {
            "value": "14283",
            "support": "19017",
            "coordinates": [
                0,
                0
            ]
        },
        {
            "value": "14275",
            "support": "14275",
            "coordinates": [
                0,
                1
            ]
        },
        {
            "value": "13653",
            "support": "13653",
            "coordinates": [
                0,
                2
            ]
        },
        {
            "value": "14750",
            "support": "24009",
            "coordinates": [
                0,
                3
            ]
        }
    ],
    "axes": [
        {
            "dimension": {
                "name": "Measures",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "MeasureName"
                    ]
                }
            ]
        },
        {
            "dimension": {
                "name": "TimeInInterval",
                "qualifyingPath": ""
            },
            "positions": [
                {
                    "path": [
                        "2020-01-01T00:00:00.000Z/2020-04-01T00:00:00.000Z - [0]"
                    ]
                },
                {
                    "path": [
                        "2020-04-01T00:00:00.000Z/2020-07-01T00:00:00.000Z - [1]"
                    ]
                },
                {
                    "path": [
                        "2020-07-01T00:00:00.000Z/2020-10-01T00:00:00.000Z - [2]"
                    ]
                },
                {
                    "path": [
                        "2020-10-01T00:00:00.000Z/2021-01-01T00:00:00.000Z - [3]"
                    ]
                }
            ]
        }
    ]
}

Basic list query

List query for Employee first name, last name, and organization

This sample request retrieves 1 month of Employee data for the employees' first names, last names, and organizations. It does not use any group bys or filters.

POST /v1/data/query/list

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/list' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "source": {
        "analyticObject": "Employee"
    },
    "columns": [{
        "columnName": "First Name",
        "columnDefinition": {
            "property": {
                "name": "Employee.First_Name",
                "qualifyingPath": "Employee"
            }
        }
    }, {
        "columnName": "Last Name",
        "columnDefinition": {
            "property": {
                "name": "Employee.Last_Name",
                "qualifyingPath": "Employee"
            }
        }
    }, {
        "columnName": "Organization",
        "columnDefinition": {
            "dimension": {
                "name": "Organization_Hierarchy",
                "qualifyingPath": "Employee"
            }
        }
    }],
    "timeInterval": {
        "fromDateTime": "2021-01-01"
    }
}'

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
8
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '
{
    "query": "SELECT Employee.First_Name, Employee.Last_Name, Organization_Hierarchy FROM Employee WHERE Visier_Time BETWEEN date(\"2020-12-01\") AND date(\"2021-01-01\")"
}`

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
    "header": {
        "0": "First Name",
        "1": "Last Name",
        "2": "Organization"
    },
    "rows": [
        {
            "0": "Cayson Keegan",
            "1": "Hilliard",
            "2": "-1;Marketing;Field Marketing;South Field Marketing"
        },
        {
            "0": "David Ashton",
            "1": "Cordova",
            "2": "-1;Marketing;Advertising;Contract Management"
        },
        {
            "0": "Edith Zoie",
            "1": "Mccullough",
            "2": "-1;Customer Support;NA Customer Support;East Call Center"
        }
    ]
}

List query with filters and sorting

List query for Employee first name, last name, and organization filtered by Woman

This sample request retrieves 1 month of women employees' first names, last names, and organizations. It does not use any group bys. The result is sorted by first name and then last name.

POST /v1/data/query/list

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/list' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "source": {
        "analyticObject": "Employee"
    },
    "filters": [{
        "selectionConcept": {
            "name": "isFemale",
            "qualifyingPath": "Employee"
        }
    }],
    "columns": [{
        "columnName": "First Name",
        "columnDefinition": {
            "property": {
                "name": "Employee.First_Name",
                "qualifyingPath": "Employee"
            }
        }
    }, {
        "columnName": "Last Name",
        "columnDefinition": {
            "property": {
                "name": "Employee.Last_Name",
                "qualifyingPath": "Employee"
            }
        }
    }, {
        "columnName": "Organization",
        "columnDefinition": {
            "dimension": {
                "name": "Organization_Hierarchy",
                "qualifyingPath": "Employee"
            }
        }
    }],
    "sortOptions": [
      { 
        "columnIndex": 0,
        "sortDirection": "SORT_ASCENDING"
      },
      { 
        "columnIndex": 1,
        "sortDirection": "SORT_ASCENDING"
    }],
    "timeInterval": {
        "fromDateTime": "2021-01-01"
    }
}'

POST /v1/data/query/sql

Copy

cURL sample request with SQL-like body

1
2
3
4
5
6
7
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/sql' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{
    "query": "SELECT Employee.First_Name, Employee.Last_Name, Organization_Hierarchy FROM Employee WHERE Visier_Time BETWEEN date(\"2020-12-01\") AND date(\"2021-01-01\") AND isFemale = TRUE"
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
{
    "header": {
        "0": "First Name",
        "1": "Last Name",
        "2": "Organization"
    },
    "rows": [
        {
            "0": "Edith Zoie",
            "1": "Mccullough",
            "2": "-1;Customer Support;NA Customer Support;East Call Center"
        },
        {
            "0": "America Simeen",
            "1": "Rodgers",
            "2": "-1;Customer Support;NA Customer Support;NE Call Center"
        }
    ]
}

List query for Employee first name, last name, and organization filtered by HR

This sample request retrieves 1 month of HR employees' first names, last names, and organizations. It does not use any group bys. The result is sorted by first name and then last name.

POST /v1/data/query/list

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/list' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "source": {
        "analyticObject": "Employee"
    },
    "filters": [{
          "formula": "include(Employee.Organization_Hierarchy, list(list(\"HR\")))"
    }],
    "columns": [{
        "columnName": "First Name",
        "columnDefinition": {
            "property": {
                "name": "Employee.First_Name",
                "qualifyingPath": "Employee"
            }
        }
    }, {
        "columnName": "Last Name",
        "columnDefinition": {
            "property": {
                "name": "Employee.Last_Name",
                "qualifyingPath": "Employee"
            }
        }
    }, {
        "columnName": "Organization",
        "columnDefinition": {
            "dimension": {
                "name": "Organization_Hierarchy",
                "qualifyingPath": "Employee"
            }
        }
    }],
    "sortOptions": [
      { 
        "columnIndex": 0,
        "sortDirection": "SORT_ASCENDING"
      },
      { 
        "columnIndex": 1,
        "sortDirection": "SORT_ASCENDING"
    }],
    "timeInterval": {
        "fromDateTime": "2021-01-01"
    }
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
    "header": {
        "0": "First Name",
        "1": "Last Name",
        "2": "Organization"
    },
    "rows": [
        {
            "0": "Phil Kruz",
            "1": "Lemon",
            "2": "-1;HR;HR West"
        },
        {
            "0": "Orpah Shelby",
            "1": "Vina",
            "2": "-1;HR;HR North East"
        },
        {
            "0": "Braedan Daiki",
            "1": "Lacerda",
            "2": "-1;HR;HR South"
        }
    ]
}

Multi-value property list query

Multi-value property query for employee compensation items

This sample request retrieves 1 month of compensation items, the employee ID of the employee who is being compensated, the compensation type, and the compensation amount. It does not use any group bys or filters.

POST /v1/data/query/list

Copy

cURL sample request with JSON body

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
curl -X POST --url 'https://{vanity_name}.api.visier.io/v1/data/query/list' \
-H 'apikey:{api_key}' \
-H 'Cookie:VisierASIDToken={security_token}' \
-H 'Content-Type: application/json' \
--data '{   
    "source": {
        "analyticObject": "Employee.Employee_Budgeted_Compensation"
    },
    "columns": [{
        "columnName": "Employee.Employee.EmployeeID",
        "columnDefinition": {
          "property": {
            "name": "Employee.EmployeeID",
            "qualifyingPath": "Employee"
          }
        }
      }, {
        "columnName": "compensationType",
        "columnDefinition": {
          "formula": "Compensation_Type"
        }
      },
      {
        "columnName": "compensationAmount",
        "columnDefinition": {
          "formula": "Employee_Budgeted_Compensation.Compensation_Amount"
        }
      }],
    "timeInterval": {
        "fromDateTime": "2021-01-01"
    }
}'

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
    "header": {
        "0": "Employee.Employee.EmployeeID",
        "1": "compensationType",
        "2": "compensationAmount"
    },
    "rows": [
        {
            "0": "Employee-26833151",
            "1": "Agency_Staff",
            "2": 3140.17
        },
        {
            "0": "Employee-26833151",
            "1": "Facilities",
            "2": 3140.17
        },
        {
            "0": "Employee-26833151",
            "1": "Health Plan 3",
            "2": 3140.17
        }
    ]
}

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
{
    "currencyRates": [
        {
            "fromCurrencyCode": "CAD",
            "toCurrencyCode": "USD",
            "rate": 0.93,
            "startTime": "1677626600000",
            "endTime": "1677628800000"
        },
        {
            "fromCurrencyCode": "CAD",
            "toCurrencyCode": "USD",
            "rate": 0.84,
            "startTime": "1677628800000",
            "endTime": "1680546444000"
        }
    ]
}