How to Create a Query for Lookup Tables in Row Number Order

Posted By Posted by: EPM Partners on March 4, 2015

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.

 

Lookup

 

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.


Blog Posted In Blog Posted In: How to, Reporting
Blog Posted In 

Leave a Reply

Your email address will not be published. Required fields are marked *