3. Format Reports40 mins
When you create a report in Salesforce, the criteria you enter is essentially a question you’re asking, and the results returned are the answer. But in order to ask a question, you have to speak the database’s language. Good news! Salesforce comes with a built-in translator, allowing you to ask your database all the questions you want through a point-and-click interface. Meet the drag-and-drop report builder, your way to get the answers you need about your data.
Now, to build a new report, you just need to open the report builder. From the Reports tab, click New Report. Then, select a report type.
Select a Report Type
Choosing the right report type is one of the most important steps in building a report. When you pick a report type, you’re picking the records and fields you’ll be able to see in your report. At this point, let’s press the pause button and make sure you understand how report types are structured.
Each report type has a primary object relationship and a field layout.
The object relationship determines which records the report type includes. Objects are standard or custom Salesforce entities, like opportunities, accounts, and products. Each object relationship specifies a primary object, like opportunities, and optionally one or more related objects. If you specify only a primary object, your report type includes only records for that object. If you also specify a related object, like products, then your report type includes primary objects with (or without, depending on configuration) related objects. For example, the report type Opportunities with Products includes opportunity records that have at least one related product record. If you add a related object, here’s how you can configure a report type’s object relationship:
- Primary object with related object—Records returned are only those where the primary object has at least one related object record. In our example of Opportunities with Products, the only records that would be displayed on the report would be opportunities that have at least one related product record.
- Primary object with or without related object—Records returned are those where the primary object may or may not have a related object record. If we were to create a custom report type, Opportunities with or without Products, then opportunities would be displayed whether or not they have a related product record.
You can have up to four related objects, and each can have the “with” or “with or without” distinction.
The field layout determines which fields the report type includes. Fields often translate to “columns” in a report. A single instance of an object (a record) is described by a set of fields, and each field also has a type. Field types include Text, Number, Checkbox, and Date/Time. For example, each opportunity record has fields (with field type in parentheses) like Account Name (Text), Amount (Number), Closed (Checkbox), and Close Date (Date/Time).
Don’t see the fields you want after selecting a report type? See too many fields? You may need to create or edit a custom report type that adds or hides fields. To create, edit, or review report types, from Setup, enter Report Types in the Quick Find box, then select Report Types.
In the next section, we’ll dive into how you can use filters to get specific results.
When you’re using the report builder to ask a question about your data, filters and filter logic allow you to get more specific, and row limits help you limit the answer you receive. First, let’s take a high-level look at these features, and then we’ll walk you through how to build a filter.
Now that you’ve got that down, let’s help Maria build and filter a report. Lance Park, the Sales Rep, mentioned Maria on Chatter to ask for a report of accounts who are direct customers.
- Go to Reports and click New Report.
- Select the ‘Accounts’ report type and click Continue.
- In the filters pane, click Add filter to include a Field Filter.
- Choose a field from the first drop-down list. For this example, let’s choose Type.
- Set the filter operator to equals.
- Select Customer - Direct for Value, and click Apply.
- For the Date Field, select Created Date and set the range to All Time.
- Click Apply.
Nice job! You’ve just created a report filter that shows all of your Accounts where the Type = Customer - Direct. Click Report to see your results. Please note, depending on which org you’re using to practice these steps, you may or may not see data in your report at runtime.
Here’s a handy shortcut. When creating a filter, try dragging a field from the fields pane to the filters pane. Voilà! You’re ready to build your next filter. You can also use the type-ahead feature to locate a field. You’ve got options!
In the above example, we used the Equals operator to create our filter, but there are a number of operators you can use in building your reports. However, a word of caution: Watch your performance carefully when using Not Equals as an operator. It may cause your report to run slowly or time out. Check out the full list of filter operators in the Filter Operators Reference.
Use Cross-Object Filters
Now that you’ve built a filter, let’s take this to the next level with cross-object filters, or “cross filters.” These allow you to extend your report types to objects related to the original objects defined in the report type. Cross filters help you fine tune your results, without writing code or using formulas. The most common use case is exception reporting. Here are some examples that the Sales team at Ursa Major Solar has requested.
- Stale Opportunities - Opportunities without activities in the past 30 days. Erin doesn’t want to waste time calling these opportunities.
- Orphan Contacts - Contacts without accounts. Lincoln wants to add these contacts to accounts, or else scrub them away.
- Neglected Accounts - Accounts with no opportunities. Lance would like to spend the afternoon doing outreach to these accounts to see if there is any interest.
Now let’s help Maria help the team by creating a report with a cross filter!
- Go to Reports and click New Report.
- Let’s select the Contacts & Accounts report type and click Continue.
- In the filters pane of report builder, click the arrow and select .
- Select a parent object from the drop-down list. Your choice determines which related objects you see in the child object list. For this example, let’s select Contacts.
- Choose with as the operator.
- Select a child object from the drop-down or search by its name. The drop-down list contains all eligible child objects of your selected parent object. For this example, let’s select Opportunities.
- Click Apply.
- Optionally add subfilters:
- Click Add Opportunities Filter in the Filter area.
- Select a field. The fields are determined by the child object in the cross filter. For example, if your cross filter is Contacts with Opportunities, you can use opportunity fields for your subfilter. For this example, let’s select Stage for our subfilter.
- Select equals for the operator.
- Click the lookup icon and select Closed Won.
- Click Apply.
Good work! You’ve created a report that shows Contacts & Accounts, where the contacts are related to opportunities that are Closed Won. This kind of report will help the team identify contacts to invite to a “solar celebration” for customers who are taking a bite out of their energy bills. If you add another subfilter on Opportunity Amount or Close Date, you can learn more about how big the deal was or when it closed.
Use Filter Logic
Filter logic lets you apply filters based on conditions. Say that Erin wants to see her accounts who are either direct customers or resellers of Ursa Major’s solar components. Filtering a standard Accounts report takes Erin most of the way there:
- Account Owner equals Erin Donaghue
- Type equals Customer - Direct
- Type equals Channel Partner / Reseller
But filters 2 and 3 conflict with each other; causing the report to return no data. To make this report work like we want it to, we need to add filter logic. To add filter logic, from the filters pane of the Report Builder, click. Then, specify how filters should apply to the report. Here are the operators available for applying filter logic.
|AND||Finds records that match both values.|
|OR||Finds records that match either value.|
|NOT||Finds records that exclude values.|
To give Erin what she wants, set filter logic to 1 AND (2 OR 3). Now the report only contains accounts owned by Erin who are either customers or resellers. With this report, Erin is sure to crush her quota this quarter!