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:

Find

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.

Awesome.

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

EssbaseDelimitedString

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.

AutoOpen

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:

There.

Is.

No.

Code.

Required.

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.

Selecting Dodeca

A small change of plan

My original, and continuing, intent with this series of blog posts is to demonstrate the awesomeness that is Dodeca.

I wrote an outline (yes, this gibberish sadly does not spring from my fingers willy-nilly but in fact is organized, sort of) that started off with a grand tour of the Dodeca interface.

As I began to take screen shots, it occurred to me that there is a lot of functionality to cover.  I don’t think I’ve got a Grand Unified Theory of Dodeca blogging just yet – there’s so much going on.

At least initially, I’m going to focus on functionality across the three products:  the Excel Essbase add-in, Smart View, and Dodeca, and then build from there.

This post is a trip back in time to English Composition class – I’m going to compare and contrast these products.  There is a point to this – you won’t understand the genius of Dodeca until you understand the limitations of its competitors.  J

Member selections

A definition

Member selection in Excel happens when:

1)    An off-grid (classic Essbase add-in) or Point of View (POV) (Smart View) member is selected; only one member can be selected, although in Smart View’s POV manager, you can preselect multiple members and then use the dimension drop down to navigate through the selections.

2)    An on-grid row or column member is selected; one or more members can be selected, either down rows or across columns.

Okay, for those of a pedantic bent whose name isn’t “Cameron”, there are two other kinds of member selection that can happen in Excel without using the Member Select (classic)/Member Selection (Smart View):

• Member selection can also occur simply by typing in the member name on the sheet (you will likely know Scenario, Version, Year, Period, etc. dimension member names or probably your most commonly selected Accounts, Products, Regions, etc.
• And I guess you could argue that member selection can also occur as part of drilling up and down in a dimension and using keep- and remove-only functions to narrow selections.

How do I get there?

Classic

Click on the Essbase menu in the Excel menubar and navigate to Member Select:

If you’re using Excel 2007 (and the above menu screenshot is from that release), you’ll first need to select the Add-Ins ribbon, and then click on the Essbase menu.

Remember how I wrote that if you have lots of automation in Excel you’ll lose it all with Excel 2007?  Remember that cool menu?  Stuck in the Add-Ins ribbon.  Remember that cool toolbar you wrote?  Gone. 

Someone came to the rescue, but you’ll still have to roll your own for your code

The good folks at in 2 Hyperion wrote a ribbon bar for the Classic add-in.  For those of us still using Classic (I hereby give up on the full name “Classic Essbase Excel add-in”), it’s a lifeline till Oracle kills Classic.  Find it here.  Highly recommended for those using Excel 2007, but Dodeca’s still better.

Smart View

It depends

Smart View splits up dimensions between on-sheet and in-POV manager.  When a dimension is on the sheet, member selection works much the same way, i.e., click on a cell, and then begin Member Selection either via the Ribbon (Excel 2007), toolbar (Excel 2003), or Hyperion menu (2003 or 2007).

On-sheet

Ribbon (Excel 2007)

Toolbar (Excel 2003)

Menu

Remember that the Hyperion menu is also available under the Add-Ins ribbon in Excel 2007.

Point of View Manager

Click on the dimension in question’s down arrowhead, select the ellipses, and Member Selection is coming your way.

Dodeca

Dodeca differentiates between the in-built member select dialog box and customizable member selectors. 

Member select = the Classic interface with a few improvements.

Member selectors = totally customizable, incredibly flexible dimension pickers that you could write in VBA, or VB.Net, or whatever, but after you taste Dodeca’s approach, you will give up on in defeat and despair.

Ad-hoc Member Select

This is similar to the way Classic works, but, of course better because Dodeca knows whether the member select is in a row, column, or off-grid dimension and expands/limits functionality to the appropriate position on the grid. 

Just click on the member, then the toolbar button to get the Member Select button.

Member Selectors

Treeview

Simplicity itself – click on the selector box in the report toolbar.

Dropdown

Classic doesn’t know a dimension dropdown selector from its elbow. 

Smart View’s POV selector can use dropdown selectors within the POV.  Dodeca’s are easier and centrally controlled.

What do they look like?

Classic

So what does it look like?  I’ve been using it so long I can almost recite the objects from memory.

Member Select description

Welcome to 1992’s idea of User Interface design.  Time machines are always fun.

What do I mean?  Think about the down arrowheads for expanding down – replace by up arrowheads for expanding up.  It’s a logical metaphor, but try to think of another hierarchical selector that works this way other than Essbase Application Manager – I dearly miss that product, but it’s time has come and gone.  Plus and minus signs seem to be the universal drill up and drill down metaphor.

At the very upper left hand corner, there’s a dimension picker dropdown control.  This is only useful if you’ve selected a blank cell and the right dimension doesn’t pop up (this will always be the first dimension in the outline), otherwise the dimension will be whatever is selected in Excel.  Of course, Classic will let you select the wrong dimension and overwrite valid selections.  Whoops.

You can use the Find button to search for strings, but remember, no leading wildcards.

Information about a member is but a click away.

Dimensionality can be viewed by member name, generation, level name, or even by Dynamic Time Series if you’re dealing with a DTS-enabled Time dimension.

And of course there’s the option of viewing this by member name, aliases by alias table, and the option to place the selections across the rows or before the selected cell.

What’s really cool about Classic’s member selection process is that the selected members can be saved either to the server (stored in the Essbase database’s directory) or locally.  Nice if you’ve defined complicated searches.

Once the selections are made, you can move the selected members to the right hand list box. 

But wait, there’s more

Once a member has been moved over to the Rules list box (betcha never looked at the description – me neither), the member can be right clicked on and member relationship selections can occur.

Right there you can pick Children, Children and Member, Descendants, and Descendants and Member.

But it all gets powerful when Subset… is selected. 

Attribute members, ANDs, Ors, NOTs, generation and level names, patterns – the whole shebang is there.  Powerful selections can be created and then saved as mentioned before.  You can even use this functionality to figure out what Attributes are associated with a member – this is missing in the Member Information button.

But it’s a hard way to find out that Massachusettes has an attribute of Medium_9000000.  At least you can select it.

Smart View

However you get there, it’s the same thing

Whether it’s through an on-sheet member select, or in the POV, the same Member Selection dialog box pops up:

In many respects, Smart View’s Member Selection dialog box is a step forward in functionality from Classic:

• Pluses and minuses are used to drill up and down.

• Filtering of members is easier because you need only pick the member, and then choose the Children, Descendants, Level, Generation, UDA, Attributes, and Subset all from the main dialog box.
• No need to decide if the selections go in rows or columns – Smart View knows the right way.

Filter

See Attribute in the Filter dropdown?  Easier to get to than moving Market to the Rules listbox and then right clicking on it, then selecting Subset, and then finally getting to the Attributes.

But there are some things missing:

• Member information

• Attribute member selections aren’t as detailed as Classic (or Dodeca)

Member information

I don’t know how often you use this – Essbase developers can always go to Essbase Administration Services and find out all we ever wanted to know about a particular member.  For the hoi polloi (that would be your users, not you, dear reader) they’re largely out of luck.  Too bad.

Attribute selection

Attributes are selectable, but not completely.  Whoops.

EAS’ Population

Smart View’s Population

See anything missing?

Classic’s Population

See what’s not missing?

Dodeca

It’s smarter than you.  Or at least it’s smarter than me.

As I was exploring Dodeca member selection for this blog, I found out a couple of cool things:

• In ad-hoc views, member selection is modal – Dodeca knows when a member is in a row, a column, or off-grid, and allows selection appropriately.Dodeca won’t allow stupid member selections that break ad-hoc reports.

• In standard views, member selection is surfaced immediately, without a trip to a dialog box.

• AppliedOLAP spent a *lot* of time figuring out how to customize member selector dialog boxes without you writing code.  I won’t cover this in this post (what, not long enough for you?), but it will be my very next post.  I cannot get over how freaking cool this stuff is.

Modal

Off-grid

Think about this – only one member for a given dimension is on the grid.  Hence, only one member from that dimension should be selectable.  Guess what, Dodeca knows this, and the Member Select dialog reflects that.

Classic breaking retrieves

It’s easy to break a Classic retrieve through Member Selection.  In this case I am selecting the four (sort of) points of the compass.  Note – this doesn’t make sense, but Classic Member Selection isn’t going to save you from yourself.

Place Down the Sheet

Whoops, forgot to untick Place Down the Sheet.

Not Placed Down the Sheet

Nope, that doesn’t work either.

Smart View breaking retrieves

Smart View is a little better at this – it understands rows, columns, and off-grid dimension members, but it will still let you make selections that don’t retrieve.

Dodeca breaking retrieves

Erm, you can’t do it.  Remember how I wrote above that Dodeca knows that a dimension is off-grid and thus does not support multiple selections?  Well, that’s the trick – it doesn’t let you select more than one member and it doesn’t break.  Genius, if you want my opinion.  Or even if you don’t.

On-grid

It’s the same as Classic, with one improvement.

Did you spot them?  Plusses and minuses to expand/collapse the hierarchy. 

No progress?  There are those who would call Classic’s Essbase Member Selection dialog box perfection itself.  And Dodeca improved upon perfection with the +/- controls.

Member Selector

Remember, this is different than plain old member select from an ad-hoc retrieve.  Member selectors have a lot of functionality that you, the developer, customizes as part of a report.  More on this in the next post, I swear.

There it is, right there on the right (position customizable – hey, everything’s customizable).  And the dimension’s available any time you want.  No more clicking on a member and then selecting a menu/toolbar/ribbon and then getting to the dimension to switch from member A to B.

In the meantime, feast your eyes on the below.

             

When I write that there’s surfaced functionality, I mean that without clicking into modal dialog boxes the following functions are instantly available:

• Expand/Collapse

• Find
• Dimensions (depends on how the View is set up)
• Swapping between dimensions

All without writing a line of code.

Conclusion

Oldie but goodie

For those of us who know and love the Classic Essbase Excel add-in (there, I wrote the whole thing), member selection is pretty nice, if somewhat old fashioned looking.  Eh, looks aren’t everything.

It’s pretty easy to break reports, so be careful.

Newer, but curiously missing some functionality

Smart View keeps on getter better and better, but there are some big holes out there such as member information and a decent attribute member select.

Retrieve intelligence is better, but it’s still easy to break ad-hoc retrieves.

Why use anything else?

Ad-hoc analysis isn’t even Dodeca’s raison d’etre, yet it does it flawlessly with Classic’s functionality and none of Smart View’s holes.

Even more importantly, its intelligence around dimension placement means that you can’t break an ad-hoc report through a member select.  This is a Good Thing.  You haven’t lived (if you can call it living) until you walk a user through why the product breaks their retrieve. 

What’s coming next?

Think of this as the first course in a multi-course meal.  You’ve had the starter, but the main plate is to come.  Hopefully your salivary glands are hard at work.

We’ve now set the baseline; the next post will (soon, I swear, just a week, maybe a little longer…) get to the interesting stuff – all of the different ways to customize member selectors in Dodeca.