Grouping

F

FlyBoy

Posted this in the Access NG, but thought maybe someone
here has run into something similar:

In Access 2002, I'm writing a report based on a query
that hits
two linked MS Project Server 2003 SQL tables,
dbo_MSP_PROJECTS
and dbo_MSP_Tasks. They are joined at the PROJ_ID field.

The report I'm writing has to semi-emulate the outlining
found
in an MS Project Plan. My report needs to have The
following format:

PROJECT NAME
SUMMARY TASK
PROJECT TASK
PROJECT TASK
PROJECT TASK

I use the following SQL statement to create the query:
(line breaks used
to fit in this NG window)
SELECT dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
GROUP BY dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
HAVING (((dbo_MSP_PROJECTS.PROJ_ID)=429));

How can I set up my report to Group by Project (that I
got), then group by
summary task and insert the task name, then by task?

The field in dbo_MSP_TASKS for summary tasks is
TASK_IS_SUMMARY, and
contains either a -1 if the task is a summary or 0 if it
is not.

Thanks.
 
J

John Cello

Since you really can't break out summary from regular
tasks the way you want, an expression that adds twenty
spaces before a regular task name will give you the
indenting you are after. Bear in mind that this will have
all summary tasks aligned, regardless of how they are
indented in the project plan. Here's the expression: IIf
([dbo_MSP_TASKS]![TASK_IS_SUMMARY]
=0," " & [dbo_MSP_TASKS]!
[TASK_ID] & " " & [dbo_MSP_TASKS]![TASK_NAME],
[dbo_MSP_TASKS]![TASK_ID] & " " & [dbo_MSP_TASKS]!
[TASK_NAME]

The full SQL statement is included below. It was done in
Access 2002.

Hope this gets you what you need.


SELECT dbo_MSP_PROJECTS.PROJ_NAME, dbo_MSP_TASKS.TASK_ID,
IIf(dbo_MSP_TASKS!
TASK_IS_SUMMARY=0," " &
dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME,dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME) AS Task, IIf(dbo_MSP_TASKS!TASK_PCT_COMP=100
Or (dbo_MSP_TASKS!TASK_START_DATE>Now()+1) Or
dbo_MSP_TASKS!TASK_WORK=0,"Green",IIf(dbo_MSP_TASKS!
TASK_FINISH_DATE<Now() And dbo_MSP_TASKS!
TASK_PCT_COMP<100,"Red",IIf(dbo_MSP_TASKS!TASK_PCT_COMP<
(((1-(dbo_MSP_TASKS!TASK_FINISH_DATE-Now())/
(dbo_MSP_TASKS!TASK_FINISH_DATE-dbo_MSP_TASKS!
TASK_START_DATE))*100)/1.3) And dbo_MSP_TASKS!
TASK_PCT_COMP<100,"Yellow","Green"))) AS StopLight,
dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE
FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
WHERE (((dbo_MSP_TASKS.TASK_ID)<>0) AND
((dbo_MSP_PROJECTS.PROJ_ID)=110) AND ((IIf
([dbo_MSP_TASKS]![TASK_IS_SUMMARY]
=0," " & [dbo_MSP_TASKS]!
[TASK_ID] & " " & [dbo_MSP_TASKS]![TASK_NAME],
[dbo_MSP_TASKS]![TASK_ID] & " " & [dbo_MSP_TASKS]!
 
F

FlyBoy

Thanks John. That's how I wanted the report to look.
-----Original Message-----
Since you really can't break out summary from regular
tasks the way you want, an expression that adds twenty
spaces before a regular task name will give you the
indenting you are after. Bear in mind that this will have
all summary tasks aligned, regardless of how they are
indented in the project plan. Here's the expression: IIf
([dbo_MSP_TASKS]![TASK_IS_SUMMARY]
=0," " & [dbo_MSP_TASKS]!
[TASK_ID] & " " & [dbo_MSP_TASKS]![TASK_NAME],
[dbo_MSP_TASKS]![TASK_ID] & " " & [dbo_MSP_TASKS]!
[TASK_NAME]

The full SQL statement is included below. It was done in
Access 2002.

Hope this gets you what you need.


SELECT dbo_MSP_PROJECTS.PROJ_NAME, dbo_MSP_TASKS.TASK_ID,
IIf(dbo_MSP_TASKS!
TASK_IS_SUMMARY=0," " &
dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME,dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME) AS Task, IIf(dbo_MSP_TASKS!TASK_PCT_COMP=100
Or (dbo_MSP_TASKS!TASK_START_DATE>Now()+1) Or
dbo_MSP_TASKS!TASK_WORK=0,"Green",IIf(dbo_MSP_TASKS!
TASK_FINISH_DATE<Now() And dbo_MSP_TASKS!
TASK_PCT_COMP<100,"Red",IIf(dbo_MSP_TASKS!TASK_PCT_COMP<
(((1-(dbo_MSP_TASKS!TASK_FINISH_DATE-Now())/
(dbo_MSP_TASKS!TASK_FINISH_DATE-dbo_MSP_TASKS!
TASK_START_DATE))*100)/1.3) And dbo_MSP_TASKS!
TASK_PCT_COMP<100,"Yellow","Green"))) AS StopLight,
dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE
FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
WHERE (((dbo_MSP_TASKS.TASK_ID)<>0) AND
((dbo_MSP_PROJECTS.PROJ_ID)=110) AND ((IIf
([dbo_MSP_TASKS]![TASK_IS_SUMMARY]
=0," " & [dbo_MSP_TASKS]!
[TASK_ID] & " " & [dbo_MSP_TASKS]![TASK_NAME],
[dbo_MSP_TASKS]![TASK_ID] & " " & [dbo_MSP_TASKS]!
-----Original Message-----
Posted this in the Access NG, but thought maybe someone
here has run into something similar:

In Access 2002, I'm writing a report based on a query
that hits
two linked MS Project Server 2003 SQL tables,
dbo_MSP_PROJECTS
and dbo_MSP_Tasks. They are joined at the PROJ_ID field.

The report I'm writing has to semi-emulate the outlining
found
in an MS Project Plan. My report needs to have The
following format:

PROJECT NAME
SUMMARY TASK
PROJECT TASK
PROJECT TASK
PROJECT TASK

I use the following SQL statement to create the query:
(line breaks used
to fit in this NG window)
SELECT dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
GROUP BY dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
HAVING (((dbo_MSP_PROJECTS.PROJ_ID)=429));

How can I set up my report to Group by Project (that I
got), then group by
summary task and insert the task name, then by task?

The field in dbo_MSP_TASKS for summary tasks is
TASK_IS_SUMMARY, and
contains either a -1 if the task is a summary or 0 if it
is not.

Thanks.
.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping 6
Reporting directly from SQL/Access for Project Server 0
Cumulative Earned Value 1
OK, so what am I doing wrong 1
Help with an Access Report 1
Baseline Start Dates are wrong when using the Grouping function 5
Summary Tasks and Grouping in Project View 2
Three Enterprise Global Templates and Outline_COde Table 2

Top