Want to know The Truth About CPM?

23 February 2013

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.

8 comments:

GlennS said...

While it is true a Smart View Essbase connection will not show the Smart List Values, useing a planning connection with Ad-Hoc should. That is the beauty of the planning connection.

Cameron Lackpour said...

Glenn,

Yes, a Planning ad-hoc grid will work, but remember, much of the world does not yet live no Smart View 11.1.2.2.310 or whatever the latest release is.

It seems odd that two essentially identical functions have zero crossover. I know that Planning came first, and that Essbase has its own row to hoe, but still...

And while we're at it, could we pretty please have some kind of SQL data layer to Essbase so we can throw in *real* Text Measures? Just like Planning? And make them addressable through a Load Rule? Please, pretty please? A man can dream, right?

Regards,

Cameron Lackpour

Chris R said...

Cameron,

Thanks for such a great blog. This post was most useful for me today.

Appreciate all that you do and your willingness to share this information.

Sincerely,

Chris Rothermel

Bob Chandler said...

Cameron,
You outline a perfect solution to a vexing problem: inventorying Smart List usage within Planning applications. I was just about to give up when I found your post. Should have come here first...

Thanks for sharing.

Sincerely,

Bob C.

Anonymous said...

Thanks cameron for this post.
Is it possible to use the Label within a business rule to refer to a dimension member?

e.g. If I have a position smartlist with members like" ID:01 and Label :Analyst". And there is an RTP for useres to select a smartlist member.:
Can I somehow use the Label of the RTP instead of the default ID ( as there is a member with that exact text in a different dimension)?
e.g.
""->Budget->"Working" = ___;

Thomas N said...

Hi Cameron,
First of all thank you for all the contributions you made about Essbase/Planning overall the forums we can find online :)

To share with the community what I found today : for one same dataform using a smartlist, through Smartview the smartlist does not allow to select the #missing drop down label whereas through the workspace the smartlist allows it.
The only way to be able to submit #missing through the smartlist is to have the situation where : #missing drop down label = name = label (no matter the ID value, the data submission through Smartview will always be #missing).

Careful though ! Because in this situation, submitting data through the workspace will send (from an essbase retrieve POV) the ID value, so the calculation rules using the indicator set by the smartlist has to manage this possibility !

Thomas N

Salman Dasti said...

Yes you can use the smart List "VALUE" in calculations and in member formula:

here is the code in member formula: (WHere 2 is the value (Not Label):

IF ( "AC"->"NA"->Jan==2)[(Samartlist_Aircraft.AC777)];

"Aircraft Seats" = 350;
ELSE
"Aircraft Seats" = 0;
ENDIF;

Thanks Cameron for sharing such a good artocle on smart list.

Regards

Salman Dasti

Matt Varner said...

Cameron,

Great post!

I'm sure already know this, but some of your readers may not -- there is now (I think starting with 11.1.2.3) a pretty easy way to see the member/smartlist associations -- just export the Account dimension with "Administration->Import and Export->Export Metadata to File" -- and look at the "SmartList" column.

All the best,

-Matt