I have the following data in Access...
In addition to the data above, I will be passing a total budget number (let's say 900000 for this example) as a parameter to the report.
I want to create a line graph (with data points) that will show the (running) effect of each project's budget amount on the total budget number. The horizontal axis of the report should display each month of a given year (2007 in this case). The vertical axis will show dollars.
The initial point of the line will be the total budget number (900000). The next point plotted will be at the end of January on the horizontal axis and at 750000 (900000-150000) on the vertical axis. The next point plotted will be near the end of March on the horizontal axis and at 495000 (750000-255000) on the vertical axis. The last point plotted will be near the middle of May on the horizontal axis and at -30000 (495000-525000) on the vertical axis. So the vertical axis will need to extend down past the horizontal axis to handle projects that go over budget (in the red). In this case, the plotted line will extend down past the horizontal axis by 30000.
I've already created a running sum of the project level budget numbers. Then I have a formula which subtracts the running sum from the total budget number. This gives me the point at which each project needs to be plotted against the vertical (dollars) axis.
Is there a way to do this given the data structure I'm currently using? If not, is there a better way to structure the data to get this chart right?
Any help would be GREATLY appreciated. Thanks.
Code:
[u]ProjectName[/u] [u]ProjectDate[/u] [u]BudgetAmount[/u]
Project 1 1/31/2007 150000
Project 2 3/23/2007 255000
Project 3 5/12/2007 525000In addition to the data above, I will be passing a total budget number (let's say 900000 for this example) as a parameter to the report.
I want to create a line graph (with data points) that will show the (running) effect of each project's budget amount on the total budget number. The horizontal axis of the report should display each month of a given year (2007 in this case). The vertical axis will show dollars.
The initial point of the line will be the total budget number (900000). The next point plotted will be at the end of January on the horizontal axis and at 750000 (900000-150000) on the vertical axis. The next point plotted will be near the end of March on the horizontal axis and at 495000 (750000-255000) on the vertical axis. The last point plotted will be near the middle of May on the horizontal axis and at -30000 (495000-525000) on the vertical axis. So the vertical axis will need to extend down past the horizontal axis to handle projects that go over budget (in the red). In this case, the plotted line will extend down past the horizontal axis by 30000.
I've already created a running sum of the project level budget numbers. Then I have a formula which subtracts the running sum from the total budget number. This gives me the point at which each project needs to be plotted against the vertical (dollars) axis.
Is there a way to do this given the data structure I'm currently using? If not, is there a better way to structure the data to get this chart right?
Any help would be GREATLY appreciated. Thanks.