Simply put, I cannot seem to get my custom reports to to create a full export to a csv file.

They will only export the rows shown in the currently displayed grid (i.e. the full manipulated date, including pagination), not the the entire dataset resulting from the sql query.

Furthermore they export the summery fields from the source table (stVehicleBooking) rather than the fields from the sql query.

The dataset is being correctly generated and displayed in the on-screen display grid, it is only the export and print buttons that are incorrect.

I feel sure there is a simple solution to this, but I simply cannot work it out.

Thank you all in advance for any advice you could offer, it would be greatly appreciated.

My current code :-

In TotalBookingsReport.php

<?php
if (!class_exists('stTotalBookingsReport')) {
	class stTotalBookingsReport extends SS_Report {
	
		//protected $description = 'Displays the total number of bookings grouped by year';
		
		public function title() {
			return 'Total Booking Report - Displays the total number of bookings grouped by year';
		}
		
		public function columns() {
			return array (
				'StartYear'					=> 'Period',
				'NumberOfBookings'			=> 'Number of Bookings',
				'TotalFullPrice'			=> 'Total Price',
				'TotalPaid'					=> 'Total Amount Paid',
				'TotalOwed'					=> 'Total Owed',
				'TotalPrice'				=> 'Total Base Price (excl Treats)',
				'TotalTreats'				=> 'Total Value of Treats',
				'TotalInboundTransfers'		=> 'Total Inbound Xfers',
				'TotalOutboundTransfers'	=> 'Total Outbound Xfers',
				'TotalOutdoorChairs'		=> 'Total Outdoor Chairs',
				'TotalSnowChains'			=> 'Total Snow Chains',
				'TotalGenerator'			=> 'Total Generator',
				'TotalSecondGasBottle'		=> 'Total Second Gas Bottle',
				'TotalStarterPack'			=> 'Total Starter Pack',
				'TotalCaravanClub'			=> 'Total Caravan Club',
				'TotalGasEasyRefill'		=> 'Total Gas Easy Refill',
				'TotalFuelEasyRefill'		=> 'Total Fuel Easy Refill',
				'TotalSatNav'				=> 'Total Sat Nav'
			);
		}
		
		public function parameterFields() {
			$fields = new FieldList ();
			
			$fields->push (
				new DropdownField (
					'ReportGrouping',
					'Calculate totals for:',
					array (
						''				=> '[ no grouping ]',
						'Year'			=> 'Year',
						'YearAndMonth'	=> 'Year & Month',
						'YearAndQtr'	=> 'Year & Quarter'
					)
				)
			);
			
			$fields->push ($start = new DateField ('StartDate', 'Start Date'));
			$fields->push ($end = new DateField ('EndDate', 'End Date'));
			$start->setConfig ('showcalendar', true);
			$end->setConfig ('showcalendar', true);
			
			return $fields;
		}
		
		public function sourceRecords($params, $sort, $limit) {
		
			// define the fields to be included and the sql fragments to build them
			$fields = array (
				'NumberOfBookings'			=> 'count(stVehicleBooking.ClassName)',
				'TotalPrice'				=> 'sum(stQuote.TotalPrice)',
				'TotalOwed'					=> 'sum(stQuote.Price)',
				'TotalPaid'					=> 'sum(stQuote.AmountPaid)',
				'TotalTreats'				=> 'sum(stQuote.PriceAirportTransferInbound + stQuote.PriceAirportTransferOutbound + stQuote.PriceOutdoorChairs + stQuote.PriceSnowChains + stQuote.PriceGenerator + stQuote.PriceSecondGasBottle + stQuote.PriceStarterPack + stQuote.PriceCaravanClub + stQuote.PriceGasEasyRefill + stQuote.PriceFuelEasyRefill + stQuote.PriceSatNav)',
				'TotalInboundTransfers'		=> 'sum(stQuote.PriceAirportTransferInbound)',
				'TotalOutboundTransfers'	=> 'sum(stQuote.PriceAirportTransferOutbound)',
				'TotalOutdoorChairs'		=> 'sum(stQuote.PriceOutdoorChairs)',
				'TotalSnowChains'			=> 'sum(stQuote.PriceSnowChains)',
				'TotalGenerator'			=> 'sum(stQuote.PriceGenerator)',
				'TotalSecondGasBottle'		=> 'sum(stQuote.PriceSecondGasBottle)',
				'TotalStarterPack'			=> 'sum(stQuote.PriceStarterPack)',
				'TotalCaravanClub'			=> 'sum(stQuote.PriceCaravanClub)',
				'TotalGasEasyRefill'		=> 'sum(stQuote.PriceGasEasyRefill)',
				'TotalFuelEasyRefill'		=> 'sum(stQuote.PriceFuelEasyRefill)',
				'TotalSatNav'				=> 'sum(stQuote.PriceSatNav)'
			);
			
			// define a group field
			if (isset($params['ReportGrouping']) && !empty($params['ReportGrouping'])) {
				switch ($params['ReportGrouping']) {
					case 'Year':
						$fields['StartYear'] = 'year(stVehicleBooking.StartDate)';
						break;
					case 'YearAndMonth':
						$fields['StartYear'] = "concat_ws('-', monthname(stVehicleBooking.StartDate), year(stVehicleBooking.StartDate))";
						break;
					case 'YearAndQtr':
						$fields['StartYear'] = "concat_ws('-', year(stVehicleBooking.StartDate), 'Qtr', quarter(stVehicleBooking.StartDate))";
						break;
					default:
						$fields['StartYear'] = 'stVehicleBooking.StartDate';
				}
			} else
				$fields['StartYear'] = 'stVehicleBooking.StartDate';
			
			$where = 'Product.ID is not null';
			
			// define the period for the report
			if (isset($params['StartDate']) && !empty($params['StartDate'])) {
				$where = " and StartDate >= '" . date('Y-m-d', strtotime($params['StartDate'])) . "'";
			}
			
			if (isset($params['EndDate']) && !empty($params['EndDate'])) {
				$where .= " and EndDate <= '" . date('Y-m-d', strtotime($params['EndDate'])) . "'";
			}
			
			// build an sql object
			$sql = new SQLQuery();
			
			foreach ($fields as $name => $field)
				$sql->selectField ($field, $name);
			
			$sql->setFrom		('stVehicleBooking')
				->addLeftJoin	('stQuote',		'stQuote.ID = stVehicleBooking.QuoteID')
				->addLeftJoin	('stVehicle', 	'stVehicle.ID = stVehicleBooking.VehicleID')
				->addLeftJoin	('Product',		'Product.ID = stVehicle.ModelID')
				->addWhere		($where)
				->setGroupBy	('StartYear')
				->setOrderBy	('stVehicleBooking.StartDate');
			
			// and return the resuts
			return Report_List::makeList('stVehicleBooking', $sql->execute());
		}
	
	}	// end class stTotalBookingsReport
}
?>

And in Report_List.php

<?php
if (!class_exists('Report_List')) {
	class Report_List {
	
		public static function makeList($dataObject, $results) {
		
			// generate an ArrayList of the SQL results array (from $sqlQuery->execute()) for output
			
			$list = array();
			
			if ($results->numRecords() > 0)
				foreach ($results as $item)
					$list[] = new $dataObject($item);
			
			return new ArrayList($list);
		}
	
	}	// end class Report_List
}
?>

Hi people,

Still not getting anywhere with this.
Anyone got any ideas?

Mike Armstrong
Silvertoad Ltd

ReportAdmin in 3.1 uses a GridField so you should be able to add a GridFieldExportButton component to the grid field to allow the data to be exported. There is a hook to allow you to edit that ReportAdmin interface - https://github.com/silverstripe-labs/silverstripe-reports/blob/master/code/ReportAdmin.php#L180

Thanks for the reply Willr, but I have already solved this one myself

In case anybode else has a similar problem, here is my final solution

----------------------

It turns out that the problem was in the record selection for the export button (core function GridFieldExportButton->generateExportFileData())

This calls GridField->getManipulatedList(), which returns the full manipulated list (including pagination)

The trick is to call GridField->getList() instea, which applies the search critria but not the pagination selection

So, override GridFieldExportButton like this:

<?php
if (!class_exists('GridFieldExportAllButton')) {

class GridFieldExportAllButton extends GridFieldExportButton {
    /**
     * Generate export fields for CSV.
     *
     * @param GridField $gridField
     * @return array
     *
     * ----------
     * replaces definition in GridFieldExportButton
     * same as original except sources data from $gridField->getList() instead of $gridField->getManipulatedList();
     * ----------
     *
     */
     public function generateExportFileData($gridField) {
         $separator = $this->csvSeparator;
         $csvColumns = ($this->exportColumns)
             ? $this->exportColumns
             : singleton($gridField->getModelClass())->summaryFields();
         $fileData = '';
         $columnData = array();
         $fieldItems = new ArrayList();

         if($this->csvHasHeader) {
             $headers = array();

             // determine the CSV headers. If a field is callable (e.g. anonymous function) then use the
             // source name as the header instead
             foreach($csvColumns as $columnSource => $columnHeader) {
                 $headers[] = (!is_string($columnHeader) && is_callable($columnHeader)) ? $columnSource : $columnHeader;
             }

             $fileData .= "\"" . implode("\"{$separator}\"", array_values($headers)) . "\"";
             $fileData .= "\n";
         }

         // -----------------------
         //$items = $gridField->getManipulatedList();
         $items = $gridField->getList();
         // -----------------------

         // @todo should GridFieldComponents change behaviour based on whether others are available in the config?
         foreach($gridField->getConfig()->getComponents() as $component){
             if($component instanceof GridFieldFilterHeader || $component instanceof GridFieldSortableHeader) {
                 $items = $component->getManipulatedData($gridField, $items);
             }
         }

         foreach($items->limit(null) as $item) {
             $columnData = array();

             foreach($csvColumns as $columnSource => $columnHeader) {
                 if(!is_string($columnHeader) && is_callable($columnHeader)) {
                     if($item->hasMethod($columnSource)) {
                         $relObj = $item->{$columnSource}();
                     } else {
                         $relObj = $item->relObject($columnSource);
                     }

                     $value = $columnHeader($relObj);
                 } else {
                     $value = $gridField->getDataFieldValue($item, $columnSource);
                 }

                 $value = str_replace(array("\r", "\n"), "\n", $value);
                 $columnData[] = '"' . str_replace('"', '\"', $value) . '"';
             }
             $fileData .= implode($separator, $columnData);
             $fileData .= "\n";

             $item->destroy();
         }

         return $fileData;
     }

} // end class GridFieldExportAllButton

}
?>

Then, in the custom report class (the one extending SS_Report):L
Override getReportField()
Remove componant GridFieldExportButton from the config
And add componant GridFieldExportAllButton

		public function getReportField() {
			$gridField = parent::getReportField();
			
			$gridField->setModelClass('reportTotalBookings');
			
			$gridConfig = $gridField->getConfig();
			
			$gridConfig->removeComponentsByType('GridFieldPrintButton');
			$gridConfig->removeComponentsByType('GridFieldExportButton');
			
			$gridConfig->addComponents (
				new GridFieldPrintAllButton('buttons-after-left'),
				new GridFieldExportAllButton('buttons-after-left')
			);
			
			return $gridField;
		}

Also do the same thing with GridFieldPrintButton as this has the same problem

<?php
if (!class_exists('GridFieldPrintAllButton')) {

class GridFieldPrintAllButton extends GridFieldPrintButton {

     /**
      * Export core.
      *
      * @param GridField
      *
      * ----------
      * replaces definition in GridFieldPrintButton
      * same as original except sources data from $gridField->getList() instead of $gridField->getManipulatedList();
      * ----------
      *
      */
     public function generatePrintData(GridField $gridField) {
         $printColumns = $this->getPrintColumnsForGridField($gridField);
         
         $header = null;

         if($this->printHasHeader) {
             $header = new ArrayList();

             foreach($printColumns as $field => $label){
                 $header->push(new ArrayData(array(
                     "CellString" => $label,
                 )));
             }
         }
         
         // -----------------------
         //$items = $gridField->getManipulatedList();
         $items = $gridField->getList();
         // -----------------------
         
         $itemRows = new ArrayList();

         foreach($items as $item) {
             $itemRow = new ArrayList();
             
             foreach($printColumns as $field => $label) {
                 $value = $gridField->getDataFieldValue($item, $field);

                 $itemRow->push(new ArrayData(array(
                     "CellString" => $value,
                 )));
             }
             
             $itemRows->push(new ArrayData(array(
                 "ItemRow" => $itemRow
             )));

             $item->destroy();
         }

         $ret = new ArrayData(array(
             "Title" => $this->getTitle($gridField),
             "Header" => $header,
             "ItemRows" => $itemRows,
             "Datetime" => SS_Datetime::now(),
             "Member" => Member::currentUser(),
         ));
         
         return $ret;

    }
} // end class GridFieldPrintAllButton

}

?>

Finally, to get the correct fields on the report

These are generated directly from the $summary_fields array on the DataObject

So, override the DataObject class with a new set of summary fields:

if (!class_exists('reportTotalBookings')) {
	class reportTotalBookings extends stVehicleBooking {
		public static $summary_fields = array (
			'StartYear'					=> 'Period',
			'NumberOfBookings'			=> 'Number of Bookings',
			'TotalFullPrice'			=> 'Total Price',
			'TotalPaid'					=> 'Total Amount Paid',
			'TotalOwed'					=> 'Total Owed',
			'TotalPrice'				=> 'Total Base Price (excl Treats)',
			'TotalTreats'				=> 'Total Value of Treats',
			'TotalInboundTransfers'		=> 'Total Inbound Xfers',
			'TotalOutboundTransfers'	=> 'Total Outbound Xfers',
			'TotalOutdoorChairs'		=> 'Total Outdoor Chairs',
			'TotalSnowChains'			=> 'Total Snow Chains',
			'TotalGenerator'			=> 'Total Generator',
			'TotalSecondGasBottle'		=> 'Total Second Gas Bottle',
			'TotalStarterPack'			=> 'Total Starter Pack',
			'TotalCaravanClub'			=> 'Total Caravan Club',
			'TotalGasEasyRefill'		=> 'Total Gas Easy Refill',
			'TotalFuelEasyRefill'		=> 'Total Fuel Easy Refill',
			'TotalSatNav'				=> 'Total Sat Nav'
		);
	}	// end class reportTotalBookings
}

Then use this class when genrating the the record list

In sourceRecords() replace:

			// and return the resuts 
			return Report_List::makeList('stVehicleBooking', $sql->execute());

			// and return the resuts 
			return Report_List::makeList('reportTotalBookings', $sql->execute());