CiviCRM Community Forums (archive)

You can't really see all the fields in that.

I would remove the groupby clause

GROUP BY YEAR(activity_civireport.activity_date_time), MONTH(activity_civireport.activity_date_time), activity_civireport.activity_type_id 

and all the aggregate bits of the functions so you can see the individual rows it is retrieving- ie replace

IFNULL(SUM(value_further_info_3_civireport.number_attending_19), 0) as civicrm_value_further_info_3_custom_19_sum  with

value_further_info_3_civireport.number_attending_19

Or drop some of them

OK - the top two are your duplicate.
So what I would do now is start removing LEFT JOIN clauses & see how many records you get each time

So LEFT JOINS say - include the records on the left side of this join and those (0 or more) on the right side that match this criteria. Return one row per match

If you have this setup

Activity	Contact1	Contact2
Wedding	Bob	Mary

Contact

Bob

Mary

AND you SQL is

SELECT * FROM tblactivitity LEFT JOIN tblcontact on Contact1 = Contact

You will see one row:
Wedding   Bob   Mary Bob

But if your SQL is


SELECT * FROM tblactivitity LEFT JOIN tblcontact on Contact1  = Contact LEFT JOIN another table with 2 matches

The previous row will then be joined against the new table and you will get one row for each of the two matches in the table

If you want to make a contribution put it towards Civi-Make-it-Happen - preferably the de-dupe one.

I wanted to help you because I could see you were really perservering with it and hitting a few walls - which is a feeling I know well! Unfortunately there aren't that many people out there who have delved into reports so it can feel like no-one wants to help you when in fact people are staying schtum because they don't know the answer.


Is the commented out bit in the statistics your attempt at the notes?

        // this is a hack that takes advantage of built-in functionality to display a row for each statistic
        // at bottom of report. We repurpose this inelegantly to display the notes for the selected contact in the filtered timeframe

I contributed....I can't thank you enough.

Yeah I've been struggling with this for quite some time. I can usually figure things out. I can do pretty much anything in Drupal but it's a little more straight forward (at least to me).

Any chance you could help with the Notes code? 

function statistics( &$rows ) {
        $statistics = parent::statistics( $rows );

        // this is a hack that takes advantage of built-in functionality to display a row for each statistic
        // at bottom of report. We repurpose this inelegantly to display the notes for the selected contact in the filtered timeframe
        $select = "SELECT n.modified_date AS n_date, c.display_name AS n_name, n.note AS n_note FROM civicrm_note n inner join civicrm_contact c ON c.id=n.entity_id
            WHERE entity_table='civicrm_contact' AND entity_id='120'";
        // need to add where clause to filter on civicrm_contact.id -- check if it is in $rows

        // not sure if activity_date_time_* is quite right - there may be suffix or prefix in actual param
        // could check that with CiviCRM debugging to display form variables, etc
        //$relative = CRM_Utils_Array::value( "activity_date_time_relative", $this->_params );
        //$from     = CRM_Utils_Array::value( "activity_date_time_from"    , $this->_params );
       // $to       = CRM_Utils_Array::value( "activity_date_time_to"      , $this->_params );
       $relative = CRM_Utils_Array::value( "{$fieldName}_relative", $this->_params );
       $from     = CRM_Utils_Array::value( "{$fieldName}_from"    , $this->_params );
       $to       = CRM_Utils_Array::value( "{$fieldName}_to"      , $this->_params );
        $clause = $this->dateClause( $field['name'], $relative, $from, $to, CRM_Utils_Type::T_DATE );
         

        $sql = $select . ' ' . $clause;
        $dao = CRM_Core_DAO::executeQuery( $sql );

        $i = 0;
        if ( $dao->fetch( ) ) {
            // $i may need to be a string key
            // $dao->n_date may need to be formatted as a string
            $statistics['counts'][$i] = array( 'value' => $dao->n_name . ' ' . $dao->n_note,
                                                     'title' =>  $dao->n_date );
        }

        return $statistics;
    }

How can I check whether the $dao is an empty recordset and return a message?

$sql = $select . ' ' . $clause;

        $dao = CRM_Core_DAO::executeQuery( $sql );

        $i = 0;
        while ( $dao->fetch( ) ) {  $i++;
         $statistics['groups2'][$i] = array( 'title' => '',
                                               'value' =>  $dao->n_date . '</td><td>' . $dao->n_name . '</td><td>'  . $dao->n_note );
           }

        return $statistics;