Show Smart List Usage – Forms & Accounts


How can I see which Smart List is associated to which member?
I had to do that on a PBCS implementation and as you know there is no access to the backend. Luckily the client named the Smart lists similar to the member names, however there were some which was shared between members!!!!
On OnPrem, it is easier I can query the tables 🙂
This query will give you the Smart List name along with the members it is assigned to.

SELECT 
	C.OBJECT_NAME as "Member",
	A.NAME as "Smart List"
FROM  HSP_ENUMERATION A,
      HSP_MEMBER B,
	  HSP_OBJECT C 
WHERE A.ENUMERATION_ID = B.ENUMERATION_ID AND
      B.MEMBER_ID=C.OBJECT_ID AND
	  UPPER(A.NAME)='YOUR SMARTLIST NAME';

What if you would like to know which Smart List members are used in which forms, you can run the below given query to get to that.

SELECT 
	formnametb.OBJECT_NAME as "Form Name",
	mbrnametb.OBJECT_NAME as "Member",
	smartlisttb.NAME as "Smart List" 
FROM  HSP_ENUMERATION smartlisttb,
      HSP_MEMBER mbrtb,
	  HSP_OBJECT mbrnametb,
	  HSP_FORMOBJ_DEF formtb,
	  HSP_FORMOBJ_DEF_MBR mbrinformtb,
	  HSP_OBJECT formnametb 
WHERE smartlisttb.ENUMERATION_ID = mbrtb.ENUMERATION_ID AND
      mbrtb.MEMBER_ID=mbrnametb.OBJECT_ID AND
	  mbrtb.MEMBER_ID=mbrinformtb.MBR_ID AND
	  mbrinformtb.OBJDEF_ID=formtb.OBJDEF_ID AND
	  formtb.FORM_ID=formnametb.OBJECT_ID AND
	  UPPER(smartlisttb.NAME)='YOUR SMARTLIST NAME';

OnPrem got the answer for that too 🙂

I hope we get the Show Usage for Smart Lists, Menus and member usage in Calc Manager rules in the system.

I did write SQLs for the above ones and you can look them up here

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “Show Smart List Usage – Forms & Accounts

  • Celvin Kattookaran

    Yes, however for that you'll have to extract the metadata using OutlineLoad and then see which members are associated with which Smart List, however it'll still not tell you the association with webform.

    For that you'll have to run reporting and then scan through a PDF file to see which smartlist members are associated with those.

    Wow, even to type it took a while 🙂