CiviCRM Community Forums (archive)

Hi All,
I was trying these past two days to create a custom report template. I followed the instructions figured in the below link: http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Reports+(How+to) .
Creating a new report template was a success for me.For now, I have problem to display additional information related to the donor. I want to have a report which displays contributions with payment instruments. I know that  under civicrm 3.1, the payment informations are stored in two database tables: civicrm_option_group and civicrm_option_value. What I need to know, how can i get those informations in a proper way:
- shall i do a juncture between civicrm_contribution and the other tables to retrieve the payment instruments informations?
- Or is there an existing function in civicrm that retrieve information? Is so can you help with an example of use

Thank you in advance for your help.

Thank you Yashodha, that helped me. Now i have another problem. I want to display in a contribution report the custom data value. I have found code which displays custom data in column

if ( !defined( 'CIVICRM_REPORT_CONTRIBUTION_CUSTOM_DATA' ) || CIVICRM_REPORT_CONTRIBUTION_CUSTOM_DATA ) {
            // Add contribution custom fields
            $query = 'SELECT id, table_name FROM civicrm_custom_group WHERE is_active = 1 AND extends = "Contribution" AND id= 17';
            $dao = CRM_Core_DAO::executeQuery( $query );
            while ( $dao->fetch( ) ) {
               
                // Assemble the fields for this custom data group
                $fields = array();
                $query = 'SELECT column_name, label FROM civicrm_custom_field WHERE is_active = 1 AND custom_group_id = ' . $dao->id;
                $dao_column = CRM_Core_DAO::executeQuery( $query );
                while ( $dao_column->fetch( ) ) {
                    $fields[$dao_column->column_name] = array(
                                                              'title' => $dao_column->label,
                                                              );
                }
               
                // Add the custom data table and fields to the report column options
                $this->_columns[$dao->table_name] = array(
                                                          'dao' => 'CRM_Contribute_DAO_Contribution',
                                                          'fields' => $fields,
                                                          'group_bys' => $fields,
                                                          );
            }
        }

And after that, in a the from function i added sql query to get value of custom data
if ( !defined( 'CIVICRM_REPORT_CONTRIBUTION_CUSTOM_DATA' ) || CIVICRM_REPORT_CONTRIBUTION_CUSTOM_DATA ) {
            // LEFT JOIN on contribution custom data fields
            $query = 'SELECT id, table_name FROM civicrm_custom_group WHERE is_active = 1 AND extends = "Contribution"';
            $dao = CRM_Core_DAO::executeQuery( $query );
            while ( $dao->fetch( ) ) {
                $alias = $this->_aliases[$dao->table_name];
                $this->_from .= "\n" . 'LEFT JOIN ' . $dao->table_name . ' ' . $alias;
                $this->_from .= "\n" . '        ON ' . $alias . '.entity_id = ' . $this->_aliases['civicrm_contribution'] . '.id';
            }
        }

But whith this code I'm having problem with the sql query, it shows me this error:

Sorry. A non-recoverable error has occurred.

DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

Database Error Code: Not unique table/alias: 'value_cotisations_militante_lu_16_civireport', 1066

Any idea?