Visual Report - resources group as legend?

[Kalmarroger]

Hi

I am trying to create a visual report (Excel - Pivot Chart) with Tasks in
column and Resource group as legend with Work as value. It does not seem to
be possible as the Resources Group only shows as parameter to Resources. I
cant figure out how to get the Groups as legend value in the chart. I have
found in a different category only Group as data and that works but then
again the other required data (Tak and Work) are not available in the same
category. What to do?? Any suggestion is appreciated.

Thanks
Roger

 

[JulieS]

Hello Roger,

I assume you are using Project 2007 due to your reference to
"visual" reports. In quick experimentation, the closest I could
come to what I think you are trying to accomplish is to save the
reporting data in an Access database through Reports > Visual
Reports, and clicking the Save Data button at the bottom of the
Visual Reports - Create Report dialog box.

Then create a query using the MSP_EpmTask, MSP_EpmAssignment, and
MSP_EpmResource tables (SQL below). Once I had created the query I
used the Pivot Table or Pivot chart view of the query, I was able to
get what I think you want: Task Names to the row, resource group to
the column and sum of work as the data.

SQL for query:
SELECT MSP_EpmTask.TaskID, MSP_EpmTask.Name,
Sum(MSP_EpmAssignment.Work) AS SumOfWork, MSP_EpmResource.Group
FROM MSP_EpmResource INNER JOIN (MSP_EpmTask INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID) ON MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID
WHERE (((MSP_EpmResource.Type)=0) AND ((MSP_EpmTask.[Is
Milestone])=No) AND ((MSP_EpmTask.[Is Summary])=No))
GROUP BY MSP_EpmTask.TaskID, MSP_EpmTask.Name, MSP_EpmResource.Group
ORDER BY MSP_EpmTask.TaskID;

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Kalmarroger]

Thanks Julie!

I tried our approach and it does what I need.

Hello Roger,

I assume you are using Project 2007 due to your reference to
"visual" reports. In quick experimentation, the closest I could
come to what I think you are trying to accomplish is to save the
reporting data in an Access database through Reports > Visual
Reports, and clicking the Save Data button at the bottom of the
Visual Reports - Create Report dialog box.

Then create a query using the MSP_EpmTask, MSP_EpmAssignment, and
MSP_EpmResource tables (SQL below). Once I had created the query I
used the Pivot Table or Pivot chart view of the query, I was able to
get what I think you want: Task Names to the row, resource group to
the column and sum of work as the data.

SQL for query:
SELECT MSP_EpmTask.TaskID, MSP_EpmTask.Name,
Sum(MSP_EpmAssignment.Work) AS SumOfWork, MSP_EpmResource.Group
FROM MSP_EpmResource INNER JOIN (MSP_EpmTask INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID) ON MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID
WHERE (((MSP_EpmResource.Type)=0) AND ((MSP_EpmTask.[Is
Milestone])=No) AND ((MSP_EpmTask.[Is Summary])=No))
GROUP BY MSP_EpmTask.TaskID, MSP_EpmTask.Name, MSP_EpmResource.Group
ORDER BY MSP_EpmTask.TaskID;

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

Hi

I am trying to create a visual report (Excel - Pivot Chart) with
Tasks in
column and Resource group as legend with Work as value. It does
not seem to
be possible as the Resources Group only shows as parameter to
Resources. I
cant figure out how to get the Groups as legend value in the
chart. I have
found in a different category only Group as data and that works
but then
again the other required data (Tak and Work) are not available in
the same
category. What to do?? Any suggestion is appreciated.

Thanks
Roger

 

[JulieS]

Great Roger. Glad to have helped and thanks for the feedback.

Julie

Thanks Julie!

I tried our approach and it does what I need.

Hello Roger,

I assume you are using Project 2007 due to your reference to
"visual" reports. In quick experimentation, the closest I could
come to what I think you are trying to accomplish is to save the
reporting data in an Access database through Reports > Visual
Reports, and clicking the Save Data button at the bottom of the
Visual Reports - Create Report dialog box.

Then create a query using the MSP_EpmTask, MSP_EpmAssignment, and
MSP_EpmResource tables (SQL below). Once I had created the query
I
used the Pivot Table or Pivot chart view of the query, I was able
to
get what I think you want: Task Names to the row, resource group
to
the column and sum of work as the data.

SQL for query:
SELECT MSP_EpmTask.TaskID, MSP_EpmTask.Name,
Sum(MSP_EpmAssignment.Work) AS SumOfWork, MSP_EpmResource.Group
FROM MSP_EpmResource INNER JOIN (MSP_EpmTask INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID) ON MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID
WHERE (((MSP_EpmResource.Type)=0) AND ((MSP_EpmTask.[Is
Milestone])=No) AND ((MSP_EpmTask.[Is Summary])=No))
GROUP BY MSP_EpmTask.TaskID, MSP_EpmTask.Name,
MSP_EpmResource.Group
ORDER BY MSP_EpmTask.TaskID;

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project



message

Hi

I am trying to create a visual report (Excel - Pivot Chart)
with
Tasks in
column and Resource group as legend with Work as value. It does
not seem to
be possible as the Resources Group only shows as parameter to
Resources. I
cant figure out how to get the Groups as legend value in the
chart. I have
found in a different category only Group as data and that works
but then
again the other required data (Tak and Work) are not available
in
the same
category. What to do?? Any suggestion is appreciated.

Thanks
Roger