Hi,

I'm trying to implement the Trend class. Within the datasource function I'm executing rawSQL. It appears the SQLHandler is adding a DATE_FORMAT function in the case of MSSQL integration and this is resulting in a function not found error being returned.

Full call stack, if it helps.

Message: Query Error >> [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'DATE_FORMAT' is not a recognized built-in function name.] >> SELECT DATE_FORMAT(oh_datetime,'%Y-%m-%d') AS oh_datetime_day, SUM(total) AS total_sum FROM ( select cast(oh_datetime as datetime) as 'oh_datetime', oh_cust_order_ref, sum(oli_gross) as 'total', ec_description from order_header left join order_line_item ON (oh_id = oli_oh_id) left join ecommerce_session ON (oh_ec_id = ec_id) where oh_os_id IN (1,2,3,4,5,6) and ec_description IS NOT NULL group by cast(oh_datetime as date), oh_cust_order_ref, ec_description order by cast(oh_datetime as date), ec_description ) as t WHERE (oh_datetime >= '2021-11-01 00:00:00' AND oh_datetime <= '2021-11-30 23:59:59') GROUP BY oh_datetime_day Line: 433 File: /var/www/html/waylandbi/vendor/koolreport/core/src/datasources/PdoDataSource.php Collapse #0: /var/www/html/waylandbi/vendor/koolreport/core/src/core/DataSource.php Line 108 : start(null) #1: /var/www/html/waylandbi/vendor/koolreport/core/src/core/Node.php Line 365 : requestDataSending(null) #2: /var/www/html/waylandbi/vendor/koolreport/dashboard/sources/PDOSource.php Line 106 : requestDataSending(null) #3: /var/www/html/waylandbi/vendor/koolreport/dashboard/data/SQLHandler.php Line 140 : run(null) #4: /var/www/html/waylandbi/vendor/koolreport/dashboard/metrics/Trend.php Line 133 : executes(null) #5: /var/www/html/waylandbi/vendor/koolreport/dashboard/Widget.php Line 205 : render(null) #6: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRenderItem.php Line 16 : view(null) #7: /var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/containers/Row.view.php Line 11 : renderItem(null) #8: /var/www/html/waylandbi/vendor/koolreport/core/src/KoolReport.php Line 502 : include(["/var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/containers/Row.view.php"]) #9: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRender.php Line 10 : render(null) #10: /var/www/html/waylandbi/vendor/koolreport/dashboard/containers/Row.php Line 47 : render(null) #11: /var/www/html/waylandbi/vendor/koolreport/dashboard/containers/Container.php Line 114 : render(null) #12: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRenderItem.php Line 18 : view(null) #13: /var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/Dashboard.view.php Line 10 : renderItem(null) #14: /var/www/html/waylandbi/vendor/koolreport/core/src/KoolReport.php Line 502 : include(["/var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/Dashboard.view.php"]) #15: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRender.php Line 12 : render(null) #16: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 320 : render(null) #17: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 291 : render(null) #18: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 271 : view(null) #19: /var/www/html/waylandbi/vendor/koolreport/dashboard/TAction.php Line 35 : actionIndex(null) #20: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 230 : action(null) #21: /var/www/html/waylandbi/vendor/koolreport/dashboard/pages/Main.php Line 137 : handle(null) #22: /var/www/html/waylandbi/vendor/koolreport/dashboard/Application.php Line 216 : handle(null) #23: /var/www/html/waylandbi/vendor/koolreport/dashboard/Application.php Line 148 : handle(null) #24: /var/www/html/waylandbi/index.php Line 11 : run(null)

Excellent! Thanks for your help.

Superb, thank you very much indeed!

Sorry to be a pain. Trying to update via composer, it shows some packages as being outdated but when trying to update the packages directly I get the following error:

$ composer require koolreport/querybuilder:3.1.0 -W ./composer.json has been updated Running composer update koolreport/querybuilder --with-all-dependencies Loading composer repositories with package information Updating dependencies Your requirements could not be resolved to an installable set of packages.

Problem 1

- koolreport/pro is locked to version 5.7.1 and an update of this package was not requested.
- koolreport/pro 5.7.1 requires koolreport/querybuilder 3.0.0 -> found koolreport/querybuilder[3.0.0] but it conflicts with your root composer.json require (3.1.0).

Installation failed, reverting ./composer.json and ./composer.lock to their original content.

Is there an updated version of pro which relies on the updated versions of the dependencies?

(Apologies also for being a composer noob - if there's something I'm missing)

Will try that, thank you very much for your help

ETA: That worked brilliantly, now all up to date, thank you.

I'm noticing I still have to use the ->run() method within the Trend class itself for it to render without error. If I remove the ->run() method from the end of the query then it tries to GROUP BY a field its declaring in the SELECT

Yes, here's the error output:

Message: Query Error >> [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'oh_datetime_day'.] >> SELECT FORMAT(oh_datetime,'yyyy-MM-dd') AS oh_datetime_day, SUM(total) AS total_sum FROM ( select 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 and oh_datetime >= '2021-01-01' group by oh_datetime ) as t WHERE (oh_datetime >= '2021-11-01 00:00:00' AND oh_datetime <= '2021-11-30 23:59:59') GROUP BY oh_datetime_day
Line: 440
File: /var/www/html/waylandbi/vendor/koolreport/core/src/datasources/PdoDataSource.php
 Collapse
#0: /var/www/html/waylandbi/vendor/koolreport/core/src/core/DataSource.php Line 108 : start(null)
#1: /var/www/html/waylandbi/vendor/koolreport/core/src/core/Node.php Line 365 : requestDataSending(null)
#2: /var/www/html/waylandbi/vendor/koolreport/dashboard/sources/PDOSource.php Line 139 : requestDataSending(null)
#3: /var/www/html/waylandbi/vendor/koolreport/dashboard/data/SQLHandler.php Line 124 : run(null)
#4: /var/www/html/waylandbi/vendor/koolreport/dashboard/metrics/Trend.php Line 133 : executes(null)
#5: /var/www/html/waylandbi/vendor/koolreport/dashboard/Widget.php Line 223 : render(null)
#6: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRenderItem.php Line 16 : view(null)
#7: /var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/containers/Row.view.php Line 10 : renderItem(null)
#8: /var/www/html/waylandbi/vendor/koolreport/core/src/KoolReport.php Line 502 : include(["/var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/containers/Row.view.php"])
#9: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRender.php Line 10 : render(null)
#10: /var/www/html/waylandbi/vendor/koolreport/dashboard/containers/Row.php Line 63 : render(null)
#11: /var/www/html/waylandbi/vendor/koolreport/dashboard/containers/Container.php Line 120 : render(null)
#12: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRenderItem.php Line 18 : view(null)
#13: /var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/Dashboard.view.php Line 10 : renderItem(null)
#14: /var/www/html/waylandbi/vendor/koolreport/core/src/KoolReport.php Line 502 : include(["/var/www/html/waylandbi/vendor/koolreport/dashboard/amazing/Dashboard.view.php"])
#15: /var/www/html/waylandbi/vendor/koolreport/dashboard/theme/TRender.php Line 12 : render(null)
#16: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 367 : render(null)
#17: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 337 : render(null)
#18: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 317 : view(null)
#19: /var/www/html/waylandbi/vendor/koolreport/dashboard/TAction.php Line 37 : actionIndex(null)
#20: /var/www/html/waylandbi/vendor/koolreport/dashboard/Dashboard.php Line 275 : action(null)
#21: /var/www/html/waylandbi/vendor/koolreport/dashboard/pages/Main.php Line 137 : handle(null)
#22: /var/www/html/waylandbi/vendor/koolreport/dashboard/Application.php Line 217 : handle(null)
#23: /var/www/html/waylandbi/vendor/koolreport/dashboard/Application.php Line 149 : handle(null)
#24: /var/www/html/waylandbi/index.php Line 11 : run(null)

Raw Query Submitted:

select
	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
and    
    oh_datetime >= '2021-01-01'
group by
	oh_datetime

Hi,

That appears to have resolved the issue with the SQL error going away (and it's also resolved another problem around the sum of the data points being incorrect) however, now when Trend is in today mode the hours data seems incorrect as I don't have anything after 12pm and the values at 1am I think are incorrect (perhaps counting sales at 01.00 and 13.00 as 1am?) but that's something I'll need to check into the data with.

Thank you very much for your ongoing assistance with this.

Yeah, that appears to be what it's doing, it's marking up 24hrs worth of sales in 12hrs within the control somewhere as I've just watched the 06.00 figure go up when a sale came in at 18.00.

SQL problem is resolved though, 100%

Have made that change and applied it and can confirm that it resolves the problem!

Brilliant support, thank you so much.