Stupid Planning queries #11 -- Where and what are my Smart Lists

Where oh where has my Smart List gone?

Smart Lists are a wonderful thing. Well, that may be slightly exaggerating their usefulness but if you want to create a drop down list in a Planning Account (or other dimension but I have never seen it) Smart Lists are the way to go. Come to think of it, they are a bit of a Hobson’s Choice (a truly fantastic movie) if you want dropdown lists in Planning forms.

Planning even gives you a great way to view the Smart Lists in your Planning application by simply going to the Administration->Manage->Smart Lists menu.

Here is a (rather short) list of Smart Lists in my sample Planning app:

Live and in person
This is a very silly and pointless form that nevertheless shows a Smart List with nothing selected.

Clicking on the down arrow:

Selected Yes


Saved to Essbase


FWIW, if I pulled the Account YesOrNo in Essbase using a Smart View adhoc analysis link, I would get a 1 in that cell as, bizarrely, Smart Lists do not resolve to Essbase Text Measures. I will try not to think about why that is the case as their functionality is the same. Different development groups is the best explanation I can think of but it is frustrating. Onwards, regardless.

So all of this is great, and more than a little basic
Yes, I know, what is there to query if you just created the Smart List? Well, in the case of this sample Planning application, there is no real reason to query much of anything as we know what the Smart List is and what member it’s tied to.

But what if you didn’t know what member the Smart List was assigned to? How would you know? As far as I can tell, there is no magic report in Planning that will give out this information.

A different story
And what if you were working on a Planning migration/modify project (ahem) and the Planning application had 31 Smart Lists, and somehow the association of Smart List to member got lost (oh, thank you accursed EPMA), and you had to go back to the original Planning app to figure out what goes where? What would you do then? Scream? Cry? Curse your bad luck? Or how about write a query that looks just like this?

The query

/*

    Purpose:     Figure out what Smart Lists are assigned to which Members
    Modified:    23 February 2013, Cameron Lackpour
    Notes:       This is a nice and easy one, isn't it?
*/
SELECT
  O.OBJECT_NAME AS 'Member',
  E.ENUMERATION_ID AS 'SL #',
  E.NAME AS 'Smart List'
FROM
  HSP_MEMBER M
INNER JOIN
  HSP_ENUMERATION E ON M.ENUMERATION_ID = E.ENUMERATION_ID
INNER JOIN
  HSP_OBJECT O ON O.OBJECT_ID = M.MEMBER_ID


And that produces a result set like this:


That hypothetical Planning application I mentioned above? Would you believe 31 Smart Lists of which 14 were actually assigned? Yup, 17 dead Smart Lists. Isn’t application maintenance a stinker? Apparently so.

Everything you ever wanted to know about a Smart List
Above I joined HSP_ENUMERATION (why wasn’t the tabled called HSP_SMARTLIST?) to HSP_MEMBER to get the link between member (in any dimension) and Smart List. But what if you just wanted a quick review of everything that ever made up a Smart List?

Query the second

/*

    Purpose:     Smart List contents by name
    Modified:    23 February 2013, Cameron Lackpour
    Notes:       Another Nice 'N Easy one.
*/
SELECT
      E.NAME,
      EE.ENTRY_ID,
      EE.NAME,
      EE.LABEL
FROM
      HSP_ENUMERATION_ENTRY EE
INNER JOIN
      HSP_ENUMERATION E ON EE.ENUMERATION_ID = E.ENUMERATION_ID
And that produces a result set like this:


And that’s it
I have to say that I wrote this blog post because I needed to get that list of Smart List associations to members and simply couldn’t find it on the series of tubes that make up the world wide web.   I’m sure it exists, somewhere, or maybe it was just so easy no one bothered to post it. Regardless, now world+dog has it.

I will note again what an incredbily helpful thing it is to write these queries – I cannot imagine going through each one of the Accounts in the application I am talking about (over three thousand across multiple Plan Types) and try to find the silly things – I’d have completely gone off my rocker (although I will admit it might be hard to spot when that happens) and I would have spent a *long* time trying to figure out where the non-assigned 17 Smart Lists should have been. Which was nowhere, thanks to the query. SQL saves the day yet again.

Be seeing you.