As of Summer '12, joined reports do not support cross filters and I'm wondering if there is a way to work around it.
Example: I have a custom objects for Events, Events Attended, and Work History. Events Attended joins an Account to an Event via Master-Detail relationships (like a simple join table.) Events Attended and Work History are related lists on Accounts. I can create a joined report to show me which events a person has attended and retrieve their previous employers, but I only want to see results for people who attended Event A. If I filter the Events Attended block on Event A, I still get records for all Accounts.
Is there a way to effectively cross filter this to hide those Accounts that did not attend Event A? Would I need a workflow or trigger to just copy every Event name into a field on the Account and filter on that?
Edit To be clear, what I am trying to do is for a given subset of Accounts (e.g. attended a specific event) retrieve fields from their related Opportunities and Work History (Work History being a related list on Accounts.)
The equivalent SQL would be
SELECT *
FROM Accounts [A]
INNER JOIN Events_Attended [EA]
ON EA.AccountId = A.AccountId
INNER JOIN Work_History [WH]
ON WH.AccountId = A.AccountId
INNER JOIN Opportunity [O]
ON O.AccountId = A.AccountId
WHERE EA.EventName = "MyEvent"
Attribution to: Mike Chale
Possible Suggestion/Solution #1
You can use custom report types and add there if you want the relationship object A with or without records related to object B. then you don't need to create a filter anymore.
Sven
Attribution to: Sven Delporte
Possible Suggestion/Solution #2
I spoke with Premier Support today and they confirmed I could not do what I wanted since each piece of a joined report is pulled separately. Currently, the best solution is to add a custom field to the Account and filter based on that.
Attribution to: Mike Chale
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/163