T-SQL

J

John Sitka

I'm not sure this is the best way to ask for direction but
hopefully someone will see the issue if they have worked on it before.
I'm very new to knowing the data definition of Project so don't throw up.

Basically I'm just trying to distribute a task outline code accross a
reporting result set.
The INSERT....EXEC's that make up the UNIONS draw from an ERP calcualtion

My issue is with derived table "J" which happens
just after the LEFT JOIN and before the UNIONS

Seems a lot of work to distribute an outline code 'name' accross enterprise
tasks. So much so that I'm on the verge of changing them to custom fields
but I was excited by the analysis posibillities but then found out task
outlines aren't
part of the default cubes.
So if anyone has a code snippent that attacks Task outline codes
so I can see the table relationship, much appreciated.





ALTER PROC ab_PWA_reportingattempt4
(
@sonom varchar(6),
@opernosm varchar(2000),
@leftdatem smalldatetime,
@rightdatem smalldatetime
--@project_namem varchar(255) NOT used yet
)
AS
DECLARE @project_name varchar(255)
DECLARE @project_id int
SET @project_name = 'KIT04.Published'
SELECT @project_id = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME =
@project_name)

--Build the RTG contribution

CREATE TABLE #RTGtable
(
RTGso varchar(255),
RTGwc varchar(255),
RTGop varchar(255),
RTGhrs Numeric(16,5)
)

INSERT INTO #RTGtable (RTGso,RTGwc,RTGop,RTGhrs)
EXEC ProjectServer0554.dbo.ab_quotedhours_1
@sono = @sonom,
@opernos = @opernosm

--Build the TC Contribution

CREATE TABLE #TCtable
(
TCso varchar(6),
TCjo varchar(10),
TCwc varchar(6),
TCop int,
TChrs Numeric(16,5)
)

INSERT INTO #TCtable (TCso,TCjo,TCwc,TCop,TChrs)
EXEC ProjectServer0554.dbo.ab_timecardhours_1
@sono = @sonom,
@opernos = @opernosm ,
@leftdate = @leftdatem,
@rightdate = @rightdatem

-- SELECT OUR DISPLAY SET

SELECT I.TASK_NAME as Task,J.oce_name as [Job Type],I.pro_ACTUALWORK as
[Actual Work] ,I.ecf_OWE as [Original
Estimate],I.variance,I.quotedhours,I.timecardhours
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X_ALL'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) -
CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME --,*
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID = F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.oce_name
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
EE
INNER JOIN
(
SELECT ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID =
FF.ENT_TaskUniqueID
LEFT JOIN
(
SELECT DISTINCT CODE_UID, CAST(OC_NAME as varchar(1))as oce_name FROM
MSP_OUTLINE_CODES
WHERE OC_FIELD_ID =188744419 AND PROJ_ID = 48 --not sure about this yet 48
= Enterprise global
)
GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

UNION

SELECT 'X_Routing_'+RTRIM(RTGwc)+'-'+CAST(RTGop as
varchar(6)),null,null,null,null,K.RTGhrs,null FROM
#RTGtable K

UNION
SELECT 'X_Timecard_'+RTRIM(TCwc)+'-'+CAST(TCop as
varchar(6)),null,null,null,null,null,L.TChrs FROM
#TCtable L

DROP TABLE #RTGtable

DROP TABLE #TCtable
 
L

Lars Hammarberg

If you don't want to extend the OLAP cube with task data (see MSDN for
details and examples), then go for a UDF using the in parameters @projid,
@taskuid as well as OC fields code and required level, returning OC
aggregation or just the level value - e.g.: select dbo.MyOCFunc(12,5,
188744589, 3)
returning: 'Country.Region.City' or whatever the code values are.

When accessing the EntGlobal - look for the PROJ_TYPE flag in MSP_PROJECTS
instead of hard-coding a projid.

--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner



John Sitka said:
I'm not sure this is the best way to ask for direction but
hopefully someone will see the issue if they have worked on it before.
I'm very new to knowing the data definition of Project so don't throw up.

Basically I'm just trying to distribute a task outline code accross a
reporting result set.
The INSERT....EXEC's that make up the UNIONS draw from an ERP calcualtion

My issue is with derived table "J" which happens
just after the LEFT JOIN and before the UNIONS

Seems a lot of work to distribute an outline code 'name' accross
enterprise
tasks. So much so that I'm on the verge of changing them to custom fields
but I was excited by the analysis posibillities but then found out task
outlines aren't
part of the default cubes.
So if anyone has a code snippent that attacks Task outline codes
so I can see the table relationship, much appreciated.





ALTER PROC ab_PWA_reportingattempt4
(
@sonom varchar(6),
@opernosm varchar(2000),
@leftdatem smalldatetime,
@rightdatem smalldatetime
--@project_namem varchar(255) NOT used yet
)
AS
DECLARE @project_name varchar(255)
DECLARE @project_id int
SET @project_name = 'KIT04.Published'
SELECT @project_id = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME =
@project_name)

--Build the RTG contribution

CREATE TABLE #RTGtable
(
RTGso varchar(255),
RTGwc varchar(255),
RTGop varchar(255),
RTGhrs Numeric(16,5)
)

INSERT INTO #RTGtable (RTGso,RTGwc,RTGop,RTGhrs)
EXEC ProjectServer0554.dbo.ab_quotedhours_1
@sono = @sonom,
@opernos = @opernosm

--Build the TC Contribution

CREATE TABLE #TCtable
(
TCso varchar(6),
TCjo varchar(10),
TCwc varchar(6),
TCop int,
TChrs Numeric(16,5)
)

INSERT INTO #TCtable (TCso,TCjo,TCwc,TCop,TChrs)
EXEC ProjectServer0554.dbo.ab_timecardhours_1
@sono = @sonom,
@opernos = @opernosm ,
@leftdate = @leftdatem,
@rightdate = @rightdatem

-- SELECT OUR DISPLAY SET

SELECT I.TASK_NAME as Task,J.oce_name as [Job Type],I.pro_ACTUALWORK as
[Actual Work] ,I.ecf_OWE as [Original
Estimate],I.variance,I.quotedhours,I.timecardhours
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X_ALL'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as
pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) -
CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME --,*
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID = F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.oce_name
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
EE
INNER JOIN
(
SELECT ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID =
FF.ENT_TaskUniqueID
LEFT JOIN
(
SELECT DISTINCT CODE_UID, CAST(OC_NAME as varchar(1))as oce_name FROM
MSP_OUTLINE_CODES
WHERE OC_FIELD_ID =188744419 AND PROJ_ID = 48 --not sure about this yet
48 = Enterprise global
)
GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

UNION

SELECT 'X_Routing_'+RTRIM(RTGwc)+'-'+CAST(RTGop as
varchar(6)),null,null,null,null,K.RTGhrs,null FROM
#RTGtable K

UNION
SELECT 'X_Timecard_'+RTRIM(TCwc)+'-'+CAST(TCop as
varchar(6)),null,null,null,null,null,L.TChrs FROM
#TCtable L

DROP TABLE #RTGtable

DROP TABLE #TCtable
Click to expand...
 
J

John Sitka

UDF, of course. Thanks for the weapon.
Hope to open up this can 'o worms again tomorrow.
Your response was most valuable.



Lars Hammarberg said:
If you don't want to extend the OLAP cube with task data (see MSDN for details and examples), then go for a UDF using the in
parameters @projid, @taskuid as well as OC fields code and required level, returning OC aggregation or just the level value -
e.g.: select dbo.MyOCFunc(12,5, 188744589, 3)
returning: 'Country.Region.City' or whatever the code values are.

When accessing the EntGlobal - look for the PROJ_TYPE flag in MSP_PROJECTS instead of hard-coding a projid.

--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner



John Sitka said:
I'm not sure this is the best way to ask for direction but
hopefully someone will see the issue if they have worked on it before.
I'm very new to knowing the data definition of Project so don't throw up.

Basically I'm just trying to distribute a task outline code accross a reporting result set.
The INSERT....EXEC's that make up the UNIONS draw from an ERP calcualtion

My issue is with derived table "J" which happens
just after the LEFT JOIN and before the UNIONS

Seems a lot of work to distribute an outline code 'name' accross enterprise
tasks. So much so that I'm on the verge of changing them to custom fields
but I was excited by the analysis posibillities but then found out task outlines aren't
part of the default cubes.
So if anyone has a code snippent that attacks Task outline codes
so I can see the table relationship, much appreciated.





ALTER PROC ab_PWA_reportingattempt4
(
@sonom varchar(6),
@opernosm varchar(2000),
@leftdatem smalldatetime,
@rightdatem smalldatetime
--@project_namem varchar(255) NOT used yet
)
AS
DECLARE @project_name varchar(255)
DECLARE @project_id int
SET @project_name = 'KIT04.Published'
SELECT @project_id = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME = @project_name)

--Build the RTG contribution

CREATE TABLE #RTGtable
(
RTGso varchar(255),
RTGwc varchar(255),
RTGop varchar(255),
RTGhrs Numeric(16,5)
)

INSERT INTO #RTGtable (RTGso,RTGwc,RTGop,RTGhrs)
EXEC ProjectServer0554.dbo.ab_quotedhours_1
@sono = @sonom,
@opernos = @opernosm

--Build the TC Contribution

CREATE TABLE #TCtable
(
TCso varchar(6),
TCjo varchar(10),
TCwc varchar(6),
TCop int,
TChrs Numeric(16,5)
)

INSERT INTO #TCtable (TCso,TCjo,TCwc,TCop,TChrs)
EXEC ProjectServer0554.dbo.ab_timecardhours_1
@sono = @sonom,
@opernos = @opernosm ,
@leftdate = @leftdatem,
@rightdate = @rightdatem

-- SELECT OUR DISPLAY SET

SELECT I.TASK_NAME as Task,J.oce_name as [Job Type],I.pro_ACTUALWORK as [Actual Work] ,I.ecf_OWE as [Original
Estimate],I.variance,I.quotedhours,I.timecardhours
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X_ALL'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) - CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME --,*
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID = F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.oce_name
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
EE
INNER JOIN
(
SELECT ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID = FF.ENT_TaskUniqueID
LEFT JOIN
(
SELECT DISTINCT CODE_UID, CAST(OC_NAME as varchar(1))as oce_name FROM MSP_OUTLINE_CODES
WHERE OC_FIELD_ID =188744419 AND PROJ_ID = 48 --not sure about this yet 48 = Enterprise global
)
GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

UNION

SELECT 'X_Routing_'+RTRIM(RTGwc)+'-'+CAST(RTGop as varchar(6)),null,null,null,null,K.RTGhrs,null FROM
#RTGtable K

UNION
SELECT 'X_Timecard_'+RTRIM(TCwc)+'-'+CAST(TCop as varchar(6)),null,null,null,null,null,L.TChrs FROM
#TCtable L

DROP TABLE #RTGtable

DROP TABLE #TCtable
Click to expand...
Click to expand...
 
J

John Sitka

Hi Lars,

Hopefully I can get a couple uninterupted hours here to work away on this.
When you mention

PROJ_TYPE =2 means Enterprise Global Template. That project can be used as the source for the set of all
Enterprise Outline codes correct?
So in referencing MSP_Projects even if the Proj_ID of the Enterprise Global Template changes I still maintain the relationship.
I think that Proj_ID (Enterprise Global Template) does change by the way. Why?









John Sitka said:
UDF, of course. Thanks for the weapon.
Hope to open up this can 'o worms again tomorrow.
Your response was most valuable.



Lars Hammarberg said:
If you don't want to extend the OLAP cube with task data (see MSDN for details and examples), then go for a UDF using the in
parameters @projid, @taskuid as well as OC fields code and required level, returning OC aggregation or just the level value -
e.g.: select dbo.MyOCFunc(12,5, 188744589, 3)
returning: 'Country.Region.City' or whatever the code values are.

When accessing the EntGlobal - look for the PROJ_TYPE flag in MSP_PROJECTS instead of hard-coding a projid.

--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner



John Sitka said:
I'm not sure this is the best way to ask for direction but
hopefully someone will see the issue if they have worked on it before.
I'm very new to knowing the data definition of Project so don't throw up.

Basically I'm just trying to distribute a task outline code accross a reporting result set.
The INSERT....EXEC's that make up the UNIONS draw from an ERP calcualtion

My issue is with derived table "J" which happens
just after the LEFT JOIN and before the UNIONS

Seems a lot of work to distribute an outline code 'name' accross enterprise
tasks. So much so that I'm on the verge of changing them to custom fields
but I was excited by the analysis posibillities but then found out task outlines aren't
part of the default cubes.
So if anyone has a code snippent that attacks Task outline codes
so I can see the table relationship, much appreciated.





ALTER PROC ab_PWA_reportingattempt4
(
@sonom varchar(6),
@opernosm varchar(2000),
@leftdatem smalldatetime,
@rightdatem smalldatetime
--@project_namem varchar(255) NOT used yet
)
AS
DECLARE @project_name varchar(255)
DECLARE @project_id int
SET @project_name = 'KIT04.Published'
SELECT @project_id = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME = @project_name)

--Build the RTG contribution

CREATE TABLE #RTGtable
(
RTGso varchar(255),
RTGwc varchar(255),
RTGop varchar(255),
RTGhrs Numeric(16,5)
)

INSERT INTO #RTGtable (RTGso,RTGwc,RTGop,RTGhrs)
EXEC ProjectServer0554.dbo.ab_quotedhours_1
@sono = @sonom,
@opernos = @opernosm

--Build the TC Contribution

CREATE TABLE #TCtable
(
TCso varchar(6),
TCjo varchar(10),
TCwc varchar(6),
TCop int,
TChrs Numeric(16,5)
)

INSERT INTO #TCtable (TCso,TCjo,TCwc,TCop,TChrs)
EXEC ProjectServer0554.dbo.ab_timecardhours_1
@sono = @sonom,
@opernos = @opernosm ,
@leftdate = @leftdatem,
@rightdate = @rightdatem

-- SELECT OUR DISPLAY SET

SELECT I.TASK_NAME as Task,J.oce_name as [Job Type],I.pro_ACTUALWORK as [Actual Work] ,I.ecf_OWE as [Original
Estimate],I.variance,I.quotedhours,I.timecardhours
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X_ALL'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) - CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME --,*
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID = F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.oce_name
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
EE
INNER JOIN
(
SELECT ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID = FF.ENT_TaskUniqueID
LEFT JOIN
(
SELECT DISTINCT CODE_UID, CAST(OC_NAME as varchar(1))as oce_name FROM MSP_OUTLINE_CODES
WHERE OC_FIELD_ID =188744419 AND PROJ_ID = 48 --not sure about this yet 48 = Enterprise global
)
GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

UNION

SELECT 'X_Routing_'+RTRIM(RTGwc)+'-'+CAST(RTGop as varchar(6)),null,null,null,null,K.RTGhrs,null FROM
#RTGtable K

UNION
SELECT 'X_Timecard_'+RTRIM(TCwc)+'-'+CAST(TCop as varchar(6)),null,null,null,null,null,L.TChrs FROM
#TCtable L

DROP TABLE #RTGtable

DROP TABLE #TCtable
Click to expand...
Click to expand...
Click to expand...
 
L

Lars Hammarberg

(Sorry for not replying earlier - too much work)

Yes, you're quite right - using data from the PROJ_TYPE = 2 project will
give you the EntGlobal data.

The reason for a changing Proj_ID number vis-a-vis the EnterpriseGlobal is
probably because the EntGlobal is, in fact, a "whole" project on its own
(call it a "project file" for simplicity) - not so with the Proj_ID=1 = the
Resource Pool, which isn't a "project file", but rather a "virtual project",
if you understand what I mean... :p


--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner




John Sitka said:
Hi Lars,

Hopefully I can get a couple uninterupted hours here to work away on this.
When you mention

PROJ_TYPE =2 means Enterprise Global Template. That project can be used as
the source for the set of all
Enterprise Outline codes correct?
So in referencing MSP_Projects even if the Proj_ID of the Enterprise
Global Template changes I still maintain the relationship.
I think that Proj_ID (Enterprise Global Template) does change by the way.
Why?









John Sitka said:
UDF, of course. Thanks for the weapon.
Hope to open up this can 'o worms again tomorrow.
Your response was most valuable.



Lars Hammarberg said:
If you don't want to extend the OLAP cube with task data (see MSDN for
details and examples), then go for a UDF using the in parameters
@projid, @taskuid as well as OC fields code and required level,
returning OC aggregation or just the level value - e.g.: select
dbo.MyOCFunc(12,5, 188744589, 3)
returning: 'Country.Region.City' or whatever the code values are.

When accessing the EntGlobal - look for the PROJ_TYPE flag in
MSP_PROJECTS instead of hard-coding a projid.

--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner



I'm not sure this is the best way to ask for direction but
hopefully someone will see the issue if they have worked on it before.
I'm very new to knowing the data definition of Project so don't throw
up.

Basically I'm just trying to distribute a task outline code accross a
reporting result set.
The INSERT....EXEC's that make up the UNIONS draw from an ERP
calcualtion

My issue is with derived table "J" which happens
just after the LEFT JOIN and before the UNIONS

Seems a lot of work to distribute an outline code 'name' accross
enterprise
tasks. So much so that I'm on the verge of changing them to custom
fields
but I was excited by the analysis posibillities but then found out task
outlines aren't
part of the default cubes.
So if anyone has a code snippent that attacks Task outline codes
so I can see the table relationship, much appreciated.





ALTER PROC ab_PWA_reportingattempt4
(
@sonom varchar(6),
@opernosm varchar(2000),
@leftdatem smalldatetime,
@rightdatem smalldatetime
--@project_namem varchar(255) NOT used yet
)
AS
DECLARE @project_name varchar(255)
DECLARE @project_id int
SET @project_name = 'KIT04.Published'
SELECT @project_id = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME
= @project_name)

--Build the RTG contribution

CREATE TABLE #RTGtable
(
RTGso varchar(255),
RTGwc varchar(255),
RTGop varchar(255),
RTGhrs Numeric(16,5)
)

INSERT INTO #RTGtable (RTGso,RTGwc,RTGop,RTGhrs)
EXEC ProjectServer0554.dbo.ab_quotedhours_1
@sono = @sonom,
@opernos = @opernosm

--Build the TC Contribution

CREATE TABLE #TCtable
(
TCso varchar(6),
TCjo varchar(10),
TCwc varchar(6),
TCop int,
TChrs Numeric(16,5)
)

INSERT INTO #TCtable (TCso,TCjo,TCwc,TCop,TChrs)
EXEC ProjectServer0554.dbo.ab_timecardhours_1
@sono = @sonom,
@opernos = @opernosm ,
@leftdate = @leftdatem,
@rightdate = @rightdatem

-- SELECT OUR DISPLAY SET

SELECT I.TASK_NAME as Task,J.oce_name as [Job Type],I.pro_ACTUALWORK as
[Actual Work] ,I.ecf_OWE as [Original
Estimate],I.variance,I.quotedhours,I.timecardhours
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X_ALL'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as
pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as
pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) -
CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME --,*
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID =
F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.oce_name
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
EE
INNER JOIN
(
SELECT
ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID =
FF.ENT_TaskUniqueID
LEFT JOIN
(
SELECT DISTINCT CODE_UID, CAST(OC_NAME as varchar(1))as oce_name FROM
MSP_OUTLINE_CODES
WHERE OC_FIELD_ID =188744419 AND PROJ_ID = 48 --not sure about this
yet 48 = Enterprise global
)
GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

UNION

SELECT 'X_Routing_'+RTRIM(RTGwc)+'-'+CAST(RTGop as
varchar(6)),null,null,null,null,K.RTGhrs,null FROM
#RTGtable K

UNION
SELECT 'X_Timecard_'+RTRIM(TCwc)+'-'+CAST(TCop as
varchar(6)),null,null,null,null,null,L.TChrs FROM
#TCtable L

DROP TABLE #RTGtable

DROP TABLE #TCtable
Click to expand...
Click to expand...
Click to expand...
 
J

John Sitka

Thanks Lars.
I've been all over the map as far as duties and responsibilities lately.
Still reading the groups, just not developing version 1.2 reporting yet
but 1.0 (proof of concept which turned into a metric) is working as required.

Watch; tomorrow I'll be up to my neck in the data definitions again.
Oh well that's what keeps it interesting huh.

Hope to contribute more soon. New hardware for Project Server may be
in the works. Hope to gain enough skill to help some folks here.



Lars Hammarberg said:
(Sorry for not replying earlier - too much work)

Yes, you're quite right - using data from the PROJ_TYPE = 2 project will give you the EntGlobal data.

The reason for a changing Proj_ID number vis-a-vis the EnterpriseGlobal is probably because the EntGlobal is, in fact, a "whole"
project on its own (call it a "project file" for simplicity) - not so with the Proj_ID=1 = the Resource Pool, which isn't a
"project file", but rather a "virtual project", if you understand what I mean... :p


--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner




John Sitka said:
Hi Lars,

Hopefully I can get a couple uninterupted hours here to work away on this.
When you mention
When accessing the EntGlobal - look for the PROJ_TYPE flag in MSP_PROJECTS instead of hard-coding a projid.
Click to expand...

PROJ_TYPE =2 means Enterprise Global Template. That project can be used as the source for the set of all
Enterprise Outline codes correct?
So in referencing MSP_Projects even if the Proj_ID of the Enterprise Global Template changes I still maintain the relationship.
I think that Proj_ID (Enterprise Global Template) does change by the way. Why?









John Sitka said:
UDF, of course. Thanks for the weapon.
Hope to open up this can 'o worms again tomorrow.
Your response was most valuable.



If you don't want to extend the OLAP cube with task data (see MSDN for details and examples), then go for a UDF using the in
parameters @projid, @taskuid as well as OC fields code and required level, returning OC aggregation or just the level value -
e.g.: select dbo.MyOCFunc(12,5, 188744589, 3)
returning: 'Country.Region.City' or whatever the code values are.

When accessing the EntGlobal - look for the PROJ_TYPE flag in MSP_PROJECTS instead of hard-coding a projid.

--

/Lars Hammarberg
www.camako.se
MSProject Premier Partner



I'm not sure this is the best way to ask for direction but
hopefully someone will see the issue if they have worked on it before.
I'm very new to knowing the data definition of Project so don't throw up.

Basically I'm just trying to distribute a task outline code accross a reporting result set.
The INSERT....EXEC's that make up the UNIONS draw from an ERP calcualtion

My issue is with derived table "J" which happens
just after the LEFT JOIN and before the UNIONS

Seems a lot of work to distribute an outline code 'name' accross enterprise
tasks. So much so that I'm on the verge of changing them to custom fields
but I was excited by the analysis posibillities but then found out task outlines aren't
part of the default cubes.
So if anyone has a code snippent that attacks Task outline codes
so I can see the table relationship, much appreciated.





ALTER PROC ab_PWA_reportingattempt4
(
@sonom varchar(6),
@opernosm varchar(2000),
@leftdatem smalldatetime,
@rightdatem smalldatetime
--@project_namem varchar(255) NOT used yet
)
AS
DECLARE @project_name varchar(255)
DECLARE @project_id int
SET @project_name = 'KIT04.Published'
SELECT @project_id = (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME = @project_name)

--Build the RTG contribution

CREATE TABLE #RTGtable
(
RTGso varchar(255),
RTGwc varchar(255),
RTGop varchar(255),
RTGhrs Numeric(16,5)
)

INSERT INTO #RTGtable (RTGso,RTGwc,RTGop,RTGhrs)
EXEC ProjectServer0554.dbo.ab_quotedhours_1
@sono = @sonom,
@opernos = @opernosm

--Build the TC Contribution

CREATE TABLE #TCtable
(
TCso varchar(6),
TCjo varchar(10),
TCwc varchar(6),
TCop int,
TChrs Numeric(16,5)
)

INSERT INTO #TCtable (TCso,TCjo,TCwc,TCop,TChrs)
EXEC ProjectServer0554.dbo.ab_timecardhours_1
@sono = @sonom,
@opernos = @opernosm ,
@leftdate = @leftdatem,
@rightdate = @rightdatem

-- SELECT OUR DISPLAY SET

SELECT I.TASK_NAME as Task,J.oce_name as [Job Type],I.pro_ACTUALWORK as [Actual Work] ,I.ecf_OWE as [Original
Estimate],I.variance,I.quotedhours,I.timecardhours
FROM
(
SELECT CASE WHEN (GROUPING(G.TASK_NAME) = 1) THEN 'X_ALL'
ELSE G.TASK_NAME END as TASK_NAME, SUM(G.pro_ACTUALWORK) as pro_ACTUALWORK,
SUM(G.ecf_OWE) as ecf_OWE, SUM(G.variance)as variance,
null as quotedhours,null as timecardhours
FROM
(
SELECT TASK_NAME, CAST(E.TASK_ACT_WORK/60000 as money) as pro_ACTUALWORK,
CAST(F.TaskEnterpriseDuration1/600.00 as money) as ecf_OWE,
CAST(F.TaskEnterpriseDuration1/600.00 as money) - CAST(E.TASK_ACT_WORK/60000 as money) as variance
FROM
(
SELECT TASK_ACT_WORK, TASK_UID, PROJ_ID, TASK_NAME --,*
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
E
INNER JOIN
(
SELECT TaskEnterpriseDuration1, ENT_ProjectUniqueID, ENT_TaskUniqueID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
F
ON E.PROJ_ID = F.ENT_ProjectUniqueID AND E.TASK_UID = F.ENT_TaskUniqueID
WHERE F.TaskEnterpriseDuration1 <> 0
AND TASK_NAME LIKE @sonom+'%'
)
G
GROUP BY G.TASK_NAME WITH ROLLUP
)
I
LEFT JOIN
(
SELECT EE.TASK_NAME, GG.oce_name
FROM
(
SELECT TASK_UID, PROJ_ID, TASK_NAME
FROM MSP_TASKS WHERE Proj_ID = @project_id
)
EE
INNER JOIN
(
SELECT ENT_ProjectUniqueID,ENT_TaskUniqueID,TaskEnterpriseOutlineCode1ID
FROM MSP_VIEW_PROJ_TASKS_ENT WHERE ENT_ProjectUniqueID = @project_id
)
FF
ON EE.PROJ_ID = FF.ENT_ProjectUniqueID AND EE.TASK_UID = FF.ENT_TaskUniqueID
LEFT JOIN
(
SELECT DISTINCT CODE_UID, CAST(OC_NAME as varchar(1))as oce_name FROM MSP_OUTLINE_CODES
WHERE OC_FIELD_ID =188744419 AND PROJ_ID = 48 --not sure about this yet 48 = Enterprise global
)
GG
ON FF.TaskEnterpriseOutlineCode1ID = GG.CODE_UID
)
J
ON I.TASK_NAME=J.TASK_NAME

UNION

SELECT 'X_Routing_'+RTRIM(RTGwc)+'-'+CAST(RTGop as varchar(6)),null,null,null,null,K.RTGhrs,null FROM
#RTGtable K

UNION
SELECT 'X_Timecard_'+RTRIM(TCwc)+'-'+CAST(TCop as varchar(6)),null,null,null,null,null,L.TChrs FROM
#TCtable L

DROP TABLE #RTGtable

DROP TABLE #TCtable
Click to expand...
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

sql string, +'/'+convert(char(3)..) 3
SQL server Insert 2
Updatable SQL 2000 partitioned view not updatable in Access 3
SQL Server SP equivalent 5
Running a T/SQL in MS Access 8
Excel/SQL Query Quandry 3
SQL ADP Reports not working due to Authentication 4

Top