$VAR1 = {
          'columnsettings' => 'vh_dr,Driver,total:count;

vh_nr,Vehicle Reg

kmstart,Start (km),align:right;sufix: km;

kmend,End (km),align:right;sufix: km;

distance,Distance (km),align:right;sufix: km;total:sum;

litres,Fuel Litres,align:right;sufix: litres;

avgprice,Average Price,align:right;prefix:R ;total:avg;

totalvalue,Total Value,align:right;prefix:R ;total:sum;

qtyusage,Litres / 100km,align:right;sufix: litres;',
          'selector' => '2',
          'data' => 'select 

   group_concat(distinct vh_nr SEPARATOR ", ") vh_nr,

   vh_dr,

   MIN(vh_km) kmstart, 

   MAX(vh_km) kmend,

   round(SUM(qty*price),2) totalvalue,

   MAX(vh_km) - MIN(vh_km) distance,

   SUM(qty) litres,

   ROUND( AVG(price), 2)  avgprice,

   ROUND( ( SUM(qty * price) ) / (MAX(vh_km) - MIN(vh_km)) , 2 ) pusage,

   ROUND( SUM(qty) / ( MAX(vh_km) - MIN(vh_km) ) * 100 , 2 ) qtyusage



from purchases



where vh_nr != \'\'

  and date >= \'[datestart]\'

  and date <= \'[dateend]\'

  and vh_nr like \'%[vhfilter]%\'

group by vh_dr

;







',
          'description' => 'Vehicle Fuel Spenditure over distance',
          'settings' => 'datestart,date,Start Date

dateend,date,End Date

vhfilter,value,Registration Numbers like',
          'rollup' => '',
          'id' => '1',
          'title' => 'Vehicle Spending / Distance',
          'category' => 'VEHICLES',
          'code' => '33'
        };