+ Start a Discussion
David Roberts 4David Roberts 4 

How to produce reports in Apex and Lightning

I found some references to displaying report data in Lightning but have got stuck.
I'm using a simple Account Visit report which has a group but I don't find it with the code I have. Basically, I see the results in a JSON but can't interpret them into my chart.
Here's my class:
public class ReportViaApexController {
    //https://absyz.com/displaying-standard-report-data-in-lightning-component/
    //https://cloudanswers.com/blog/run-salesforce-reports-in-apex
    //https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_analytics_report_data.htm
    //https://www.linkedin.com/pulse/reports-apex-its-use-vijayakumar-vm
    //
    @AuraEnabled
    public static string getReportPlanned(String AccId){
        //Report repRec = [SELECT Id, Name, DeveloperName FROM Report WHERE DeveloperName LIKE 'Catalogue_Summary%'];
        Report repRec = [SELECT Id, Name, DeveloperName FROM Report WHERE DeveloperName LIKE 'Visit_Reports_by_accounts%'];
        //00O8E000000HkNvUAK
        //
        string reportPlannedId=repRec.Id;
        string filterId=String.valueOf(AccId).substring(0, 15);
        // Retrieves report metadata
        Reports.ReportDescribeResult describe = Reports.ReportManager.describeReport(reportPlannedId);
        Reports.ReportMetadata reportMd = describe.getReportMetadata();
        // Add/Override filters
        Reports.ReportFilter filterlist = reportMd.getReportFilters()[0];
        filterlist .setValue(filterId);
        //filterlist .setValue('In Progress');
        //and Run report
        Reports.ReportResults reportResult = Reports.ReportManager.runReport(reportPlannedId,reportMd);
        system.debug('ReportResultsJSON '+JSON.serialize(reportResult));
        
        MAP<String,Reports.ReportFact> summarizedRows=reportResult.getFactMap();
        
        System.debug('summarizedRows: ' + summarizedRows);
        
        // Get the first down-grouping in the report
        Reports.Dimension dim = reportResult.getGroupingsDown();
        System.debug('dim: ' + dim);
        Reports.GroupingValue groupingVal = dim.getGroupings()[0];
        System.debug('Key: ' + groupingVal.getKey());
        System.debug('Label: ' + groupingVal.getLabel());
        System.debug('Value: ' + groupingVal.getValue());
        
        // Construct a fact map key, using the grouping key value
        String factMapKey = groupingVal.getKey() + '!T';
        
        // Get the fact map from the report results
        Reports.ReportFactWithDetails factDetails =
            (Reports.ReportFactWithDetails)reportResult.getFactMap().get(factMapKey);
        
        // Get the first summary amount from the fact map
        Reports.SummaryValue sumVal = factDetails.getAggregates()[0];
        System.debug('Summary Value: ' + sumVal.getLabel());
        
        // Get the field value from the first data cell of the first row of the report
        Reports.ReportDetailRow detailRow = factDetails.getRows()[0];
        System.debug(detailRow.getDataCells()[0].getLabel());
        
        
        
        return JSON.serialize(reportResult);
    }//Getreportplanned

}//ReportViaApexController

My component from the bundle ReportViaApex:
<aura:component controller="ReportViaApexController" access="global" implements="force:appHostable,flexipage:availableForAllPageTypes,flexipage:availableForRecordHome,force:lightningQuickAction">

    <ltng:require scripts="{!$Resource.chartJS23}" afterScriptsLoaded="{!c.createPlannedchart}"/>
    <aura:attribute name="accrecId" type="String" default = "0018E00000Wg0ZLQAZ" />
    <canvas aura:id="chartCanvas" height="300" width="300"></canvas>
    
</aura:component>

and my controller:
({
    
    createPlannedchart: function(component) {
        var chartCanvas = component.find("chartCanvas").getElement();
        console.log(chartCanvas);
        var action = component.get("c.getReportPlanned");
        var getPlannedReportId = '00O8E000000HkNvUAK'; //Visit_reports_by_accounts%
        var accIdsub = '0018E000008IK3SQAW'; //an account
        var recId = component.get("v.accrecId");
        console.log('got recId = '+ recId);
        var urlPlannedreport = '/lightning/r/Report/'+getPlannedReportId+'/view?queryScope=userFolders&fv0='+accIdsub;
        //fv is filter value
    //e.g. 
    //https://virtualworlds--triggertes.lightning.force.com/lightning/r/Report/00O8E000000HkNvUAK/view?queryScope=userFolders&fv0=0018E000008IK3SQAW
    //console.log(urlPlannedreport);
       
    action.setParams({
        "AccId": recId // component.get("v.accrecId")
    });
    action.setCallback(this, function(response) {
        var state = response.getState();
        if (state === "SUCCESS") {
            var reportResultData = JSON.parse(response.getReturnValue());
            console.log(JSON.stringify(reportResultData));
            var chartData = [];
            var chartLabels = [];
            var gDown = reportResultData.groupingsDown;
            console.log('gDown = ',gDown);
            var nullcheck = reportResultData.groupingsDown.groupings;
            if (nullcheck !== null) {
                for (var i = 0; i < (reportResultData.groupingsDown.groupings.length); i++) {
                    //Iterate and prepare the list of Labels for the chart
                    var labelItem = reportResultData.groupingsDown.groupings[i].label;
                    chartLabels.push(labelItem);
                    var keyTemp = reportResultData.groupingsDown.groupings[i].key;
                    //Prepeare the chart data to be plotted.
                    var valueTemp = reportResultData.factMap[keyTemp + "!T"].aggregates[0].value;
                    chartData.push(valueTemp);
                }//next i
            }//endif nullcheck
            
            
            //Construct chart-doughnut/bar
            var chart = new Chart(chartCanvas, {
                type: 'bar',
                data: {
                labels: chartLabels,
                datasets: [{
                label: "Count",
                data: chartData,
                backgroundColor: [
                "#52BE80",
                "#76D7C4",
                "#1E8449",
                "#2ECC71",
                "#FFB74D",
                "#E67E22",
                "#F8C471",
                "#3498DB",
                "#00BCD4",
                "#D32F2F",
                "#82E0AA",
                "#AFB42B"
                ]
            }]
                                  },
                                  options: {
                                  responsive: true,
                                  title: {
                                  display: true,
                                  text: 'Planned Visits'
                                  },
                                  scales: {
                                  
                                  yAxes: [{
                                  ticks: {
                                  // max: 100,
                                  stepSize: 25,
                                  beginAtZero: true
                                  },
                                  scaleLabel: {
                                  display: true,
                                  labelString: 'Visits Count'
                                  },
                                  }],
                                  xAxes: [{
                                  scaleLabel: {
                                  display: true,
                                  labelString: 'Created Date'
                                  }
                                  }]
                                  },
                                  maintainAspectRatio: false,
                                  legend: {
                                  display: false,
                                  position: "right",
                                  fullWidth: false,
                                  }
                                  }
                                  });
                                  
                                  
            
        } else if (state === "ERROR") {
            console.log('Problem, response state: ' + state);
        }//endif error
        
        });
        $A.enqueueAction(action);
        
        
    },//createPlannedchart
})

I hope someone can't point me in the right direction.

Here's my returned metadata:
{"reportMetadata":{"topRows":null,"standardFilters":null,"standardDateFilter":{"startDate":null,"endDate":null,"durationValue":"CUSTOM","column":"DUE_DATE"},"sortBy":null,"showSubtotals":false,"showGrandTotal":false,"scope":"organization","reportType":{"type":"AccountCustomEntity$Visit_Report__c","label":"Companies with Visit Reports"},"reportFormat":"SUMMARY","reportFilters":[{"value":"0018E00000Wg0ZLQAZ","operator":"equals","filterType":"fieldValue","column":"ACCOUNT_ID"}],"reportBooleanFilter":null,"name":"Visit reports by accounts","id":"00O8E000000HkNvUAK","historicalSnapshotDates":null,"hasRecordCount":true,"hasDetailRows":false,"groupingsDown":[{"sortOrder":"ASCENDING","sortAggregate":null,"name":"ACCOUNT.NAME","dateGranularity":"NONE"}],"groupingsAcross":null,"division":null,"developerName":"Visit_reports_by_accounts","detailColumns":["CUST_NAME","Visit_Report__c.Date__c","Visit_Report__c.Type__c","Visit_Report__c.Goal_of_the_visit__c","Visit_Report__c.Goal_of_next_visit__c","Visit_Report__c.Main_competitor__c"],"description":null,"customSummaryFormula":null,"currencyCode":"GBP","crossFilters":null,"buckets":null,"aggregates":["RowCount"]},"reportExtendedMetadata":{"groupingColumnInfo":{"ACCOUNT.NAME":{"name":"ACCOUNT.NAME","label":"Account Name","groupingLevel":0,"dataType":"STRING_DATA"}},"detailColumnInfo":{"Visit_Report__c.Main_competitor__c":{"name":"Visit_Report__c.Main_competitor__c","label":"Main competitor","dataType":"PICKLIST_DATA"},"Visit_Report__c.Goal_of_next_visit__c":{"name":"Visit_Report__c.Goal_of_next_visit__c","label":"Goal of next visit","dataType":"STRING_DATA"},"Visit_Report__c.Goal_of_the_visit__c":{"name":"Visit_Report__c.Goal_of_the_visit__c","label":"Goal of the visit","dataType":"STRING_DATA"},"Visit_Report__c.Type__c":{"name":"Visit_Report__c.Type__c","label":"Type","dataType":"PICKLIST_DATA"},"Visit_Report__c.Date__c":{"name":"Visit_Report__c.Date__c","label":"Date","dataType":"DATE_DATA"},"CUST_NAME":{"name":"CUST_NAME","label":"Visit Report: Visit Report Name","dataType":"STRING_DATA"}},"aggregateColumnInfo":{"RowCount":{"name":"RowCount","label":"Record Count","downGroupingContext":null,"dataType":"INT_DATA","acrossGroupingContext":null}}},"hasDetailRows":false,"groupingsDown":{"groupings":null},"groupingsAcross":{"groupings":null},"factMap":{"T!T":{"key":"T!T","aggregates":[{"value":0,"label":"0"}]}},"allData":true}

​​​​​​​