Data Sources¶

CxReports allows you to connect to various data sources to retrieve data for use in your reports. This flexibility ensures that you can integrate data from APIs, JSON or CSV files, SQL databases, and even perform custom computations using JavaScript. Understanding how to set up and manage these data sources is crucial for creating dynamic and informative reports.

Data sources can be categorized into global or report-specific types:

Report-Specific Data Sources: These are tied to individual reports and are defined within the context of those reports.
Reusable Data Sources: Designed to be easily attached to multiple reports without the need to redefine them, enhancing efficiency and maintainability.
Global Data Sources: These are available across all reports within your application, allowing you to maintain consistency and reduce redundancy in data management.

To manage data sources, navigate to the Report tab in the ribbon and click on Data Sources. Here, you can view existing sources or add new ones.

Configuring Data Sources¶

When setting up a new data source, you need to specify several key attributes:

Name: A unique identifier for the data source. This name is used to reference the data within the $data object in your reports. For example, $data.people refers to data from a source named 'people'.
Type: The type of data source (SQL, API, JSON, CSV, or JavaScript) determines the additional configuration options required.
Description (optional): Free text describing what the data source returns, shown in the data source list.

SQL Data Sources¶

For SQL data sources, you must provide a SQL query to fetch data from your database. It's important to map any parameters used in your query to ensure they are handled correctly.

API Data Sources¶

API data sources fetch data from external APIs. Configure these by specifying the URL, choosing between GET or POST methods, and setting necessary headers and body content.

Two optional JavaScript hooks let you compute the request or reshape the response instead of configuring them statically:

Request Builder — runs before the request and overrides parts of it.
Response Mapper — runs after the request and decides what gets stored.

Each has its own Type selector next to the editor. Leave it as None and the hook is ignored; set it to Custom to run your function.

Both receive the same three values:

Value	What it holds
`$params`	The report's parameter values.
`$data`	The results of other data sources, by name.
`$dict`	The report's dictionary entries.

Reading $data means depending on another data source. Declare it under Dependencies, or your hook runs once against nothing and never runs again — see Waiting for another data source.

Request Builder¶

Selecting a Request Builder hands the body and headers to it. The Body and Headers editors disappear when you choose Custom, and what they held is no longer sent — the builder decides. Headers that come from an API connection still apply, and the builder can override them by returning a header of the same name.

The URL stays where it is, so returning no endpoint means the configured URL is used.

Return an object with the parts of the request you want to set:

Key	Type	Effect
`endpoint`	string	Replaces the configured URL or path.
`query`	object	Appended to the URL as query-string parameters. Keys and values are URL-encoded, and entries whose value is `null` are skipped.
`headers`	array of `{ key, value }`	Merged with the configured headers. When a name appears in both, the builder's value wins.
`body`	string or object	Replaces the configured body. A string is sent as it is; anything else is converted to JSON.

({ $params, $data, $dict }) => {
    return {
        endpoint: `/accounts/${$params.accountId}/transactions`,
        query: { from: $params.startDate, to: $params.endDate },
        body: { includeClosed: false }
    };
}

Response Mapper¶

Receives the parsed response as its first argument and returns the value stored under the data source's name. Use it to unwrap an envelope, flatten a nested structure, or combine the response with data you already loaded.

(response, { $params, $data, $dict }) => {
    // the API wraps its rows in a result object
    return response.result.map(row => ({
        ...row,
        accountName: $data.accounts.find(a => a.id === row.accountId)?.name
    }));
}

Whatever you return is what the report sees, so returning nothing leaves the data source empty.

Waiting for another data source¶

When the request body is built from another data source — common when queries are sent to an API gateway rather than to a database directly — that data source must be declared as a dependency, and the request builder must say when it is not ready yet.

Declaring the dependency is what makes the data source run again after the other one loads. It does not stop the first attempt: every data source runs once as soon as the report opens, before anything else has finished. If that first attempt sends an incomplete request, the failure stops the whole export, and the retry that would have worked never gets used. The result is an empty or failed PDF.

Return false from the request builder to say "not ready". No request is sent, nothing is stored, and the report keeps waiting. As soon as a declared dependency loads, the builder runs again:

({ $params, $data, $dict }) => {
    // accounts hasn't loaded yet - don't send anything
    if (!$data.accounts) return false;

    return {
        body: JSON.stringify({
            sql: `SELECT * FROM transactions WHERE account_id IN (${$data.accounts.map(a => a.id).join(',')})`
        })
    };
}

Only false gates the request. Returning nothing is a normal result — the request goes out with whatever the builder did set, plus the configured URL and any API connection headers.

Check the condition against something the data source actually needs. If the gate never opens — for example it waits on a data source that was never declared as a dependency — the report waits until the export times out.

Note

This applies to the request builder of a data source. Lookup parameters do not gate: in the browser false is sent as-is, and when their options are resolved on the server the request stops with an error.

Hooks in scheduled jobs¶

When a report is produced by a scheduled job, both hooks run on the server instead of in the browser, and that changes what they can do:

Only $params is available. $data and $dict are undefined, so a hook that reads another data source fails or silently produces nothing.
The Type selector is ignored — a Request Builder or Response Mapper that has code in it runs even when its type is set to None.
Returning false from the Request Builder cannot gate anything, because job data sources are independent and cannot depend on each other. The run stops with an error naming the data source, rather than sending a request the builder deliberately held back.

A hook that reads $data therefore works in the report editor and in preview, but not in a scheduled job. Keep hooks on $params alone if the report is also going to be delivered by a job.

JavaScript Data Sources¶

JavaScript data sources are useful for computations, such as summing values or combining data from multiple sources. Here’s a basic template for a JavaScript data source:

({$data, $params, $dict}) => {
  let result = null;
  // perform calculations
  return result;
}

Data Transformation Data Sources¶

Data Transformation data sources reshape data you already load, without writing code. Pick a source data source and add steps; each step performs one operation on the output of the previous step and shows a preview of its result:

Select columns — keep, rename and reorder columns
Computed column — add a column from an expression such as {$record.qty} * {$record.price}
Filter — keep rows matching one or more conditions; each condition compares to a constant, a parameter or an expression such as {$params.threshold} + 1, and a condition whose parameter or expression yields nothing is ignored
Sort and Limit
Group & aggregate — group by one or more columns and compute sum, count, distinct count, average (optionally weighted), min and max; optionally keep only the top groups and collapse the rest into a single "Other" group
Join — add columns from another data source by matching keys (left, inner, right or full); a join can appear at any position in the list
Union — append the rows of another data source, matching columns by name
Partition — split rows into one object per key, each carrying its rows, so a chart or section can repeat per group

Every data source a transformation reads, including join and union sources, must be declared as a dependency of the transformation. Transformations run in the browser, like JavaScript data sources.

Transforming data on a single element¶

When only one element needs a tweak, you do not have to create a separate data source. Wherever you pick a data source expression — a data table, a pie chart, a chart series, a repeating element or a repeating table row — the picker comes with the same set of buttons: preview the rows the element receives, edit the data source, create one, and Transform data, which opens the same step builder for that element alone. Everything comes from the picked expression, so join and union are not offered; sort, limit, filter (including filters bound to parameters), computed columns, grouping with top N + Other, and partitioning are. The button shows how many steps are applied, and Clear steps (or removing every step) restores the raw data. Generate Columns on a data table follows the transformed output. Chart series no longer have a separate Sort Field; a sort step does the same, and existing series sorting is converted automatically.

CSV and JSON Data Sources¶

These data sources are ideal for incorporating static data into your reports. Simply paste the CSV or JSON content directly and reference it within your report.

MongoDB Data Sources¶

MongoDB data sources allow you to query MongoDB databases using JavaScript functions. These functions take report parameters as arguments and construct commands with additional filtering or pipeline logic. Here are some examples:

Find CommandAggregate CommandDistinct Command

({ $params }) => { 
    let filter = {};
    if ($params.genre)
        filter.genres = $params.genre;
    return {
        find: 'movies',        
        filter
   };
}

({ $params }) => { 
    let $match = {};
    if ($params.genre)
        $match.genres = $params.genre;
    return {
        aggregate: 'movies',        
        cursor: {},
        pipeline: [{ $match }, ...]
   };
}

({ $params }) => {     
    return {
        distinct: 'movies',
        key: 'genres'
    };
}

For a comprehensive list of MongoDB commands, refer to the MongoDB Command Reference.

Please get in touch with support for access to a demo environment offering concrete examples.

Dependencies¶

Data sources can depend on parameters or other data sources. Changes in any dependent element trigger the data source to reload, ensuring your report data is always up to date based on the latest inputs.

Dependencies belong to the report's data source, not to the definition behind it, so a data source that reuses a definition (Type set to Reusable data source) declares its own. Each report that reuses the same definition picks its own parameters and data sources. This matters most for a reusable JavaScript data source: reading $data.other in its code is not a declaration, so without the dependency it runs once against whatever happened to be loaded and never runs again.

Previewing Results¶

Use the Preview button at the bottom of the data source editor to execute the current definition and inspect the result in the Data Explorer — without saving or closing the window. Parameter mappings are evaluated against the report's current parameter values, so the preview reflects exactly what the report would receive. This makes it easy to iterate on a query until the data looks right.

For API data sources the preview runs the same steps as the report itself: the configured headers and body are sent, a custom request builder is evaluated against the current parameters and data before the call, and a custom response mapper is applied to the result. If the request builder returns false (its inputs are not available yet), nothing is sent and the preview tells you so. Parameter lookups configured against an API still have their own Preview button next to the endpoint field, where you type the URL parameter values by hand.

By effectively managing and configuring data sources, you can leverage the full power of CxReports to create dynamic, data-driven reports tailored to your needs.