How to filter Opportunity Owner ID in a report using Apex?

Question:

When building a report programmatically using Apex, how can you set up a filter for the “Owner ID” field on the Opportunity object? Specifically, I tried to use the ReportFilter from the Reports namespace to set the filter for the Owner ID but encountered errors. Here’s the code I attempted:

Reports.ReportFilter ownerFilter = new Reports.ReportFilter(
    'OPPORTUNITY_OWNER',
    'equals',
    UserInfo.getUserId(),
    Reports.ReportFilterType.fieldValue,
    'Opportunity'
);

However, I received the following error message:

reports.InvalidFilterException: [For the filter 1: Specify a valid filterable column because OPPORTUNITY_OWNER is invalid.]

I also tried other variations like OwnerId, OWNER_ID, and OWNER, but the error message persisted. What is the correct column name to use for filtering by the Owner’s ID?

Answer:

When adding a filter for the Opportunity Owner in the standard report builder, you will see options like Opportunity Owner, Alias, Email, Phone, etc. However, the Owner ID is not listed as a filterable field. This restriction is likely the reason why filtering by OwnerId doesn’t work in Apex when attempting to build the report programmatically.

CRS Info Solutions provides top Salesforce training in India with real-time projects, certification guidance, interview coaching, and a practical, job-ready approach.

In the report builder, when you add the Opportunity Owner field as a filter, the system resolves the column name as FULL_NAME rather than OwnerId. This behavior is consistent when you debug the report filter’s getColumn() method in Apex.

Although filtering by the OwnerId field directly is not supported, you can work around this limitation by creating a custom formula field on the Opportunity object. This field, such as OppyOwnerId__c, would return the Owner ID. Then, you can filter the report using the custom formula field like this:

Reports.ReportFilter ownerFilter = new Reports.ReportFilter(
    'Opportunity.OppyOwnerId__c', 
    'equals',
    UserInfo.getUserId(),
    Reports.ReportFilterType.fieldValue,
    'Opportunity'
);

Code explanation:
The given code snippet creates a filter for a report programmatically in Apex using the Reports.ReportFilter class. The filter is designed to apply to the “Opportunity” object and checks if the custom formula field OppyOwnerId__c (which returns the Opportunity’s Owner ID) is equal to the current user’s ID, which is retrieved using UserInfo.getUserId(). The Reports.ReportFilterType.fieldValue specifies that the filter is based on a field’s value, and 'Opportunity' denotes the object on which the filter is being applied. This filter is useful when you need to limit the report results to only the opportunities owned by the current user.

Summing Up:

To filter an Opportunity report by the Owner ID using Apex, direct filtering by the standard OwnerId field is not possible due to limitations in the report builder. Instead, a custom formula field (OppyOwnerId__c) can be created to store the Owner’s ID. Using this custom field, you can apply a filter in Apex by comparing it with the current user’s ID. This workaround enables efficient and dynamic filtering of opportunities based on their owner.

Boost Your Career with Expert Salesforce Training in India

Take your career to the next level with CRS Info Solutions’ premier Salesforce training in India program, designed to equip you with the skills and knowledge essential for thriving in the dynamic Salesforce ecosystem. Our comprehensive courses cover Salesforce Admin, Developer, and AI modules, combining in-depth theoretical learning with practical, real-world applications. Whether you’re a newcomer or an experienced professional, our structured curriculum ensures you gain proficiency in the tools and techniques needed to excel.

Our Salesforce training in India emphasizes hands-on experience and personalized mentorship, along with extensive study materials and expert-led certification guidance. This holistic approach prepares you not only for certifications but also for successful interviews and career growth.

Don’t miss out—sign up for a free demo class today and take the first step toward unlocking exciting career opportunities! Enroll now and start your journey to success!!!