Oftentimes when I am demonstrating or teaching aspects of Dodeca to people, they are amazed at the sheer number of options and configurations that are available on a view. Fortunately, I am able to tell them that yes, there are many, many options – and they are there if you need or want them, but they won’t get in your way. The defaults are very sensible and getting a basic Essbase-based Dodeca view running is incredibly easy.

Another thing to keep in mind is that for the most part, the extreme amount of options and flexibility we have on a single view is often available to us in lieu of code. So, tasks that typically required some non-trivial amount of VBA code are now completely code free. When we need some advanced functionality that isn’t available out of the box, we can use Workbook Scripts, which is an event-driven scripting technology that is particularly well suited to working with spreadsheets and the data contained in them.

That all said, today I want to walk through a bit of a cross-functional example that starts with a very typical Dodeca view based on an Essbase retrieve range, then enhance it to give our users the ability to zoom in on the different time periods in the view without having to rebuild the view. So we’re going to blur the line a bit between static and dynamic reports, and our users are going to enjoy some additional flexibility and convenience with regard to their reporting (and keeping users happy is always a good thing, right?).

Imagine the following fairly typical view based on Essbase data:

A fairly typical Dodeca Essbase view built around a single retrieve and some light formatting

In the preceding screenshot, you can see that we have a single retrieve range from Essbase with Scenario, Years, and Time on the axes. As with many views in Dodeca, since we can put the retrieve(s) anywhere we want and add convenience formatting, we have the report title, location, and selected department in the upper left corner.

Also note that the row and column headers and gridlines have been turned off. This is easily accomplished via setting a couple of options on the view configuration. For reports like this where the headers don’t really add much, it can really clean things up.

To get a sense of what the Dodeca template looks like, however, let’s go to the view template designer and turn on row and column headers and unhide any hidden rows:

The template for the view, with rows/columns unhidden

Things are a little more interesting here. Notice that column A is a “template column” that was previously hidden, and row 6 was also previously hidden. This is a very typical technique where the contents of the row are needed to perform the Essbase retrieve, but we don’t actually want to show them.

Template Columns & Template Rows

I’ll get to the notion of template columns and template rows in more depth later in this article, but as a brief aside, I want to explain where we’re headed and what’s going on. As the name might suggest, a template column (or row) is often used as a prototype for copy and paste or other grid expansion operations, particularly when there is a dynamic number of rows to be built. Several facilities in Dodeca can be configured to look to a template, such as a defined range with an arbitrary number of cells, and essentially copy it for new rows/columns that are being generated. This allows us to easily style up dynamic content with the exact style, cell formulae, spacing, and other attributes that we want, without having to resort to code or other shenanigans.

Templates are not limited to a single row/column, either. They are frequently wider/longer, allowing us to easily generate dynamic ranges with complex formulae and other features. In this case we are just going to use a single column as the template.

The last thing I want to point out about row and column templates is that you will often find them above or to the left of the content that is going to dynamically grow. For example, in the grid above, the content is going to possibly grow to include more columns, so I have the column template to the left of all of that. This makes my life a little easier since I know that the location of the column template isn’t going to move around on me. Because rows and columns that we hide on the view template will stay hidden (but are still completely functional), I can just hide the template and not have to worry about it.

Now Back to Our Template Layout

Just to make things clear about the Essbase retrieve range, here it is exactly on the template (Note the defined name of Ess.Retrieve.Range.1):

The retrieve range for the template highlighted

Again, the top row with the POV members is going to be hidden because we are going to format those more nicely in the upper left. Our column template also gets a name and defined area:

A range (Column.Template) that will be used for formatting columns

The column template isn’t going to have Essbase data retrieved directly in to it, as you’ll see later, it’s purely for formatting purposes. Notice that it also has a defined name of Column.Template (so that we can refer to it easily in Workbook Scripts and other places that might need it).

That’s actually just about all we need for the view template itself. The rest of what we need to configure is the view properties and a workbook script to help with some of the dynamic formatting that we want.

There are a few options that we’re gong to use on this report that I haven’t talked about before, but are going to come in very handy. Check out the options I’m using in my Behavior – Essbase category:

Special options that we will look at: AllowEssbaseOperationsOnRetrieveRange and AutoAddRetrieveSubRanges

Of note, I have AllowEssbaseOperationsOnRetrieveRange set to True and AutoAddRetrieveSubRanges also set to True. The first option is where I can tell Dodeca that yes, I want users to be able to interact with a given Essbase grid on the build view. By default this is off (we’re turning this on so that users can double click on a time period to expand out to the children time periods on the built view. I just need a couple of other options set to round things out:

Additional options for Member Drillthrough (note allowed dimensions and cell double click policy)

Besides some of the UI things I set, the main options to note are the Member Drillthrough options. This is where I can configure the exact behavior for double clicking on a member on the grid. Nicely enough, I can even specify the exact dimensions that a member can drill into. Because I only want to let the Periods dimension be expandable (and not others), I simply specify that dimension name here and now the user will only be able to expand that dimension.

Our New Best Friend, AutoAddRetrieveSubRanges

Next, let’s talk about this AutoAddRetrieveSubRanges option. This is a really, really fantastic option because of all the amazing utility and convenience it can provide to us. What this option does, is that when it’s set, Dodeca will retrieve Essbase data, and then automatically create new subranges (defined names) within the overall retrieve range that outline exactly where the rows, columns, POV dimensions, and data reside.

Whoa.

Think about this for a minute. This is particularly poignant in the context of what I might call “Configuration over code”. Let’s say that we weren’t using Dodeca for this report. Let’s say that we had an Excel book that we wanted to automate with some VBA. So we write some code to connect, cycle through a range, retrieve from Essbase, format the data, put it in the right cells, clean up the connection, and more. Then maybe we need to write some more code to figure out which axis is which (row/column/POV/data). That’s a lot of code to write. That’s an insane amount of code to write considering that we can have this behavior out of the box with a couple of checkboxes in Dodeca. Just to hammer this home, let’s take a look at the effect of turning this option on for a typical Essbase retrieve.

There are four ranges that are going to be added automatically to the view when it is built. Here’s the range that gets created automatically for the POV members:

The effect of auto add sub ranges (Page Header)

And for the column headers:

The effect of auto add sub ranges (Column Header)

And for the row headers:

The effect of auto add sub ranges (Row Header)

And for the data itself:

The effect of auto add sub ranges (Data Range)

Before getting in to the workbook script that is going to tie this all together, I want to show the look we are going for. The report will nominally built to show quarters and the full year value, but we want the user to be able to double click on a quarter and expand the report out (via a quick retrieve to Essbase) to the the months in that quarter – all without having to rebuild the view itself. Double-clicking on Quarter 1 should make the report look like this:

Result after double clicking on Qt1 (children are shown)

A Little Workbook Script to Tie It All Together

You might be wondering why the automatically generated range names were so important earlier. Well, it has to do with the workbook script that we are adding to this view. You see, one thing that we need to define for ourselves for this blend of static/dynamic view is how the newly drilled members should be formatted. And as it turns out, my requirements for this report are that the color of a header column should be different depending on whether it is a period/month, quarter, or the whole year.

Workbook scripts (WBS) are event driven groups of actions that can be added to a view. In many ways, they are to Dodeca what VBA is to Excel. That said, they are also meant to be more powerful, flexible and maintainable.

In the world of Essbase and Excel programming, there are very many actions and activities that revolve around iterating through rows and columns of cells. Sometimes these activities need to operate on a whole range, sometimes on every individual cell, sometimes it depends. It’s these types of activities where WBS really shine and our automatic named ranges are going to come into play.

As it turns out, the list of columns that we need to dynamically format using the WBS exactly coincides with the column headers range that was automatically added to the Essbase retrieve range.

What I’m going to do now is step through the entire configuration of the workbook script so you get a sense of how they work and some of the features they offer.

Properties

Remember that I wanted to have different colors depending on the level of the time period? Let’s setup properties on the WBS so I can easily configure these. Every WBS can have its own properties, which are basically like constant variables (in the world of programming). In the following screenshot, in the Properties section of the WBS, you can see that I have defined three different properties (ColorLevel0, ColorLevel1, ColorLevel2) and defined them with a particular color each:

Overview of Workbook Script (WBS) for the view: Properties, Event Links, and Procedures

Isn’t that kind of interesting, actually? I didn’t have to specify some hexadecimal value or anything – WBS inherently knows about a property type that happens to be a color, and it even pops up a color picker dialog when we want to configure them. Nice.

Event Links

Workbook Scripts are event-driven. Essentially this means that we can attach behavior to wherever we need it in the view lifecycle. For example, for this script I need to put custom behavior in for when the workbook opens (to set some colors), and then some behavior that fires any time that a retrieve operation is performed in the book (e.g., when the user double clicks on a time period).

You can see the two events I am using in the previous screenshot. I have procedures specified for the AfterSheetRetrieveOperation and AfterWorkbookOpen events, that will execute the methods OnAfterSheetRetrieve and OnAfterWorkbookOpen respectively.

Procedures

This is the last thing we need to make it all work, and this is really the crux of the script, where we actually do something. Let’s first look at the OnAfterWorkbookOpen procedure. We’re going to use this event to initialize color values in the workbook. You see, one of the ways that Excel works with colors is that there can be a fixed palette where an integer refers to a specific color (it’s also possible to create colors arbitrarily, but in this example it’s actually more useful to refer to colors by index). Referring to colors by index is going to be really useful for us because we are actually going to just take the level of an Essbase member (0 = Month, 1 = Quarter, 2 = Total) and use it to figure out the right color. We definitely don’t want to use the default color palette because this would have us end up with red, green, and blue.

What we can do, however, is set arbitrary colors for indexes. Therefore, when the workbook opens, we want three methods to fire to set a color index for each of the variables we defined earlier. In the following screenshot, notice the three SetColor methods. You can ignore the first seven rows of the method (the rows containing SpecifySheetBy, SheetSpec, Address, so on). These are part of every WBS method but oftentimes we don’t need to set them (we’re going to use them in a moment for the other methods, though, so stay tuned).

The only configuration for the SetColor method we need to concern ourselves with right now is the Color and the Index. Each SetColor method is going to set a different color index (1, 2, and 3) for one of the three color level properties we set. The @PVal (property value) function references the respective property value for us.

Note that for technical reasons I decided that the color index to change for a given level would be that level plus one. So the color index for members at level 0 is 1, level 1 is 2, and level 2 is color index 3.

Setting color index values for the sheet based on properties

Where the Magic Happens

We only need to set the colors once when the workbook opens (as opposed to after every grid operation or more often). Now we can turn our attention to the final piece of the puzzle, which is where we are going to dynamically format the newly expanded columns, using a color index we set, processing across a range that is automatically added to the sheet for us, dynamically formatting things based on our column template.

There are just two methods that make up our OnAfterSheetRetrieve procedure. The first is to set the cell background color. This is the background color for the header cell in a given column and is dynamic based on the level of the member. The second method is where we copy the column template over.

Set the Column Background Color Dynamically

Let’s walk through the method to format the column header color. Unlike our earlier SetColor operations, we are going to adjust some of the base method properties. We’re going to specify the Address and CellByCell options:

Applying a fill color based on the level of the header of a column

The Address value determines what range the method operates on. Turning on CellByCell means that the method will be called iterative for each cell. Again, I mentioned this earlier but it bears repeating this. This configuration is all in lieu of VBA or other code. Another way to look at it this is that what would typically be many nested FOR loops and conditions in code is now just a couple of simple configurations that we can optionally use.

That all said, the real magic in this method is the FillColor. While we could specify an arbitrary color here, we are actually going to make this dynamic based on the level of the member being processed. We can accomplish that with the following code:

=@MbrLevel(@ValueText(@ACell())) + 1

The function names preceded with @ are all native WBS functions. They typically offer us very handy behavior when it comes to processing things with WBS methods. This formula will take the text value of the active cell, then find out its Essbase member level, then add 1 to it. This lines up with one of the color palette index values we set earlier.

If I wanted to translate the entire method to English, I might do it as the following: please process over every single cell in the range Ess.ColumnHeader.Range.1, then set the fill color to the color palette index that is the level of that member name plus 1.

Copying in the Template Column

Now that we have the background cell color set, we can turn our attention to copying in the template column. We mostly just want this for the formatting, and indeed, we’re going to only copy the format from the template column. This will bring in the numeric formatting we want background color, and borders.

Let’s take a look at the method definition:

Copying the template range based on the current cellThis time we are using the CopyRange method. As with before, we are going to process over each cell in the column header range, cell-by-cell. The CopyRange method has a few things to set: the source range to copy (our defined range Column.Template), and a paste type (Formats only, as opposed to values or other types of pastes).

The PasteRange is the most crucial element in this particular method. You may notice from the screenshot that it’s a bit of a doozy. I won’t explore it in exhaustive detail, but I want to point out that basically what’s going on is that we are iterating over each cell in the column header, and we need to specify the column range to paste the formats of the column template into. The address for that column range is therefore based on the address of the current cell, but is below it and has a certain determinable height (i.e., the same height as the column template itself). If you’re interested in the complete explanation of the formula, please contact me and I’ll do a follow up post.

All Together Now

Alright, it took a little while to walk through, but we made it. We built a normal view template based on some Essbase data and selector values (a bread and butter view for Dodeca, if you will) with some light formatting, but then decided that we wanted our users to have a little bit of advanced functionality to dig into one of the dimensions a little further. So we turned on the ability to zoom on members in just a particular dimension, then added some workbook scripting to apply some dynamic formatting for us. Although we had a couple of relatively “dense” expressions in a couple of workbook script methods, it was all pretty logical – and perhaps more importantly, is much more maintainable and less complex than the equivalent VBA would have been.

Now when the user opens up and builds the view for the first time, they get this:

A fairly typical Dodeca Essbase view built around a single retrieve and some light formatting

And a simple double click on Quarter 1 will change the report into this:

Result after double clicking on Qt1 (children are shown)

And that happens all without having to go to a different view or rebuild the report. I hope you found this example with advanced view options and some custom WBS useful, and as always, don’t hesitate to reach out with any questions.