How to Query Private Reports and Dashboards

Have you ever wondered how many private reports & dashboards exist in your Salesforce environment?

Another consultant and I were working with a client this week, and the client was debating whether or not to provide all the users in their organization with the ability to create reports.

On the upside, the debate went, they knew this would be a good way to empower users to ask (and answer) questions about their work inside Salesforce.

On the downside, most users don't know Salesforce well enough to run effective reports.

You end up with many duplicated reports.

You get reports that don't have the right filters and so don't show all the records you think they should.

And tons of reports get saved to the default "Private Reports" folder where they don't help other team members.

This led to an interesting question...

"How many reports do we have in the Private Reports folder today?"

I used to think as System Administrators that even we couldn't access or easily count the total number of private reports.

Turns out I was wrong!

My colleague mentioned he was pretty sure you could query for that information.

That got me curious, and I looked up the Object Reference table for both Reports and Dashboards.

Serendipity!

Sure enough, down at the bottom, there were two SOQL queries that show exactly how to query for private report and dashboards.

Using a very similar syntax, I created and ran a SOQL query to generate a full list of Private Reports in the environment.

And it worked!

Some of my newsletter readers even found other awesome ways to query for this information. Here's one awesome one to show the count by user in descending order.

SELECT Owner.Name, COUNT(Id) FROM Report USING SCOPE allPrivate GROUP BY Owner.Name ORDER BY COUNT(Id) DESC

And there you have it, easy queries to find private reports & dashboards.

With one another takeaway: the Salesforce Object Reference tables are filled with hidden gems.

Just the type of a thing a Salesforce Admin loves to nerd out on.

See you next week!