Answers.Body: <p>There was a pretty good 101 session about custom report in CiviCon Denver 2015. Unfortunately, there don't seems to be a video about it. You can check resources here : <a href="https://denver2015.civicrm.org/sessions/civicrm-custom-reports-101" rel="noreferrer">https://denver2015.civicrm.org/sessions/civicrm-custom-reports-101</a> (pdf and custom extension).</p>
  
  <p>My recommended steps for doing a custom report :</p>
  
  <ul>
  <li>you must first do the sql query you want in mysql - understand which tables, which joint, which columns are needed, ... - you might want to visually navigate on <a href="https://doc.symbiotic.coop/dev/civicrm/v47/schema/index.html" rel="noreferrer">CiviCRM 4.7 database schema</a></li>
  <li>then create a new <a href="https://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension" rel="noreferrer">create module extension</a> using <a href="https://github.com/totten/civix" rel="noreferrer">Civix</a> or by copying <a href="https://github.com/freeform/ca.freeform.civireport101" rel="noreferrer">civireport101</a>
  
  <ul>
  <li>in <code>CRM/MyExt/Form/Report/MyReport.mgd.php</code>, you will have all the information needed for CiviCRM to find your report and be able to create new instance of it</li>
  <li>in <code>CRM/MyExt/Form/Report/MyReport.php</code> you will have the recipe for the report</li>
  </ul></li>
  <li>now, the hard part... you need to customize the <code>MyReport.php</code>. You should start from real CiviCRM report or extensions report (e.g. <a href="https://github.com/freeform/ca.freeform.civireport101" rel="noreferrer">civireport101</a>) and use the <a href="https://wiki.civicrm.org/confluence/display/CRMDOC/CiviReport+Reference" rel="noreferrer">official documentation</a>
  
  <ul>
  <li>column definition goes in the constructor <code>function __construct() {</code>, you need to understand how CiviCRM store the column definition</li>
  <li>select <code>function select()</code> / where <code>function where()</code> : if your constructor is done correctly, you should be able to reuse one base report function</li>
  <li>from <code>function from()</code> : you should be able to reuse one base report but if you prefer, you can simply put your from here - just be careful with aliases <code>$this-&gt;_from = "FROM civicrm_contact {$this-&gt;_aliases['civicrm_contact']}";</code></li>
  <li>Regarding the page break, you might be able to do it in the <code>alter_display()</code> function...</li>
  </ul></li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-20T08:03:13.373Z
Answers.Text: Thanks, incredibly useful reply.  Typical though, I have started by looking at the schema and street address is stored as a single string rather than split into house / street - so I'll have to start getting messy grouping by substrings etc.
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-20T15:21:51.157Z
Answers.Text: It depends if you use the option Street Address Parsing or not (Admin -> Localization -> Address settings). If you use it, the address should be split into different columns.
Answers.UserDisplayName: samuelsov
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-05T16:32:35.467Z
Answers.Text: what happend to the link to CiviCRM 4.7 database schema that was so useful
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-07T14:31:05.293Z
Answers.Text: @AlanFuller it should be back online. Thanks for letting me know.
Answers.UserDisplayName: samuelsov
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-10T10:39:51.580Z
Answers.Text: was back but down again
Answers.UserDisplayName: Alan Fuller
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-10T15:22:30.160Z
Answers.Text: Seems good from here. There might be some interruptions from time to time as it's not our most critical service. Anyway, we have monitoring on this service now so it should be up most of the time.
Answers.UserDisplayName: samuelsov
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-19T13:50:26.427Z
Answers.LastActivityDate: 2016-09-19T13:50:26.427Z
Answers.UserDisplayName: samuelsov
Body: <p>I'm not asking for the actual solution, but more a pointer to references examples for the structure so I can move forward. </p>
  
  <p>CiviCRM 4.7 or WordPress - I need to create a report that I think will be achieved by custom coding only.</p>
  
  <p>My original request was here
  <a href="https://civicrm.stackexchange.com/questions/14843/custom-report-by-relationship">Custom Report By Relationship</a></p>
  
  <p>Essentially I have a a 'street rep (individual)' that has a relationship with a 'household ( a physical address)' that makes 'contributions'  and a 'household' has a relationship with an 'individual' as head of household.</p>
  
  <p>So want to build a street collection report structured</p>
  
  <p>Street Rep (page break)
      ---  Street Name  (sub section )
            --- door number, head of household name, last years contribution (line)</p>
  
  <p>Although the requirement is relatively simple it has a level of complexity that my unfamiliarity with Civi structures is making it tough for me.</p>
  
  <p>I really appreciate any pointers or help.</p>
  
Tags: <reports><custom-reports>
Title: How to write custom report SQL
ThreadText: # How to write custom report SQL
  
  (Asked by: username_0 on 2016-09-19)
  
  I'm not asking for the actual solution, but more a pointer to references examples for the structure so I can move forward.
  
  CiviCRM 4.7 or WordPress - I need to create a report that I think will be achieved by custom coding only.
  
  My original request was here
  [Custom Report By Relationship](https://civicrm.stackexchange.com/questions/14843/custom-report-by-relationship)
  
  Essentially I have a a 'street rep (individual)' that has a relationship with a 'household ( a physical address)' that makes 'contributions' and a 'household' has a relationship with an 'individual' as head of household.
  
  So want to build a street collection report structured
  
  Street Rep (page break)
  --- Street Name (sub section )
  --- door number, head of household name, last years contribution (line)
  
  Although the requirement is relatively simple it has a level of complexity that my unfamiliarity with Civi structures is making it tough for me.
  
  I really appreciate any pointers or help.
  
  **Tags:** reports, custom-reports
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-09-19. Score: 5
  
  There was a pretty good 101 session about custom report in CiviCon Denver 2015. Unfortunately, there don't seems to be a video about it. You can check resources here : <https://denver2015.civicrm.org/sessions/civicrm-custom-reports-101> (pdf and custom extension).
  
  My recommended steps for doing a custom report :
  
  * you must first do the sql query you want in mysql - understand which tables, which joint, which columns are needed, ... - you might want to visually navigate on [CiviCRM 4.7 database schema](https://doc.symbiotic.coop/dev/civicrm/v47/schema/index.html)
  * then create a new [create module extension](https://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension) using [Civix](https://github.com/totten/civix) or by copying [civireport101](https://github.com/freeform/ca.freeform.civireport101)
    + in `CRM/MyExt/Form/Report/MyReport.mgd.php`, you will have all the information needed for CiviCRM to find your report and be able to create new instance of it
    + in `CRM/MyExt/Form/Report/MyReport.php` you will have the recipe for the report
  * now, the hard part... you need to customize the `MyReport.php`. You should start from real CiviCRM report or extensions report (e.g. [civireport101](https://github.com/freeform/ca.freeform.civireport101)) and use the [official documentation](https://wiki.civicrm.org/confluence/display/CRMDOC/CiviReport+Reference)
    + column definition goes in the constructor `function __construct() {`, you need to understand how CiviCRM store the column definition
    + select `function select()` / where `function where()` : if your constructor is done correctly, you should be able to reuse one base report function
    + from `function from()` : you should be able to reuse one base report but if you prefer, you can simply put your from here - just be careful with aliases `$this->_from = "FROM civicrm_contact {$this->_aliases['civicrm_contact']}";`
    + Regarding the page break, you might be able to do it in the `alter_display()` function...
  
  > **Comments:**
  > **username_0**: Thanks, incredibly useful reply.  Typical though, I have started by looking at the schema and street address is stored as a single string rather than split into house / street - so I'll have to start getting messy grouping by substrings etc.  
  > **username_1**: It depends if you use the option Street Address Parsing or not (Admin -> Localization -> Address settings). If you use it, the address should be split into different columns.  
  > **username_0**: what happend to the link to CiviCRM 4.7 database schema that was so useful  
  > **username_1**: @username_0 it should be back online. Thanks for letting me know.  
  > **username_0**: was back but down again  
  > **username_1**: Seems good from here. There might be some interruptions from time to time as it's not our most critical service. Anyway, we have monitoring on this service now so it should be up most of the time.  
  
  ---
  
  