0

I'm currently getting some basic information regarding all of my reports like so:

SELECT Id, Name FROM Report

I have around 5000 reports. I'd like to be able to get the type of report as well (Opportunity, Lead List, etc), but I don't think you can necessarily do that with SOQL. I've looked at the Metadata API and the Bulk API. Unless I'm missing an easy way to do this with SOQL, what's the best route to go? I would like to retrieve this information fairly quickly, so I'm not sure if the Bulk API would be suitable for that.

3
  • As sf.dev mentioned, you can't retrieve the type on Report, but have you tried List<Report> r = [SELECT id, Name FROM Report LIMIT 5000]? I forget the exact number, but unless I'm mistaken, if you add a LIMIT clause to your query, it will override the default 200 records returned Jun 12, 2015 at 16:29
  • What do you need the list of names for? An admin report would give you these details (though not all on one page) or you could use Mavens Mate / Eclipse to access all reports in your org..
    – Alex S
    Jun 12, 2015 at 20:13
  • I actually am doing a LIMIT, just hadn't included it as part of that query. The list of names is just so I can present them to someone in a human readable format.
    – TJ1S
    Jun 12, 2015 at 20:40

3 Answers 3

1

If I had to do this enough to justify writing code, I might treat it as a Python text-processing problem, not a Salesforce problem per se.

Since you don't sound afraid of the Metadata API, I'm presuming you're a programmer and can pick up file I/O, data structure manipulation, etc. in Python pretty quickly.


First, I'd extract my report names (sounds like it can be done through SOQL) to some form of text that a Python interpreter on my local hard drive can read (e.g. run the query through simple-salesforce, export to csv on my hard drive & read in the CSV with Python, etc.)

Then I'd take that list of report "full names" (includes file path) and turn it into some sort of iterable collection of strings in Python (e.g. a list of strings). For example, ['ReportFullName1','ReportFullName2','ReportFullName3'].

Then I'd use Python to loop over my collection and generate text in the following format, and save it to my hard drive as "getme.xml":

<?xml version="1.0" encoding="UTF-8"?>
<Package xmlns="http://soap.sforce.com/2006/04/metadata">
    <fullName>GetObjectsPackage</fullName>
    <types>
        <members>ReportFullName1</members>
        <name>Report</name>
    </types>
    <types>
        <members>ReportFullName2</members>
        <name>Report</name>
    </types>
    <types>
        <members>ReportFullName3</members>
        <name>Report</name>
    </types>
    <version>36.0</version>
</Package>

Then I'd go to https://workbench.developerforce.com/metadataRetrieve.php, and upload getme.xml.

I'd download the ZIP file returned, grab the "reports" subfolder out of the "unpackaged" folder in the ZIP file and put it onto my hard drive.


I'd then use another Python script to loop over every "*.report" file in the "reports" folder and extract two pieces of data from it - e.g. as an iterable collection of key-value pairs:

  1. its "full report name" (reassemble from file path or, if you just need the report name inside its folder, grab from the XML that the file is made of)
  2. its report type (grab from the XML that the file is made of - tip below)

The same code would then take the collection of ReportName-ReportType pairs I'd built from parsing the ".report" files and write them out to a 2-column CSV on my hard drive so I could peruse it.


Tip:

A ".report" file is just XML in the following format:

<?xml version="1.0" encoding="UTF-8"?>
<Report xmlns="http://soap.sforce.com/2006/04/metadata">
    ...Stuff you can ignore...
    <name>Report Name Within Its Folder</name>
    <reportType>Your_Report_Type__c</reportType>
    ...Stuff you can ignore...
</Report>

Here's an example of Python code that grabs the value between `... tags from a file path to some "something.report" file on your hard drive.

It relies on importing the "minidom" module: from xml.dom import minidom

currentType = minidom.parse(VariableWithFilepathGoesHere).getElementsByTagName('reportType')[0].firstChild.nodeValue

This line of code could go inside a loop through lots of file paths as described above.


(Okay ... or ... between me starting this post and finishing it, @mlpSFadmin could point out that Salesforce's web-based report builder apparently gives you all the tools you need for obtaining this CSV file if you build your own custom "Report Type" on the Report object. Fun thought experiment anyway.)

1

I know this is an old post, but I'm having the same issue. I'm digging around to see if anything has changed since SFDC posted confirming it's not possible, but I fear he's right.

I created an idea for this, however, as the idea he linked I believe is for something else. If I find a better solution, I'll post it, otherwise please vote!

EDIT This is possible through the UI if you create a Custom Report type for Reports (the standard "Reports" report type has limited fields, but a custom report type for them will include things like namespace prefix and Report Type).

2
  • If the original poster's problem can be nearly (not SOQL, but tabular data like people usually want out of SOQL) solved with what you just described in this comment, I suggest changing your "answer" to include this approach. It would help future thread visitors quickly notice your idea. I haven't tried it myself, but it sounds like a wonderful workaround for most people who simply need "spreadsheet-style data to skim through."
    – k..
    Aug 24, 2016 at 17:10
  • Good point! Done!
    – mlpSFadmin
    Aug 26, 2016 at 21:08
0

You cannot retreive report type from Soql query.Here is the idea exchange link

1
  • If I'm reading that idea correctly, that's not what the OP was asking. I think that idea is looking for a way to create new Custom Report Types based off a SOQL query. The OP on the other hand just wants to know how to include ReportType in the SELECT statement of a SOQL query. I am also looking for this and will post a link to an idea here if I can find one/need to create one!
    – mlpSFadmin
    Aug 24, 2016 at 14:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .