REST V10 Report Data - So Close

The API Endpoint you are using above is simply the <module>/<record_id> API Endpoint and that is why you are returning the Report Record, and not the Report Data.

You can get the Report Data in PDF format by using the following API Endpoint:

Reports/<record_id>/pdf

The only two Export Types included in Sugar by default are PDF and Base64. The developer Article you mentioned above, shows how to add in a CSV export of the data, by adding in a custom Endpoint to the API, and this would be the only way to get a JSON format of the results as well. TO use the Developer article above, your request should go to Reports/<record_id>/csv

For your scenario you would want to extend the ReportsExportApi (modules/Reports/clients/base/api/ReportsExportApi.php) and add in a 'exportJSON' method to return the JSON formatted results of the report, when accessing Reports/<record_id>/json

Hey Mike,

Thank you I was adding the /json & /csv to the end and trying other combinations as I assumed more than just pdf must exist and was trying to find through the sugar src the available options, what through me off was being able to export data to CSV within the CRM so I assumed i was doing something incorrect or using the wrong parameter

I tried base64 but when I decode it I see in the header information related to pdf so assume that just returns the report in pdf format via base64 encode

Thanks for confirming that I must extended that class & define json to add it myself.

After I do this, I will share this with the community.

Cheers

Hey Mike,

Having trouble even getting the example for CSV picked up by the Rest Service

I have the file name exactly the same as the class name in a location you specified (minus the .php)

My understanding is, when it works it should auto generate and be visible on the /rest/v10/help screen

I don't see a reference to it there and the method path does not exist.

thanks but so far, very easy to see this is the right area to extend it to do what I want with it!

EDIT

Found on  https://developer.sugarcrm.com/2014/03/10/sugarcrm-cookbook-adding-a-rest-endpoint-2/

Hey, what gives? First things first let’s check to see if it registered correctly by looking for the endpoint definition in /help, navigate over to /rest/v10/help in your browser and look for it. Not there? didn’t think so.

We added the class and it didn’t load. Since the REST API looks for so many files in so many directories we have to add some heavy duty caching in order to speed up the url lookup on every single request. In order for changes in endpoint definitions to show up we need to login as an admin and run quick repair and rebuild.

After quick repair, let’s check /rest/v10/help again and you should see a line like this:

Our Developer Guide might have better and more up to date information on Extending the V10 API: https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.7/Integration/Web_Services/v10/Extend…

Your new Endpoint might not be showing in the Help documentation, if you were directly extending the default ReportsExportApi, and not changing the Registered endpoint (i.e. Reports/:record_id/:export_type ) it would still show in the same help section, you just merely added an Export Type to that Endpoint.

If that doesn't help, below is a Custom Reports Endpoint I had built one morning awhile back, when someone asked if it was possible to alter the Report Run-Time filters via the API when generating a PDF. Placing this code in custom/modules/Reports/clients/base/api/CustomReportsExportApi.php will add in the logic to pass in run-time filters to the standard Report Export API, and will add an extra Endpoint to the Help documentation for POST to Reports/:report_id/:export_type . Since this extends the exact API I mentioned, you should be able to add in a exportJson() method to it, to manipulate the Report Obj passed into it. No guarantees this still works, as I did not test it again since I last looked at it.

<?php if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');  require_once('data/BeanFactory.php'); require_once('include/download_file.php'); require_once('modules/Reports/clients/base/api/ReportsExportApi.php');  class CustomReportsExportApi extends ReportsExportApi {      public function registerApiRest() {                   return array(                           'exportRecord' => array(                                   'reqType' => 'GET',                                   'path' => array('Reports', '?', '?'),                                   'pathVars' => array('module', 'record', 'export_type'),                                   'method' => 'exportRecord',                                   'rawReply'=> true,                                   'shortHelp' => 'This method exports a record in the specified type',                                   'longHelp' => '',                           ),               'exportRecordPost' => array(                                   'reqType' => 'POST',                                   'path' => array('Reports', '?', '?'),                                   'pathVars' => array('module', 'record', 'export_type'),                                   'method' => 'exportRecord',                                   'rawReply'=> true,                                   'shortHelp' => 'This method exports a record in the specified type. Allows for passing in of Report Run Time filters to alter the exported report data.',                                   'longHelp' => '',                           ),                  );           }      public function exportRecord($api, $args) {         $this->requireArgs($args, array(             'record',             'export_type'         ));         $args['module'] = 'Reports';         $GLOBALS['disable_date_format'] = FALSE;         $method = 'export' . ucwords($args['export_type']);         if (!method_exists($this, $method)) {             throw new SugarApiExceptionNoMethod('Export Type Does Not Exists');         }         $saved_report = $this->loadBean($api, $args, 'view');         if (!$saved_report->ACLAccess('view')) {             throw new SugarApiExceptionNotAuthorized('No access to view records for module: Reports');         }         if (isset($_POST)){             $reportObj = $this->updateRunTimeFilters($saved_report);         }         return $this->$method($api, $reportObj);     }      protected function updateRunTimeFilters(SugarBean $report) {         global $current_user;         // do this to go through the transformation         $report_def = isset($_POST['report_def']) ? html_entity_decode($_POST['report_def']) : array();         $reportID = $report->id;         if (!empty($_POST['reset_filters'])) {             $rCache = new ReportCache();             $rCache->delete($report->id);             $newArray = array();             $newArray['filters_def'] = $report->report_def['filters_def'];             $reportCache = saveReportFilters($reportID, json_encode($newArray));             $report = new Report($report->content);         } else if (!empty($_POST['report_def'])) {             $requestFilterDef = json_decode($report_def, TRUE);             $reportContent = json_decode($report->content, TRUE);             $reportContent['filters_def'] = $requestFilterDef['filters_def'];             if (isset($requestFilterDef['order_by'])) {                 $reportContent['order_by'] = $requestFilterDef['order_by'];             }             if (isset($requestFilterDef['summary_order_by'])) {                 $reportContent['summary_order_by'] = $requestFilterDef['summary_order_by'];             }             $report_def = json_encode($reportContent);             $report = new Report($report_def);             $newArray = array();             $newArray['filters_def'] = $requestFilterDef['filters_def'];             $reportCache = saveReportFilters($reportID, json_encode($newArray));         }         return $report;     } }

Hey Mike,

You actually perfectly responded to my next question, passing arguments to the run time filters but again completely stuck with them, I am unsure of the structure of the array to POST if I want to pull data out from a reports between TWO dates, I see your example is changing the order by.

EDIT; your code in a php code tag

<?php if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');


require_once('../SG/data/BeanFactory.php');
require_once('../SG/include/download_file.php');
require_once('../SG/modules/Reports/clients/base/api/ReportsExportApi.php');


class CustomReportsExportApi extends ReportsExportApi
{
    public function registerApiRest() {
        return array(
            'exportRecord' => array(
                'reqType' => 'GET',
                'path' => array('Reports', '?', '?'),
                'pathVars' => array('module', 'record', 'export_type'),
                'method' => 'exportRecord',
                'rawReply'=> true,
                'shortHelp' => 'This method exports a record in the specified type',
                'longHelp' => '',
                ),
            'exportRecordPost' => array(
                'reqType' => 'POST',
                'path' => array('Reports', '?', '?'),
                'pathVars' => array('module', 'record', 'export_type'),
                'method' => 'exportRecord',
                'rawReply'=> true,
                'shortHelp' => 'This method exports a record in the specified type. Allows for passing in of Report Run Time filters to alter the exported report data.',
                'longHelp' => '',
                ),
            );
    }
    public function exportRecord($api, $args) {
                $this->requireArgs($args, array(
                                    'record',
                                    'export_type'
                                ));


                $args['module'] = 'Reports';
                $GLOBALS['disable_date_format'] = FALSE;
                $method = 'export' . ucwords($args['export_type']);


                if (!method_exists($this, $method))
                {
                        throw new SugarApiExceptionNoMethod('Export Type Does Not Exists');
                }         $saved_report = $this->loadBean($api, $args, 'view');


                if (!$saved_report->ACLAccess('view'))
                {
                    throw new SugarApiExceptionNotAuthorized('No access to view records for module: Reports');
                }
                if (isset($_POST)){
                        $reportObj = $this->updateRunTimeFilters($saved_report);
                }


        return $this->$method($api, $reportObj);
    }


    protected function updateRunTimeFilters(SugarBean $report) {
                    global $current_user;         // do this to go through the transformation


                             $report_def = isset($_POST['report_def']) ? html_entity_decode($_POST['report_def']) : array();
                             $reportID = $report->id;


                            if (!empty($_POST['reset_filters'])) {
                                $rCache = new ReportCache();
                                $rCache->delete($report->id);


                                $newArray = array();
                                $newArray['filters_def'] = $report->report_def['filters_def'];
                                $reportCache = saveReportFilters($reportID, json_encode($newArray));
                                $report = new Report($report->content);
                            }
                            else if (!empty($_POST['report_def'])) {
                                $requestFilterDef = json_decode($report_def, TRUE);
                                $reportContent = json_decode($report->content, TRUE);


                                $reportContent['filters_def'] = $requestFilterDef['filters_def'];
                                if (isset($requestFilterDef['order_by'])) {
                                    $reportContent['order_by'] = $requestFilterDef['order_by'];
                                }
                                if (isset($requestFilterDef['summary_order_by'])) {
                                        $reportContent['summary_order_by'] = $requestFilterDef['summary_order_by'];
                                }
                                $report_def = json_encode($reportContent);


                                $report = new Report($report_def);
                                $newArray = array();
                                $newArray['filters_def'] = $requestFilterDef['filters_def'];
                                $reportCache = saveReportFilters($reportID, json_encode($newArray));
                            }
                        return $report;
    }
};

Cheers

Hi Gavan,

So the POST data for editing filters matches that as seen when you actually go to the Reports module and edit the Run-time filters. Since this was a quick prototype, I didn't re-write any code, this was merely a movement of code that already existed within the Reports module moved to the API. If you monitor those Requests in the browser console, you can get an understanding of how it should be formatted. Essentially, when the Run-time filters are edited on the Reports, it submits the 'filters_def' portion of the Report Definition (see the saved_reports table for the full report definition). We don't really have documentation on the technical structure of report definitions, because up until now, programmatic usage of Report definitions hasn't really be a thing as far as I've known. That is about the best I can do for an explanation for how the code I provided worked for manipulating the definition. You could add more logic to make argument passing much simpler, as the Report Definition is a bit bulky.

Here is a snippet of passing the Filter Definition:

"report_def": "{"filters_def":{"Filter_1":{"0":{"name":"user_name","table_key":"Tasks:assigned_user_link","qualifier_name":"one_of","runtime":1,"input_name0":["seed_max_id"],"column_name":"Tasks:assigned_user_link:user_name","id":"rowid0"},"operator":"AND"}}}"

Hey Mike,

I think I am just going to host my custom static report functionality on the server Sugar CRM is hosted on and just run my MYSQL query directly to the database which is much more easier for me to work with and edit, and thankfully you can get the exact SQL query used when running a report by selecting Show Query and I can easily put my requested date range in.

I don't want to give up, but it just seems like I am barking up the wrong tree.

Cheers

Hi @Mike Russell, I have followed this steps but I m getting the error while listing the reports. error=> Export type does not exist.

My Code.

file name => CustomReportsExportApi.php

class CustomReportsExportApi extends ReportsExportApi {
 

    public function registerApiRest() {
         return parent::registerApiRest();  
    }

   protected function exportJson(ServiceBase $api, SugarBean $report)
    {

         //my code here

   }

}