One of the most powerful features of Salesforce is the report builder. The report builder allows us to analyze data by selecting the object(s), field(s) while filtering the results with criteria. Once you have mastered the art of report building, the leap to SOQL queries is relatively small. Let’s take a look at a fairly simple example to get started.
First let’s build a simple report with our basic Opportunity information:
- Login to your Developer Org and click on the Report tab.
- Click the New Report button.
- Select Opportunities for the report type and click Create.
- Leave the default, All Opportunities from the Show dropdown.
- Change the Date Field Range to All Time.
- Keep only the following fields:
- Opportunity Name
- Lead Source
- Expected Revenue
- Close Date
- Next Step
- Stage Name
- Click Run Report.
Next let’s repeat the process in our Developer Console’s Query Tab:
- Launch the Developer Console.
- Click on the Query Editor Tab.
- Enter the following Query into the Query Editor:
Opportunity QueryJava1SELECT Name, Type, LeadSource, Amount, ExpectedRevenue, CloseDate, NextStep, StageName FROM Opportunity
- Click the Execute button.
The Query Results displayed at the top of the Developer Console should be identical to the Report Results created above. Now let’s breakdown the above query.
- The SELECT clause – All queries must include this statement, this is equivalent to dragging the fields over in the report builder. You can request the system return to you one or more fields. If more than one field, each field must be separated by a comma. One of the key differences between the report builder and the SELECT statement is the way we describe the fields. The report builder by default uses the field label whereas the SOQL statement uses the API Name. We can find the API Name by clicking the field name found on the object’s setup page. For a standard field the Field Name is also the API Name, so for example the Opportunity Name field has a Field Name of “Name” which is also it’s API Name. The API Name is slightly different for Custom Fields because they end with two underscores followed by a c “__c”.
- The FROM clause – This clause is also required and is how we tell the system which object we want to retrieve the fields from. In the report builder this is similar to selecting the type of Report you want to create. Where the Report Type defines which object(s) should be included on the report. Although less obvious when using a Standard object, we are also using the API Name of the Object here, for Opportunities it is Opportunity, and custom objects will once again end in “__c”.