Spreading values between dates

Question

Post reply

Spreading values between dates

dean gross

SSCrazy

Points: 2632
More actions
July 2, 2007 at 7:52 am

#177316

I have a table with Task, Cost, Start Date and End Date fields. I would
like to calculate the average monthly cost for each cost and create a SSRS
chart showing the total monthly costs for all of the tasks. The number of
months between Start and End change for each task. What is the best way to
approach this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Piers-378326 SSC Enthusiast Points: 121 More actions · Answer 1

I think I know what you mean, but could you provide a little more detail ?

Do you mean average monthly cost for each task (rather than cost) ?

dean gross SSCrazy Points: 2632 More actions · Answer 2

Yes, avg monthly cost, then display it month-by-month

Current table is something like

Task Cost Start End

1 100 1/1/07 4/1/07

2 200 3/1/07 7/10/07

I need

Task JanCost FebCost MarCost etc

1 33 33 33

2 0 0 40

OR

Task Month Cost

1 Jan 33

1 Feb 33

1 Mar 33

2 Mar 40

Piers-378326 SSC Enthusiast Points: 121 More actions · Answer 3

Thanks for the extra detail. I would start by writing a SQL statement to select the task, cost and create a column for month. Something like

select TaskName, TaskCost, month(taskdate) as TaskMonth

from Tasks

where taskdate between @Startdate and @Enddate

I take you know how to create a dataset, and setup the date range parameters.

Drag a chart onto the report and make it a decent size. Click on it once so it is selected then a second or so later click it again and the data drop area's appear around the report.

Drag the TaskMonth column onto the Category area.

Drag the TaskName column onto the Series area.

Drag the TaskCost column onto the Data area.

Now you just need to change some expressions for the fields.

Right click on TaskCost (in the data area) and click properties. Change the value to =Avg(...) instead of sum. [... represents whatever was there before]

Right click on TaskMonth (in the data area) and click properties. Change the Label to =MonthName(...) [... represents whatever was there before]. Click the sorting tab and change the sort order to TaskMonth.

Set the format of the TaskCost to "c" and make sure you set the Language for the report (it defaults to US). To do this click on the grey area outside the white paper area and change the property.

Try previewing the report. Hopefully that should be about it. If you need to handle more than one year this can be incorporated if you make it a separate column and get the sorting right too.