Salesforce Report Challenge: Total of Opportunities for All Accounts Within a Hierarchy

I received a report request today that sounded something like this: I need to get a total of all won opportunities for any account in this account hierarchy, regardless of whether we worked directly for them or were subcontracted*.

Sounds simple, right? 

In theory, yes. But, if you happen to have a complicated account hierarchy (eg: you have any federal government accounts), this is not an easy task. In fact, it's not natively possible to run a report like this in Salesforce.

In my case, I was looking at 80+ accounts organized in a hierarchy that was five levels deep. 

Rather than tell the requestor that the report was impossible, I decided to get a little creative.

Step 1: Get a list of all the accounts to include

I clicked on the [View Hierarchy] link next to the account name. Because I had multiple parent/child relationships, I scrolled until I found the account name that I was looking for within the hierarchy. Then I highlighted all the accounts underneath that, copied and then pasted them into Excel.

This is what my spreadsheet looked like:

At this point, I needed to extract the record IDs from the account name url. I remembered blogging about this before and read through the old post to remind myself how to extract the record ID from a hyperlink.

My spreadsheet then looked something like this:

Step 2: Generate a list of record IDs to use as a report filter

After I extracted my record IDs, I created a formula to concatenate the record IDs into a format that I could use as my report filter. (I demonstrated this method in a previous post.)

Step 3: Create a report and add the filter criteria

I created the report in Salesforce and pasted the concatenated IDs in my filter. Since I wanted to filter by two account fields on the opportunity, as well as by the parent relationship, I had to add those three fields to my filter. (Note: Several of these fields are custom formula fields based on the lookup to the Account object.)

Because the filter criteria can include only so much text, I split the record ID list into two filters for each field that I wanted to apply the filter to.

Here's how it looked:

Final Thoughts

I'd much prefer an approach that is more dynamic, rather than hard-coding the record IDs to generate a report. But for now, it does what I need it to do.

If you'd like to see some improvements to functionality surrounding parent/child accounts and account hierarchy functionality, vote up these ideas on the IdeaExchange: Add more account hierarchy functions for parent/child accounts, Report on Account Hierarchy

Have you ever received a request for a Salesforce report that stumped you? Or do you have an idea to improve my workflow? I'd love to hear your suggestions and stories in the comments below.

*We have a Secondary Account custom lookup field so that we can track not only the direct client the opportunity is for, but also the account that may have hired them (when we are operating as a subcontractor).

Simple TextExpander Snippet to Search Salesforce with a Copied Keyword

Here's a simple text expansion idea that popped into my head today and will save me clicks/time on a daily basis!

I often receive emails asking about a record in Salesforce by a keyword, such as Opportunity Name, without a link to the record.

My previous workflow involved:

1. Highlighting the Opportunity Name in the email
2. Using CTRL + C  to copy what I've highlighted
3. Navigating to Salesforce
4. Putting my cursor in the search box
5. Using CTRL + V to paste what I copied
6. Using the ENTER key to execute the search
7. View the search results to find what I'm looking for

I realized today that it would be a lot easier to automate steps 3-6 using TextExpander. So, I created a new snippet comprised of the url of the page after I do a search in Salesforce.

My new process is: 

1. Highlighting the Opportunity Name in the email
2. Using CTRL + C  to copy what I've highlighted
3. Opening a new tab in my browser (CTRL + T)
4. Typing the snippet abbreviation, in my case: s.sr
5. Viewing the search results to find what I'm looking for

The snippet is made up of three parts: 1) the Salesforce search url, 2) the value I've copied to my clipboard, and 3) the [ENTER] keystroke

Creating this simple snippet shaves a few clicks whenever I need to perform a search based on keywords I've received in an email. It might not seem like a big deal, but every click or keyboard stroke counts in my book!

(Updated) Sending a Link to Google Slides in Presentation Mode

Update 9/12/17 - It seems that Google has disabled this functionality. But, here's another method that I found while searching for solutions. 

Copy the share url (click on the Share button and click 'get shareable link') and update like shown in the sample below: 

Original link: https://docs.google.com/presentation/d/1234567890/edit?usp=sharing
Update to: https://docs.google.com/presentation/d/1234567890/present?usp=sharing

When deploying changes to Salesforce, part of my process involves creating documentation and tutorials to explain the changes to users.

I've found Google Slides to be very useful for this purpose. I create a short slide deck with an overview of the changes, along with screen captures so they'll know exactly what to expect.

I generally send a link to the shared slide deck, but I realized recently that my recipients might not be sure what to do with that link, or how to view the presentation in an easily navigable format.

My previous method involved sending a link to the presentation, which opened in this preview mode.

So, to make the process easier, I've started sending a link to the slides in presentation mode.

To do this, I click on 'Present in new window' under the Present button dropdown, and then copy the url in the new window that opens.

Now, when I share the link, the slide deck will open in presentation mode and the viewer can use the arrows on the keyboard to navigate.

I'm always looking for ways to streamline and improve how I support end users. Have suggestions or ideas to improve the process I've described above? I'd love to hear them in the comments!

Promoting My Brand Internally as a Salesforce Admin with New User Welcome Emails

In my last post about using the Vertical Response WYSIWYG editor to create emails for my Salesforce workflows, I mentioned that I would share how I use this method to promote my brand as a Salesforce Admin within the company. So, here's the story.

When it comes to my personal brand, I'd summarize it in these words: approachable, knowledgable, responsive and helpful.

I want to be the person my coworkers go to when they have a suggestion for improving CRM for the company, who they contact when they aren't sure how to follow up on a lead assigned to them, the name that pops into their head synonymous with Salesforce.

Conveying that image starts with my very first correspondence with them, typically the 'new user welcome email' that I send when I create their user account in Salesforce.

To help enforce this brand, I recently began redesigning my internal support emails, including that new user welcome email.

Here's what it looked like before:

It wasn't horrible. I just thought it could be a little more streamlined and use a bit more pep. My video tutorial was also quite out of date (besides, who has time for a 1 hour video tutorial?). So, I transferred the most important points into an updated Google Slide presentation.

And here's my redesign:

I like that this format is personable; I've added my picture at the bottom because working remotely means I rarely get to meet my coworkers. It's pops a bit more with the simplified layout and graphics. The flow of information is improved and it makes it easier for the user to see what they have to do next.

Here's a peek at a few slides in the presentation that I link in my email:

A little side-by-side comparison:

And finally, here's how I use this email template:

1. Create new user.
2. Navigate to 'Mass Email Users' under 'Manage Users' in Setup.
3. Refresh 'New Users' list view that I've customized to show users created today.
4. Select my email template. 
5. Send, leaving the default to bcc myself checked.

If you're interested in improving your personal brand, here are a few articles that I found useful:

• How to Build Your Personal Brand Within a Company - Forbes
• The 10 Pillars To Creating a Personal Brand in a Digital World - jeffbullas.com

PS - The icons I used in my updated email are from: http://icons8.com/free-ios-7-icons-in-vector/