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   1693 Views

Avatar
wildflower1975

Community Member, 59 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 [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

Community Member, 59 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