Jump to:

5539 Posts in 1738 Topics by 1224 members

Customising the CMS

SilverStripe Forums » Customising the CMS » SSReport in 2.3.7 and SS_Report in 2.4.0

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

Page: 1
Go to End
Author Topic: 1638 Views
  • wildflower1975
    Avatar
    Community Member
    59 Posts

    SSReport in 2.3.7 and SS_Report in 2.4.0 Link to this post

    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

  • wildflower1975
    Avatar
    Community Member
    59 Posts

    Re: SSReport in 2.3.7 and SS_Report in 2.4.0 Link to this post

    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?

  • ib
    Avatar
    Community Member
    2 Posts

    Re: SSReport in 2.3.7 and SS_Report in 2.4.0 Link to this post

    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

    1638 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.