Generating (Tricky) View

[daniel schmied]

Hello,

do you have some suggestions to this.....

I have tasks, which have two parent Tasks like this:

+SummaryTask A
|+Another Summary Task AA
|-NonSummaryTask 1
|-NonSummaryTask 2
|-NonSummaryTask 3
|-...
|+Another Summary Task AB
|-NonSummaryTask 1
|-NonSummaryTask 2
+SummaryTask B
|-Another Summary Task BB
|-NonSummaryTask1
|-....

Do you know (a simple) way to create a view based on MSP_Tasks which
shows up all "NonSummaryTasks" including information about the
parent-Task (for example Another Summary TaskAA) and with the
parent-parent-Task (for example SummaryTask A).

I started playing around with some join on the outline-code, but that
wasnt good idea. So i checked wbs-code but stuck.

Do you know how to get this done by using SQL?

Thank you very much,
Daniel

 

[Jan De Messemaeker]

Hi Daniel,

If you want to do this in Project VBA (that is, on an open project using
Project's objects, not the data base thing) I can help.

 

[JackD]

I can't really figure out what you are trying to do...
But it appears that the Outline Number is likely to be the key. You will
probably have to use some sort of string manipulation in order to make the
correct comparison. For example Task A is Outline Number 1, AA is 1.1, AB is
1.2 AANSTask1 is 1.1.1, AANSTask2 is 1.1.2 ... By taking the correct portion
of the string you can determine which branch of the tree the task is in.

 

[Ed Morrison]

First create a function to get the parent task:
CREATE FUNCTION [dbo].[Get_Parent] (
@PROJ_ID as integer,
@TASK_ID as integer,
@Outline_Level as integer)

RETURNS varchar(255) AS
BEGIN
declare @Task_Name as varchar(255)
Select @Task_Name=task_name
from msp_tasks
where proj_id=@proj_id
and task_id<@task_id
and task_Outline_Level=@Outline_Level-1
return @Task_Name
END

Then create the select statement:

Select task_name, dbo.Get_Parent(proj_id, task_id, task_outline_level)
from msp_tasks
where task_is_summary=0

This will return the tasks parent. You can create another function (or
modify the existing function) with task_Outline_Level=@Outline_Level-2 to
get the grandparent task. Please let me know how this works for you.

 

[daniel schmied]

Hi,
thanks for that great Input!
The Problem is, that the Function Get_Parent always returns the first
Parent-Task in Database. I think this is because the use of
"task_id<@task_id" in where clause...

Any other suggestions are greatly appreciated!
Thanks,
DS

 

[Ed Morrison]

I tested the function again. It always returns the name of the summary task
that the task falls under. If this is not what you are looking for, please
let us know. If it is and you are still having problems, post the actual
select statement you are using. Thanks.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com

Hi,
thanks for that great Input!
The Problem is, that the Function Get_Parent always returns the first
Parent-Task in Database. I think this is because the use of
"task_id<@task_id" in where clause...

Any other suggestions are greatly appreciated!
Thanks,
DS


"Ed Morrison" <ed(dot)morrison at msProjectExperts(dot)com> wrote in

First create a function to get the parent task:
CREATE FUNCTION [dbo].[Get_Parent] (
@PROJ_ID as integer,
@TASK_ID as integer,
@Outline_Level as integer)

RETURNS varchar(255) AS
BEGIN
declare @Task_Name as varchar(255)
Select @Task_Name=task_name
from msp_tasks
where proj_id=@proj_id
and task_id<@task_id
and task_Outline_Level=@Outline_Level-1
return @Task_Name
END

Then create the select statement:

Select task_name, dbo.Get_Parent(proj_id, task_id, task_outline_level)
from msp_tasks
where task_is_summary=0

This will return the tasks parent. You can create another function (or
modify the existing function) with task_Outline_Level=@Outline_Level-2 to
get the grandparent task. Please let me know how this works for you.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com

 

[daniel schmied]

First of all thanks again for your help and patient!
I tried again and figured out, that i did some mistakes. --Your script
is working! -Great!

What i did was trying to return not only the task_name of the parent
task; in addition to this i have to return the task_id of the parent
task. So i started modifying your script and that got me an error. I
figured out, that your script is some kind of scalar-function which is
only able to return a single value.

Right now i just trying to modify your script for returning the
parent-task name and id. But i cant get this work :-( It drive me
nuts....
The thing is, that i want to access the function in the select-clause
as you mentioned.

Is there any way to get this done? So close to the right solution, but
cant figure it out for myself....

Thanks alot,
DS

 

[Ed Morrison]

I should have wrote it this way the first time.

Select t.task_name, t2.task_id, t2.task_name
from msp_tasks t
inner join msp_tasks t2 on t.proj_id=t2.proj_id
where t2.task_id<t.task_id
and t2.task_outline_level=t.task_outline_level-1
and t.proj_id=5

If you need to add additional "parent" tasks, add additional joins,etc.
--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com

 

[daniel schmied]

That did it! Thank you!