Help Charting Related Data

[nesor]

[Projects] table

[Dates] table

I'm trying to generate a bar chart - based on the current set of projects - showing the number of projects per month.

Should the chart layout be based on records from the Projects or Dates table?

I'm also stumped on the calculations for the vertical and horizontal columns.

Any help would be greatly appreciated — Thanks!

[comment]

I am afraid that's entirely not clear. What is the Dates table and what is the relationship between it and the Projects table? And what is the "current set of projects"?

Let me ask you a counter-question: charting aside, can you display these numbers on screen?

[nesor]

The dates table contains all of the dates related to projects. A project typically spans multiple dates.

The Dates table is related to the Projects table via a project_id foreign key.

By Found Set, I'm referring to the current set of records a user is viewing in the Project list (the layout from which the user will generate the chart). For example, I may search for projects between Jan 1, 2010 and March 31, 2010. The query searched the related Dates table and produces a list of Projects on my Project list that fall within this time period. Now I'd like to generate a chart based on the projects in my project list.

With respect to your question about seeing the data on screen, I'm not sure I understand the question. In the Project List layout, I correctly see each project and Grand Total, as well as the earliest related Date for each project (e.g. If a project has related dates 1/1/10, 1/2/10, and 1/3/10, the Project List layout shows 1/1/10).

[comment]

Suppose there are only three projects:

Project A has dates in January and February;

Project B has dates in February only;

Project C has dates in February and March.

I presume that means:

January: 1 project:

February: 3 projects;

March: 1 project.

Is that correct? (My question was regarding this last tabular array.)

[nesor]

That is correct

[comment]

Well, the problematic part here is not the charting - it's how to count the number of projects in each month - see:

http://fmforums.com/forum/showtopic.php?tid/196904/post/297916/#297916

Once you have that, the chart itself will become trivial.

[nesor]

I'll check that out. Thank you for your help.