Problems with Open Reports in SQL Syntax

firmans · 2 December 2013 04:36

I just installed fresh SuiteCRM appps on the server and populate it with demo data but when i try to run the reports, it returned fail message with sugarcrm log as follow

Mon Dec  2 11:28:40 2013 [18552][1][FATAL] Error running count query for AOR_Report List:  Query Failed: SELECT count(*) c FROM (SELECT leads.id AS 'leads_id', leads.date_entered AS 'Date Created0', leads_cstm.practice_name_c AS 'Practice Name1' FROM leads LEFT JOIN leads_cstm leads_cstm ON leads.id = leads_cstm.id_c  WHERE leads.deleted = 0  GROUP BY leads.date_entered, leads_cstm.practice_name_c ORDER BY Date Created0 ASC, Practice Name1 ASC) as n: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Created0 ASC, Practice Name1 ASC) as n' at line 1

Previously i try to run the report that i made but it also returns error as follow

Mon Dec  2 11:26:53 2013 [18550][1][FATAL] Error running count query for AOR_Report List:  Query Failed: SELECT count(*) c FROM (SELECT leads.id AS 'leads_id', leads_cstm.practice_name_c AS 'Practice Name0', leads.date_entered AS 'Date Created1' FROM leads LEFT JOIN leads_cstm leads_cstm ON leads.id = leads_cstm.id_c  WHERE leads.deleted = 0  GROUP BY leads_cstm.practice_name_c, leads.date_entered ORDER BY Practice Name0 ASC, Date Created1 ASC) as n: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name0 ASC, Date Created1 ASC) as n' at line 1
Mon Dec  2 11:27:00 2013 [18552][1][FATAL]  Query Failed: SELECT id FROM aor_field WHERE aor_field.aor_report_id = 'cd26c4e8-f188-4136-25fc-529c0903c241' AND aor_field.deleted=0: MySQL error 1146: Table 'suitecrm.aor_field' doesn't exist
Mon Dec  2 11:28:39 2013 [18552][1][FATAL] aor_reports for aor_report_aor_fields failed to load

If anyone can give me suggestion what’s wrong with the OpenReport with mentioned fail messages

Thanks

Will · 2 December 2013 09:12

Hi firmans,

Thanks for highlighting this issue. What database are you running on?

Thanks,

Will.

firmans · 2 December 2013 10:04

I am running mysql database

Will · 2 December 2013 10:14

Hi firmans,

What version of mysql? Did your install run without error?

Thanks,

Will.

firmans · 2 December 2013 10:30

Hi will,

i run mysql 5.5 and no error in the beginning of the installation as well as after that, all modules working well, but only the openreports experience that problem

I have tried to look through search engines about similar but can’t find one, the closest one i found is case occurs to MS SSQL but i don;t use it in our installation

What is possible happening yah

marekb · 6 January 2014 11:48

Not sure if this is related but though would ask here as well:

New install of:
Suite CRM 7.10
MySQL 5.5.34
Ubuntu Linux

When I try to run a report I’ve created SuiteCRM reports:
“Database failure. Please refer to sugarcrm.log for details.”

sugarcrm.log:
Error running count query for AOR_Report List: Query Failed: Select count(*) c From (Select opportunities.id AS ‘opportunities_id’, opportunities.assigned_user_id AS 'Assigned User0 ASC) as n: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the rright syntax to use near ‘User0 ASC) as n’ at line 1

There is data in the database, what could be happening?

Thanks,

Marek.

Will · 6 January 2014 11:52

Hi marekb,

There looks like there is an error in the query @ 'Assigned User0. This should be ‘Assigned User’. This may be causing the issue.

Thanks,

Will.

marekb · 6 January 2014 11:55

Thanks for responding so quickly Will

I didn’t create the code, I used the inbuilt report builder. Is there a setting or something else I should be using/doing?

Regards.

Will · 6 January 2014 11:56

Hi marekb,

This may be an issue with the query generated by the module. What kind of report are you trying to generate, can you send some screenshots?

Thanks,

Will.

diskman01 · 21 January 2014 19:47

I am also getting this same error with reports. I can generate ANY report and it will work fine. Until I add a “sort” function in the “Display Fields”. Either ascending or descending will throw the error. No sort order and it generates reports fine. Although they are hard to read as they are unsorted.

It appears to be a broken query from the reports module…

Will · 22 January 2014 15:09

Hi there,

The bug related to sorting in Advanced OpenReports has been fixed in the bug fix release version 7.0.2 of SuiteCRM.

Thanks,

Will.