Salesforce: Finding Reports and Dashboards from Private folder

Use case: unable to delete report because it used in dashboards.

When you try to delete the report, Salesforce returns the following error:

Report cannot be deleted
One or more dashboards depend on this report. Please delete the dashboard components referring to this report and try again. 

The issue is, it does not tell us which dashboard content report that we want to delete.

So, let us find the related dashboards.

1. Create Report Type 
Reports (A) with at least one related record from Dashboard Components (B)
You can add Dashboard information to this report, such as:
- Dashboard ID
- Dashboard Running User (run as specified user, or let authorized users change running user)
- Folder
- Running User (this is viewing user name)
- Running User Active
- Title

You may find in some of the reports, there is no Dashboard info, even the report type is Reports with at least one related record from Dashboard Components, this is pretty confusing, right?

Possibility (1)
The dashboard has been deleted, you are right, however, once the dashboard is deleted (in recycle bin), the system allows you to delete the report.

 If you see from the above screenshot, the first line does not have dashboard info, this because the dashboard is deleted, and system allows me to delete the report, so this does not fit our use case.

Possibility (2)
The dashboard is stored in someone Private folder.

The difference here, we can see dashboard Title and no other info. For this case, we cannot delete the report.

2. Query from Private folders
For reports used as the source of dashboards that are stored in someone's private dashboard, you need to query from the Private folder. You need to have this permission of "Manage All Private Reports and Dashboards", so you can query the dashboard and report stored in the Private folder. You also need to add  'allPrivate' query scope to find Reports and Dashboards in private folders.

To return reports in private folders that haven't been run for more than one year:
SELECT Id, OwnerId FROM Report USING SCOPE allPrivate WHERE LastRunDate < LAST_N_DAYS:365

To query reports inside a specific User's private folder:
SELECT Id FROM Report USING SCOPE allPrivate WHERE OwnerId = '005A0000000Bc2deFG'

To query all dashboards stored in the User's private folder:
SELECT Id, Title, FolderName, FolderId, CreatedById, LastModifiedById FROM Dashboard USING SCOPE allPrivate ORDER BY Title 

Note:
For Dashboard:
- You should look at FolderId - this is where the dashboard or report is stored.
- The dashboard or report can be created by someone else, so don't look at CreatedById.

For Report:
- Look for OwnerId; this will tell you who owned the report stored in the private folder.


First Query
SELECT Id, DeveloperName, OwnerId, CreatedById, Owner.Name, CreatedBy.Name, CreatedBy.IsActive, Name, LastRunDate FROM Report USING SCOPE allPrivate

This query will return all reports stored in all user's private folders.

Second Query

SELECT Id, DeveloperName, OwnerId, CreatedById, Owner.Name, CreatedBy.Name, CreatedBy.IsActive, Name, LastRunDate FROM Report

This query will return all reports in your private folder, public folders, and all custom folders.

So, if you combine the results of the above query, you will get duplicate results for reports that sit in your private folder, so you can exclude those reports from the second query, such as

SELECT Id, DeveloperName, OwnerId, CreatedById, Owner.Name, CreatedBy.Name, CreatedBy.IsActive, Name, LastRunDate FROM Report WHERE OwnerId <> '00580000004JEgTAAW'

Reference: 

• Delete Reports and Dashboards from personal or private folders
• Locate Reports and Dashboards Stored within Personal or Private Folders