SOQL Query Generation From Reports – Salesforce

Overview of SOQL Query Generation From Reports :

These days, a company or organization needs to evaluate and compare every single piece of data to determine how much they have grown and how far they’ve come from the past. In Salesforce, a common challenge arises when trying to retrieve and use data effectively — this is where SOQL Query Generation From Reports becomes useful.

A developer can access this data using the Developer Console. They can query a set of fields, apply filters, and group records by specific values.

However, this approach doesn’t work well for users or customers who are unfamiliar with the Developer Console or query language. In such cases, reports act as a user-friendly alternative to retrieve and view data.

Reports in Salesforce work similarly to queries. They allow filtering, grouping of records, and displaying specific fields. Additionally, reports can include charts to present the data visually using graphs.

To bridge the gap between user-friendly reports and developer-level querying, SOQL Query Generation From Reports helps convert visual data into actionable SOQL queries.

In several cases, reports may have been created over a year ago. As a result, the current developer or administrator might be unaware of their details or structure.

When trying to reuse or understand these old reports, many teams search blogs and documentation to find a method for extracting the underlying logic.

SOQL queries help decode the structure and purpose of these reports. However, Salesforce does not provide a standard feature to generate SOQL queries directly from reports.

To achieve this, we need to process the report metadata. This metadata includes details such as:

• Fields and their data types
• Field API names
• Standard and custom filters (with fields, operators, and values)
• Group By fields

In this blog, we will explore how to extract metadata and construct a SOQL query from a Salesforce report step by step.

The following keywords were constructed :

S.NO	KEYWORD	DESCRIPTION
1.	SELECT	Fields that need to be queried.
2.	FROM	Object.
3.	WHERE	Set of conditions that reduce the row count.
4.	ORDER BY	Sorting results based on a field’s values either Ascending/descending
5.	GROUP BY	It is used with Aggregate functions that group the result by considering a set of given fields

PROCESS :

OBJECT :

• Two ways to know the object used in the report.
  1. Object plural name that is in the report’s metadata.
  2. If two or more objects are used in the report need to create a record in the Custom metadata.

• If the object doesn’t get in the first method then automatically go for the second method.

FIELDS :

• Initially collect all the used object’s fields details and compare this with the report metadata.
• For the best matching, all the fields are saved in the following format,
  1. Object Name.Field Name (Ex: Contact.OwnerId)
• Generated SOQL query contains only the aggregate function accepted fields when GROUP BY is used. For example, Text fields don’t accept the Average aggregate function.
• All the aggregate functions and their accepted data type are stored in the Report Data Type Custom metadata. If any need please change that.

FILTERS :

• Filters are used to reduce the number of rows retrieved from the database.
• Two kinds of filters are used in the report
  1. Standard filters
  2. Custom filters
• The Standard filter includes the created date limit.
• The Remaining are the custom filters.
• These filters are split into two parts in the metadata. So two methods are needed to process this information to get the filter details.
• Filter fields and values are based on the report metadata. So filters are constructed based on the result of the metadata.

GROUP BY FIELDS :

• These fields are used to group the rows by using the aggregate functions.
• The Query contains only the aggregate function accepted fields. Otherwise, it’ll skip.

ORDER BY FIELDS :

• These fields sort the rows either ascending or descending.
• GROUP BY and ORDER BY are not accepted in the same query.

NOTE : When the label of the report metadata differs from the original field label, the generated SOQL query may skip that field. And only accepts the object and its related object fields of a report. Ex: Case -> Account, Contact, and Asset.

Custom Metadata Types :

1. Report Details :

Lookup Objects field value should be with comma separated.

Ex :

S.NO	Label	Report Data Types Name	Child Object To Query	Lookup Objects
1.	CaseReport	CaseReport	Case	Account,Contact,Asset

2. Report Data Types :

Ex :

S.NO	Label	Report Data Types Name	Data Types
1.	SUM	SUM	PERCENT,NUMBER,CURRENCY,FOR MULA (PERCENT),FORMULA (NUMBER),FORMULA (CURRENCY)

ReportUtil.apxc

public  without sharing class ReportUtil {
    public static String ObjName;
    public static String relatedObj;
    public static Map<String, String> fieldAPIMap = new Map<String, String>();
    public static Map<String, String> fieldAPIWithDataTypeMap = new Map<String, String>();
    public static Set<String> APISet = new Set<String>();
    public static Set<String> isInGroupable = new Set<String>();
    public static Map<String, Report_Data_Types__mdt> grpByDataTypeMap = Report_Data_Types__mdt.getAll();
    public static Map<String, Set<String>> aggregateMap = new Map<String, Set<String>>();
    @AuraEnabled
    // To construct the SOQL Query
    public static queryWrapper getQuery(String recordId){
        List <Report> reportList = [SELECT Id,DeveloperName FROM Report where Id =: recordId];
        String objQyery = '';
        queryWrapper wrapper;
        if(!reportList.isEmpty()){
            String reportDevName = reportList[0].DeveloperName;
            Reports.ReportResults reportResults = Reports.ReportManager.runReport((String)reportList.get(0).get('Id'));
            getObjName(reportResults, reportDevName);
            if(String.isNotBlank(ObjName)){
                getFieldAPIWithLabel();
                String groupByString = getGroupBy(reportResults);
                getAggregateResults(reportResults);
                String fieldString = getFieldNames(reportResults, String.isNotBlank(groupByString) ? new Set<String>(groupByString.split(',')) : new Set<String>());
                String queryFields;
                if(String.isNotBlank(groupByString)){
                    queryFields = groupByString;
                }
                if(String.isNotBlank(fieldString)){
                    queryFields = String.isNotBlank(queryFields) ? queryFields + ',' + fieldString : fieldString;
                }
                String filterString = getFilters(reportResults);
                String sortByFields = getSortByFields(reportResults);
               
                if(String.isNotBlank(queryFields) && String.isNotBlank(ObjName)){
                    objQyery = 'SELECT ' + queryFields + ' FROM ' + ObjName;
                    if(String.isNotBlank(filterString)){
                        objQyery += ' WHERE '+ filterString;
                    }
                    if(String.isNotBlank(groupByString)){
                        objQyery+= ' GROUP BY ' + groupByString;
                    }
                    else if(String.isNotBlank(sortByFields)){
                        objQyery+= ' ORDER BY ' + sortByFields;
                    }
                }
                wrapper = new queryWrapper();
                wrapper.objectName = ObjName;
                wrapper.filterString = filterString;
                wrapper.groupByString = groupByString;
                wrapper.sortByFields = sortByFields;
                wrapper.queryFields = queryFields;
                wrapper.query = objQyery;
                System.debug('objQyery--->'+objQyery);
            }
        }
        return wrapper;
    }
    // Getting Objects API Name
    public static void getObjName(Reports.ReportResults reportResults, String reportDevName){
        ObjName = [SELECT QualifiedApiName FROM EntityDefinition WHERE PluralLabel =: reportResults.getReportMetadata().getReportType().getLabel() LIMIT 1]?.QualifiedApiName?.toUpperCase();
        if(String.isBlank(ObjName)){
            Report_Details__mdt reportDetailsMdt = Report_Details__mdt.getInstance(reportDevName);
            ObjName = reportDetailsMdt?.Child_Object_To_Query__c.toUpperCase();
            relatedObj = reportDetailsMdt?.Lookup_Objects__c.toUpperCase();
        }
        System.debug('ObjName--->'+ObjName);
        System.debug('relatedObj--->'+relatedObj);
    }
    // Initially collect all details about the Object & it's fields
    public static void getFieldAPIWithLabel(){
        List<String> ObjToQuery = String.isNotBlank(relatedObj) ? new List<String>((ObjName + ',' + relatedObj).split(',')) : new List<String>{ObjName};
            for(FieldDefinition field : (List<FieldDefinition>) Database.query('SELECT EntityDefinition.QualifiedApiName, Label, DataType, QualifiedApiName, IsApiGroupable FROM FieldDefinition WHERE EntityDefinition.QualifiedApiName IN: ObjToQuery')){
                fieldAPIMap.put(field.QualifiedApiName.toUpperCase(), field.Label.toUpperCase());          
                String dataType = field.DataType;
                fieldAPIWithDataTypeMap.put((field.EntityDefinition.QualifiedApiName+'.'+field.QualifiedApiName).toUpperCase(), (dataType.contains('Number') ? 'Number' : dataType.contains('Currency') ? 'Currency' : dataType.contains('Percent') ? 'Percent' : dataType).toUpperCase());
                if(field.IsApiGroupable){
                    isInGroupable.add((field.EntityDefinition.QualifiedApiName+'.'+field.QualifiedApiName).toUpperCase());
                }
                APISet.add((field.EntityDefinition.QualifiedApiName +'.'+ field.QualifiedApiName).toUpperCase());
            }
    }
    // To construct the GROUP BY fields
    public static String getGroupBy(Reports.ReportResults reportResults){
        Reports.ReportExtendedMetadata reportExtendedMtd = reportResults.getReportExtendedMetadata();
        String groupString;
        for(Reports.GroupingColumn groupColumn : reportExtendedMtd.getGroupingColumnInfo().Values()){
            String groupColumnName = returnAPIByReportName(groupColumn.getName());
            if(String.isNotBlank(groupColumnName) && isInGroupable.contains(groupColumnName)){
                if(String.isNotBlank(groupString)){
                    groupString += ', '+groupColumnName;
                }
                else{
                    groupString = groupColumnName;
                }
            }
        }
        return groupString;
    }
    // To Construct aggregateMap
    public static void getAggregateResults(Reports.ReportResults reportResults){
        Reports.ReportExtendedMetadata reportExtendedMdt = reportResults.getReportExtendedMetadata();
        Set<String> sumSet = new Set<String>(grpByDataTypeMap.get('SUM').Data_Types__c.split(','));
        for(Reports.AggregateColumn aggregate :  reportExtendedMdt.getAggregateColumnInfo().Values()){
            String aggName = aggregate.getName().toUpperCase();
            String aggLabel = aggregate.getLabel().toUpperCase();
            String aggFieldAPI;
            if(aggName.startsWith('S!')){
                aggFieldAPI = returnAPIByReportName(aggName.removeStart('S!'));
                if(String.isNotBlank(aggFieldAPI) && sumSet.contains(fieldAPIWithDataTypeMap.get(aggFieldAPI))){
                    if(!aggregateMap.containsKey(aggFieldAPI)){
                        aggregateMap.put(aggFieldAPI, new Set<String>());
                    }
                    aggregateMap.get(aggFieldAPI).add('SUM('+ aggFieldAPI +')');
                }
            }
        }
    }
    // To construct fields that need to query.
    public static String getFieldNames(Reports.ReportResults reportResults, Set<String> groupBySet){
        Reports.ReportExtendedMetadata reportExtendedMdt = reportResults.getReportExtendedMetadata();
        Map<String, Reports.DetailColumn> reportDetailColumn = reportExtendedMdt.getDetailColumnInfo();
        String fieldAPIs;
        for(Reports.DetailColumn key : reportDetailColumn.values()){
            String fieldAPI = returnAPIByReportName(key.getName().toUpperCase());
            if(!groupBySet.isEmpty()){
                if(String.isNotBlank(fieldAPI) && aggregateMap.containsKey(fieldAPI)){
                    fieldAPI = aggregateMap.get(fieldAPI).toString().removeStart('{').removeEnd('}');
                    if(String.isNotBlank(fieldAPIs)){
                        fieldAPIs += ', '+fieldAPI;
                    }
                    else{
                        fieldAPIs = fieldAPI;
                    }                
                }
            }
            else if(groupBySet.isEmpty()){
                if(String.isNotBlank(fieldAPI)){
                    if(String.isNotBlank(fieldAPIs)){
                        fieldAPIs += ', ' + fieldAPI;
                    }                        
                    else{
                        fieldAPIs = fieldAPI;
                    }
                }
            }
        }
        if(String.isBlank(fieldAPIs)){
            return NULL;
        }
        return new Set<String>(fieldAPIs.split(',')).toString().removeStart('{').removeEnd('}');
    }
    // To construct WHERE (Filters) condition fields.
    public static String getFilters(Reports.ReportResults reportResults){
        Reports.ReportMetadata reportMdt = reportResults.getReportMetadata();
        String filterString;
        for(Reports.ReportFilter rf : reportMdt.getreportFilters()){
            System.debug('rf--->'+rf);
            String filter = generateFilter(rf);
            if(String.isNotBlank(filter)){
                if(String.isNotBlank(filterString)){
                    filterString += ' AND ' + filter;                    
                }
                else{
                    filterString = filter;
                }
            }
        }
        return filterString;
    }
    // To construct ORDER BY fields
    public static String getSortByFields(Reports.ReportResults reportResults){
        Reports.ReportMetadata reportMdt = reportResults.getReportMetadata();
        List<Reports.SortColumn> sortColumnList = reportMdt.getSortBy();
        String sortFields;
        for(Reports.SortColumn sortColumn : sortColumnList){
            String getField = returnAPIByReportName(sortColumn.getSortColumn());
            if(String.isNotBlank(getField)){
                if(String.isNotBlank(sortFields)){
                    sortFields += ', ' + getField + ' ' + (String.valueOf(sortColumn.getSortOrder()) == 'DESCENDING' ? 'DESC' : 'ASC');
                }
                else{
                    sortFields = getField + ' ' + (String.valueOf(sortColumn.getSortOrder()) == 'DESCENDING' ? 'DESC' : 'ASC');
                }
            }    
        }
        return sortFields;
    }
   
    // To construct every single filter fields with it's value.
    private static String generateFilter(Reports.ReportFilter rf){
        String filterField = returnAPIByReportName(rf.getcolumn());
        if(String.isNotBlank(filterField)){
            String filterValue = rf.getValue();
            String filterOperatorString = rf.getOperator();
            String filterOperator = filterOperatorString == 'equals' ? '=' : filterOperatorString == 'notEqual' ? '!=' : (filterOperatorString == 'contains' || filterOperatorString == 'startsWith') ? 'LIKE' : filterOperatorString == 'lessThan' ? '<' : filterOperatorString == 'lessOrEqual' ? '<=' : filterOperatorString == 'greaterThan' ? '>' : filterOperatorString == 'greaterOrEqual' ? '>=' : filterOperatorString == 'greaterOrEqual' ? 'LIKE' : NULL;
            if(String.isBlank(filterOperator) || String.isBlank(filterField)){
                return NULL;
            }
            return filterField + ' ' + filterOperator + ' '+ filterValue;
        }
        return '';
    }
    // To return the field API.
    private static String returnAPIByReportName(String field){
        field = field.toUpperCase();
        if(fieldAPIMap.containsKey(field) && APISet.contains(fieldAPIMap.get(field))){
            return fieldAPIMap.get(field);
        }
        return '';
    }
    @AuraEnabled
    public static List<Report> getReportResults(){
        return [SELECT Id, Name FROM Report Order By Name ASC];
    }
    // wrapper class
    public class queryWrapper{
        @AuraEnabled
        public String objectName { get; set;}
        @AuraEnabled
        public String filterString { get; set;}
        @AuraEnabled
        public String groupByString { get; set;}
        @AuraEnabled
        public String sortByFields { get; set;}
        @AuraEnabled
        public String queryFields { get; set;}
        @AuraEnabled
        public String query {get; set;}
    }
}

REPORT SEARCH :

RESULT :

1. OBJECT :

2. FIELDS :

3. GROUP BY FIELDS :

4. ORDER BY FIELDS :

5. FILTERS :

6. QUERY :

We are Inno Valley Works, We are a passionate team of developers, best thinkers and consultants who can solve anything and everything.
With our highly engaging team, you can easily bring the vision to all your business ventures come true.
We have team, put your problem, get your solution

Drop us a message info@innovalleyworks.com , we will coordinate to implement the feature in your org.

 

🎥 Check Out Our YouTube Channel

Explore helpful tutorials, product updates, and feature walkthroughs from the team at Innovalley Works.

👉 Visit Our Channel