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
Celvin,
Doesn't the OutlineLoad Utility Web show you the Smart List? Wouldn't that resolve it for PBCS and other (Classic) implementations?
-Chris Rothermel
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 🙂
Celvin – Do you know if there a way to pull Smartlist values from Essbase instead of querying the back-end Planning relational tables?
The values, you mean labels? If yes then no, it is the ID that gets stored in Essbase