CiviCRM Community Forums (archive)

Hi,

I've been asked to put together a report that lists member organizations, their membership status info, and the address info of their "primary contact" (a custom relationship).

I have created a custom report to get the member info, the membership status, and the primary contact's contact_id. 
(Via query to civicrm_membership, then joining to civicrm_contact, civicrm_status, and civicrm_relationship)

I now need to run a query to get the contact_id's phone/address information for every row returned.

Is there a way to nest queries like this via civicrm custom report? Is there some other way to go about this?

Any help would be much appreciated!

Hi Eileen,

I tried this one two of my sites and on both I am getting :

  Unable to extract the extension. Extension has been installed.

Log Says:

Warning: curl_setopt(): CURLOPT_FOLLOWLOCATION cannot be activated when safe_mode is enabled or an open_basedir is set in CRM_Core_Extensions_Extension->download() (line 365 of /var/www/vhosts/site.com/httpdocs/sites/all/modules/civicrm/CRM/Core/Extensions/Extension.php).

Though php_safe mode is off. I'm not sure about the open_basedir part though.

Any way to debug this further? I'd really like to take a look at these extended reports.

Perhaps some instructions on how to manually install?

Thanks!

If you don't need it to respect ACL's or have the fancier filtering widgets then you could just do it as a custom search instead. But then you lose all the surrounding framework. Depends on your needs.

http://wiki.civicrm.org/confluence/display/CRMDOC42/Create+a+Module+Extension#CreateaModuleExtension-Addacustomsearch

So after banging my head against this while annoying the devs on IRC, this is what I came up with, just in case anyone has a use case like mine where a large amount of flat data is requested for export to feed other more antiquated processes.

Nutshell: When you need to translate an existing SQL query into an on-demand csv export using the existing CiviCRM GUI.

Handy Link: http://wiki.civicrm.org/confluence/display/CRMDOC41/CiviReport+structure+and+customization


<?php

//Member Breakdown - Hail Mary.

require_once 'CRM/Report/Form.php';

class CRM_Report_Form_Member_Breakdown extends CRM_Report_Form 
{
  protected $_customGroupExtends = array( 'Membership' );

 
function __construct( ) {
        $this->_columns = array( );
        parent::__construct( );
    }
 

  function postProcess( ) 
  {
    $this->beginPostProcess( );

    // get the acl clauses built before we assemble the query
   // $this->buildACLClause( $this->_aliases['civicrm_membership'] );

    $sql  = "(SELECT ANYTHING, NEST AS DEEP AS YOUR SCRIPT TIMEOUT WILL ALLOW)";

   $this->_columnHeaders =
                array(
                 'a_column_name' => array( 'title' => 'Rewritten Column Name'),
 'another_column_name' => array( 'title' => 'Another Rewritten Column Name'),
                       );



    $rows = $graphRows = array();
    $this->buildRows ( $sql, $rows );

    $this->formatDisplay( $rows );
    $this->doTemplateAssignment( $rows );
    $this->endPostProcess( $rows ); 
  }
  

} 


Derek - just saw I didn't reply back earlier - the reports I did have had a few problems ironed out & should install properly now.

However, looks like you sorted it (although the site is down at the moment so I can't follow the link)

Hi. I created a report with nested queries as Derek did, and everything works great.  now i would like my users to be able to filter results in the where clause of a nested query.
is that possible using __construct()?