Need some help with reports

[Pablo Cedeño]

Hi everybody!!

I think this is a common question, but i'm newbie working with MS Project and i dont know how to create completely new reports using a Macro.

If anybody knows something about it, i really appreciate any help.

If there's more than one solution for this - i think so -, i hope you people tell me which ones are easier to implement.

It's just a report that shows informations like this:

Column Name Value
***** ***
***** ***

Note: I need to calculate some of this values.

Regards!!

 

[John]

Pablo,
First, welcome to the newsgroup. Although the information you show isn't
very specific, it is doubtful that you need any kind of macro to get
what you want. Most likely, you can get what you want by simply
displaying an already existing field as a column in a Project view or if
the data you want needs to be calculated, you can do so with a custom
field.

In order to better help you it would help to have a little more
information. What are you trying to do? Try to describe it in terms of
what you have and what you want as an end product. An annotated example
of your expected output would be helpful. We can help you figure out the
best way to get there.

John

 

[Pablo Cedeño]

First, thanks for your help John.

I would try to describe you what i'm trying to do.

I need to create a report and access it at every time that i want, that's
why y thought in a macro, remember i'm newbie and I don't know too much
about MS Project.

I have a table with some information about projects, for example, days left
to complete some project, % of completion, etc, and some other values that
should be calculated according to many variables inside each project. This
table contains all data that i want to display, that's why i created it.

Now, with this information i want to create a report, for each colum i just
have a value and i want to show these values for each column. That's why i
wrote something like this:

Column Name Value
***** ***
***** ***

That's because i want a report with this structure, a list of all column's
names and its respective value.

Another questions, how can a calculate those values just before I show the
report?. Can I assign a formula to those fields and each time that some
value is modified, the result is updated??. These question are because I
have to update those values each time i show the report.

Ok, hope this can help you to give me a light in this issue. And if you need
some extra information, just ask!, thanks again.

 

[Steve House]

What does the source table look like? Where are you obtaining the valus in
it? Is it structured like the standard table in Project?

 

[John]

Pablo,
OK, as a newbie to Project I know it can be very confusing and
frustrating. Similar to Steve's question, you say you have a table with
some information but what form is the table? Is it in a Project file? Is
it an Excel worksheet? Is it a table in a word document? If I had to
guess, I'd say you have an Excel worksheet with all your information and
you perhaps would like to put at least some of the schedule type
information into Project. However I'm only guessing.

Unfortunately the information you have provided is still much to general
to allow us to understand exactly what you want and consequently be able
to help. At this point I think the best approach is for you to provide
the "table of information" so I can look at it. However since this is a
newsgroup and posting attachments is frowned upon, I suggest you send me
an e-mail with the attachment. Once I get a better understanding of what
you have, we can post the messages back to the newsgroup for the benefit
of others if appropriate.

John

 

[Pablo Cedeño]

Hi Steve,
Thanks for your interest.

Ok, I just created a table with some calculated values and others predefined
values.

The reason for this is that i need a summary for a project, but more
specifically report than any other given by MS Project. For example, i have
a column value "Work", this represents the days that i have to complete the
project, no problem with that, another like "Finish Variance", to know the
delay in the project, but an example of a calculated value would be
something like this:

% of Effort = (% estimated effort - % actual effort) / % estimated
effort - (Just a formula that i should follow)
*And those values should be in the table too, estimated effort and actual
effort.

**Note: No all values in the table can be acquired with predefined values,
in some cases, i should use VBA-??-, to calculate values because i need to
pass through each task getting information and in the end, calculate the
value for the column.

The table looks like:

Work | Finish Variance | ... | Estimated Effort | Actual Effort | % of
Effort | ...
90 days | 10 days | ... | 95% | 80%
| 0.15% | ...

Just one row, and all those values should be display in the report(The
values in the table above are just for reference).

I would try to explain better...
First, I need a custom table, some values predefined by MS Project, others
calulated each time that its needed, then, when i get those values, i should
show them in a report for each project.

OK, i really appreciate your help, maybe this looks extrange, but try to
think in the simpliest way as possible, just one table, with calculated
values, and then a report with those values. If you know a site where i can
obtain some information about creating custom reports, not modifying
existing ones, i really appreciate it.

And accept my apologies if you still don't understand what i'm trying to do.
If you want to keep assisting me, feel free to ask me anything that you
need.

Regards.

 

[Pablo Cedeño]

John, now i get it what are you talking about.

No, the table isn't anything like Excel worksheet, or anything like that
I'm tryin to create a table from MS Project, with some columns like "Work"
"Baseline Finish", etc, all values from predefined Project values, It's just
like a Entry Table or Cost Table, but my table has custom columns, some of
them i should calculate to get the report, other, just take it from the ms
project.

Now, some values i can calculate them using custom fields, others, i need
another way to calculate values because any predefined funtion, like "Work"
into the project, returns the information that i need, this information can
be acquired only passing through each task. Once i have all the information,
i need to print a report with that information.

I'm not working with anything that isn't from Project, It's just a table,
inside the ms project document, like any other that Project provide, but i
need one way to calculate values, i don't know if a using VBA is one way or
what should i do?

And about the attachement, i can not sent it to you because its not finish
yet, i need to know how calculate values, and how can i add custom fields.
At this time, my table only has "Name", "Duration", my third value would be
the { Date( ) - [Baseline Finish] } to calculate the days that the project
has been late. And something that is making me this harder, its the many
fields that MS Project provide, in some cases, i don't know the right
functionality for each.

Hope this help you, if you have a comment, please, write me again and i'll
write you as soon as possible.

 

[John]

Pablo,
OK, lets get down to some basics. Something you said in your last
response to Steve bothers me. You say you have a "Work" column that
represents the days to complete the project. Normally the Work field
contains Hours and it represents the amount of effort one or more
resources is assigned to complete a task. The Duration field on the
other hand is used to define the time period (often in days) to complete
the task. Is there some confusion on the difference between these two
fields?

Your post says that you need a summary for a project and more
specifically a report for that summary. May I assume that you have one
or more Project files with a full set of tasks with task names,
durations, task linkages, and resource assignments? May I further assume
that you have set a baseline and are tracking the status of the
schedule? If these assumptions are NOT true, then we are going to have a
hard time providing help. On the other hand, if these assumption ARE
true, then depending on how the file is statused, it is likely most of
the "custom" information you seek is probably already available in a
pre-defined Project field. For example, the field "Finish Variance" is
the difference (in work days) between the Baseline Finish and the Actual
Finish. In a similar manner, there are also other pre-calculated
variance fields (e.g. Duration Variance, Work Variance, etc.).

If for some reason the information you need does not already exist most
likely it can be calculated using a simple formula in a custom field.
However, if that is not adequate then yes, VBA can get you anything you
need to produce any report you need in whatever form.

John

 

[Francis Dion]

Pablo,

We have implemented a couple of solutions not unlike what you are
trying to do using the XpertDoc report engine.

The data you need to calculate by going through all the tasks could be
aggregated either from the report itself or from VBA.

If you decide you like this tool and would need help to implement your
solution, send an email to their website support staff. I think you'll
find that they are very helpful. (I should know ;-)

You can check it out at www.xpertdoc.com

Good luck!

John, now i get it what are you talking about.

No, the table isn't anything like Excel worksheet, or anything like that
I'm tryin to create a table from MS Project, with some columns like "Work"
"Baseline Finish", etc, all values from predefined Project values, It's just
like a Entry Table or Cost Table, but my table has custom columns, some of
them i should calculate to get the report, other, just take it from the ms
project.

Now, some values i can calculate them using custom fields, others, i need
another way to calculate values because any predefined funtion, like "Work"
into the project, returns the information that i need, this information can
be acquired only passing through each task. Once i have all the information,
i need to print a report with that information.

I'm not working with anything that isn't from Project, It's just a table,
inside the ms project document, like any other that Project provide, but i
need one way to calculate values, i don't know if a using VBA is one way or
what should i do?

And about the attachement, i can not sent it to you because its not finish
yet, i need to know how calculate values, and how can i add custom fields.
At this time, my table only has "Name", "Duration", my third value would be
the { Date( ) - [Baseline Finish] } to calculate the days that the project
has been late. And something that is making me this harder, its the many
fields that MS Project provide, in some cases, i don't know the right
functionality for each.

Hope this help you, if you have a comment, please, write me again and i'll
write you as soon as possible.

Pablo,
OK, as a newbie to Project I know it can be very confusing and
frustrating. Similar to Steve's question, you say you have a table with
some information but what form is the table? Is it in a Project file? Is
it an Excel worksheet? Is it a table in a word document? If I had to
guess, I'd say you have an Excel worksheet with all your information and
you perhaps would like to put at least some of the schedule type
information into Project. However I'm only guessing.

Unfortunately the information you have provided is still much to general
to allow us to understand exactly what you want and consequently be able
to help. At this point I think the best approach is for you to provide
the "table of information" so I can look at it. However since this is a
newsgroup and posting attachments is frowned upon, I suggest you send me
an e-mail with the attachment. Once I get a better understanding of what
you have, we can post the messages back to the newsgroup for the benefit
of others if appropriate.

John

 

[Pablo Cedeño]

John, thanks again,

Ok, i know the function of "Work" field and others, i just want to specify
some of the columns that my table has, some of them "Work", "Baseline
Finish" "Finish Variance", etc. Sorry if i wrote something bad, or you
didn't understand me.

Besides those fields, i need to calculate others and those can not be
calculated using formulas for a custom field, thats why i need to know how
to access those fields from VBA, if you say that i can modified fields from
VBA.

On the other hand, the assumptions you made were true, i have many tasks,
information of every project, etc, and i need to generate a report each time
that i want to see advance, delays, needed work, etc.

Now i can say that i have two questions, maybe more simpliest that before, i
have been investigating about this problem, and now i know the basics.
I now how to create my table, but i need one way to calculate the columns
from VBA, if you can give me an example of that, i appreciate that. One
Second: When everything in my table has been calculated, how can i show this
information in a report?, maybe with a macro?, modifying an existing report?
, how?

I think everything is more clear to you now, -i hope-. Sorry if i made a
mistake, but i'm trying to write the best and clear that i can.

Regards, and sorry again if something bothers you.

 

[Pablo Cedeño]

Thanks Francis,

I'll check that tool, and i know that its gonna be very helpful for me.
Because, in the end, i think that the solution to my problem could be
simple.
Maybe, My inexperience using MS Project confuse this problem more than
needed.

Regards.

 

[John]

Pablo,
Without knowing exactly what you are trying to calculate it is a little
hard to agree whether or not you can do it with a formula in a custom
field. However, let me answer your questions. First a VBA example. Let's
say you want to create a field that contains
1-(BaselineWork-RemainingWork)/BaselineWork. Note: this could easily be
done with a simple formula but it will serve to illustrate the VBA
process. The VBA code is as follows:

Sub Test()
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.BaselineWork > 0 Then
t.Text1 = Format(1 - (t.BaselineWork - t.RemainingWork) /
t.BaselineWork, "#.00")
Else
t.Text1 = "-"
End If
End If
Next t
End Sub

It should be noted that if the formula did not set up the result as a
ratio and the work values were used directly, they would be in minutes
and an appropriate factor would have to be used to convert to hours.

With regard to your second question, the above macro will automatically
populate the Text1 field. All that is necessary for a report is to set
up a view with a table showing Text1 or use the custom report wizard. If
you want a fully customized report, the VBA code can be set up to export
the Project data to Excel (for example) where a tabular report can be
created. This is a little more involved, but not all that difficult.

John

 

[Pablo Cedeño]

Thanks a lot, John.

Sorry if i didn't specify correctly the things, but this help is just what
i'm looking for.

I'll try to create a report as you say.

Regards.

 

[Pablo Cedeño]

John, i have another question that came from my tests.

In your example, i can modify values of one field for each task, for
example, if i want to modified "Work" field i just iterate on each task and
modified the value. That's ok.

But, i have one column that the value should be assigned just to the
project. not needed for each task.
This is the case, i need to obtain the total delay of the project, but i
just have to take the bigger delay for all tasks, and that value should be
assigned to the total delay of the project.
I calculate this total delay >>> Now() - [Baseline Finish], that tells me
how many days has been pass since the estimated finish of the project at
now, but if i do that, this calculates those values for each task, but i
need to assign the bigger value to the project., how can i access values
from the project, and how can i modified fields at project level?

I now know that Task."FieldName" give me the value for "FieldName" column,
but, how access the value of the project, or this value is automatically
calculated by MS Project?

And how can i create a table with only totals for a project?, without see
information about tasks, just about project, nothing else is important to
show.

Hope you understand me,
Thanks.

 

[John]

Pablo,
If I understand you correctly, you only want project summary values.
These are easy to obtain from the ProjectSummaryTask properties. For
example, if you want to get the Baseline Finish for the whole project,
the syntax is:
ActiveProject.ProjectSummaryTask.BaselineFinish

Generally, any field that is available at an individual task level is
also available at the project summary level.

A "table" with only totals for a project can be very simple. Just
collapse the entire project to just the top summary line and then
display only the fields you need. If you need to show just the summary
for several projects at once, create a master file by using
consolidation (i.e. Project/Insert Projects) and collapse each
subproject to its top summary line.

If you don't like the format of a view for your report, you can create a
custom report to show only the information you want. Use your custom
table, perhaps a filter for just the top summary line and so forth.

John

 

[Pablo Cedeño]

Thanks John,

That's what i needed!
Ok, i wont bother you for a while, thanks again

Cheers