Query for getting lookup table in Row Number order

For your reporting needs, you may want to display values from a Lookup Table exactly in the order they are saved in PWA.

 

Within PWA, this setting is controlled using “Display order for lookup table” option on Edit Lookup Table Page.

 

 

However, this sort order is not available in Reporting Database. Following query uses Published (and reporting) schema to get this information. This is tested on Project Server 2013 on premises.

 

SELECT [MemberValue] , tmv .LT_VALUE_SORT_INDEX

FROM [MSPLT_Target Market_OlapView] tm

inner join pub .MSP_LOOKUP_TABLE_VALUES tmv

on tm.LookupMemberUID = tmv.LT_STRUCT_UID

order by LT_VALUE_SORT_INDEX

 

Replace “Target market” with your Lookup Table name in the above query.