Custom field on Lookup object on standard report is blank/missing

Question

In Service Cloud, I'm trying to make a standard report on Cases. Our Cases have a custom lookup field, named Customer / Customer__c. This field is a Lookup(Account). And the Account has a custom field on it named Building Name.

When I create a standard Cases report, the Building Name field is available to add to the report.

However, the Building Name column on the report is blank (shows a "-") for all Cases. All Cases do have an Account set in the Customer__c field, and all of those Accounts do have a Building Name set.

I'm also unable to filter on the Building Name field, even though it's available in the filter list. Filtering on that value shows no results for Cases.

I'm an admin and have verified the custom field is viewable for my profile.

What am I missing? Is it possible to use a custom field to on a Lookup relationship to show on and filter on a standard report?

Answer 1

There's some magic baked into the standard Cases report that explains this.

Standard Cases have the AccountId field, which is a Lookup(Account). The standard Cases report type has a built in lookup through this AccountId field. It's the equivalent of making a custom report type > edit field layout > add lookup fields > add Case.Account.[fields].

Meaning that the "Building Name" on my report is available as a column because it's coming from Case.Account.Building_Name__c.

This column is not coming from my Case.Customer__c.Building_Name__c field, which is why it's blank on the report, and why I can't filter on it.

If I want to filter on this custom field on the lookup field record, I need to make a custom report type to add it 😢. It can't be done on a standard report.