Using Excel to keep a track of moving tasks/costs

D

DM

Hi All,

I am trying to use Excel to create a live report (linked via MS Query or
similar) that shows how much a project is worth over a set of months and
then to also show if the task moves, what the change to the monthly costs
are, i.e. it moves from being a task starting in October and finishing in
December task to moving to January and finishing in March.
I can create a view in Data Analysis, as below (hope the formatting works!)
but this is not 'live' i.e. updated periodically via the cube building
service, and if you do export to Excel, this is it seems a read only view.
What I cant find in the database (Reporting or Published) is any indication
which shows cost allocation in a particular period, i.e. month, so I can
recreate this view in Excel. It just seems there's no time 'dimension'
stored, or maybe I'm looking in the wrong place....

Any help appreciated!

Thanks

Cost Year Quarter Month 2008 2008 Total
Quarter3 Q3 Total Quarter4 Q4 TotalProject
Name Aug Sept Oct Nov DecProject
1 3000 2000 5000 3000 2000 2000 7000 12000
Project 2 1236 1251 2487 1236 1251 1251 3738 6225
Project 3 2132 151 2283 2132 151 151 2434 4717
Project 4 1351 3512 4863 1351 3512 3512 8375 13238
Project 5 9623 2123 11746 9623 2123 2123 13869 25615
Project 6 2626 151 2777 2626 151 151 2928 5705
Project 7 23512 12123 35635 23512 12123 12123 47758 83393
Project 8 1351 1544 2895 1351 1544 1544 4439 7334
Project 9 1356 2658 4014 1356 2658 2658 6672 10686
Project 10 1354 1351 2705 1354 1351 1351 4056 6761
Project 11 13514 5445 18959 13514 5445 5445 24404 43363
Grand Total 61055 32309 93364 6836 32309 32309 125673 219037
 
R

Ray McCoppin

Trying looking at the views in the reporting database. They have joins in
them to create the time dimension. So you query might look something like
this.


SELECT MSP_EpmProject_UserView.ProjectName,
Year(MSP_EpmAssignmentByDay_UserView.TimeByDay)as [Year],
Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)as [Month],
SUm(MSP_EpmAssignmentByDay_UserView.AssignmentCost)as [Cost]
FROM MSP_EpmProject_UserView INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmProject_UserView.ProjectUID =
MSP_EpmAssignmentByDay_UserView.ProjectUID
Group by
ProjectName,Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)
Order by
ProjectName,Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)

Hope this helps

--
Ray McCoppin

http://www.randsmanagement.com
SRS gantt charts
Project Server 2007 Mass Resource Tool
 
D

DM

Fantastic! Thanks so much for your help - it was a bit more than a pointer
in the right direction!

Regards

DM

Ray McCoppin said:
Trying looking at the views in the reporting database. They have joins in
them to create the time dimension. So you query might look something like
this.


SELECT MSP_EpmProject_UserView.ProjectName,
Year(MSP_EpmAssignmentByDay_UserView.TimeByDay)as [Year],
Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)as [Month],
SUm(MSP_EpmAssignmentByDay_UserView.AssignmentCost)as [Cost]
FROM MSP_EpmProject_UserView INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmProject_UserView.ProjectUID =
MSP_EpmAssignmentByDay_UserView.ProjectUID
Group by
ProjectName,Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)
Order by
ProjectName,Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)

Hope this helps

--
Ray McCoppin

http://www.randsmanagement.com
SRS gantt charts
Project Server 2007 Mass Resource Tool


DM said:
Hi All,

I am trying to use Excel to create a live report (linked via MS Query or
similar) that shows how much a project is worth over a set of months and
then to also show if the task moves, what the change to the monthly costs
are, i.e. it moves from being a task starting in October and finishing in
December task to moving to January and finishing in March.
I can create a view in Data Analysis, as below (hope the formatting
works!)
but this is not 'live' i.e. updated periodically via the cube building
service, and if you do export to Excel, this is it seems a read only
view.
What I cant find in the database (Reporting or Published) is any
indication
which shows cost allocation in a particular period, i.e. month, so I can
recreate this view in Excel. It just seems there's no time 'dimension'
stored, or maybe I'm looking in the wrong place....

Any help appreciated!

Thanks

Cost Year Quarter Month 2008 2008 Total
Quarter3 Q3 Total Quarter4 Q4 TotalProject
Name Aug Sept Oct Nov DecProject
1 3000 2000 5000 3000 2000 2000 7000 12000
Project 2 1236 1251 2487 1236 1251 1251 3738 6225
Project 3 2132 151 2283 2132 151 151 2434 4717
Project 4 1351 3512 4863 1351 3512 3512 8375 13238
Project 5 9623 2123 11746 9623 2123 2123 13869 25615
Project 6 2626 151 2777 2626 151 151 2928 5705
Project 7 23512 12123 35635 23512 12123 12123 47758 83393
Project 8 1351 1544 2895 1351 1544 1544 4439 7334
Project 9 1356 2658 4014 1356 2658 2658 6672 10686
Project 10 1354 1351 2705 1354 1351 1351 4056 6761
Project 11 13514 5445 18959 13514 5445 5445 24404 43363
Grand Total 61055 32309 93364 6836 32309 32309 125673 219037
Click to expand...
Click to expand...
 
D

DM

Actually have another question regarding this - now I have this data live
and the correct information - does anyone know how I can keep the history of
this information in either a SQL database or in Excel? e.g. schedule a
history 'snapshot' on a nightly basis which can then allow us to track
changes day to day, or week to week?

Thanks

Ray McCoppin said:
Trying looking at the views in the reporting database. They have joins in
them to create the time dimension. So you query might look something like
this.


SELECT MSP_EpmProject_UserView.ProjectName,
Year(MSP_EpmAssignmentByDay_UserView.TimeByDay)as [Year],
Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)as [Month],
SUm(MSP_EpmAssignmentByDay_UserView.AssignmentCost)as [Cost]
FROM MSP_EpmProject_UserView INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmProject_UserView.ProjectUID =
MSP_EpmAssignmentByDay_UserView.ProjectUID
Group by
ProjectName,Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)
Order by
ProjectName,Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),Month(MSP_EpmAssignmentByDay_UserView.TimeByDay)

Hope this helps

--
Ray McCoppin

http://www.randsmanagement.com
SRS gantt charts
Project Server 2007 Mass Resource Tool


DM said:
Hi All,

I am trying to use Excel to create a live report (linked via MS Query or
similar) that shows how much a project is worth over a set of months and
then to also show if the task moves, what the change to the monthly costs
are, i.e. it moves from being a task starting in October and finishing in
December task to moving to January and finishing in March.
I can create a view in Data Analysis, as below (hope the formatting
works!)
but this is not 'live' i.e. updated periodically via the cube building
service, and if you do export to Excel, this is it seems a read only
view.
What I cant find in the database (Reporting or Published) is any
indication
which shows cost allocation in a particular period, i.e. month, so I can
recreate this view in Excel. It just seems there's no time 'dimension'
stored, or maybe I'm looking in the wrong place....

Any help appreciated!

Thanks

Cost Year Quarter Month 2008 2008 Total
Quarter3 Q3 Total Quarter4 Q4 TotalProject
Name Aug Sept Oct Nov DecProject
1 3000 2000 5000 3000 2000 2000 7000 12000
Project 2 1236 1251 2487 1236 1251 1251 3738 6225
Project 3 2132 151 2283 2132 151 151 2434 4717
Project 4 1351 3512 4863 1351 3512 3512 8375 13238
Project 5 9623 2123 11746 9623 2123 2123 13869 25615
Project 6 2626 151 2777 2626 151 151 2928 5705
Project 7 23512 12123 35635 23512 12123 12123 47758 83393
Project 8 1351 1544 2895 1351 1544 1544 4439 7334
Project 9 1356 2658 4014 1356 2658 2658 6672 10686
Project 10 1354 1351 2705 1354 1351 1351 4056 6761
Project 11 13514 5445 18959 13514 5445 5445 24404 43363
Grand Total 61055 32309 93364 6836 32309 32309 125673 219037
Click to expand...
Click to expand...
 
H

hmhart

One way of many --

Create a report in the SQL Server Reporting Services (SSRS) that contains
your data using the ProjectServer_Reporting database. Configure this as a
snapshot report that runs once a day. Configure with as many history reports
as you want kept on the server. If people want to review far back history,
suggest setting up that report to also produce a separate weekly and/or
monthly run that keeps its own history. The SSRS reports allow export as
Excel, PDF, and a few other formats as selected by the user.

We are just getting into the design of some reports like this, so this is
about as much as I can help. But others here have much experience.
 
D

DM

Thanks for the tip - searching on the net seems to point in this direction
too, though ideally I'd like a single database table from which we could
query and create further custom reports as we're doing now manually from a
different cobbled together system.
 
D

DM

Found a different way which I think suits - I just run the select statement
and do an insert into a table. The entries just build up as needed and we
can query it using Excel.
 
Top