Plots#

The Plots module is a Component in CtrlPortals that enables the creation of dynamic, SQL-driven charts and graphs. It allows you to visualize data from external databases which makes it ideal for building dashboards and reports that reflect real-time or aggregated data from your M-Files environment or other connected systems.

SQL Data Source collection#

Before you can use SQL-based plots in the Plots module, you need to define one or more SQL data sources in your CtrlPortals configuration.

Configuration > Data Sources > SqlDataSources

Required fields#

Name A unique identifier that must exactly match the DataSourceName used in your plot’s SQLConfiguration.
Type The type of SQL data source. Currently, only MSSQL is supported.
Server Name The name or IP address of the SQL server.
Database Name The name of the target database to connect to.
User Name The user name for connecting to the SQL database. This is required for authentication.
Password The password for the SQL user. This is required for authentication.

🧩 Configuration Options#

Each plot is defined within a Segment and includes the following configuration options:

Title: The title displayed above the chart.
Width: Defines the width of the segment (e.g., "eight" for half-width).
Layout: Controls the visual layout of the chart, such as legend position and bar stacking. (optional)
Visuals: A list of trace objects that define the data source, chart type, and data mapping.

🎨 Layout#

The Layout object allows you to customize how your chart looks.

Common Layout Options#

Bar Mode
Controls how bars are displayed in bar charts.
- Use "stack" to stack bars on top of each other.
- Leave empty or use "group" to show bars side by side.
Legend Vertical Alignment Controls where the legend appears vertically. Options include: "top" or "bottom"
Legend Horizontal Alignment Controls where the legend appears horizontally. Options include: "left" or "right"
Legend Horizontal If enabled, the legend will be displayed horizontally instead of vertically.

🎛️ Visuals#

The Visuals property defines the list of charts or graphs that will be displayed within a single plot. Each visual represents a specific data visualization — for example a stacked bar chart, or a pie chart.

You can configure one or multiple visuals per plot, enabling you to combine different chart types in a single view. For example, you might display a stacked bar chart alongside a line chart to compare trends and totals simultaneously.

Each visual includes:

Name: A label for the trace, useful in multi-series charts.
Visual Type: The type of visual to render.
Use Secondary Y-Axis: (Optional) Set this to "Yes" if you want the visual to use a secondary Y-axis. This is helpful when combining visuals with different value ranges in the same plot (e.g. Bar chart wogether with Line chart).
Data Source Type: This tells the system where the data is coming from. Must be "SQL" for SQL-based plots.
SQL Configuration: Configuration for retrieving and mapping SQL data.

🗃️ SQL Configuration#

The SQL Configuration object defines how data is retrieved from a SQL database and how it should be mapped for visualization in the plot.

This is the only part of the configuration where you need to write SQL manually.

Configuration Fields#

Data Source Name The name of the SQL data source is case-sensitive so it should match the one you added inside the "SQLDataSources".
Plot Query The SQL query used to fetch the data. This must be written manually and should return the columns you want to use for labels, values, and optional grouping.
Plot Parameters A list of parameters used in the SQL query. These can be used to make your query dynamic (e.g., filtering by date).
Value Column The name of the column in your PlotQuery that contains the numeric values to be plotted (e.g., counts, totals, percentages).
Label Column Specifies the column used for labeling data points in the chart:
- In bar charts, these labels appear on the x-axis (e.g., categories, object type names).
- In pie charts, labels define the slices of the pie.
Label ID Column (optional) An internal identifier for each label. This is required if you want to enable interactions when clicking on the plot to fetch more details via the "Select Query". It allows to uniquely identify the selected item and retrieve related data (e.g., object ID).
Trace Column (optional) Defines how your data is split into multiple series or groups within the same chart (e.g., by workflow state, object type or status).
- In a stacked bar chart, this could represent different workflow states stacked within each object type.
- In a line chart, each trace could represent a different object type over time.
⚠️ The corresponding column names specified in the Trace Column need to be present in the "Plot Query".
Trace ID Column (optional) An internal identifier for each trace series. Required if you want to support interactive clicking that distinguishes one clicked trace from the other:
- For example, in a stacked bar chart grouped by customer (Label Column) and workflow state (Trace Column), you’ll need both Label ID Column (customer ID) and Trace ID Column (workflow state ID) to identify which part of the chart was clicked.
Without Trace ID Column, you cannot use @labelId or @traceId in your "Select Query".
Select Query (optional): Used to retrieve detailed data for a specific trace or data point when users interact with the chart (e.g., clicking on a bar or line).

You can use the following placeholders in your SQL query:
- @parameterName — for any custom parameters defined in your configuration.
- @labelId, @labelName — based on the selected label.
- @traceId, @traceName — based on the selected trace (if applicable).
⚠️ To use these placeholders, the corresponding columns (Label ID Column, Label Column, Trace ID Column, Trace Column) must be present in the "Plot Query" result. Without them, the system cannot resolve the placeholder values during interaction.
Select Parameters (optional): Define the parameters that you are able to use in the "Select Query".

Plot elements

Plot Parameters & Select Parameters#

Both Plot Parameters and Select Parameters are used to inject dynamic values into SQL queries.

These parameters allow you to tailor data retrieval based on the current context—such as the object being viewed, the logged-in employee, or other user-specific data. This is especially useful in customer portals or dashboards where data needs to be filtered by customer, department, or user role.

Both parameters are defined using:

Parameter Name The name of the parameter used in the SQL query:
- Must match the parameter name in the SQL query.
- Must be unique within the same query.
Parameter Type Defines the source of the parameter value.
- Property: Retrieves a value from a property definition.
- Placeholder: Uses a predefined placeholder value (e.g., @labelId, @traceName).
Parameter Context Specifies where the parameter value should be retrieved from:
- Object: Uses the current object in context.
⚠️ This feature is only available inside portals, not dashboards. For example, you can use it to retrieve a customer code from the current Customer object in a Portal, allowing you to query related data from an external ERP system.
- Employee: Uses the employee object of the currently logged-in user.
Parse Value As (optional) Defines how the parameter value should be interpreted in the SQL query.

Examples#

1. Display Contracts by Workflow State#

This example shows how to count documents classified as “Contract or Agreement” in each workflow state — useful for tracking contract progress.

Plot Query#

SELECT
    ws.Name AS LabelColumn,
    ws.ID AS LabelIDColumn,
    COUNT(d.[Workflow State_ID]) AS ValueColumn
FROM
    Document d
INNER JOIN
    [Workflow State] ws ON d.[Workflow State_ID] = ws.ID
WHERE
    d.Class_ID = 4
GROUP BY
    ws.Name, ws.ID
ORDER BY
    ValueColumn DESC;

🔍 Explanation

ws.Name AS LabelColumn:
Retrieves the name of each workflow state (e.g., Draft, Approved).
ws.ID AS LabelIDColumn:
Retrieves the internal ID of the workflow state, used for linking and filtering.
COUNT(d.[Workflow State_ID]) AS ValueColumn:
Counts how many documents are in each workflow state.
INNER JOIN [Workflow State] ws ON d.[Workflow State_ID] = ws.ID:
This retrieves information about the documents’ workflow states from the workflow state table, which provides us with the name of the workflow state.
WHERE d.Class_ID = 4:
Filters to only include documents of class “Contract or Agreement”. You can change this number to analyze a different document class (e.g., invoices, contracts, reports).
GROUP BY:
This is the most important step for summarizing data. It creates a separate pile for each workflow state (e.g., all “Draft” documents go in one pile, all “Approved” in another). The COUNT function then counts how many documents are in each pile.
ORDER BY ValueColumn DESC:
Sorts the results from most to least documents. It’s used for charts because it puts the tallest bar first, making it easy to read.

Select Query#

Those queries are used when a user clicks on a chart element (e.g., a bar representing a workflow state). It retrieves detailed document data and related customers, which is then displayed in a table view.

1. Show Objects Clickable Names#

This query returns documents in a selected workflow state.

SELECT (
        SELECT 
            ID AS id,
            0 AS type,
            [Name or title] AS name
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS Name
FROM [dbo].[Document] d
WHERE [Workflow State_ID] = @labelId