Sure.
Projects<Opportunities<Orders<Invoices
Projects>Project Members<Contacts
There is a field on the Opportunities Table for Value (which when we take that and Date Created we can get the total opportunities created). There is a field on the invoices table called Billing Amount and when you take that and the invoice date you can get total revenue
Project Members is a join table between Projects and Contacts as that is a many to many relationship. I summarize the project members up to projects and down as necessary. So that is what I am looking to filter on. If, for example, the user types in Mark Shnier to the filter it should filter out all of the total opportunities and total revenue as a line chart month over month for your business with us.
Qrew LegendLet's start with ther basics to see if there is an easy answer.
On this Relationship Projects>Project Members
You can create a combined text summary field to summarize the Contact Names.
Then make a field called perhaps [Contacts (DF)] (ie Dynamic Filter).
Formula Multi Select Text
Split(ToText([my combined text summary field]))
You will find that that field will work as a Dynamic Filter allowing one or more unique contacts to be selected. You can the look up that field down from Projects to Opportunities and use it as a Dynamic Filter on your chart report.
Understood but can formula queries work with dynamic filters. I thought they dont support that and would not filter the data based on the dynamic filter since the query is explicitly pulling records? Or do I need to use that field in the query itself?
