need to create a summary table
Posted on 2012-03-20
I have a request to summarize projects by average monthly amounts for the duration of the project, then plot on a report.
So far I have created the duration for a project with the total and average monthly amount like so:
Project StartDate Duration (in months) monthly amount total amount
ABC 2009-01-06 00:00:00.000 35 20000 700000
Now I am kinda gettin fuzzy where I go from here. I was thinking I should build another table with a row for each project with 12 fields for months and fill in the monthly amounts, one field for the each project (ABC), and one column for the year.
In this case it would take 3 separate rows one for 2009, one for 2010, and ond for 2011.
The idea is to be able to sum up all of the average monthly amounts for each year, then plot them on an SSRS graph report.
After I have this, I am still trying to figure out how I am going to plot this data on a graph. Keep in mind that I am talking about 500 - 1000 projects that span anywhere from 6 to 36 months each. I expect the final graph will span one year at a time.
Any help getting me started in the right direction would be greatly appreciated.