I'm trying to create a subpanel to display historical monthly profit for an account (stored in quotes_cstm), i can run the query directly in the database without fail, so the SQL is correct from a syntax point of view, i am however struggling to have this data displayed in the subpanel.

I've tried following the helpful advice in this post. But the aggregate functions do not display as columns for selection.

In custom/Extension/modules/Ext/Layoutdefs/subpanel_SaleStats.php i have the following:
Code:
$layout_defs['Accounts']['subpanel_setup']['salestats'] =
        array('order' => 49,
            'module' => 'Quotes',
            'subpanel_name' => 'salestats',
            'get_subpanel_data' => 'function:get_sale_stats',
            'generate_select' => true,
            'title_key' => 'LBL_SALE_STATS_THIS_YEAR',
            'top_buttons' => array(),
            'function_parameters' => array(
                'import_function_file' => 'custom/modules/Accounts/customSaleStatsSubPanel.php',
                'account_id' => $this->_focus->id,
				'year' => date('Y'),
                'return_as_array' => 'true',
			),			
);
Then in custom/modules/Accounts/customSaleStatsSubPanel.php i have the following:
Code:
function get_sale_stats($params) {
	
    $args = func_get_args();
    $accountId = $args[0]['account_id'];
	$year = $args[0]['year'];
    $return_array['select'] = " SELECT 
								SUM(quotes_cstm.psp_c) AS PSP,
								products.avg_qty 'Order Qty',
								ROUND(AVG(quotes_cstm.psp_c),2) AS 'Avg Order PSP',
								COUNT(accounts.id) AS 'Total Orders',
								products.avg_price 'Avg Cents/Fax' ,
								ROUND(SUM(quotes_cstm.psp_c)/12,2) AS MonthAvg";

    $return_array['from'] = " FROM quotes ";
    $return_array['where'] = " WHERE ( quotes_accounts.account_role='Bill To' AND account_type='Customer'
									 AND quotes_cstm.psp_c>0
									 AND (p.avg_qty>1)

     								AND (YEAR(quotes_cstm.order_date_c)IN(".$year.")) and(accounts.id='".$accountId."') " ;
	 
    $return_array['join'] = "
								LEFT JOIN quotes_accounts ON (quotes.id=quotes_accounts.quote_id)
								LEFT JOIN accounts  ON (quotes_accounts.account_id=accounts.id)
								LEFT JOIN quotes q ON (quotes_accounts.quote_id=quotes.id)
								LEFT JOIN (
									SELECT p1.quote_id,category_id,ROUND(AVG(p1.quantity)) AS avg_qty,ROUND(AVG(p1.discount_amount),2) AS avg_price FROM products p1 WHERE p1.quantity>1 GROUP BY p1.quote_id
								 ) p ON p.quote_id = q.id  ";
	$return_array['join_tables'][0] = '';
	
    return $return_array;
}
I have tried adding the field definitions to custom/modules/Quotes/metadata/subpanels/salestats.php as referenced in this post, so the definition looks for example like this:
Code:
'PSP' => array(
      'force_blank' => true,
      'force_exists' => true,
       'vname' => 'PSP',
),
Of course i've run a repair and rebuild. The sub panel shows up in Accounts, but the data displayed is not correct. When i investigate the log file i see that the query produced does not included the custom aggregate functions i request in the original SQL. Is this a fault, or have i just missed something?

I'm running the Pro version which is self hosted the version number is 6.5.15 (Build 1083).

Any help much appreciated!