Creating reports - the basics

Create Silverstipe basic reports using SS_Report

All reports in the Silverstripe Reports section extend the SS_Reports class - that goes for reports belonging to the Reports section of the CMS, as well as for the SiteReports you find in the bottom of the left SireTree menu.

Some documentation on the SS_Report class can be found in the classfile itself (/cms/code/Report.php), and in the SilverStripe API. There used to be a SSReport class, but that is now deprecated. There are no real tutorials on this subject, so I'll just follow my own ideas here...

A simple 'hello world' report

In its simplest form a report ccoul look like this - a title, a description and that's it. Not much of a report...!

class SimplePageReport extends SS_Report {

	// the report title
	function title() {
		return 'Hello World Report';
	}

	// the report description
 	function description() {
		return 'This report does absolutely nothing but show itself';
	}

 	function getCMSFields() {
		$fields = new FieldSet(
			new LiteralField('ReportTitle', "<h3>{$this->title()}</h3>" ),
			new LiteralField('ReportDescription', "<h3>{$this->description()}</h3>" )
		);
	}
 }

The ReportAdmin uses the Template cms/templates/ReportAdminForm.ss, and the only thing it'll find to render are the title() to use in the left menu, and the ReportTitle and ReportDescription fields shown on the right. All other calls just get a blank...

Register the report

In order to show the report, you need to register it in your _config.php file.

SS_Report::register("ReportAdmin", "SimplePageReport");

Note: if you were to create a SideReport, you would use SS_Report::register("SideReport", "SimplePageReport");

Report basics: a tableListField and some filters

The SS_Report class, together with the ReportAdminForm,ss template, is fully aimed at creating reports similar to the default BrokenLinks report: a title, a TableListField of some kind to show the data, and a set of filters to limit the view. So it's probably a good idea to find out first how to use that basic functionality, let's say to show a list of all pages on the site and filter them on pagetype.

This time we skip the getCMSFields() function, and use the default one in SS_Report. It will add a TableListField by calling the built-in getReportField() method. All we need to do is provide it with some data:

  • function columns() - The colums for the TableListField -
  • function sourceQuery($params) - A  SQLQuery object that represents the query to get the data
<?php
class SimplePageReport extends SS_Report {

	/*
	 *  SS_Report->getCMSFields() uses $description the property directly!
	 */
	protected $description = 'Shows a list of all pages in the CMS';


	// the report title
	function title() {
		return 'Simple Page Report';
	}


	/**
	 * Return an array of columns to display in your report.
	 */
	function columns() {
		return array(
			'ClassName' => 'Page type',
			'Title' => 'Title',
			'MenuTitle' => 'Menu title',
			'Created' => 'Created'
		);
	}


	/**
	 * Return the {@link SQLQuery} that provides your report data.
	 */
	function sourceQuery($params) {
		$sqlQuery = new SQLQuery();
		$sqlQuery->select = array('ID', 'Title', 'ClassName', 'MenuTitle', 'Created');
		$sqlQuery->from = array('SiteTree');
		return $sqlQuery;
	}
}

Careful: DataObject::get won't work! SQLQuery gives all the flexibility to create more flexible queries as in complex joins, distinct queries and other options! 

Adding a filter

Let's start by adding a simple textfield filter where you can type in the Pagetype to filter on. Adding a filter means two things:

  1. function parameterFields() - add the filterfield(s) to the form
  2. function sourceQuery($params) - use the filter found in $params in the SQLQuery
	/**
	 * Return a FieldSet specifying the search criteria for this report.
	 */
	function parameterFields() {
		$fields = new FieldSet(
			new TextField('ClassName', 'Page type')
		);
		return $fields;
	}


	/**
	 * Return the {@link SQLQuery} that provides your report data.
	 */
	function sourceQuery($params) {
		$sqlQuery = new SQLQuery();
		$sqlQuery->select = array('ID', 'Title', 'ClassName', 'MenuTitle', 'Created');
		$sqlQuery->from = array('SiteTree');

		// check for filters
		if(!empty($params['ClassName'])) {
			$sqlQuery->where = array("ClassName = '" . $params['ClassName'] . "'");
		}
		return $sqlQuery;
	}

A dropdown filter

Creating a dropdown filter for the pagetypes is just that tiny bit more complex:

	/**
	 * Return a FieldSet specifying the search criteria for this report.
	 * in this case: a dropdown to filter on ClassName
	 */
	function parameterFields() {
		$pagetypes = $this->getDistinctPageTypes()->map();
		$fields = new FieldSet(
			new DropdownField('ClassName', 'Pagetype', $pagetypes)
		);
		return $fields;
	}


	/*
	 * getting the Pagetypes requires a DISTINCT query on ClassName. Therefor
	 * we use SQLQuery. map() wants a two fields, so twice the ClassName.  
	 * Need to give them different aliases though
	 */
	function getDistinctPageTypes() {
		$sqlQuery = new SQLQuery();
		$sqlQuery->distinct = true;
		$sqlQuery->select = array('ClassName as ID1','ClassName as ID2');
		$sqlQuery->from = array('SiteTree');
		return $sqlQuery->execute();
	}

One irritating quirk: creating a dropdown on ClassName will automatically give you the 'The page type will be updated after the page is saved' popup, that belongs to the pagetype dropdown in the page behaviour tab, but is in fact linked from CMSMain-right.js to every ClassName dropdown. Anyway, for the sake of this example...

Pagination

Pagination of the tableListField is hardcoded into the getReportField() method of the SS_Report class, and set to 50 records per page. overriding this method is easy, and can be used to manage other settings as well:

	/**
	 * Return a field, such as a ComplexTableField or TableListField that is
	 * used to show and manipulate data relating to this report.
	 */
	function getReportField() { 
		$tableListField = parent::getReportField();
		$tableListField->setPageSize(10);
		return $tableListField ;
	}

Formatting headers and fields

We can use the Columns() method to format the titles and the fields. Formatting the titles is easy: just fill in some (HTML) string. Beware: it's not always clear how SilverStripe will render HTML in the titles...

To display fields in a certain way, we can either use casting or formatting. 

  • casting - show the value as it would be outputted by a special object like 'Date' or 'Text', and optionally use casting methods like Date->Nice, or Text->FirstSentence.
  • formatting - use html and variables like $ID or $Title to create specal output, like links. These fields don't actually relate one-on-one to a database field, but can be mixed!

Note: casting doesn't seem to support parameters, so something like Date->FormatI18N('%B ...') cannot be used. To do something like that you could probably create a Date Decorator, and for instance have some FullI18N() function return the full date translated. The following example casts the Created field, and formats an edit field to link to the page in the CMS:

function columns() {
	return array(
		'ClassName' => 'Page type',
		'Title' => 'Title',
		'Created' => array(
			'title' => 'Created',
			'casting' => 'Date->Full'
		),
		'Edit' => array(
			'title' => 'Edit',
			'formatting' => '<a href=\"admin/show/$ID\">$MenuTitle</a>'
		)
	);
}

Sorting the fields

The TableListField will take care of sorting the columns based on which columnheader is clicked - but beware of the following

  • if the sourceQuery() is setup to do a 'request *', sorting is no longer available
  • if Fieldformatting is used, like in the edit link above, it's no longer clear what is sorted, but most times it will not be the visible output!
  • You cannot actively exclude headers from being sortable

Why can we not exclude individual headers from being sortable?

  1. SS_Report feeds the SQLQuery object (that is returned by your reports sourceQuery() function) to the TableListField as a CustomQuery.
  2. The TableListField Headings() function calls $this->isFieldSortable($fieldName) to see if a field is sortable
  3. isFieldSortable calls the custom query's canSortBy($fieldName) function. The CustomQuery is a SQLQuery object, so the SQLQuery::canSortBy() method is used.

Conclusion: unfortunately we cannot influence the SQLQuery::canSortBy() function directly, so for that we need to extend the SQLQuery class, so it can then have its own canSortBy() method...

Extending SQLQuery to enable canSortBy()

This simple class extends SQLQuery with a $sortableColumns array that is ignored if it is not set. If it is set and canSortby() finds the field sortable, it will then be held against the array. If it's not in there, it will not be sortable:

class SimplePageReport_Query extends SQLQuery {

	public $sortableColums;

	/**
	 *  determine which colums are sortable. If empty, let
	 *  the SimplePageReport_Query make its own decision
	 */
	public function setSortableColumns($sortableColumns) {
		$this->sortableColums = $sortableColumns;
	}


	/**
	 * Returns true if this query can be sorted by the given field.
	 * The parent result is held against the sortableColums array
	 * and if it's not in there, the result will be false 
	 */
	function canSortBy($fieldName) {
		$fieldName = preg_replace('/(\s+?)(A|DE)SC$/', '', $fieldName);
		
		$canSortBy = parent::canSortBy($fieldName);

		// add: if there are sortable fields, and this field is not in them, return false
		if ($canSortBy && 
		    isset($this->sortableColums) && 
		    !(in_array($fieldName, $this->sortableColums))
		) $canSortBy = false;

		return $canSortBy;
	}
}

Now to set this in motion, we need to change the sourceQuery method to use the new SimplePgeReport_Query object:

	/**
	 * Return the {@link SQLQuery} that provides your report data.
	 * In this version the SimplePageReport_Query object is used so we
	 * can set the columns we want to be sortable:
	 */
	function sourceQuery($params) {
		$sqlQuery = new SimplePageReport_Query();
		$sqlQuery->select = array('ID', 'Title', 'ClassName', 'MenuTitle', 'Created');
		$sqlQuery->from = array('SiteTree');
		if(!empty($params['ClassName'])) {
			$sqlQuery->where = array("ClassName = '" . $params['ClassName'] . "'");
		}

		$sqlQuery->setSortableColumns(array('ClassName', 'Title', 'Created'));

		return $sqlQuery;
	}

Using SS_Report_FakeQuery

The SS_Report_FakeQuery class (stupid name!) is in fact a SQLQuery extension like the above: a dressed-up SQLQuery object with extra's like canSortBy(). In order to use SS_Report_FakeQuery, skip the sourceQuery method in favor of a sourceRecords() method that returns a DataObjectSet.

Note:Unfortunately, setting the TableListField pagesize is compromized: pagination will work as if only ten records are shown, but in fact all of them are. Setting a limit to DataObject::get will just get you 10 records. That sort of rules out this object for me...

@TODO:

  • Use SS_Report_FakeQueryas an extended query object and add a sortColumns() function
  • Add some actions (doing what?)
  • Extend ReportAdmin to use another report template (would copying ReportAdminForm.ss to mysite/templates/ work?)
  • Create complex reports extending the getReportField() method?
  • Create complex reports any other way?
Convert SQLQuery result to DataObjectSet:
$result = $sqlQuery->execute();
 $myDataObjectSet = singleton('DataObject)->buildDataObjectSet($result);

Comments

  • Really thanx for this tutorial.
    Since very less documentation of SS_Report from SilverStripe site.

    Especially your example of
    function sourceQuery($params){}

    Thx!

    Verstuurd door Adi Surya, 04/12/2011 5:00pm (5 jaar geleden)

Het versturen van reacties is uitgeschakeld.

RSS feed voor reacties op deze pagina