function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sanjeev Kumar 20Sanjeev Kumar 20 

Getting reports based on objects type in SOQL

Hi All !

Can anyone tell me how to get reports based on objects type in SOQL?
I'm trying to fetch from Report Object. But some how I want to fetch only those who belongs to a specific standard/custom object.

for example : 
My current query is ->
SELECT Id, Name FROM Report WHERE Format='Tabular' Order by Name limit 10000

All I want is to apply a report type kind of filter so that I can get reports for a specific object (contact, account or custom object etc).
RamuRamu (Salesforce Developers) 
Hi Sanjeev, basically the current salesforce api exposes only the Report object to query the information on reports, however, to find the objects involved in a particular report, you would need to know the report type used which defines the objects involved in that report type. As the report type object is not exposed in the standard api it is not possible to fetch the information on objects associated in a report type. You would have to use metadata api to fetch this information
 
Mikola SenykMikola Senyk

Yes, as Ramu told you, you cannot get report type from SQOL.
However, from Metadata API it is possible.
You can start from here (https://github.com/financialforcedev/apex-mdapi/blob/master/apex-mdapi/src/classes/MetadataService.cls) (class to work with metadata):
Basically, you have to call Metadata API in the following sequence:

  1. Connect to API with session ID of current user. This user should have “Modify All Data” permission.
  2. Call listMetadata() method to find folders for type 'ReportFolder'
  3. Use full name of folders to find reports (listMetadata() call, ListMetadataQuery.folder = full name of folder, ListMetadataQuery.type_x = 'Report')
  4. Use list of report full names to get report metadata - call readMetadata() for type 'Report'
  5. In the result array you will find property reportType for each item. This is what you need.
Note: Standard object names will be as is e.g. Opportunity, Account etc. Names of custom object will be in format similar to CustomEntity$Merchandise__c (where CustomEntity$ is a prefix).
Sanjeev Kumar 20Sanjeev Kumar 20
@Ramu & Mikola Senyk thanks for your replies.
Could you please share some examples or related links.
Thanks, 
Sanjeev
Mikola SenykMikola Senyk
Hi Sanjeev,
Basic documentation you can find by the following link:
https://www.salesforce.com/us/developer/docs/api_meta/
Also look at the following example of code: 
MetadataService.MetadataPort service = new MetadataService.MetadataPort();
service.SessionHeader = new MetadataService.SessionHeader_element();
service.SessionHeader.sessionId = UserInfo.getSessionId();
System.debug('@@@ metadata service created');

// get list of Report folders
MetadataService.ListMetadataQuery mdQuery = new MetadataService.ListMetadataQuery();
mdQuery.type_x = 'ReportFolder';
MetadataService.ListMetadataQuery[] mdqList = new MetadataService.ListMetadataQuery[] {};
mdqList.add(mdQuery);
Double mdVersion = 32.0;
MetadataService.FileProperties[] fpList = service.listMetadata(mdqList, mdVersion);
System.debug('@@@ metadata result size = ' + fpList.size());
for (MetadataService.FileProperties fp: fpList) {
    System.debug('@@@ folder name: ' + fp.fullName);
}

// get list of report from folders and collect fullNames
String[] reportNames = new String[]{};
mdQuery = new MetadataService.ListMetadataQuery();
mdQuery.type_x = 'Report';
mdQuery.folder = 'unfiled$public';
mdqList = new MetadataService.ListMetadataQuery[]{};
mdqList.add(mdQuery);
MetadataService.FileProperties[] reportList = service.listMetadata(mdqList, mdVersion);
System.debug('@@@ report list size = ' + reportList.size());
for (MetadataService.FileProperties fp: reportList) {
    reportNames.add(fp.fullName);
    System.debug('@@@ report ID=' + fp.id + ' and name: ' + fp.fullName);
}

// read metadata from report
MetadataService.IReadResult res = service.readMetadata('Report', reportNames);
System.debug('@@@ report count ' + res.getRecords().size());
for (MetadataService.Metadata md: res.getRecords()) {
    if ( md instanceof MetadataService.Report ) {
        MetadataService.Report mdReport = (MetadataService.Report) md;
        System.debug('@@@ report base object: ' + mdReport.reportType);
    }
}