Skip to content
Pricefx REST API
/Pricefx API Reference/
/
SQL Query a Data Manager...

The Pricefx Backend API

Pricefx REST API Reference

Download OpenAPI description
pricefx-server_openapi.json
pricefx-server_openapi.yaml
Overview
URL
https://www.pricefx.com
Pricefx
documentation@pricefx.com
Languages
Servers
Mock server
https://api.pricefx.com/_mock/openapi/reference/pricefx-server_openapi
URL:
https://{baseUrl}/pricefx/{partition}

General

Operations

Products

Operations

Product Extensions

Operations

Product Image

Operations

Customers

Operations

Customer Extensions

Operations

Sellers

Operations

Seller Extensions

Operations

Condition Records

Operations

Competition Data

Operations

Imports

Operations

Data Change Requests

Operations

Lookup Tables / Company Parameters

Operations

User Admin

Operations

Authentication

Operations

Admin Tools

Operations

Logics

Operations

Logs

Operations

Custom Forms

Operations

Calculated Field Sets

Operations

Jobs & Tasks

Operations

Advanced Configuration Options

Operations

Data Manager

Operations

Query a Data Manager Object

Request

Returns a list of objects that match the criteria specified in the request.

Security
basic or X-PriceFx-jwt
Query
timeoutstring

Set this parameter to override the default timeout (60 seconds) of the query. The maximum allowed timeout is 300 seconds. The default timeout value and the maximum timeout value can be configured in the backend. Setting higher timeout can be useful, for example, when a query takes long time (e.g., when processing large tables).

Default "60"
Example: timeout=20
Bodyapplication/json
operationTypestringnon-empty
startRownumber
endRownumber
sortByArray of objects
textMatchStylestringnon-empty
dataobjectrequired
data.​queryobject
oldValuesany
curl -i -X POST \
  -u <username>:<password> \
  'https://api.pricefx.com/_mock/openapi/reference/pricefx-server_openapi/datamart.query?timeout=20' \
  -H 'Content-Type: application/json' \
  -d '{
    "operationType": "fetch",
    "startRow": 0,
    "endRow": 100000,
    "sortBy": [],
    "textMatchStyle": "exact",
    "data": {
      "query": {
        "name": null,
        "datamart": "DM.TransactionsDM",
        "label": "Series1",
        "source": "DM.TransactionsDM",
        "projections": {
          "bandBy": {
            "alias": "bandBy",
            "expression": "CustomerID",
            "function": "",
            "parameters": {},
            "name": "CustomerID",
            "label": "CustomerID"
          },
          "bubbleSize": {
            "alias": "bubbleSize",
            "expression": "SUM({field})",
            "function": null,
            "parameters": {
              "field": "Quantity",
              "quantity": "Quantity",
              "base": "ListPrice"
            },
            "name": "Quantity",
            "label": "∑Quantity",
            "default": null,
            "advancedProjection": true,
            "formatString": "∑{field}"
          },
          "bubbleSize_n": {
            "alias": "bubbleSize_n",
            "expression": "SUM({field})",
            "function": null,
            "parameters": {
              "field": "Quantity",
              "quantity": "Quantity",
              "base": "ListPrice"
            },
            "name": "Quantity",
            "label": "∑Quantity (norm)",
            "default": null,
            "advancedProjection": true,
            "formatString": "∑{field}"
          },
          "groupBy": {
            "alias": "groupBy",
            "expression": "Region",
            "function": "",
            "parameters": {},
            "name": "Region",
            "label": "Region"
          },
          "x": {
            "alias": "x",
            "expression": "SUM({field})",
            "function": null,
            "parameters": {
              "field": "BasePrice",
              "quantity": "Quantity",
              "base": "ListPrice"
            },
            "name": "BasePrice",
            "label": "∑BasePrice",
            "default": null,
            "advancedProjection": true,
            "formatString": "∑{field}"
          },
          "y": {
            "alias": "y",
            "expression": "SUM({field})/SUM({quantity})",
            "function": null,
            "parameters": {
              "field": "ListPrice",
              "quantity": "Quantity",
              "base": "ListPrice"
            },
            "name": "ListPrice",
            "label": "∑ListPrice/∑Quantity",
            "default": null,
            "advancedProjection": true,
            "formatString": "∑{field}/∑{quantity}"
          }
        },
        "options": {
          "currency": "EUR",
          "regression": [
            "y",
            "x"
          ],
          "distribution": [
            "m1",
            "x",
            "y"
          ],
          "normalization": [
            "bubbleSize_n"
          ]
        },
        "filter": {
          "criteria": [],
          "operator": "and",
          "_constructor": "AdvancedCriteria"
        },
        "aggregateFilter": null,
        "dimensionFilters": [],
        "limit": null,
        "rollup": true,
        "sortBy": []
      }
    },
    "oldValues": null
  }'

Responses

OK

Bodyapplication/json
responseobject
Response
application/json
{
  "response": {
    "node": "e2e-staging-node",
    "startRow": 0,
    "data": [ … ],
    "endRow": 3,
    "totalRows": 3,
    "status": 0
  }
}

SQL Query a Data Manager Object

Request

Processes and queries data from Price Analyzer or Price Optimizer. The queries can be aggregated, do various computations including statistical projections. Supports filtering.

Security
basic or X-PriceFx-jwt
Query
timeoutstring

Set this parameter to override the default timeout (60 seconds) of the query. The maximum allowed timeout is 300 seconds. The default timeout value and the maximum timeout value can be configured in the backend. Setting higher timeout can be useful, for example, when a query takes long time (e.g., when processing large tables).

Default "60"
Example: timeout=20
Bodyapplication/json

sources that SQL can use are query definitions. The sources become CTEs (Common Table Expression) in the final SQL. These are then used as a reference in the main query instead of referring to the actual tables directly. The request example compares the volume by month 2019 to 2020.

operationTypestring
startRowinteger
endRowinteger
dataobject
curl -i -X POST \
  -u <username>:<password> \
  'https://api.pricefx.com/_mock/openapi/reference/pricefx-server_openapi/datamart.sqlquery?timeout=20' \
  -H 'Content-Type: application/json' \
  -d '{
    "operationType": "fetch",
    "startRow": 0,
    "endRow": 10000,
    "data": {
      "sources": {
        "s0": {
          "query": {
            "source": "DM.InvoicesDM",
            "projections": {
              "Month": {
                "expression": "Invoice_DateMonth"
              },
              "Quantity": {
                "expression": "SUM({field})",
                "parameters": {
                  "field": "Sales_Quantity"
                },
                "advancedProjection": true
              }
            },
            "options": {
              "currency": "EUR"
            },
            "filter": {},
            "aggregateFilter": null,
            "dimensionFilters": [
              {
                "Invoice_DateYear": [
                  "2019"
                ]
              }
            ],
            "rollup": true
          }
        },
        "s1": {
          "query": {
            "source": "DM.InvoicesDM",
            "projections": {
              "Month": {
                "expression": "Invoice_DateMonth"
              },
              "Quantity": {
                "expression": "SUM({field})",
                "parameters": {
                  "field": "Sales_Quantity"
                },
                "advancedProjection": true
              }
            },
            "options": {
              "currency": "EUR"
            },
            "filter": {},
            "aggregateFilter": null,
            "dimensionFilters": [
              {
                "Invoice_DateYear": [
                  "2020"
                ]
              }
            ],
            "rollup": true
          }
        }
      },
      "with": {
        "w0": "SELECT Month FROM (VALUES ('\''M01'\''), ('\''M02'\''), ('\''M03'\''), ('\''M04'\''), ('\''M05'\''), ('\''M06'\''), ('\''M07'\''), ('\''M08'\''), ('\''M09'\''), ('\''M10'\''), ('\''M11'\''), ('\''M12'\'')) AS t(Month)"
      },
      "sql": "SELECT w0.Month \"Month\", SUM(s0.quantity) \"Volume 2019\", SUM(s1.quantity) \"Volume 2020\", 100 * (SUM(s1.quantity) / SUM(s0.quantity) - 1.0) \"VolumeΔ%\" FROM w0 LEFT OUTER JOIN s0 ON w0.Month = RIGHT(s0.Month,3) LEFT OUTER JOIN s1 ON w0.Month = RIGHT(s1.Month,3) GROUP BY w0.Month ORDER BY w0.Month"
    }
  }'

Responses

OK

Bodyapplication/json
responseobject
Response
application/json
{
  "response": {
    "node": "e2e-staging-node",
    "startRow": 0,
    "data": [ … ],
    "endRow": 3,
    "totalRows": 3,
    "status": 0
  }
}

List Rollups

Request

Retrieves a list of Datamart Rollups based on filter settings.

Security
basic or X-PriceFx-jwt
Bodyapplication/json
operationTypestringnon-empty
startRownumber
endRowany
textMatchStylestringnon-empty
dataobject
oldValuesany
curl -i -X POST \
  -u <username>:<password> \
  https://api.pricefx.com/_mock/openapi/reference/pricefx-server_openapi/datamart.getrollups \
  -H 'Content-Type: application/json' \
  -d '{
    "operationType": "fetch",
    "startRow": 0,
    "endRow": null,
    "textMatchStyle": "substring",
    "data": {
      "fieldName": "owner",
      "operator": "equals",
      "value": null,
      "_constructor": "AdvancedCriteria"
    },
    "oldValues": null
  }'

Responses

OK

Bodyapplication/json
responseobject
Response
application/json
{
  "response": {
    "status": 0,
    "startRow": 0,
    "node": "string",
    "data": [ … ],
    "endRow": 0
  }
}

Actions

Operations

Action Types

Operations

Price Lists

Operations

Manual Price Lists

Operations

Live Price Grids

Operations

Calculation Grids

Operations

Visual Configuration

Operations

Quotes

Operations

Contracts (Agreements & Promotions)

Operations

Visual Configuration

Operations

Rebate Agreements

Operations

Rebate Calculations

Operations

Rebate Record Group

Operations

Sales Compensations

Operations

Claim Types

Operations

Claims

Operations

Optimization

Operations

Workflow

Operations

Workflow Delegation

Operations

Attachments

Operations

Product Image

Operations

Configuration

Operations

Internationalization

Operations

Metadata

Operations

MCP

Operations

Clicmanager

Operations

Comments

Operations

Notifications

Operations

Heartbeat

Operations

Key-Value Store

Operations

Entities

otherSchemas

Schemas