How to create yearly totals chart from simple orders table?

Need Help
#1

Hi,

I have a very simple table:

id INT
net_price DECIMAL
order_date DATE

I want to create a Dashboard data source and widget that displays one bar per year (e.g. 2023, 2024, 2025), where each bar represents the sum of net_price grouped by order_date (year).

However, the documentation does not include an example of how to correctly configure a date-based dimension for grouping (year/month) or how to make the widget use order_date as a date field.

How should I define the Data Source (dimension + metric) and configure the widget to achieve yearly grouping of orders by order_date?

#3

Hey @Renatio

Can you show us what you have so far from following the documenation?

#4 
class OrdersDataSource extends ReportDataSourceBase
{
    const DIMENSION_ORDER_DATE = ReportDimension::CODE_DATE;

    const METRIC_TOTAL_AMOUNT = 'total_amount';

    public function __construct()
    {
        $this->registerDimension(new ReportDimension(
            self::DIMENSION_ORDER_DATE,
            'order_date',
            'renatio.orders::lang.field.order_date',
            'order_date'
        ));

        $this->registerMetric(new ReportMetric(
            self::METRIC_TOTAL_AMOUNT,
            'renatio_orders_orders.net_price',
            'renatio.clients::lang.field.total_amount',
            ReportMetric::AGGREGATE_SUM,
            [
                'style' => 'currency',
                'currency' => 'EUR',
            ],
        ));
    }

    protected function fetchData(ReportFetchData $data): ReportFetchDataResult
    {
        if (! in_array($data->dimension->getCode(), [self::DIMENSION_ORDER_DATE], true)) {
            throw new SystemException('Invalid dimension');
        }

        $reportQueryBuilder = new ReportDataQueryBuilder(
            'renatio_orders_orders',
            $data->dimension,
            $data->metrics,
            $data->orderRule,
            $data->dimensionFilters,
            $data->limit,
            $data->paginationParams,
            $data->groupInterval ?: 'year',
            $data->hideEmptyDimensionValues,
            Carbon::parse('2020-01-01'),
            now(),
            $data->startTimestamp,
            'order_date',
            null,
            $data->totalsOnly
        );

        return $reportQueryBuilder->getFetchDataResult($data->metricsConfiguration);
    }
}

I also tried adding this:

$reportQueryBuilder->onConfigureQuery(
    function (QueryBuilder $query, ReportDimension $dimension, array $metrics) {
        $query->addSelect([
            Db::raw('MAX(order_date) as oc_dimension_label'),
        ]);
    }
);

The error:

"SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wanger_system.renatio_orders_orders.order_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql, SQL: select DATE(order_date) AS oc_dimension, order_date AS oc_dimension_label, sum(renatio_orders_orders.net_price) as oc_metric_total_amount from renatio_orders_orders where DATE(order_date) is not null and deleted_at is null and order_date between 2020-01-01 00:00:00 and 2025-11-27 23:59:59 group by oc_dimension order by oc_dimension asc)" on line 824 of /Users/michalplodowski/Herd/wanger-system/vendor/laravel/framework/src/Illuminate/Database/Connection.php
#5

Hey @Renatio,

This was a confirmed bug. The fix will be available in the next patch release (v4.0.20).

For date dimensions you typically don’t need a labelColumnName at all - the date is the label and the UI formats it appropriately. So your code could be simplified to:

$this->registerDimension(new ReportDimension(
    self::DIMENSION_ORDER_DATE,
    'order_date',
    'renatio.orders::lang.field.order_date'
));
#6

Thank you, I will check this out when the fix is released.

#7

Hi, I have almost the same issue in OctoberCMS 4.1.8.

I’m trying to display simple line chart of registrations by period of time. But it works correctly only when I set group=day in OCMS dashboard Interval widget.

How to make it work for week/month/year/quarter grouping?

<?php namespace Acme\App\DataSources;

use Dashboard\Classes\ReportDataSourceBase;
use Dashboard\Classes\ReportData;
use Dashboard\Classes\ReportDimension;
use Dashboard\Classes\ReportFetchData;
use Dashboard\Classes\ReportFetchDataResult;
use Dashboard\Classes\ReportMetric;
use Dashboard\Classes\ReportQueryBuilder;

use Acme\App\Models\Consumer;

class ConsumersDataSource extends ReportDataSourceBase
{
    const DIMENSION_DATE = 'date';
    const METRIC_REGISTRATIONS = 'registrations';

    public function __construct()
    {

        $this->registerDimension(new ReportDimension(
            ReportDimension::CODE_DATE, // 'date'
            'created_at',
            "Date"
        ));

        $this->registerMetric(new ReportMetric(
            self::METRIC_REGISTRATIONS,
            'id',
            "Registrations",
            ReportMetric::AGGREGATE_COUNT
        ));

    }

    protected function fetchData(ReportFetchData $data): ReportFetchDataResult
    {

        return $this->fetchGraphData($data);
    }

    protected function fetchGraphData(ReportFetchData $data): ReportFetchDataResult
    {
        return ReportQueryBuilder::table('acme_app_consumers')
        ->dateColumn('acme_app_consumers.created_at')
        ->dimension($data->dimension)
        ->metrics($data->metrics)
        ->dateRange($data->dateStart, $data->dateEnd, 'acme_app_consumers.created_at')
        ->get($data->metricsConfiguration);
    }
}