Configure Offset and Limit Pagination in the REST Data Source

This article explains how to configure offset and limit pagination in the Jet Analytics REST data source using the Socrata Open Data API as an example. The same approach works for any API that uses offset/limit pagination, regardless of the parameter names used — for example, take/size, start/size, or skip/take.

The Socrata Open Data API is available at soda.demo.socrata.com and provides datasets covering topics such as environment, governance, and health.

Step 1: Find the base URL

Navigate to the dataset you want to use. In this example, the dataset is Current Employee Names, Salaries and Position Title.

To find the API endpoint URL, click Actions > API and copy the endpoint URL shown.

Set the Base URL in the data source connection to:

https://soda.demo.socrata.com/resource/

Step 2: Configure the pagination parameters

Offset and limit pagination requires two query parameters: one to control how many records to skip (the offset) and one to control how many records to return per page (the limit). The Socrata API uses $offset and $limit. The default limit is 1000 records; the maximum is 2000.

Dynamic SQL query for the offset value

Use the {TX_CurrentPage} operator to drive the offset. This variable starts at 0 and increments by 1 on each iteration. Multiply it by the page size to calculate the correct offset for each page.

SELECT {TX_CurrentPage} * 2000 as offsetValue

This query stores the calculated offset in a variable named offsetValue. Also specify the SQL Server connection for the query to run against (a database name is not required):

server=localhost

Set the User field to a SQL authentication user and the Password field to that user's password.

Note:

Use {TX_CurrentPage}, not {TX_NextPage}. Using TX_NextPage will cause the same first page to be fetched repeatedly without advancing the offset.

Offset parameter

Create a parameter where:

Name:$offset
Value:{offsetValue}

This passes the calculated offset value to the API on each iteration.

Step 3: Configure the stop condition

The pagination loop must stop when the API returns no more data. Add a parameter to detect an empty response:

Name:stopCondition
Type: XPath
Value:(*/*/*/*)[1]

This XPath expression checks whether there is any content at the fourth level of the response XML. Using four levels prevents the loop from stopping prematurely if the API returns a structure containing only the pagination parameters (such as skip and take) with no actual data records.

[Insert image (Socrata_StopCondition_Config) here]

Note:

If your API returns a response like the following even when there is no data, use (*/*/*/*)[1] (four levels) rather than (*/*/*)[1] (three levels) to correctly detect the empty state:

<TX_Autogenerated_Root> <TX_Autogenerated_Element> <skip>39000</skip> <take>1000</take> </TX_Autogenerated_Element> </TX_Autogenerated_Root>

An equivalent XPath is (TX_Autogenerated_Root/TX_Autogenerated_Element)[1]/*[1]/*[1].

The full data source connection configuration should look like this:

Step 4: Configure the endpoint

Add an endpoint with the following settings:

Name: Employee salaries
Path: employee-salaries.json

Add query parameters to the endpoint:

$limit = 2000 — sets the page size to the maximum allowed value.
(Optional) $order = :id — orders the results by the ID field. Use a colon prefix to reference a field name.

With this configuration, the data source will paginate through all rows in the dataset — in this example, approximately 32,600 rows across 17 pages.