Reporting Directly from Database using SQL

B

Brian Gibson

We are having trouble producing a report within Project using View |
Reports.We are trying to develop a report that looks at a specific week
(current week?) and categorizes the hours work on a task based on Enterprise
fields values (Expense/Capital Expense) for that task by resource.

The report looks like this:

Resource Name
Project Name

Expense | Capital | Total
Sun
Mon
Tues
Wed
Thur
Fri
Sat

I believe this requires to report against the SQL tables, what table(s)
should we use to get daily actual hours worked by a resource. I understand
this may not be as straight forward as it might seem. The more specific the
better. I am not familiar enough with the SQL tables. We just took a cursory
look at the table and realized it was not straight forward. Any estimates of
the level of effort would be nice as well if it is difficult.

Thanks,

Brian
 
J

Jack D.

Brian said:
We are having trouble producing a report within Project using View |
Reports.We are trying to develop a report that looks at a specific week
(current week?) and categorizes the hours work on a task based on
Enterprise fields values (Expense/Capital Expense) for that task by
resource.

The report looks like this:

Resource Name
Project Name

Expense | Capital | Total
Sun
Mon
Tues
Wed
Thur
Fri
Sat

I believe this requires to report against the SQL tables, what table(s)
should we use to get daily actual hours worked by a resource. I understand
this may not be as straight forward as it might seem. The more specific
the better. I am not familiar enough with the SQL tables. We just took a
cursory look at the table and realized it was not straight forward. Any
estimates of the level of effort would be nice as well if it is difficult.

Thanks,

Brian
Click to expand...

I think that it would be easier to do this with VBA instead of SQL, however,
I am not entirely sure that you can do it.
The reason I say this is that your Expense and Capital fields are not
time-phased so you can not get the daily value for them.
The only timephased fields available are the following:

Actual Cost (task-timephased field)
Actual Fixed Cost (task-timephased field)
Actual Overtime Work (task-timephased field)
Actual Work (task-timephased field)
ACWP (task-timephased field)
BAC (task-timephased field)
Baseline Cost (task-timephased field)
Baseline Work (task-timephased field)
Baseline1-10 Cost (task-timephased fields)
Baseline1-10 Work (task-timephased fields)
BCWP (task-timephased field)
BCWS (task-timephased field)
Cost (task-timephased field)
CPI (task-timephased field)
Cumulative Cost (task-timephased field)
Cumulative Percent (%) Complete (task-timephased field)
Cumulative Work (task-timephased field)
CV (task-timephased field)
CV Percent (%) (task-timephased field)
Fixed Cost (task-timephased field)
Overallocation (task-timephased field)
Overtime Work (task-timephased field)
Percent (%) Complete (task-timephased field)
Regular Work (task-timephased field)
SPI (task-timephased field)
SV (task-timephased field)
SV Percent (%) (task-timephased field)
Work (task-timephased field)

Now, perhaps I am misinterpreting the way you have stated your condition.
If the enterprise codes are simply to tag which task falls into each
category and the categorization is exclusive then you would use the
TimeScaleData method to obtain the work values for the specified days.
You could then write the values out to excel.
This would not be too difficult and you could easily have it working very
simply within a few hours. Certainly you could spend much more time than
that adding functionality and error handling.

Take a look at some of the macros on my site (particularly the export
heirarchy to excel macro) for an example of what you would end up with.


--
Please try to keep replies in this group. I do check e-mail, but only
infrequently. For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP
email: J -at- eM Vee Pee S dot COM


+++++++++++++++++++
 
Top