Dynamically Filter Line Chart with Formula Queries (or open to better ideas to do this)

Question

Hi everyone, I had a user request a line chart that is filterable by contact showing the Total Opportunities Created as one line and another line showing the total revenue generated. The idea is the lines would have 2-3 colors per set (YTD, last year, 2 years ago) and would be filterable by the contact they select. The challenge is to my knowledge you cannot dynamically filter formula queries. So how would you go about this? Basically when he does his quarterly business review meetings with clients he wants to pull up a chart showing how many opportunities we generated and how much revenue generated for that person. Than switch to another person for the next meeting

markshnier__you · Answer

Can you tell us a little more about the structure of your app. You may not have relationships, but what are the records that are holding these totals and what are the formulas queries summarizing? What are the sort of child tables.&nbsp;

ivanweiss · Answer

Sure.Projects&lt;Opportunities&lt;Orders&lt;InvoicesProjects&gt;Project Members&lt;ContactsThere is a field on the Opportunities Table for Value (which when we take that and Date Created we can get the total opportunities created).&nbsp; There is a field on the invoices table called Billing Amount and when you take that and the invoice date you can get total revenueProject Members is a join table between Projects and Contacts as that is a many to many relationship.&nbsp; I summarize the project members up to projects and down as necessary.&nbsp; So that is what I am looking to filter on.&nbsp; 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.

markshnier__you · Answer

Let'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.

ivanweiss · Answer

Understood but can formula queries work with dynamic filters.&nbsp; I thought they dont support that and would not filter the data based on the dynamic filter since the query is explicitly pulling records?&nbsp; Or do I need to use that field in the query itself?

markshnier__you · Answer

Well, I gather from your question that your chart is simply a report of opportunities. So it's just filtering which opportunities will show.

Forum Discussion

Dynamically Filter Line Chart with Formula Queries (or open to better ideas to do this)

6 Replies

Related Content

Report Dynamic filters

Formula Query with custom key

Query giving values of zero

Syntax help using a variable in a formula query

QrewTip: Formula Logic Order