Following on from my query here How to write custom report SQL
(CiviCRM 4.7 on WordPress)
I built the SQL I needed as step 1, which works fine in mysql / phpmyadmin.
I have used civix, to generate a module and to clone the 'empty' report from 101 and register the report - so far so go, if I want a report that says 'hello'.
Next step slot in my SQL and test .... and within civi it gives sysntax errors.
Debug shows that it is inserting SQL_CALC_FOUND_ROWS all over the SQL, where as I need it only on the final select.
Although my requirement is simple my SQL is complex, and I can't achieve it in one SELECT statement ( maybe possible by a SQL wizard ), as it requires contact relationships in two directions and a 'pivot' of contributions, it builds 3 temporary table and joins them up in a final select.
My main question is 'can CiviReport only handle a single select?' and the second question is is there something I can do, or should I just resort to coding outside CiviReport structures?
Thanks so far for the help given, it has been fantastic.
CREATE TEMPORARY TABLE tmpcontrhist AS SELECT SQL_CALC_FOUND_ROWS house_id, YEAR(contr.receive_date) memb_year, contr.total_amount memb_amount FROM tmpareareps, civicrm_contribution contr WHERE house_id = contr.contact_id; Database Error Code: 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 'CREATE TEMPORARY TABLE tmpcontrhist AS SELECT SQL_CALC_FOUND_ROWS house_id, ' at line 21, 1064