Answers.Body: <p>In the CRM/Report folder: grep -r -i "TEMPORARY" *</p>
  
  <p>This gives you some existing reports that use temp tables which you can look at to get some ideas.</p>
  
  <p><code>
  Form/Activity.php
  Form/Contribute/Detail.php
  etc...</code></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T16:03:18.247Z
Answers.Text: 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 :)
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T16:11:00.123Z
Answers.Text: Take a look at "custom search" instead. You don't have all the widgets but you can just plug in your sql. https://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension#CreateaModuleExtension-Addacustomsearch
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T16:22:52.907Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T13:35:32.820Z
Answers.LastActivityDate: 2016-09-22T13:35:32.820Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>Maybe I have a limited MySQL knowledge, but I think you need to join the two tables? So I would do something like</p>
  
  <pre><code>FROM tmpareareps tmp JOIN civicrm_contribution_recur recur ON tmp.house_id = recur.contact_id
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T12:04:50.153Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-22T11:36:35.970Z
Answers.LastActivityDate: 2016-09-22T11:36:35.970Z
Answers.UserDisplayName: ErikH - CiviCooP
Body: <p>Following on from my query  here <a href="https://civicrm.stackexchange.com/questions/14975/how-to-write-custom-report-sql">How to write custom report SQL</a></p>
  
  <p>(CiviCRM 4.7 on WordPress)</p>
  
  <p>I built the SQL I needed as step 1, which works fine in mysql / phpmyadmin.</p>
  
  <p>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'.</p>
  
  <p>Next step slot in my SQL and test ....  and within civi it gives sysntax errors.</p>
  
  <p>Debug shows that it is inserting SQL_CALC_FOUND_ROWS all over the SQL, where as I need it only on the final select.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Thanks so far for the help given, it has been fantastic.</p>
  
  <p>Eaxmple issue</p>
  
  <pre><code>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
  </code></pre>
  
Tags: <mysql><reports><custom-reports>
Title: Complex multiple Selects in Custom Coded Reports
ThreadText: # Complex multiple Selects in Custom Coded Reports
  
  (Asked by: username_0 on 2016-09-22)
  
  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
  
  **Tags:** mysql, reports, custom-reports
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2016-09-22. Score: 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...`
  
  > **Comments:**
  > **username_0**: 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 :)  
  > **username_1**: Take a look at "custom search" instead. You don't have all the widgets but you can just plug in your sql. https://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension#CreateaModuleExtension-Addacustomsearch  
  > **username_0**: 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.  
  
  ---
  
  ### Answer by username_2 on 2016-09-22. Score: 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
  
  > **Comments:**
  > **username_0**: 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.  
  
  ---
  
  