This is the second “Tip and Trick” from my “Dreamforce 2 You” presentation. This tip came out of two questions and comments that I have heard a lot.
- How can I see my Accounts without Opportunities?
- There are too many fields to select for reports and I don’t want my users seeing all the fields
Both of these items can be fixed by creating a Custom Report Type. Let’s start from the beginning; To create a custom report type you will need to go to Setup>Create>Report Types and then follow the wizard.
- To see Accounts without Opportunities you will need to use Accounts as the Primary Object and Opportunities as the “B” Object. On the “B” object, make sure you select the second option – “A” records may or may not have related “B” records. This will allow you to see all accounts regardless if they have an opportunity or not. Something else I like to do with a parent-child outer join (what you just created) is add a Roll-up Summary field to count the number of child records, then use that field as criteria for your report, i.e.: Show me all Accounts where “Count of Opportunities = 0.”
- To solve the problem of too many fields, follow the steps above. Instead of using the outer join, recreate the standard report. Use Opportunities and click “Save”. This will take you to the next screen where you will see the section named, “Fields Available for Reports”. Click on the “Edit” button and now you can start taking fields away.
Make sure your users have access to the new report type and now the user experience is much better! No need to feel overwhelmed or worried with your users creating a report, you are in control of what they see and have access to! Training is much easier, and you get to keep your secrets!
A couple of weeks ago I was lucky to attend and present at the, “Dreamforce 2 You” Event that was put on by the Orlando and Tampa User Groups. This was the second year of the event and it doubled in every way (tripled in some ways too – from 2 sessions to 6)! There will be more postings to come out of this event, but I wanted to start with some helpful information from my presentation about Reporting.
In my presentation I shared two tips that I always rely on. I will focus on the first one today – using custom formula fields for reports/dashboards. Before you were able to add columns to a table on a dashboard (or now, overcoming the limitations), we needed another way of displaying more information while keeping a dashboard easy to read. Instead of just seeing the Opportunity Name with the Amount field, let’s use a custom formula field to concatenate some other valuable information.
For this example, I was presented with a business case that the VP of a division wanted to see what new opportunities entered the funnel this week. This person wanted to see the 2 amount fields, the account, the owner, and the opportunity name (what the project is). 5 fields to display on a table. I knew I could formula field some of them together and now only display 3 fields. Here is the formula I used to combine three fields into one for a more detailed view – Account Name, Opportunity Name and Opportunity Owner.
Account.Name &" - "& Name &" - "& CreatedBy.FirstName &" "& CreatedBy.LastName
Simple right? Now you can have a dashboard that looks like:
Stop causing yourself a headache with reporting and stop thinking that a formula field is only for calculations. Using a formula field for reporting purposes can solve many problems and make your dashboards more detailed while still being easy to understand.
Finally, a brief, “Thank You!” to @CRMJen, @jhoskins and @jackieforce for all their hard work for putting this event on. I would also like to thank Ingo Fochler from The New York Times Company for teaming up with me for this presentation. I will blog more about this event and my second tip shortly.
*Disclaimer – This was just one example of using a formula field for reporting/dashboard purposes. The field does not have to be on the page layout to work, but make sure you set up the field with the proper field-level security so that it is visible to your users.
