SUM values incorrect on Trends

Wayland Games commented 4 days ago

Hi there,

I've just inherited our current KoolReport project and I'm in the process of upgrading our KoolReport Pro from version 5.7.1 to the latest (6.6.3) and the dashboard module from version 2.0.0 to 4.8.3, and upgrading the box from PHP 7.4 to 8.2.

I'm currently in the process of comparing the two systems to make sure the data is the same and I've noticed that on the Trend widgets the sum value across the period is much lower than when I'm adding up the individual data points, in fact it doesn't even correlate to any single data point.

Here is the Raw SQL I'm using in my dataSource:

SELECT
CAST(oh_datetime AS DATETIME2(0)) AS 'oh_datetime',
CAST(SUM(oli_gross) AS DECIMAL(9,2)) AS 'total'
FROM order_header
LEFT JOIN order_line_item ON (oh_id = oli_oh_id)
WHERE
oh_os_id IN (1,2,3,4,5,6)
AND
oh_ec_id IS NOT NULL
GROUP BY
CAST(oh_datetime AS DATETIME2(0))

And here is the fields() method:

protected function fields(): array
	{
		return [
			$this->group(DateTime::create("oh_datetime")),
			$this->sum(
				Currency::create("total")
					->label('Order Total')
					->GBP()
					->symbol()
			),
		];
	}

Let me know if you need anymore information and I hope that makes sense, I'm unsure what you would require!

Kind regards, Sam

Sebastian Morales commented 3 days ago

Pls remove SUM and GROUP BY parts and use the following raw sql for your Trend widget's dataSource method:

    protected function dataSource()
    {
        return YourDB::rawSQL("
SELECT
CAST(oh_datetime AS DATETIME2(0)) AS 'oh_datetime',
CAST(oli_gross AS DECIMAL(9,2)) AS 'total'
FROM order_header
LEFT JOIN order_line_item ON (oh_id = oli_oh_id)
WHERE
oh_os_id IN (1,2,3,4,5,6)
AND
oh_ec_id IS NOT NULL
        ");
    }

Let us know how this works for you. Tks,

Wayland Games commented 3 days ago

Hi Sebastian,

I've just tried that and it's still not showing the correct value.

However! I've just removed the ->run() method from the chain and now it works! How bizarre. What exactly does this method do and is it needed?

Kind regards, Sam

Sebastian Morales commented 3 days ago

Oh, great to hear you made it work! Without the ->run() method, dataSource() returns an object while with ->run() it returns a scalar or an array value. It seems in this case the Trend class needs an object dataSource.

Wayland Games commented 3 days ago

Ah that makes sense! Thank you very much for your assistance :)

KoolReport's Forum

SUM values incorrect on Trends #3455

Build Your Excellent Data Report