Custom reports based on the current record in Salesforce

There are many things that you can show with related lists and roll-up summaries under a record but sometimes you would need an extra layer of depth or a more personalized report on activities or other records related to the one you are looking at currently. While power users have no problems creating a report they need it would still be useful to have a link or a button to create commonly used reports related to that record with a click of a button, right?

Unfortunately there is no standard functionality to do this in Salesforce but luckily there is a way around it. We need to create a report and a custom link to that report that includes the Account id, Campaign name or basically any other piece of information that we can use to filter the results of that report. It's not very complicated, you just need to master the ways of ?pv0=.

Creating the report

Let's say that your users would need to have a report of all the opportunities under an account. We might include product information or various summaries or maybe a pipeline of all open opportunities with full comments. Whatever the report is, you should make it so that it will show the desired information when you manually enter an Account ID (or name) as the first filter.

When we have the report that works like we want to just by changing the Account ID, we can save the report and copy the URL of the saved report and go forward with the custom link. Actually we just need the part after salesforce.com, but you'll see the formatting below.

Oh, and I'm using the Account ID here because it is the best way to single out a specific account and if you do it just by the account name there might be problems if you have duplicate data. With the ID you can be sure that the report is showing the right account and only the right account.

Creating the custom link

If you want to have a link in the Account page layout, you need to create the custom link in the Buttons and Links under the Accounts. And same goes naturally for campaigns, activities or custom objects.

After naming your link you can select whether you want it to be a button or a link. The instructions and examples are pretty clear on that part. You will also need to make sure that the Content Source is URL. Then we can move on to the more interesting stuff.

The trick here is to paste the URL of the destination report in to the field and add ?pv0= right after the address. This tells the report to fill the first filter with whatever is behind the wonderfully obfuscated series of characters. And since we want to have the Account ID there, we can choose it from the Insert Field -menu.

The end result should look like this:

/00OD0000003tr8s?pv0={!Account.Id}

That link will direct you to the report specified in the URL and insert the Account ID as the first filter. If you want to define the other filter rows you can also use ?pv1= for the second filter row, ?pv2= for the third and so on.

These can also be chained together so if you wanted to show the opportunities of other users in this account you would need to add a second filter row "Owner ID not equal to" in to the report and then make the link as follows:

/00OD0000003tr8s?pv0={!Account.Id}?pv1={!User.id}

Add to page layout

Maybe this goes without saying but you will also need to add the custom link/button to the page layout to make it visible for the users.

Other uses

You could also use this for creating reports of all the opportunities of accounts that have the current account as a parent {!Account.ParentId}. Or basically any other field that you can find in the Insert Field menu. It is definitely worth checking out because this can be a powerful tool if used in a clever way.

We use it amongst other things to show a campaign report that gives just the relevant fields when we want to export the names, email addresses and member ID:s to our external emailing service. There really is a million uses for this and if you have done something that you are proud of by using this method I'd love to hear about it in the comments.