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 have come from the past. In Salesforce, a developer can see and get these data from the developer console. They can query a set of fields they want, and filter and group them by a set of fields and values easily. But, this is not gonna work as expected for the user/customer who has no idea about the query and developer console. In this situation, reports take the place to get these data. Reports are working as well as the query, here we can use a filter, group the records, and display the fields we need. And a report also provides a chart that shows data in a compact mode like graphs.
In several cases, the majority of the reports were written over a year ago. Therefore, the developer/Administrator who is currently at work was unaware of it. Because they are unsure about its nature or contents. I looked up information about it on many blogs. They require a method to produce queries from the reports for their initial use. SOQL query makes them understand the report. There are no standard functions to build the SOQL query from a Salesforce report. For that reason, we must process the metadata for that specific report. All details, including fields with the data type, fields API, standard and custom filters (containing fields, operators, and value), and groups by fields, are included in the report metadata. We will learn how to process the report’s metadata and create a SOQL query from it in this blog.
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.
- Object plural name that is in the report’s metadata.
- 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,
- 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
- Standard filters
- 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 :
.
