Want to know The Truth About CPM?

28 May 2010

The Mighty Listbox and Treeview -- part 1

The best member selectors you’ve ever seen

What, he’s still going on about this?  How much can anyone write about member selectors? 

Oh sure, Dodeca’s in-built ad-hoc member selectors are the best.  And yes, you have to admit, may it ever be so reluctantly, dynamically driving comboboxes through delimited lists, API queries, report scripts, and a merger of SQL and Essbase dimensions is pretty intriguing, but more?

Oh yes, more, because those other two posts were but mere preludes to two coolest member selectors:  listboxes and treeviews.

As always, remember, these dimension pickers/member selectors/call them what you will are all code free, unless of course you want to wrap code around it.  Try doing that in Classic, i.e., the Classic Legacy Old-Fashioned Same-As-It-Ever-Was Excel add-in, aka CLOFSAUEWE, aka Clof-Saw-You, or even in Oracle’s latest and greatest client tool, SmartView.

What do they look like?

The path to member selector nirvana is the same as the ‘umble ComoboBox - via the Dodeca toolbar.  Just click on the dimension control.  Note that Dodeca shows a default “Select a market” message as nothing has been selected yet.  Of course, this message is configurable, as we’ll see shortly.

Starting off simple with Listboxes

Remember, the UI to a listbox is, well, a list within a box.  And that isn’t terribly exciting.  But don’t forget that all of those ultra cool ways of populating a combobox apply to Listboxes as well.  Beauty is only skin deep, character goes to the bone.

Names, Aliases, and Alias Tables

If the dimension has both member names and aliases, these buttons flip between the two.  Also, if the Essbase database has multiple alias tables, the table can be selected on the fly.

Top level TreeView

This is where Dodeca’s member selectors get interesting.  An Essbase dimension of any size is almost always (dare I say always) a hierarchy (For those even more pedantic than I, all dimensions are hierarchical by definition, I mean a more than a two generation hierarchy)Dodeca does an excellent job of displaying them in an easy to navigate TreeView control.

And here is the Market dimension from My Very Favorite Essbase Database In The Whole Wide World, Sample.Basic, aka MVFEDITWWWSB, also aka meph-edit-wysb, in all of its simple glory. 

TreeView’s buttons

Big deal you say – a treeview is a treeview is a treeview.

But as always with Dodoca, even the out of the box controls have high functionality.  See all those buttons at the bottom of the treeview?  Lots and lots of functionality, all there for the taking.

Expand All/Collapse All

Just like the button text states, these modal buttons expand to all descendants/collapse to one generation above whatever is selected, e.g., East or Market collapses to Market, New York collapses to East, West, South, and Central.

Expand Branch/Collapse Branch

Same as Expand All/Collapse All, but limited to a single branch of the hierarchy.

Show Selection

Expands the TreeView to the selected member if the hierarchy has been collapsed.

Cool little doohickey that doesn’t have a name/I shall call it the Member Options control

Did you catch that little downward pointing arrowhead all the way to the right of the Show Selection button?

Member Tips

With this on, hover the mouse over a member name and you see all kinds of cool information about a member including member name, alias, its shared status, and any UDAs that apply to the member.

Member Information

One of the many things I love about Dodeca are the little surprise-and-delight features.  Member Info you say…

Ta-da, more than you ever thought you could possibly know about the member Oregon:


Not much use in Sample.Basic’s Market dimension, but think about your really, really big Product dimension in that mighty ASO reporting cube.  Nice to have, eh?

Tabbed dimension selector

If you have more than one treeview/listbox selector open, you can tab switch between the two.

Pin or Auto Hide

As with seemingly everything in Dodeca, there are options.

Want the member selector to stay firmly in view?  Pin it up.  Want it to slide away to give you more space? Select Auto Hide.

When the member selector is hidden, simply hover over the dimension name and it will reappear.

How do I populate them?

You’ve had the sizzle, now let’s take a bite of the steak.  Vegetarians/vegans can instead have a mouthful of tofu.  I prefer the ultrafirm Japanese tofu – everything else is a bit too slimy for my taste.  I digress, again.

See the previous post on The Humble ComboBox for an definition of a Selector and Selector List.  This is the beauty of a continuing blog series – they build upon each other.

Selector Lists


Before Dodeca can display a selector, it must first have a selector list.  We’ll start with the simplest – a delimited string.  Remember how I wrote “no code required”?  Here it is.

Okay, there is a little bit of code – I need to type the word “Market” into the DelimtedString property

Edit the View property sheet and change the selector.

And we see the default dimension.  Not one line of code – one word of code.  Powerful enough for you yet?

Hmm, no description and guess what, this report is only valid for the West, and the dimension picker ought to open when the View is selected.


Default message and It’s The Law of the West

And here is the View with a limited Market hierarchy and a default message just for the West.  By the way, the Market TreeView pops up automatically.  Your choice (as always with Dodeca), I just decided to set it up this way.

I’m going to go on again about the coolness of this, just in case you weren’t paying attention.  Here’s the key thing:

Finally a phoneticism (is that a word?) that sounds half-way decent – TINCR.  Remember that TINCRing with Dodeca = awesomeness.

Think about how much code you’d need to do this in Classic or SmartView.  Think long and hard.  Funny, all I can think about is Dodeca.  That and actually having some free time because all of the code  I don’t have to write.

This post’s conclusion

And that’s where I’m going to stop this post because:
1)    What, this isn’t long enough for you?
2)    The next subject of combining Essbase, Dodeca metadata, and SQL to build report and user specifc METAREAD filters without ever touching Essbase.sec ,gets a little, mind, just a little, involved and I want to keep you focused (Mum, Glenn, and one or two other sad individuals whom I’m not related to).
3)    It’s so freaking cool, it really deserves its own topic.  Maybe it’ll be a little shorter.  Maybe.
A plea for feedback
I’m not getting a lot of comments on these Dodeca posts.  Is there interest in this?  I’m enjoying myself with this.  Anyone else?  You can always reach me via LinkedIn if the comments section isn’t your style.


Unknown said...

I think it's great stuff. We own Dodeca, but this helps me get more familiar with it and gives me more ammunition when I go to sell it to the users. The Applied OLAP folks can talk about how easy it is, but you're actually showing it with this series. Hopefully it's helping those on the fence realize how powerful this product really is.

Anonymous said...


I enjoy these posts, even though we don't own Dodeca. It's always interesting to see cleverly designed products.

Question: is there a way to see the associated attributes (not UDAs) of a member--i.e. Package Type for Product?

Cameron Lackpour said...


I am told that Attribute info is in beta now and should be available by the end of next week (that would be 11 June 2010).

It is as if AppliedOLAP anticipated your every desire.


Cameron Lackpour

>>even though we don't own Dodeca
^^^ Surely only a matter of time? :)