Hello community , 

Hi, we are in the process of redesigning the profiles and roles in our org, we want to perform an in-depth impact study on the reports and dashboards that uses profile/role in the filter.

We tried to make an extract with soql query of our reports that lastRun between 2023 and 2024 , we have 5000 reports.We want to extract only the reports that uses profile/role in the filters but with soql query we can't achieve this requirement.

So ,we did a lot of research and came to understand that you can't query reports that use profile/role with sqol query but rather with the metadata service api .

We developed an apex batch that consumes a metadata service api, but it still didn't work, even though I'm sure I have reports that use profile/role.

attached is my code: 

public class analyzeReportsBatch implements Database.Batchable<SObject> ,Database.AllowsCallouts{

String query;

    public Database.QueryLocator start(Database.BatchableContext context) {

        if(Test.isRunningTest()){

         // this would return only one record during test execution

              this.query = 'SELECT Id, DeveloperName, Name FROM Report where CALENDAR_YEAR(LastRunDate) >=2024 LIMIT 1';  

        }

        else{

                this.query = 'SELECT Id, DeveloperName, Name FROM Report WHERE CALENDAR_YEAR(LastRunDate) >= 2024 ';

        }

        return Database.getQueryLocator(query);

    }

    public void execute(Database.BatchableContext context, List<Report> reports) {

        if (!reports.isEmpty()) {

            system.debug('number reports'+reports.size());

        // Instantiate MetadataService

        MetadataService.MetadataPort service = new MetadataService.MetadataPort();

        service.SessionHeader = new MetadataService.SessionHeader_element();

        service.SessionHeader.sessionId = UserInfo.getSessionId();

        List<String> reportIds=new List<String>();

        // Iterate through reports

        for (Report report : reports) {

            String reportId = report.Id;

            String reportName = report.Name;

            // Retrieve report metadata

            List<MetadataService.Report> reportsmdt = (List<MetadataService.Report>) service.readMetadata('Report', new List<String> {'ReportId'}).getRecords();

           // Serialize report metadata into JSON format

            String reportMetadataJson = JSON.serialize(reportsmdt);

            system.debug('khouloud '+reportMetadataJson);

            // Perform parsing and analysis here to identify profile and role filters

            // Example: Check if the report metadata contains profile or role filters

            if (reportMetadataJson.contains('Profile') || reportMetadataJson.contains('Role')) {

                system.debug('test if ');

                System.debug('Report ' + reportName + ' (ID: ' + reportId + ') uses profile or role filters');

                // You can perform further actions here as needed

            }

        }

        }

    }

    public void finish(Database.BatchableContext context) {

        // Check if running in a test context

    }

}

PS:we know that we can detect the fields used in a report with "Where is used". but this feature doesn't work for profile/Role.

Is there a solution to complete this task?

Thank you,

Khouloud