Retrieving custom fields in PS 2007

[Kristi]

Hi-

I am trying to pull back (SQL) my custom fields from the db behind my
instance of Project Server 2007. I have a few questions:

1. Are the custom fields available in the ProjectServer_Reporting db?
2. If not, how can I get the data from the db that are stored in (where is
that data)?
3. If they are stored in ProjectServer_Reporting, how can I get the data
(where is that data)?

Thanks!

 

[Rod Gill]

Yes custom fields are in the reporting database and handled neatly. I always
use the Views in the reporting database rather than the tables as the Views
have already done a lot of the work for you.

The three views to look for are (from memory so they are probably not
exactly right!)
msp_epmproject_userview
msp_epmresource_userview
msp_epmassignment_userview

Custom fields are automatically tagged on as extra columns at the end of
these Views.
--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Kristi]

Perfect - thanks!

Yes custom fields are in the reporting database and handled neatly. I always
use the Views in the reporting database rather than the tables as the Views
have already done a lot of the work for you.

The three views to look for are (from memory so they are probably not
exactly right!)
msp_epmproject_userview
msp_epmresource_userview
msp_epmassignment_userview

Custom fields are automatically tagged on as extra columns at the end of
these Views.
--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Sharry Heberer [MSFT]]

One extra piece of information. Values for multi-value custom fields are
not pulled into these views. You have to do a little bit extra work to get
these. Let me know if you need the queries for multi-value fields and I can
get them.

Also, in general, we strongly urge the use of the UserViews wherever you
can, since we did try and do alot of the work for the most common data users
would want to see. Stay away from the OlapViews, too - these were designed
specifically for the use of Analysis Services, and running reports from
these could cause collisions with cube building. In most cases, there is a
matching UserView for every OlapView.

 

[Kristi]

I actually do need to get a partial value for a custom field. For example, I
have a custom field that has a 'value' and a 'description'. I need to be
able to pull only the 'value' - is that possible?

Thanks!

 

[Sharry Heberer [MSFT]]

I think we are talking about two different things. I was talking about how
custom fields can be marked as multi-value, and so entities using that
custom field would be able to set more than 1 value for that particular
field. Like a resource could have Skills of both "program manager" and
"editor" or something like that. That is what is termed "multi-value". But
if you're not using these types of fields, then my original point is
unnecessary for you.

Now on to your latest comment....

The value is what is shown in the UserViews. Do you mean that you have a
Enterprise Text Custom Field whose values are sort of "multi-part"? Like,
if you had a CF for Locations, and your Lookup Table contained values of
"1 - Seattle," "2 - Los Angeles," "3 - New York," etc... then one "value"
(as Project Server knows it) is the whole thing ("1 - Seattle" for example)
and that is what will be in the Reporting Database. If you need only the
"1" part you will have to parse the text yourself.

I hope some or all of this answers your questions. Please feel free to post
back if you still have questions.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Kristi]

Sharry-

Thanks for your assistance. I will try to explain my situation a bit more
clearly by using an example:

I have a custom project field called 'Project and IO' that has a lookup
table. The Lookup table allows me to define 2 things: a value and a
description. I would like to pull back ONLY the values, but when I pull back
[project and io] from MSP_EpmProject_UserView, it pulls back both the value
and description (with a period in between them).

Any ideas? Thanks!

 

[Sharry Heberer [MSFT]]

I don't know how/why that would happen. What we put in the view is the full
structure of the value, so let's say you have a lookup table like this:

A (description is "this is A")
1 (description is "this is 1")
B (description is "this is B")
2 (description is "this is 2")
C (description is "this is C")
3 (description is "this is 3")

And you select "3" for the value for a project CF (say it's called "foo")
that uses this LT. What we will show in the "foo" column of
msp_epmproject_userview is "C.3". You should not see something like "3.this
is 3".

If you are seeing the description in this column, then something has
happened to your view definition, maybe. Or - can you double check the
definition of the lookup table, especially the structure (the Code Mask
section)? Maybe someone changed it underneath you and you don't know about
it?

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Sharry-

Thanks for your assistance. I will try to explain my situation a bit more
clearly by using an example:

I have a custom project field called 'Project and IO' that has a lookup
table. The Lookup table allows me to define 2 things: a value and a
description. I would like to pull back ONLY the values, but when I pull
back
[project and io] from MSP_EpmProject_UserView, it pulls back both the
value
and description (with a period in between them).

Any ideas? Thanks!

I think we are talking about two different things. I was talking about
how
custom fields can be marked as multi-value, and so entities using that
custom field would be able to set more than 1 value for that particular
field. Like a resource could have Skills of both "program manager" and
"editor" or something like that. That is what is termed "multi-value".
But
if you're not using these types of fields, then my original point is
unnecessary for you.

Now on to your latest comment....

The value is what is shown in the UserViews. Do you mean that you have a
Enterprise Text Custom Field whose values are sort of "multi-part"?
Like,
if you had a CF for Locations, and your Lookup Table contained values of
"1 - Seattle," "2 - Los Angeles," "3 - New York," etc... then one
"value"
(as Project Server knows it) is the whole thing ("1 - Seattle" for
example)
and that is what will be in the Reporting Database. If you need only the
"1" part you will have to parse the text yourself.

I hope some or all of this answers your questions. Please feel free to
post
back if you still have questions.

--
This posting is provided "AS IS" with no warranties, and confers no
rights.

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Manmeet Chaudhari]

Hi Sharry
I do have a project level multi select field.
I am creating a report by connecting Excel to ProjectServer_Reporting
database.
However my multi value fields do not appear in Excel.

Can you please help me with the same
As i read in this discussion you have solution to my problem
--
Manmeet Chaudhari
Maestros Mediline Systems Ltd
Mumbai

 

[Gerry (H.K.)]

Hi there,

I've created a Project enterprise custom field and assign a value to my
project. However when I published the project to project server I am unable
to retrieve that custom field's information from the views you stated below
in reporting database. Is there any more views or tables that stored
enterprise custom field information in reporting database? Thanks in advance.

Gerry

 

[Jonathan Sofer]

Have you verified that the field and its value are visible in the Project
Center views? If they aren't there then the publish is not successful and
therefore will not propagate to the reporting database.

I also agree with Rod that using the views he stated below is the best
approach for retrieving custom enterprise field data and I have never had
any issues with the data not making it into those views assuming the
enterprise field was set up correctly and the publish was done successfully.

Jonathan

 

[Gerry (H.K.)]

Thanks, I found that the custom field did not propagate to the reporting
database right after I published the project. I've checked the view at a
later time and found the custom field was there. Thanks.

Gerry

 

[oldbradfordian]

Please can you provide an explanation or sample code for how to access values
for multi-value fields in an SQL report?

Thank you very much.

 

[Jonathan Sofer [MVP]]

Multi value fields do not make their way into the reporting database
therefore the only place this data exists is in the draft and published
databases.

Running reports off of the draft and published databases are not recommended
or supported by Microsoft. However, here is a sample query built in the
Published DB that pulls back data for project "Test Project" for the muilti
value field called "MultiValueFieldTest".

I had one task with 2 values selected in my MultiValueFieldTest custom field
so this query returned 2 rows for me.

SELECT MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID, MSP_TASKS.TASK_NAME,
MSP_LOOKUP_TABLES.LT_NAME,
MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT,
MSP_LOOKUP_TABLE_VALUES.LT_VALUE_FULL
FROM MSP_PROJECTS INNER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_UID =
MSP_TASKS.PROJ_UID INNER JOIN
MSP_TASK_CUSTOM_FIELD_VALUES ON MSP_TASKS.TASK_UID =
MSP_TASK_CUSTOM_FIELD_VALUES.TASK_UID AND
MSP_TASKS.PROJ_UID =
MSP_TASK_CUSTOM_FIELD_VALUES.PROJ_UID INNER JOIN
MSP_LOOKUP_TABLE_VALUES ON
MSP_TASK_CUSTOM_FIELD_VALUES.CODE_VALUE =
MSP_LOOKUP_TABLE_VALUES.LT_STRUCT_UID INNER JOIN
MSP_LOOKUP_TABLES ON MSP_LOOKUP_TABLE_VALUES.LT_UID =
MSP_LOOKUP_TABLES.LT_UID
WHERE (MSP_PROJECTS.PROJ_NAME = N'Test Project') AND
(MSP_LOOKUP_TABLES.LT_NAME = N'MultiValueFieldTest')

Hope this helps, Jonathan

This posting is provided "AS IS" with no warranties

 

[Rod Gill]

I don't have a system to check on available right now, but from memory any
multi-value Custom Field has a new View created automatically in the
Reporting DB with the name of the Custom field. This View has the multiple
values, or maybe only multiple selected choices (I can't remember). I've
forgotten the format of that name, but try looking at all the names of the
Views and see what's there (there aren't that many of them).

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

Multi value fields do not make their way into the reporting database
therefore the only place this data exists is in the draft and published
databases.

Running reports off of the draft and published databases are not
recommended or supported by Microsoft. However, here is a sample query
built in the Published DB that pulls back data for project "Test Project"
for the muilti value field called "MultiValueFieldTest".

I had one task with 2 values selected in my MultiValueFieldTest custom
field so this query returned 2 rows for me.

SELECT MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID, MSP_TASKS.TASK_NAME,
MSP_LOOKUP_TABLES.LT_NAME,
MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT,
MSP_LOOKUP_TABLE_VALUES.LT_VALUE_FULL
FROM MSP_PROJECTS INNER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_UID =
MSP_TASKS.PROJ_UID INNER JOIN
MSP_TASK_CUSTOM_FIELD_VALUES ON MSP_TASKS.TASK_UID =
MSP_TASK_CUSTOM_FIELD_VALUES.TASK_UID AND
MSP_TASKS.PROJ_UID =
MSP_TASK_CUSTOM_FIELD_VALUES.PROJ_UID INNER JOIN
MSP_LOOKUP_TABLE_VALUES ON
MSP_TASK_CUSTOM_FIELD_VALUES.CODE_VALUE =
MSP_LOOKUP_TABLE_VALUES.LT_STRUCT_UID INNER JOIN
MSP_LOOKUP_TABLES ON MSP_LOOKUP_TABLE_VALUES.LT_UID =
MSP_LOOKUP_TABLES.LT_UID
WHERE (MSP_PROJECTS.PROJ_NAME = N'Test Project') AND
(MSP_LOOKUP_TABLES.LT_NAME = N'MultiValueFieldTest')

Hope this helps, Jonathan

This posting is provided "AS IS" with no warranties




__________ Information from ESET Smart Security, version of virus
signature database 4545 (20091026) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4545 (20091026) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[oldbradfordian]

Thank you, Jonathan. I'll give it a try.

Multi value fields do not make their way into the reporting database
therefore the only place this data exists is in the draft and published
databases.

Running reports off of the draft and published databases are not recommended
or supported by Microsoft. However, here is a sample query built in the
Published DB that pulls back data for project "Test Project" for the muilti
value field called "MultiValueFieldTest".

I had one task with 2 values selected in my MultiValueFieldTest custom field
so this query returned 2 rows for me.

SELECT MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID, MSP_TASKS.TASK_NAME,
MSP_LOOKUP_TABLES.LT_NAME,
MSP_LOOKUP_TABLE_VALUES.LT_VALUE_TEXT,
MSP_LOOKUP_TABLE_VALUES.LT_VALUE_FULL
FROM MSP_PROJECTS INNER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_UID =
MSP_TASKS.PROJ_UID INNER JOIN
MSP_TASK_CUSTOM_FIELD_VALUES ON MSP_TASKS.TASK_UID =
MSP_TASK_CUSTOM_FIELD_VALUES.TASK_UID AND
MSP_TASKS.PROJ_UID =
MSP_TASK_CUSTOM_FIELD_VALUES.PROJ_UID INNER JOIN
MSP_LOOKUP_TABLE_VALUES ON
MSP_TASK_CUSTOM_FIELD_VALUES.CODE_VALUE =
MSP_LOOKUP_TABLE_VALUES.LT_STRUCT_UID INNER JOIN
MSP_LOOKUP_TABLES ON MSP_LOOKUP_TABLE_VALUES.LT_UID =
MSP_LOOKUP_TABLES.LT_UID
WHERE (MSP_PROJECTS.PROJ_NAME = N'Test Project') AND
(MSP_LOOKUP_TABLES.LT_NAME = N'MultiValueFieldTest')

Hope this helps, Jonathan

This posting is provided "AS IS" with no warranties