2

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.

Eaxmple issue

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
Improve this question

2 Answers 2

Reset to default
1

In the CRM/Report folder: grep -r -i "TEMPORARY" *

This gives you some existing reports that use temp tables which you can look at to get some ideas.

Form/Activity.php Form/Contribute/Detail.php etc...

Improve this answer
3
  • Gosh, this is far harder than slotting in a bit of SQL and away you go! Need to learn a whole new framework for a one off. Not sure if it is etiquette but any one want to quote to code this up - I can provide working SQL, or if this isn't the right place - apologies - and advise :)
    – Alan Fuller
    Commented Sep 22, 2016 at 16:03
  • Take a look at "custom search" instead. You don't have all the widgets but you can just plug in your sql. wiki.civicrm.org/confluence/display/CRMDOC/…
    – Demerit
    Commented Sep 22, 2016 at 16:11
  • Thanks, an easier starting point. By the way any reason why in the example I looked at the buildtemptables was called in the from function rather than select - answering my own question maybe select deals with columns, and in the case in looking at the temp table logic requires stuff that isn't available that early.
    – Alan Fuller
    Commented Sep 22, 2016 at 16:22
0

Maybe I have a limited MySQL knowledge, but I think you need to join the two tables? So I would do something like

FROM tmpareareps tmp JOIN civicrm_contribution_recur recur ON tmp.house_id = recur.contact_id
Improve this answer
1
  • 1
    The SQL join fine. I could use JOIN or just use the WHERE, it has the same impact, the issue is CiviCRM ADD the SQL_CALC_FOUND to the SELECT which is invalid in the CREATE TEMPORARY TABLE AS.. The code I provide to Civi does not have SQL_CALC_FOUND in it.
    – Alan Fuller
    Commented Sep 22, 2016 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.