reportComponent

AnalyticsUtils.cls

/**
 * @description       : used for interfacing with the Reports API and Report class to get information from reports to display in a lightning component
 *                      currently used with our Account Lightning Detail page to get bid history information displayable on the Account
 *                       USED WITH PERMISSION FROM LICENSE IN REPO FOR 2015 VERSION
 * @author            : clay b
 * @group             : reports
 * @last modified on  : 11-07-2021
 * @last modified by  : sfdc cb
 * Modifications Log
 * Ver   Date         Author   Modification
 * 1.0   06-26-2021   clay b   Initial Version
 **/
global without sharing class AnalyticsUtils {
  public static String reportName { get; set; }
  public static String theSeedKeyword { get; set; }

  public AnalyticsUtils() {
    String theReport = '';
    reportName = theReport;
  }


  /**
   * @description called by the LightningReports controller with the reportComponent Aura component to retrieve report data filtered by  an Account Id
   * @author clay b | 06-26-2021
   * @param reportId
   * @param accountId
   * @return reportResponse returns the data from the API call so that it can be passed in for processing as a Summary,Tabular, or Matrix report
   **/
  public static reportResponse getReportResponse(String reportId, String theSeedKeyword) {
     reportName = reportId != null
      ? [SELECT Name FROM Report WHERE Id = :reportId LIMIT 1]?.Name
      : '';
    Reports.ReportDescribeResult rptDescribe = Reports.ReportManager.describeReport(
      reportId
    );

    Reports.ReportMetadata rptMetadata = rptDescribe.getReportMetadata();
    rptMetadata.setHasDetailRows(true);
    //get the report result


    List<Reports.ReportFilter> rptFilters = rptMetadata.getReportFilters();
    system.debug ('rptFilters' + rptFilters);

    for (Reports.ReportFilter rptFilter : rptFilters) {
			system.debug ('get column return value ::: ' + rptFilter.getColumn());
			if (rptFilter.getColumn() == 'Keyword__c.Seed_Keyword__c') {
        system.debug ('get column return value matched on Account.Id::: ' + rptFilter.getColumn());

        rptFilter.setValue(theSeedKeyword);
      } else if (rptFilter.getColumn() == theSeedKeyword) {
        system.debug ('get column return value matched on ID Field::: ' + rptFilter.getColumn());

        rptFilter.setValue(theSeedKeyword);
      }
    }

    Reports.ReportResults results = Reports.ReportManager.runReport(
      reportId,
      rptMetadata,
      true
    );


    //get the metadata
    Reports.ReportMetadata reportMetadata = results.getReportMetadata();

    //find out what type of report it is by looking at the groupings down and groupings across
    Integer groupingsDown = 0;
    Integer groupingsAcross = 0;

    List<Reports.GroupingInfo> groupingDownList = reportMetadata.getGroupingsDown();
    List<Reports.GroupingInfo> groupingAcrossList = reportMetadata.getGroupingsAcross();

    if (groupingDownList != null) {
      groupingsDown = groupingDownList.size();
    }

    if (groupingDownList != null) {
      groupingsAcross = groupingAcrossList.size();
    }

    String reportType = 'tabular';
    if ((groupingsDown > 0) && (groupingsAcross == 0)) {
      reportType = 'summary';
    }

    if ((groupingsDown > 0) && (groupingsAcross > 0))
      reportType = 'matrix';
  //system.debug('reportType ::: ' + reportType);

    reportResponse rr = new reportResponse();
    rr.reportType = reportType;

    if (reportType == 'tabular') {
      rr.tabResp = getTabularReportResponse(results);
    } else if (reportType == 'summary') {
      rr.sumResp = getSummaryReportResponse(results);
    } // else if (reportType == 'matrix') {
    //rr.tabResp = getMatrixReportResponse(results);
    //}
    rr.reportName = reportName;
  //system.debug(rr);
    return rr;
  }

  public static reportResponse getReportResponse(String reportId) {
    String theSeedKeyword = theSeedKeyword;
    reportName = reportId != null
     ? [SELECT Name FROM Report WHERE Id = :reportId LIMIT 1]?.Name
     : '';
   Reports.ReportDescribeResult rptDescribe = Reports.ReportManager.describeReport(
     reportId
   );

   Reports.ReportMetadata rptMetadata = rptDescribe.getReportMetadata();
   rptMetadata.setHasDetailRows(true);
   //get the report result


   List<Reports.ReportFilter> rptFilters = rptMetadata.getReportFilters();
   system.debug ('rptFilters' + rptFilters);

   for (Reports.ReportFilter rptFilter : rptFilters) {
     system.debug ('get column return value ::: ' + rptFilter.getColumn());
     if (rptFilter.getColumn() == 'Keyword__c.Seed_Keyword__c') {
       system.debug ('get column return value matched on Account.Id::: ' + rptFilter.getColumn());

       rptFilter.setValue(theSeedKeyword);
     } else if (rptFilter.getColumn() == theSeedKeyword) {
       system.debug ('get column return value matched on ID Field::: ' + rptFilter.getColumn());

       rptFilter.setValue(theSeedKeyword);
     }
   }

   Reports.ReportResults results = Reports.ReportManager.runReport(
     reportId,
     rptMetadata,
     true
   );


   //get the metadata
   Reports.ReportMetadata reportMetadata = results.getReportMetadata();

   //find out what type of report it is by looking at the groupings down and groupings across
   Integer groupingsDown = 0;
   Integer groupingsAcross = 0;

   List<Reports.GroupingInfo> groupingDownList = reportMetadata.getGroupingsDown();
   List<Reports.GroupingInfo> groupingAcrossList = reportMetadata.getGroupingsAcross();

   if (groupingDownList != null) {
     groupingsDown = groupingDownList.size();
   }

   if (groupingDownList != null) {
     groupingsAcross = groupingAcrossList.size();
   }

   String reportType = 'tabular';
   if ((groupingsDown > 0) && (groupingsAcross == 0)) {
     reportType = 'summary';
   }

   if ((groupingsDown > 0) && (groupingsAcross > 0))
     reportType = 'matrix';
 //system.debug('reportType ::: ' + reportType);

   reportResponse rr = new reportResponse();
   rr.reportType = reportType;

   if (reportType == 'tabular') {
     rr.tabResp = getTabularReportResponse(results);
   } else if (reportType == 'summary') {
     rr.sumResp = getSummaryReportResponse(results);
   } // else if (reportType == 'matrix') {
   //rr.tabResp = getMatrixReportResponse(results);
   //}
   rr.reportName = reportName;
 //system.debug(rr);
   return rr;
 }

  /**
  * @description called by the getReportResponse method above in controller with the results and report information after it recognizes the reportType as "Tabular"
                 matrix reports or summary reports are handled separately below
  * @author clay b | 06-26-2021
  * @param reportId
  * @param accountId
  * @return tabularReportResponse returns the data from the Tabular report and passes it back to the Component for processing
**/
  public static tabularReportResponse getTabularReportResponse(
    Reports.ReportResults results
  ) {
    tabularReportResponse trr = new tabularReportResponse();
    List<fieldDef> reportFields = new List<fieldDef>();
    List<List<fieldData>> fieldDataList = new List<List<fieldData>>();

    //get the metadata
    Reports.ReportMetadata reportMetadata = results.getReportMetadata();

    //get a string array of the field names
    List<String> fieldNames = reportMetadata.getDetailColumns();

    //get the extended metadata
    Reports.ReportExtendedMetadata reportExtendedMetadata = results.getReportExtendedMetadata();

    //get the map of the column names to their name and label
    Map<String, Reports.DetailColumn> detailColumnMap = reportExtendedMetadata.getDetailColumnInfo();

    //loop over the detailColumnMap and get the name, label, and data type
    for (String fieldName : fieldNames) {
      Reports.DetailColumn detailColumn = detailColumnMap.get(fieldName);
      fieldDef fd = new fieldDef();
      fd.fieldName = detailColumn.getName();
      fd.fieldLabel = detailColumn.getLabel();
      fd.dataType = detailColumn.getDataType().name();
      reportFields.add(fd);
    }
    // Get the fact map from the report results
    Reports.ReportFactWithDetails factDetails = (Reports.ReportFactWithDetails) results.getFactMap()
      .get('T!T');

    List<Reports.ReportDetailRow> reportDetailRowList = factDetails.getRows();

    //loop over the rows
    for (Reports.ReportDetailRow reportDetailRow : reportDetailRowList) {
      Integer cellCounter = 0;
      List<fieldData> fieldDataRow = new List<fieldData>();
      //loop over the cells in the row
      for (
        Reports.ReportDataCell reportDataCell : reportDetailRow.getDataCells()
      ) {
				fieldData fd = new fieldData();

        if ( reportFields[cellCounter].dataType == 'DATE_DATA')  {
          fd.fieldValue = String.valueOf(Date.valueOf(reportDataCell.getValue()));

        }  else if ( reportFields[cellCounter].dataType == 'CURRENCY_DATA')  {
          fd.fieldValue = String.valueOf(reportDataCell.getLabel());

        } else {
          fd.fieldValue = String.valueOf(reportDataCell.getValue());

        }

				fd.fieldLabel = String.valueOf(reportDataCell.getLabel());
				fd.dataType = reportFields[cellCounter].dataType;
				fd.isHyperLink = isHyperlink(fd.fieldValue);
				cellCounter++;
				fieldDataRow.add(fd);
      }

      //add the row to the list
      fieldDataList.add(fieldDataRow);
    }

    trr.reportFields = reportFields;
    trr.fieldDataList = fieldDataList;
    trr.reportName = reportName;
  //system.debug(trr);

    return trr;
  }

  /**
  * @description called by the getReportResponse method above in controller with the results and report information after it recognizes the reportType as "Summary"
                 matrix reports or summary reports are handled separately below
  * @author clay b | 06-26-2021
  * @param reportId
  * @param accountId
  * @return summaryReportResponse returns the data from the Tabular report and passes it back to the Component for processing
**/
  public static summaryReportResponse getSummaryReportResponse(
    Reports.ReportResults results
  ) {
    summaryReportResponse srr = new summaryReportResponse();
    List<fieldDef> reportFields = new List<fieldDef>();

    //get the metadata
    Reports.ReportMetadata reportMetadata = results.getReportMetadata();

    //get a string array of the field names
    List<String> fieldNames = reportMetadata.getDetailColumns();

    //get the extended metadata
    Reports.ReportExtendedMetadata reportExtendedMetadata = results.getReportExtendedMetadata();

    //get the map of the column names to their name and label
    Map<String, Reports.DetailColumn> detailColumnMap = reportExtendedMetadata.getDetailColumnInfo();

    //get the map of the grouping column names to their name and label
    Map<String, Reports.GroupingColumn> groupingColumnMap = reportExtendedMetadata.getGroupingColumnInfo();

    //get the grouping column info
    Reports.GroupingInfo groupingInfo = reportMetadata.getGroupingsDown()[0]; //only supports one grouping level
    Reports.GroupingColumn groupingColumnDetail = groupingColumnMap.get(
      groupingInfo.getName()
    );

    //loop over the detailColumnMap and get the name, label, and data type
    for (String fieldName : fieldNames) {
      Reports.DetailColumn detailColumn = detailColumnMap.get(fieldName);
      fieldDef fd = new fieldDef();
      fd.fieldName = detailColumn.getName();
      fd.fieldLabel = detailColumn.getLabel();
      fd.dataType = detailColumn.getDataType().name();
      reportFields.add(fd);
    }
    srr.reportFields = reportFields;

    //get the summary grouping down dimension grouping values.  only going 1 level deep
    List<summaryReportGroup> groupList = new List<summaryReportGroup>();
    for (
      Reports.GroupingValue groupingValue : results.getGroupingsDown()
        .getGroupings()
    ) {
    //system.debug('groupingValue ::: ' + groupingValue);
      summaryReportGroup srg = new summaryReportGroup();
      srg.fieldName = groupingColumnDetail.getLabel();
      srg.fieldValue = (String) groupingValue.getValue();
      srg.fieldLabel = groupingValue.getLabel();
      srg.groupKey = groupingValue.getKey();
      srg.isHyperLink = isHyperlink(srg.fieldValue);

    //system.debug('results ::: ' + results);
    //system.debug('srg ::: ' + srg);
    //system.debug('srg.groupKey ::: ' + srg.groupKey);
      //use our group key to get the group rows from the fact map
      Reports.ReportFactWithDetails factDetails = (Reports.ReportFactWithDetails) results.getFactMap()
        .get(srg.groupKey + '!T');
      List<Reports.ReportDetailRow> reportDetailRowList = factDetails.getRows();

      List<List<fieldData>> fieldDataList = new List<List<fieldData>>();

      //loop over the rows
      for (Reports.ReportDetailRow reportDetailRow : reportDetailRowList) {
        Integer cellCounter = 0;
        List<fieldData> fieldDataRow = new List<fieldData>();
        //loop over the cells in the row
        for (
          Reports.ReportDataCell reportDataCell : reportDetailRow.getDataCells()
        ) {
          fieldData fd = new fieldData();
          fd.fieldLabel = (String) reportDataCell.getLabel();
          fd.dataType = reportFields[cellCounter].dataType;
          fd.isHyperLink = isHyperlink(fd.fieldValue);
          if (fd.dataType == 'Date')
            fd.fieldValue = String.valueOf(
              Date.valueOf(reportDataCell.getValue())
            );

          cellCounter++;
          fieldDataRow.add(fd);
        }

        //add the row to the list
        fieldDataList.add(fieldDataRow);
      }
      srg.fieldsInGroup = srr.reportFields.size();
      srg.fieldDataList = fieldDataList;
      groupList.add(srg);
    }
    srr.groupList = groupList;

    return srr;
  }

 
  public static tabularReportResponse getMatrixReportResponse(Reports.ReportResults results) {
 
    tabularReportResponse trr = new tabularReportResponse();
    List<fieldDef> reportFields = new List<fieldDef>();
    List<List<fieldData>> fieldDataList = new List<List<fieldData>>();

    //get the metadata
    Reports.ReportMetadata reportMetadata = results.getReportMetadata();

    //get a string array of the field names
    List<String> fieldNames = reportMetadata.getDetailColumns();

    //get the extended metadata
    Reports.ReportExtendedMetadata reportExtendedMetadata = results.getReportExtendedMetadata();

    //get the map of the grouping column names to their name and label
    Map<String, Reports.GroupingColumn> detailColumnMap = reportExtendedMetadata.getGroupingColumnInfo();

    //create the reportFields header row from the grouping fields

    //first add the grouping down field info
    Reports.GroupingInfo groupingInfoDown = reportMetadata.getGroupingsDown()[0]; //only supports one grouping level
    Reports.GroupingColumn groupingColumnDown = detailColumnMap.get(groupingInfoDown.getName());
    fieldDef fdGroupDown = new fieldDef();
    fdGroupDown.fieldName = groupingColumnDown.getName();
    fdGroupDown.fieldLabel = groupingColumnDown.getLabel();
    fdGroupDown.dataType = groupingColumnDown.getDataType().name();
    reportFields.add(fdGroupDown);

    //now add all the groupings across
    for (Reports.GroupingValue groupingValue : results.getGroupingsAcross().getGroupings()) {
      fieldDef fd = new fieldDef();
      fd.fieldName = (String) groupingValue.getValue();
      fd.fieldLabel = groupingValue.getLabel();
      fd.dataType = 'DOUBLE_DATA';
      reportFields.add(fd);
    }

    //get the matrix grouping down dimension grouping values.  only going 1 level deep
    List<summaryReportGroup> groupListDown = new List<summaryReportGroup>();
    for (Reports.GroupingValue groupingValue : results.getGroupingsDown().getGroupings()) {
      summaryReportGroup srg = new summaryReportGroup();
      srg.fieldValue = (String) groupingValue.getValue();
      srg.fieldLabel = groupingValue.getLabel();
      srg.groupKey = groupingValue.getKey();
      srg.isHyperLink = isHyperlink(srg.fieldValue);
      groupListDown.add(srg);
    }

    //get the matrix grouping across dimension grouping values.  only going 1 level deep
    List<summaryReportGroup> groupListAcross = new List<summaryReportGroup>();
    for (Reports.GroupingValue groupingValue : results.getGroupingsAcross().getGroupings()) {
      summaryReportGroup srg = new summaryReportGroup();
      srg.fieldValue = (String) groupingValue.getValue();
      srg.fieldLabel = groupingValue.getLabel();
      srg.groupKey = groupingValue.getKey();
      srg.isHyperLink = isHyperlink(srg.fieldValue);
      groupListAcross.add(srg);
    }

    //now we need to do a nested loop of the groupings down and across to get all the data from the fact map
    for (summaryReportGroup down : groupListDown) {
      List<fieldData> fieldDataRow = new List<fieldData>();

      //first cell is the grouping down
      fieldData fd = new fieldData();
      fd.fieldValue = down.fieldValue;
      fd.fieldLabel = down.fieldLabel;
      fd.dataType = 'STRING_DATA';
      fd.isHyperLink = down.isHyperLink;
      fieldDataRow.add(fd);

      for (summaryReportGroup across : groupListAcross) {
        //use our composite down!across key to get values from the fact map
        Reports.ReportFactWithDetails factDetails = (Reports.ReportFactWithDetails) results.getFactMap()
          .get(down.groupKey + '!' + across.groupKey);
        Reports.SummaryValue summaryValue = factDetails.getAggregates()[0]; //only support 1 set of aggregates

        fd = new fieldData();
        fd.fieldValue = (String) (summaryValue.getValue() + '');
        fd.fieldLabel = (String) (summaryValue.getLabel() + '');
        fd.dataType = 'DOUBLE_DATA';
        fieldDataRow.add(fd);
      }
      //add the row to the list
      fieldDataList.add(fieldDataRow);
    }

    trr.reportFields = reportFields;
    trr.fieldDataList = fieldDataList;

    return trr;
  }
 

  /**
   * @description quick way to see if a value in our table is an ID, and therefore should be hyperlinked to the Sobject record to click from component to Bid, for example.  If it successfully can cast it to an ID it deems it hyperlinkable
   * @author clay b | 06-26-2021
   * @param sVal
   * @return Boolean
   **/
  public static Boolean isHyperlink(Object sVal) {
    Boolean isHyperLink = true;
    Id theId;
    try {
      theId = (Id) sVal;
    } catch (Exception e) {
      isHyperLink = false;
    }
    return isHyperLink;
  }

  /**
   * Data structures used to handle the report response from the API
   */
  public class reportResponse {
    public String reportType { get; set; }
    public String reportName { get; set; }
    public tabularReportResponse tabResp { get; set; }
    public summaryReportResponse sumResp { get; set; }
    public reportResponse() {
    }
  }

  public class summaryReportResponse {
    public String reportName { get; set; }
    public List<fieldDef> reportFields { get; set; }
    public List<summaryReportGroup> groupList { get; set; }
    public summaryReportResponse() {
    }
  }

  public class summaryReportGroup {
    public String reportName { get; set; }
    public String fieldName { get; set; }
    public String fieldValue { get; set; }
    public String fieldLabel { get; set; }
    public String groupKey { get; set; }
    public Boolean isHyperLink { get; set; }
    public Integer fieldsInGroup { get; set; }
    public List<List<fieldData>> fieldDataList { get; set; }
    public summaryReportGroup() {
    }
  }

  public class tabularReportResponse {
    public String reportName { get; set; }
    public List<fieldDef> reportFields { get; set; }
    public List<List<fieldData>> fieldDataList { get; set; }
    public tabularReportResponse() {
    }
  }

  public class fieldDef {
    public String fieldName { get; set; }
    public String fieldLabel { get; set; }
    public String dataType { get; set; }
    public fieldDef() {
    }
  }

  public class fieldData {
    public String fieldValue { get; set; }
    public String fieldLabel { get; set; }
    public String dataType { get; set; }
    public Boolean isHyperLink { get; set; }
    public fieldData() {
      isHyperLink = false;
    }
  }
}

LightningReportsController.cls

/**
 * @description       : used with the report component for bid history to get default reports for the page builder dropdown
 * @author            : sfdc cb
 * @group             : report embed
 * @last modified on  : 11-07-2021
 * @last modified by  : sfdc cb
 **/
global without sharing class LightningReportsController {
  @AuraEnabled
  public static String getReportResponse(String reportId) {
 
    return JSON.serialize(
      AnalyticsUtils.getReportResponse(reportId)
    );
  }

  @AuraEnabled
  public static Map<String,String> sendToGoogleSheets(String reportId){
    Map<String,String> theResult = new Map<String,String>();
    try {
     // theResult = GoogleAPI_GoogleSheets.convertReportToCSV(reportId);
    } catch (Exception e) {
      throw new AuraHandledException(e.getMessage());
    }
    return theResult;
  }

  @AuraEnabled
  public static List<Report> getReportsForSearch() {
    Report defaultReport = [SELECT Id, Name FROM Report LIMIT 1];
    Report[] theReports = [
      SELECT Id, Name
      FROM Report
      WHERE Name = 'Bid History'
      ORDER BY Name
    ];
    if (theReports.size() > 0) {
      return theReports;
    } else {
      return new List<Report>{ defaultReport };
    }
  }

  @AuraEnabled
  public static Map<String,String[]> getReportFolders(){
    String[] theFolderNames = new List<String>();
    Set<String> uniqueList = new Set<String>();
    Map<String,String[]> folderToReports = new Map<String,String[]>();

     try {
      Report[] reports = [SELECT Id,Name,Owner.Name FROM Report WHERE (NOT Owner.Name LIKE :UserInfo.getName())];
      for(Report r: reports){
        if (r.Owner.Name != UserInfo.getName()) {
          List<String> theReports = folderToReports.get(r.Owner.Name);
      
          if (folderToReports.get(r.Owner.Name) != null){
            theReports.add('{"Name":"'+r.Name+'","Id":"'+r.Id+'"}');
            folderToReports.put(r.Owner.Name,theReports);
          } else {
            folderToReports.put(r.Owner.Name,new List<String>{('{"Name":"'+r.Name+'","Id":"'+r.Id+'"}')});

          }

        }
      }
      theFolderNames = new List<String>(uniqueList);
    } catch (Exception e) {
      throw new AuraHandledException(e.getMessage());
    }
    return folderToReports;
  }
  @AuraEnabled
  public static String getReportMetaData(String reportId) {
    String reportName = reportId != null
      ? [SELECT Name FROM Report WHERE Id = :reportId LIMIT 1]?.Name
      : '';
    Reports.ReportDescribeResult rptDescribe = Reports.ReportManager.describeReport(
      reportId
    );
    
    Reports.ReportMetadata rptMetadata = rptDescribe.getReportMetadata();
    rptMetadata.setHasDetailRows(true);

    Reports.ReportResults results = Reports.ReportManager.runReport(
      reportId,
      rptMetadata,
      true
    );


    //get the report result
  
    //get the metadata
    Reports.ReportMetadata reportMetadata = results.getReportMetadata();

    //find out what type of report it is by looking at the groupings down and groupings across
    Integer groupingsDown = 0;
    Integer groupingsAcross = 0;

    String returnString = JSON.serialize(reportMetadata);
    return returnString;
  }
}

reportComponent

<!--
  @description       : 
  @author            : sfdc cb
  @group             : 
  @last modified on  : 11-07-2021
  @last modified by  : sfdc cb
-->
<aura:component controller="LightningReportsController" implements="force:appHostable,flexipage:availableForAllPageTypes,force:hasRecordId">

    <!-- Handle component initialization in a client-side controller -->
    <aura:handler name="init" value="{!this}" action="{!c.doInit}"/>

    <!-- Handle loading events by displaying a spinner -->
    <aura:attribute name="loaded" type="Boolean" default="true" />
    <aura:attribute name="reportId" type="String" default="" />
    <aura:attribute name="idField" type="String" default="" />
    <aura:attribute name="selectedReport" type="String" default="" />
    <aura:attribute name="reportName" type="String" default="" />
    <!-- Handle loading a report which was clicked on -->
	<aura:handler event="c:reportLoadEvent" action="{!c.loadReport}"/>    

    <!-- load all the reports for search -->
    <aura:attribute name="reportList" type="Report[]"/>
    
    <!-- Dynamically load the report rows -->
    <aura:attribute name="reportResponse" type="Object"/>    

    <div class="slds-scope">

        <div class="exampleHolder">
            <lightning:card title="{!v.reportName}"> 
                <aura:set attribute="actions">
                    <lightning:icon iconName="standard:lightning_component" size="x-small" alternativeText="Success!" onclick="{!c.handleRefresh}"/>
                </aura:set>
                <aura:if isTrue="{! v.loaded }">
                    <div class="slds-scrollable">

                        <div data-role="page" data-theme="d" id="reportPage">   
                            <div role="main" class="">    
                                
                                <ul data-role="listview" data-filter="true" data-filter-reveal="true" data-filter-placeholder="Search reports..." data-inset="true">
                                    <aura:iteration var="report" items="{!v.reportList}">
                                        <c:reportSearchComponent report="{!report}" />
                                    </aura:iteration>
                                </ul>
                                
                                <!-- this is how tabular and matrix reports are displayed -->
                                <!-- Iterate over the list of report rows and display them -->
                                <!-- special case for the header row -->
                                <lightning:layout>
                                    <lightning:layoutItem>

                                        <article class="slds-card_boundary">

                                            <div style="border: 1px solid rgb(101, 153, 205);" class="slds box">
                                                <table class="slds-table slds-table_cell-buffer slds-table_bordered slds-table_col-bordered">
                                            
                                                    <aura:renderIf isTrue="{!v.reportResponse.reportType == 'summary' ? false : true}">
                                                        <thead>
                                                            <c:reportRowComponent row="{!v.reportResponse.tabResp.reportFields}" isHeader="true"/>
                                                        </thead>
                                                        <tbody>
                                                            <aura:iteration var="row" items="{!v.reportResponse.tabResp.fieldDataList}">
                                                                <c:reportRowComponent row="{!row}" isHeader="false"/>
                                                            </aura:iteration>
                                                        </tbody>
                                                        <aura:set attribute="else">
                                                            <!-- this is how summary reports are displayed -->
                                                            <thead>
                                                                <c:reportRowComponent row="{!v.reportResponse.sumResp.reportFields}" isHeader="true"/>
                                                            </thead>
                                                            <tbody>
                                                                <aura:iteration var="group" items="{!v.reportResponse.sumResp.groupList}">
                                                                    <c:reportGroupComponent group="{!group}"/>
                                                                </aura:iteration>
                                                            </tbody>
                                                            
                                                        </aura:set>
                                                    </aura:renderIf>   
                                                    
                                                </table>
                                            </div>
                                        </article>
                                    </lightning:layoutItem>
                                </lightning:layout>
                    
                                    
                    
                            </div>
                        </div>     
                    </div>            
                    <aura:set attribute="else">
                        <lightning:spinner alternativeText="alternativeText" size="small" variant="base" />
                    </aura:set>
                </aura:if>
            </lightning:card>

        </div>
    </div>
</aura:component>

reportComponent.cmp

<!--
  @description       : 
  @author            : sfdc cb
  @group             : 
  @last modified on  : 11-07-2021
  @last modified by  : sfdc cb
-->
<aura:component controller="LightningReportsController" implements="force:appHostable,flexipage:availableForAllPageTypes,force:hasRecordId">

    <!-- Handle component initialization in a client-side controller -->
    <aura:handler name="init" value="{!this}" action="{!c.doInit}"/>

    <!-- Handle loading events by displaying a spinner -->
    <aura:attribute name="loaded" type="Boolean" default="true" />
    <aura:attribute name="reportId" type="String" default="" />
    <aura:attribute name="idField" type="String" default="" />
    <aura:attribute name="selectedReport" type="String" default="" />
    <aura:attribute name="reportName" type="String" default="" />
    <!-- Handle loading a report which was clicked on -->
	<aura:handler event="c:reportLoadEvent" action="{!c.loadReport}"/>    

    <!-- load all the reports for search -->
    <aura:attribute name="reportList" type="Report[]"/>
    
    <!-- Dynamically load the report rows -->
    <aura:attribute name="reportResponse" type="Object"/>    

    <div class="slds-scope">

        <div class="exampleHolder">
            <lightning:card title="{!v.reportName}"> 
                <aura:set attribute="actions">
                    <lightning:icon iconName="standard:lightning_component" size="x-small" alternativeText="Success!" onclick="{!c.handleRefresh}"/>
                </aura:set>
                <aura:if isTrue="{! v.loaded }">
                    <div class="slds-scrollable">

                        <div data-role="page" data-theme="d" id="reportPage">   
                            <div role="main" class="">    
                                
                                <ul data-role="listview" data-filter="true" data-filter-reveal="true" data-filter-placeholder="Search reports..." data-inset="true">
                                    <aura:iteration var="report" items="{!v.reportList}">
                                        <c:reportSearchComponent report="{!report}" />
                                    </aura:iteration>
                                </ul>
                                
                                <!-- this is how tabular and matrix reports are displayed -->
                                <!-- Iterate over the list of report rows and display them -->
                                <!-- special case for the header row -->
                                <lightning:layout>
                                    <lightning:layoutItem>

                                        <article class="slds-card_boundary">

                                            <div style="border: 1px solid rgb(101, 153, 205);" class="slds box">
                                                <table class="slds-table slds-table_cell-buffer slds-table_bordered slds-table_col-bordered">
                                            
                                                    <aura:renderIf isTrue="{!v.reportResponse.reportType == 'summary' ? false : true}">
                                                        <thead>
                                                            <c:reportRowComponent row="{!v.reportResponse.tabResp.reportFields}" isHeader="true"/>
                                                        </thead>
                                                        <tbody>
                                                            <aura:iteration var="row" items="{!v.reportResponse.tabResp.fieldDataList}">
                                                                <c:reportRowComponent row="{!row}" isHeader="false"/>
                                                            </aura:iteration>
                                                        </tbody>
                                                        <aura:set attribute="else">
                                                            <!-- this is how summary reports are displayed -->
                                                            <thead>
                                                                <c:reportRowComponent row="{!v.reportResponse.sumResp.reportFields}" isHeader="true"/>
                                                            </thead>
                                                            <tbody>
                                                                <aura:iteration var="group" items="{!v.reportResponse.sumResp.groupList}">
                                                                    <c:reportGroupComponent group="{!group}"/>
                                                                </aura:iteration>
                                                            </tbody>
                                                            
                                                        </aura:set>
                                                    </aura:renderIf>   
                                                    
                                                </table>
                                            </div>
                                        </article>
                                    </lightning:layoutItem>
                                </lightning:layout>
                    
                                    
                    
                            </div>
                        </div>     
                    </div>            
                    <aura:set attribute="else">
                        <lightning:spinner alternativeText="alternativeText" size="small" variant="base" />
                    </aura:set>
                </aura:if>
            </lightning:card>

        </div>
    </div>
</aura:component>

reportComponent.cmp-meta.xml

<?xml version="1.0" encoding="UTF-8"?>
<AuraDefinitionBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>52.0</apiVersion>
    <description>DESCRIPTION</description>
</AuraDefinitionBundle>

reportComponent.css

.THIS {

}
.THIS.exampleHolder{
    position: relative;
    display: inline-block;
    margin-left: 15px;
    width: 95%;
    height: 95%;
    vertical-align: middle;
    white-space: nowrap;
}

reportComponent.design

<design:component>
  <design:attribute
    name="selectedReport"
    datasource="apex://ReportPickListValues"
  />  
  <design:attribute
  name="idField"
  label="ID Field to Match"
/>
</design:component>

reportComponent2

‎‎​

reportComponentController.js

({
    doInit: function (component, event, helper) {
        //        component.set('v.loaded', !component.get('v.loaded'));
        helper.getReportResponse(component, event, helper);


    },
    loadReport: function (component, event, helper) {

    },
    showSpinner: function (component, event, helper) {

    },
    hideSpinner: function (component, event, helper) {

    },
    handleRefresh: function (component, event, helper) {
        component.set('v.loaded', !component.get('v.loaded'));
        helper.getReportResponse(component, event, helper);
    },
    handleGoogleSheets: function (component, event, helper) {
        helper.sendToGoogleSheets(component, event, helper);
    }
})

reportComponentHelper.js

({
    getReportResponse: function (component, event, helper) {
        var action = component.get("c.getReportMetaData");
        action.setParams({ reportId: component.get("v.reportId") });
        action.setCallback(this, function (a) {
            var reportResponseObj = JSON.parse(a.getReturnValue());

            console.log('callback  -' + a.getReturnValue());
            if (reportResponseObj !== null && reportResponseObj !== undefined) {
                console.log('%creportComponentHelper.js line:7 reportResponseObj', 'color: #007acc;', reportResponseObj);
                 let theBadges = [];
                reportResponseObj.detailColumns.forEach(element => {
                    let theString = element;
                    theBadges.push( theString.substring(theString.indexOf('.') + 1) );
              
                });
                component.set("v.detailColumns",theBadges);
                console.log('%creportComponentHelper.js line:13 component.get(v.detailColumn', 'color: #007acc;', component.get('v.detailColumns'));
           //     component.set("v.reportName", reportResponseObj.reportName);
            //    component.set("v.sumResp", reportResponseObj.sumResp);
             //   component.set("v.reportResponse", reportResponseObj);
              //  component.set('v.loaded', !component.get('v.loaded'));
            } else {
                component.set('v.loaded', !component.get('v.loaded'));

            }



        });
        $A.enqueueAction(action);
    },
    sendToGoogleSheets: function (component, event, helper) {
        var action = component.get("c.sendToGoogleSheets");
        let headers = [];
        let rows = [];
        action.setParams({ reportId: component.get("v.reportId") });
        action.setCallback(this, function (a) {
            var reportResponseObj = a.getReturnValue();
            console.log('%creportComponentHelper.js line:7 reportResponseObj', 'color: #007acc;', reportResponseObj);
            headers = JSON.parse(reportResponseObj.headers);
            rows = JSON.parse(reportResponseObj.rows);
            console.log('%creportComponentHelper.js line:33 headers', 'color: #007acc;', headers);
            console.log('%creportComponentHelper.js line:34 rows', 'color: #007acc;', rows);
            let theWholeTable = [];
            let theRow = [];
            rows.forEach(row => {
                theRow = [];
                for (let index = 0; index < headers.length; index++) {
                    const element = row[index];
                    theRow.push(element);

                }

                theWholeTable.push(theRow);
            });
            theWholeTable.unshift(headers);
            let csvstringData = theWholeTable.map(row => row.map(item => (typeof item === 'string' && item.indexOf(',') >= 0) ? `"${item}"` : String(item)).join(',')).join('\n');

            console.log(' ::: ' + csvstringData);
            component.set('v.csvString', csvstringData);


        });
        $A.enqueueAction(action);
    }
})

reportGroupComponent.cmp

<aura:component >
	<aura:attribute name="group" type="Object"/>
    
    <tr class="slds-line-height_reset" style="background-color: #d6d6d6;">
        <td class="slds-truncate" colspan="{!v.group.fieldsInGroup}">
            <b>{!v.group.fieldName}: </b>
            <aura:renderIf isTrue="{!v.group.isHyperLink}">
                <c:sobjectHyperlink sObjectId="{!v.group.fieldValue}" hyperlinkLabel="{!v.group.fieldLabel}"/>
                <aura:set attribute="else">{!v.group.fieldLabel}</aura:set>                        
            </aura:renderIf>            
        </td>
    </tr>
    
    <aura:iteration var="row" items="{!v.group.fieldDataList}">  
        <tr class="slds-hint-parent">
            <aura:iteration var="cell" items="{!row}">
                <td class="slds-truncate">
                    <aura:renderIf isTrue="{!!cell.isHyperLink}">
                        <c:sobjectHyperlink sObjectId="{!cell.fieldValue}" hyperlinkLabel="{!cell.fieldLabel}"/>
                        <aura:set attribute="else">{!cell.fieldLabel}</aura:set>                        
                    </aura:renderIf>                            
                </td>
            </aura:iteration>
        </tr>
    </aura:iteration>

        

</aura:component>

reportGroupComponent.cmp-meta.xml

<?xml version="1.0" encoding="UTF-8"?>
<AuraDefinitionBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>52.0</apiVersion>
    <description>DESCRIPTION</description>
</AuraDefinitionBundle>

reportGroupComponent.css

.THIS {}
.THIS .group {
	background-color: #d6d6d6;
}

reportLoadEvent.evt

<aura:event type="APPLICATION" description="report is clicked">
	<aura:attribute name="report" type="Report"/>
</aura:event>

reportLoadEvent.evt-meta.xml

<?xml version="1.0" encoding="UTF-8"?>
<AuraDefinitionBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>52.0</apiVersion>
    <description>DESCRIPTION</description>
</AuraDefinitionBundle>

ReportPickListValues.cls

global class ReportPickListValues extends VisualEditor.DynamicPickList{
    
    global override VisualEditor.DataRow getDefaultValue(){
        VisualEditor.DataRow defaultValue = new VisualEditor.DataRow('select report','select report');
        return defaultValue;
    }
    global override VisualEditor.DynamicPickListRows getValues() {

        Report[] theReports = [SELECT Name From Report];

        VisualEditor.DynamicPickListRows myValues = new VisualEditor.DynamicPickListRows();
        VisualEditor.DataRow defValue = new VisualEditor.DataRow('select report','select report');
        myValues.addRow(defValue);
        for(Report rep : theReports){

            VisualEditor.DataRow p1 = new VisualEditor.DataRow(rep.Name, String.valueOf(rep.Id));
            myValues.addRow(p1);
          //system.debug(p1);
        }
        return myValues;
    }

}

reportRowComponent.cmp

<aura:component>
  <aura:attribute name="row" type="Object[]" />
  <aura:attribute name="isHeader" type="Boolean" />

  <tr class="slds-line-height_reset">
    <aura:iteration var="cell" items="{!v.row}">
      <aura:renderIf isTrue="{!v.isHeader}">
        <th class="" scope="row">
          <div class="slds-truncate">{!cell.fieldLabel}</div>
        </th>
        <aura:set attribute="else">
          <td class="slds-truncate">
            <div class="slds-truncate">{!cell.fieldLabel}</div>
          </td>
        </aura:set>
      </aura:renderIf>
    </aura:iteration>
  </tr>
</aura:component>

reportRowComponent.cmp-meta.xml

<?xml version="1.0" encoding="UTF-8"?>
<AuraDefinitionBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>52.0</apiVersion>
    <description>DESCRIPTION</description>
</AuraDefinitionBundle>

reportRowComponent.css

.THIS{}

reportSearchComponent.cmp

<aura:component implements="flexipage:availableForAllPageTypes">
	<aura:registerEvent name="rLoadEvent" type="c:reportLoadEvent"/>   
	<aura:attribute name="report" type="Report"/>
    <li class="ui-screen-hidden">
        <a href="javascript:void(0);" onclick="{!c.showReport}">{!v.report.Name}</a>
    </li>    
</aura:component>

reportSearchComponent.cmp-meta.xml

<?xml version="1.0" encoding="UTF-8"?>
<AuraDefinitionBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>52.0</apiVersion>
    <description>DESCRIPTION</description>
</AuraDefinitionBundle>

reportSearchComponentController.js

({
    showReport : function(component, event, helper) {
	 	var report = component.get("v.report");        
		var rLoadEvent = $A.get("e.c:reportLoadEvent");
		rLoadEvent.setParams({"report": report});
		rLoadEvent.fire();
    }  
})