Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Customising the CMS /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

SSReport in 2.3.7 and SS_Report in 2.4.0


Go to End


3 Posts   2459 Views

Avatar
wildflower1975

Community Member, 63 Posts

8 April 2010 at 9:50pm

Edited: 16/04/2010 10:05am

Are there any tutorials out there on how to write a custom SQL query with COUNT() and GROUP BY,
maybe put them in a DataObjectSet,
then feed them into TableListField?

this kind of does it transforming_a_result_to_dataobjectset

but it looks like I need to make a custom DataObject for the singleton?

 $myDataObjectSet = singleton('Player')->buildDataObjectSet($result);

Is this "the way" to do it?
Is TableListField the only/best way to show Report results?

I see there's lots of 'Report' objects in the api documentation like DataReport
and SQLReport
but the documentation is set to ToDo (or is it the functionality?) and those files aren't
Located in /sapphire/forms/DataReport.php (line 6)
Located in /sapphire/forms/SQLReport.php (line 3)

I see in 2.4.0 the Report section has been updated so this could all end up deprecated.

cheers

Avatar
wildflower1975

Community Member, 63 Posts

16 April 2010 at 10:05am

I'm trying to write a report using a Group By query and have tried both sourceRecords and sourceQuery methods without success.

function sourceRecords($params = null,$sort = null, $limit = null) {
		
		$query = new SQLQuery(
		
		$select = "CaseManagerID, Count(AmountSought) as Count, Sum(AmountSought) as Total",
		$from = "CreditApplicationSubmission",
		$where = "",
 		$orderby = "",
		$groupby = "CaseManagerID",
		$having = "",
 		$limit = ""
		);
		$results = $query->execute();
	
   $data = new DataObjectSet(); 
       
   foreach($results as $row) { 
      $data->push($row);
   } 
		//Debug::show($data);
		return $data; 

this returns

Fatal error: Call to a member function toMap() on a non-object in C:\wamp\www\silverstripe\cms\code\Report.php on line 395

Am I creating the DatObjectSet correctly for this method?

for the sourceQuery method:

	function sourceQuery() {
		
		$query = new SQLQuery(
		
		$select = "CaseManagerID, Count(AmountSought) as Count, Sum(AmountSought) as Total",
		//$select = "*",
		$from = "CreditApplicationSubmission",
		$where = "",
 		$orderby = "",
		$groupby = "CaseManagerID",
		$having = "",
 		$limit = ""
		);
		
		return $query;

I end up with an error:

ERROR [Notice]: Undefined index: ClassName
IN POST /silverstripe/admin/reports/show/CreditApplicationSubmissionReport
Line 2678 in C:\wamp\www\silverstripe\sapphire\core\model\DataObject.php
/code]

If I use the $select = "*" in the sourceQuery method then it all works,
so it looks like it can't handle SQL functions like SUM or COUNT or am I doing it wrong?

Avatar
ib

Community Member, 2 Posts

6 July 2010 at 10:58am

hi wildflower,

to fix the Undefined index: ClassName you should add classname and recordclassname to your select as documented in
http://doc.silverstripe.org/sqlquery#transforming_a_result_to_dataobjectset