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.

Customising the CMS

SSReport in 2.3.7 and SS_Report in 2.4.0


Reply

3 Posts   1651 Views

Avatar
wildflower1975

8 April 2010 at 9:50pm (Last edited: 16 April 2010 10:05am), Community Member, 59 Posts

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 [url http=http://doc.silverstripe.org/doku.php?id=sqlquery#transforming_a_result_to_dataobjectset]transforming_a_result_to_dataobjectset[/url]

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 [url http=http://api.silverstripe.org/default/DataReport.html]DataReport[/url]
and [url http=http://api.silverstripe.org/default/SQLReport.html]SQLReport[/url]
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

16 April 2010 at 10:05am Community Member, 59 Posts

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

6 July 2010 at 10:58am Community Member, 2 Posts

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