performing queries for fields that have concatenated values

[Chris_J_S]

We have a field that has a look-up table of possible values. The user can
select more than one value. My DBA tells me that he cannot find this field
in the Data Base so that we can add it to our detailed task report. What are
we doing wrong?

 

[Andrew Lavinsky]

To quote Rod Gill from another post a bit lower on this page:

"Look at the built in Views ending with _Userview. They have all custom fields
automatically appended to their end."

Has your DBA tried this?


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

 

[Gaurav Wason]

Your DBA is correct. Work around is create a calculated field that will pull
info of this multivalue field. (Example: Multiple value field name is
locations US, Canada, India) create a new filed called location_calculated
and formula will [locations]. Use this field to get info in reports. Hope
this helps.
--
Please rate this post if it has helped

Thanks
Gaurav Wason
(e-mail address removed), PMP, MCITP roject Made Easy (Project Server Archive Tool)
and (Project Owner Tool)
http://projectmadeeasy.com

 

[Piet Remen]

The project server reporting database might be able to help you out. Through
SQL Manager, expand the views folder. Whenever you create a lookup table, two
views are dynamically generated. One for olap and one for users. Genereally
use the ones suffixed with '_UserView'. They are all generated with a prefix
of 'dbo.MSPLT_
'
, which I assume stands for Microsoft Project Lookup Table, so are easily
identifed. Heirarchal lookup table values utilise the 'ParentLookupMemberUID'
field which show the items parent node so you can weave a little SQL magic to
work out the heirarchy if need be.

The easy way to get custom field values is to use another set of views in
the same spot. Again these views are auto-magically updated as you create and
remove custom fields in project server which can save you a bit of SQL
overhead. Depending on which entity custom field you have defined will
determine which user view is appropriate. All custom field columns are
appended to the end of the view and named the same as the custom field. Look
here for the value. If heirarchal values selected you would see the value of
Node1.Node2.Node3. In this scenario I use a SQL string split function to
return me the end node or a particular element even since we know what the
values are delimeted by. A bit of binging/Googling will find you this type of
function.

The golden reference is:

dbo.MSP_EpmProject_UserView - Displays project entity custom fields
dbo.MSP_EpmTask_UserView - Displays task entity custom fields
dbo.MSP_EpmResource_UserView - Displays resource entity custom fields

As for multiple field selections... well after writing all this I realise
this is not going to help you as multi selection custom fields don't comply
to these rules... hmmmm that sux . Sorry but you may have to resort to
working out the cryptic structure of the publish database tables. Try looking
through the following tables of the publish database:

MSP_CUSTOM_FIELDS
MSP_PROJ_CUSTOM_FIELD_VALUES
MSP_PROIJECT_CUSTOM_FIELD_VALUES
MSP_LOOKUP_TABLE_VALUES

Regards,

Piet Remen
www.projectserver.com.au

 

[Gaurav Wason]

Easiest option is to create a calculated field. I have done it multiple times
and it always work and the data becomes available in *Thanks Piet

dbo.MSP_EpmProject_UserView - Displays project entity custom fields
dbo.MSP_EpmTask_UserView - Displays task entity custom fields
dbo.MSP_EpmResource_UserView - Displays resource entity custom fields

--
Please rate this post if it has helped

Thanks
Gaurav Wason
(e-mail address removed), PMP, MCITP roject Made Easy (Project Server Archive Tool)
and (Project Owner Tool)
http://projectmadeeasy.com