How to write custom report SQL

Question

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

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.

Answer 1

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
• then create a new create module extension using Civix or by copying 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) and use the official documentation
  • 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...