Report that shows the number of resources

[Gaby]

Hello

I need a report that shows number of resources for projects, I mean that
I need to show the number of resources that appear in the Resource Sheet for
a Enterprise Project. From Data Analysis views I just get a measure Called
cOUNT Resource from the Resource Non Timephased Cube, but in this cube I
can´t get the project list dimension, to show the number of resources for a
list of projects.
This requirement needs development or customize the OLAP cube? Any Ideas for
obtain this goal?
Thanks in advance

 

[Paul Conroy]

If you are familiar with VBA you could create a macro to populate an Ent.
Custom Field in the "before save" event. There is a resourcecount method
under the project subclass which will return the number of resources on the
project. You could then add this field to the cube.

ProjectServerExperts produce a great book on writing VBA with MS Project if
you need further guidance.

 

[Gaby]

Thanks Paul.
I`ll try with VBA for obtain this data, but i have another question.
In fact my first post it was the first part about all my question.
The scenario is the next one:
I need Obtain a report that shows the number of resources in enterprise
projects Group by the area or department that require the project.

For this goal I create a custom field for projects linked to a lookup table
that shows the different areas that could require a project. But the trouble
is that the number of resources in the report must be the number of total
resources in the pool resources.

I KNOW THAT this report could be inconsistent because one resource could be
assigned to more than one project but my client need that the assigment only
counts just for the project that has the latest finish date. I think that
this reports requires development, but you can any idea how to get that?

Thanks in advance

 

[John Sitka]

here is half the query that will do it,
you would join this to the other (another) set which
contains your definition of the latest finish date.


SELECT
C.PROJ_NAME,
COUNT(B.RES_NAME) as cnt
FROM ProjectServer.dbo.MSP_PROJECTS C
LEFT JOIN ProjectServer.dbo.MSP_RESOURCES B
ON C.PROJ_ID = B.PROJ_ID
WHERE C.PROJ_ID NOT IN (the set of all project ID's that you have no interest in)
AND
B.RES_UID >=1
GROUP BY C.PROJ_NAME
ORDER BY C.PROJ_NAME

I couldn't finish it becasue I don't understand what this part is...

-->I KNOW THAT this report could be inconsistent because one resource could be
-->assigned to more than one project but my client need that the assigment only
-->counts just for the project that has the latest finish date. I think that
-->this reports requires development, but you can any idea how to get that?

I don't think you wrote it wrong just in my world every project contains every resource,
how else would you get anything done? ;-) So I don't know how to solve it.

 

[James Fraser]

here is half the query that will do it,
you would join this to the other (another) set which
contains your definition of the latest finish date.

SELECT
C.PROJ_NAME,
COUNT(B.RES_NAME) as cnt
FROM ProjectServer.dbo.MSP_PROJECTS C
LEFT JOIN ProjectServer.dbo.MSP_RESOURCES B
ON C.PROJ_ID = B.PROJ_ID
WHERE C.PROJ_ID NOT IN (the set of all project ID's that you have no interest in)
AND
B.RES_UID >=1
GROUP BY C.PROJ_NAME
ORDER BY C.PROJ_NAME

I couldn't finish it becasue I don't understand what this part is...

-->I KNOW THAT this report could be inconsistent because one resource could be
-->assigned to more than one project but my client need that the assigment only
-->counts just for the project that has the latest finish date. I think that
-->this reports requires development, but you can any idea how to get that?

I don't think you wrote it wrong just in my world every project contains every resource,
how else would you get anything done? ;-) So I don't know how to solve it.

BTW, the query provided is for 2003, and won't work with 2007. 2007
has a MSP_PROJECT_RESOURCES table mentioned a couple days ago. Not
sure what version the OP is using...


James Fraser

 

[Paul Conroy]

Great approaches from John and James.

I had a few minutes to spare over lunch and thought I try the VB Macro method.

I created a new Ent. Custom Field called ResCount (Project/Number)

Added the following macro to the Ent.Global "Before Save" event

Call
ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("ResCount"), ThisProject.ResourceCount)

Everytime I save the project the field is updated with the number of
resources.

However, it doesn't address you're requirement for greater granulatity.

 

[Gaby]

Thanks Paul, with this vb macro I can get the number of resources for a
project? Am I right?
But do you have any idea how can I get a report that show the number of
resources group by the area that require the enterprise projects??? If I add
this custom field called RESCOUNT to the cube i can get the report?
Aditionally I need that this report not counts resources if the resources is
assigned to more than one project. My client says that they have been work
with a development in-house tool that shows the number of resources that
works in different areas that requires projects. For example, the report
shows 10 resources who works for projects in Financial area and 100
resources who works for projects in Accounting area for a period of time
(weekly). The total number of resources is 110, and this is the exactly
number of resources that this organization has . The report could show less
than 110 resources (that means some resources are not assigned to any
project) or 110 resources, but not more than 100 because this report is for
the company`s president and he wants to see the exactly number of resources,
don´t care if there are resources working it in more than one project.
If the company´s president see a report that summarize 111 resources or more
he won´t understand because he says that in his company there are 110
resources.

Could you give me any ideas about this requirement?, or some alternative for
show that. Please, help me.

Thanks in advance,


"

 

[Paul Conroy]

Yes this macro will return the number of total resources on the project.

If I was tasked with creating the detailed report, I would do this with SQL
Reporting Services.

A report could be created which allows the user to enter a date range then
filter out duplicate resources. They could be grouped by RBS or other custom
field to show the area in which they work.

This is reasonably straight forward for anyone that has an understand of SQL
and Reporting Services.

HTH

Paul