Charting by Related Data
I have a charting question that I’m hoping won’t be too difficult to accomplish. I have a database of donors that, for this chart, will use the related tables: Partners and Donations. These are related through the __pkPartnerID and _fkPartnerID fields.
I have a form view layout based on the Partners table that is essentially a quickview for each donor. It has a Tab Panel that organizes all relevant data for each partner. One of those tabs is naturally “Donations”. While I could just make a portal displaying all donations, I’d like to do a column chart displaying “donations (y-axis) by Month (x-axis)”.
Here are where things may get tricky:
1) Each donor may have more than one donation in a given month, so if there were a month where a partner had two donations, I would want the sum of those listed for that month.
2) I would like only the last 12 months of data displayed in the chart (dynamically updating)
3) If a given month has no donations, I would like the graph to display that “0” or “null” value. (When I was testing charting this, if a month had no values it would not be displayed at all)
Where I’m having a hard time with constructing a solution for this is that its charting based off of related records. I’ve searched on how to accomplish each one of the parameters listed above and found some information, but I haven’t seen them implemented in a solution all together.
Maybe I’m way off, but this seems like a chart with these parameters would be useful and very desired for many different applications (Customer sales by month, etc…). Following that line of thinking, my guess would be that it’s a fairly common chart and wouldn’t be that hard to create. Is a chart with these parameters possible, and if so, what am I missing to create it?