Customised reporting

[Marko]

Hi, I'm having immense problems trying to obtain what I thought would be a
simple report! The custom report "Task usage" displays the resource name,
tasks and then the actual hours displayed by month, with totals. This is fine
but I'd like to insert some of my custom columns, such as "Project code",
"Region" etc etc. I've tried creating and editing tables but I can't get the
desired result. Any help would be appreciated! Thanks.

 

[John]

Hi, I'm having immense problems trying to obtain what I thought would be a
simple report! The custom report "Task usage" displays the resource name,
tasks and then the actual hours displayed by month, with totals. This is fine
but I'd like to insert some of my custom columns, such as "Project code",
"Region" etc etc. I've tried creating and editing tables but I can't get the
desired result. Any help would be appreciated! Thanks.

Marko,
First of all, if you are trying to get timescaled data on custom fields,
that can only be done with VBA and a special algorithm (to generate the
timescaled data). On the other hand if you are simply trying to show
static values of your custom fields then creating a custom view table is
the answer. However, your post indicates that you tried that but weren't
getting the desired result. What exactly is your "desired result"?

Be aware that although Reports can be customized, the level of
customization is limited. Sometimes it is easier to create a custom view
(i.e. Gantt, Task Usage, Resource Usage, etc.) and save or print that to
get the format you need. If none of those options work, then the only
other option is by using an advanced feature of Project, namely VBA.
With VBA, ANY customized report can be created and formatted.

Hope this helps.
John
Project MVP

 

[Marko]

Thanks for your reply John.

The actual "Task Usage" view would suffice because this can show the "Actual
hours" in a monthly chart format. However, as far as the text based columns
are concerned (those displayed in Gantt view), it only includes the tasks,
(and the actual resource name as a heading of course.) All I wish to do is
to have the ability to include a few more columns (mainly custom columns I
have set up), so as to make the report a lot more useful.

As for views, well I know one can use "Resource Usage" view, which will give
you the data desired, but when I attempt to insert columns in this view, the
drop down list doesn't seem to include any of the custom columns I have set
up! Also, it's not quite the same as a report I can easily print-out.

I thought this would be a reasonably simple task! I know it is possible to
play with code (VBA / SQL?), but it doesn't appear easy, (impossible for me,
at least!) I've heard about add-ons such as E4SE but I'm not sure if they
would help.

Any more thoughts you have (esp. on paragraphs 1 and 2 above) would be
appreciated.

Thanks for your time and hopefully hear from you soon.

Regards,
Mark

 

[John]

Thanks for your reply John.

The actual "Task Usage" view would suffice because this can show the "Actual
hours" in a monthly chart format. However, as far as the text based columns
are concerned (those displayed in Gantt view), it only includes the tasks,
(and the actual resource name as a heading of course.) All I wish to do is
to have the ability to include a few more columns (mainly custom columns I
have set up), so as to make the report a lot more useful.

As for views, well I know one can use "Resource Usage" view, which will give
you the data desired, but when I attempt to insert columns in this view, the
drop down list doesn't seem to include any of the custom columns I have set
up! Also, it's not quite the same as a report I can easily print-out.

I thought this would be a reasonably simple task! I know it is possible to
play with code (VBA / SQL?), but it doesn't appear easy, (impossible for me,
at least!) I've heard about add-ons such as E4SE but I'm not sure if they
would help.

Any more thoughts you have (esp. on paragraphs 1 and 2 above) would be
appreciated.

Thanks for your time and hopefully hear from you soon.

Regards,
Mark

Mark,
You may not be aware but there are actually 3 sections to the overall
Project database structure - Tasks, Resources, and Assignments. Each of
these structures has its own separate spare fields. In other words, Task
Text1 is not the same as Resource Text1 or Assignment Text1. What that
means is that if you are in a Resource based view (e.g. Resource Usage),
the Text1 field is the Resource Text1 field and if the assignments for
each resource are also shown, the Text1 field next to each assignment is
the Assignment Text1 field. So if you are attempting to show the Task
Text1 field (such as seen on the Gantt Chart view), you will not see it
in the Resource Usage view. Hopefully that clarifies things.

All is not lost however. Go to the MVP website at:
http://www.mvps.org/project/faqs.htm
and take a look at FAQ 37 - Custom Fields in Tables
Although the solution presented uses a very simple VBA routine, it can
also be accomplished by copy/paste of one type of spare field to another
(i.e. Task Text1 to Assignment Text1 or Resource Text1). It's just that
VBA makes the process more efficient.

As far as printing a view versus a Report, I would much rather print a
view simply because views are much easier to customize and have more
flexibility. However, if I really need something custom, I go straight
to VBA. Obviously VBA isn't for everyone but if you are willing to
learn, it will open up a whole new scope of possibilities. If you are
just not in to macros there is always the option of hiring someone to
write the code for you.

Hope this helps.
John
Project MVP

 

[Marko]

Hi John,

Thanks for taking the time to reply. Much appreciated!

That's made things a lot clearer. I think I know what my options are now. We
also have a consultant coming in to look at a few problems soon, so I suspect
the VBA option may be suggested.

Regards,

Mark

 

[Marko]

Hi John,
I got someone to knock up the macro below and it has succeeded in enabling
me to add the custom columns in resource usage view. However, when I run it,
it only seems to populate the info on the very first line (against the
resource.) All the tasks are listed below but the rest of the fields
associated with them are empty. The tasks are also strangely listed under the
resource name column. Nearly there, so a bit frustrating! Any suggestions
appreciated.

....
Dim t As Task
Dim r As Resource

On Error Resume Next
For Each t In ActiveProject.Tasks
For Each r In t.Resources
r.Text1 = t.Text1
r.Text2 = t.Text2
r.Text3 = t.Text3
r.Text4 = t.Text4
r.Text5 = t.Text5
r.Text6 = t.Text6
r.Text7 = t.Text7
Next r
Next t
--------------

 

[John]

Hi John,
I got someone to knock up the macro below and it has succeeded in enabling
me to add the custom columns in resource usage view. However, when I run it,
it only seems to populate the info on the very first line (against the
resource.) All the tasks are listed below but the rest of the fields
associated with them are empty. The tasks are also strangely listed under the
resource name column. Nearly there, so a bit frustrating! Any suggestions
appreciated.

...
Dim t As Task
Dim r As Resource

On Error Resume Next
For Each t In ActiveProject.Tasks
For Each r In t.Resources
r.Text1 = t.Text1
r.Text2 = t.Text2
r.Text3 = t.Text3
r.Text4 = t.Text4
r.Text5 = t.Text5
r.Text6 = t.Text6
r.Text7 = t.Text7
Next r
Next t
--------------

Marko,
"Knock up"? That's an interesting way to describe someone creating VBA
code. "Knock out" perhaps, but "knock up"?

I thought I clarified the difference between Tasks, Resources and
Assignments but I guess I didn't go far enough. What you see on the
Resource Usage view are Resources and Assignments, not Resources and
Tasks. Although it is a fine point it is very pertinent to what you want
to do. The code has successfully copied Task text field information to
equivalent Resource text fields and that is all you see. In addition the
Task text information needs to be copied to the Assignment text fields.
This point is perhaps made a little more confusing by the fact that the
column heading simply says "Textx". What it really means is that on
Resource rows, it is Resource Textx and on Assignment rows, it is
Assignment Textx.

The Usage views are hybrid views consisting of either Tasks and
[resource] Assignments or Resources and [task] Assignments. However,
Project's views only allow a single row for column definition. In the
Resource Usage view, for example, the primary field is Resources so the
column heading is Resource Name. The fact the Project also allows the
user to see Assignments in this view is bonus information. I guess the
programmers could have added a separate column identifier but then I
guess they felt it was obvious so why bother. (I tend to disagree on the
"obvious" assumption since many users have a difficult time
understanding the difference between Tasks, Resources, and Assignments).

Hope this helps.
John
Project MVP

 

[Marko]

Hi John,

Thanks very much for your help. A couple of us "lashed" together the code
(I'm told that's one of the words you guys use!) and I now have pretty much
exactly what I required!

As they say "It's easy when you know how"!

Best regards,

Mark

Hi John,
I got someone to knock up the macro below and it has succeeded in enabling
me to add the custom columns in resource usage view. However, when I run it,
it only seems to populate the info on the very first line (against the
resource.) All the tasks are listed below but the rest of the fields
associated with them are empty. The tasks are also strangely listed under the
resource name column. Nearly there, so a bit frustrating! Any suggestions
appreciated.

...
Dim t As Task
Dim r As Resource

On Error Resume Next
For Each t In ActiveProject.Tasks
For Each r In t.Resources
r.Text1 = t.Text1
r.Text2 = t.Text2
r.Text3 = t.Text3
r.Text4 = t.Text4
r.Text5 = t.Text5
r.Text6 = t.Text6
r.Text7 = t.Text7
Next r
Next t
--------------

Marko,
"Knock up"? That's an interesting way to describe someone creating VBA
code. "Knock out" perhaps, but "knock up"?

I thought I clarified the difference between Tasks, Resources and
Assignments but I guess I didn't go far enough. What you see on the
Resource Usage view are Resources and Assignments, not Resources and
Tasks. Although it is a fine point it is very pertinent to what you want
to do. The code has successfully copied Task text field information to
equivalent Resource text fields and that is all you see. In addition the
Task text information needs to be copied to the Assignment text fields.
This point is perhaps made a little more confusing by the fact that the
column heading simply says "Textx". What it really means is that on
Resource rows, it is Resource Textx and on Assignment rows, it is
Assignment Textx.

The Usage views are hybrid views consisting of either Tasks and
[resource] Assignments or Resources and [task] Assignments. However,
Project's views only allow a single row for column definition. In the
Resource Usage view, for example, the primary field is Resources so the
column heading is Resource Name. The fact the Project also allows the
user to see Assignments in this view is bonus information. I guess the
programmers could have added a separate column identifier but then I
guess they felt it was obvious so why bother. (I tend to disagree on the
"obvious" assumption since many users have a difficult time
understanding the difference between Tasks, Resources, and Assignments).

Hope this helps.
John
Project MVP

 

[John]

Hi John,

Thanks very much for your help. A couple of us "lashed" together the code
(I'm told that's one of the words you guys use!) and I now have pretty much
exactly what I required!

As they say "It's easy when you know how"!

Best regards,

Mark

Mark,
Yeah, lashed works ok, still not the term I would use but then to each
his own.

You're welcome, I'm glad you got what you need. I would modify your last
statement with one qualifier, "usually it's easy when you know how". I
know how, but it ain't always easy.

John