I keep telling myself that I’m going to do more blog posts that are short and sweet, instead of these epic 6,000 word monsters, but I’m just having too much fun. Today’s article is going to be a little bit of thinking outside the box. Outside the box – but inside the grid. This is actually inspired by a use-case I saw a Dodeca customer present on at Kscope this year.

The basic original idea was “Why not make a calendar view in Dodeca?” Those of us that are heavy in the Essbase/Excel world are used to modeling financial data, but spreadsheets are used for countless different activities. Create a workout plan. Create a list of your favorite movies – and even make a calendar.

In the context of Dodeca, a calendar, whether it be static or dynamic, is a really cool use of the tool, if a bit unorthodox. A lot of financial departments and companies have very complex but methodical financial processes, particularly around the “close period”, and keeping everyone on track and coordinated is important. And companies that have Dodeca already have a very quick and very easy way to make dynamic spreadsheets centrally available to their users without having to email around a bunch of Excel files.

For today’s post I am going to start off with a basic calendar, then absolutely turbocharge it. The user is going to be able to select a month and year from Dodeca selectors and the calendar will dynamically update. We’re going to make it so we can add comments to each cell of the calendar. The comments will be associated with arbitrary intersections of our choosing (a great feature of Dodeca comments that I’ll go into extensively in this post). We’re going to accomplish this using the built-in Dodeca comments functionality. Along the way, I’m going to show off some of the power and versatility of Dodeca comments and use practically every option available.

To be clear, this is going to be a pretty technical post (even by my standards), but I want to emphasize at the outset that the techniques here aren’t limited to this “dynamic comment calendar concept”. In particular, the way that I’m going to setup and leverage comment ranges and the Excel OFFSET function will be applicable to many contexts. These techniques are very powerful and can provide the basis for powerful, rapidly developed solutions that users will love.

Dynamic Calendar in Excel

The first thing we need to do is create a dynamic calendar in a spreadsheet. In this case, I want to be able to have two input cells (the year and and month) and then generate a properly aligned calendar based on that. Like this:

A dynamically generated calendar in Excel

I thought when I originally Googled for something like “dynamic calendar in Excel” there would be a million hits, some of which included a ready to go Excel file that I could steal borrow be inspired from. After a bit of searching I found an incredible step-by-step guide on setting up the formulae for a dynamic calendar in Excel that I started with. You can see in the above screenshot that I’m on a cell that’s blank at the moment (based on the month/year) but actually has a formula in it (as with the other cells). Here’s a peek under the covers at what the other formulae look like:

Formulae used for dynamic calendar

This is a great start to the calendar: we can now get a properly laid out month for any combination of year and month. If you have your Dodeca hat on then you should already be thinking: “Hey, we can just put selectors in for the month and year!” and you’d be on the right track.

Now I’m going to tweak the layout a bit to accomplish the following things:

• Add four cells below each calendar entry that will eventually have comments in them
• Selectorize/tokenize the inputs (to create [T.Year] and [T.Month] that will be filled in with user selections
• Add a title to the calendar that is dynamically updated
• Add a row to help with offsetting cells (row 4 in the below screenshot, I’ll explain in a bit)
• Add a column to help with identifying particular comments (column J in the screenshot, again, I’ll explain in a bit)

Here’s how things are looking now:

Formatting the dynamic calendar a bit

A Brief Aside: “Dev Mode” Selector Values

I want to point out a small but useful technique that I’m using for the selectors to help with development. It’s very common in Dodeca (and Excel of course) to have cells that depend on other cells. For example, in the above screenshot, the title of the calendar is just a concatenation of two other cells (the month and year). It’s also pretty common to have formulae that expect the input value to be something particular, such as a number or the name of a month. For example, I can generate the full text month name of a particular date by using the formula =TEXT(DATE(2015, 2, 1), "mmmm"). In this case, the DATE function takes three parameters (year, month, day), and the TEXT function takes a date value and a formatting code (“mmmm” means full month name). If one of the parameters to the date function, however, is not a number, then the function fails and anything dependent on that cell will also be invalid.

Frequently the process of developing a view in Dodeca is to build a normal spreadsheet using some temporary/dummy values, then the very last thing we do is swap in a selector token such as [T.Month] – but in doing so, we can break formulae that are expecting a number. So I want to point out a small but useful technique I am using that helps me get around this issue without having to constantly change tokens back and forth.

Check out the following screenshot:

“Dev mode” selectors

I have my selector tokens in column B (cells B1 and B2). And next to the month token in cell C2 I have an Excel IF statement. What I’m saying is if the contents of the cell are the token name itself, then evaluate to [in this case] the value 12 (December) for testing purposes, otherwise, just use the value in the cell. And the next cell over (D2), I have my normal formula to generate the full month name, but rather than trying to process the token value over in cell B2, it uses the “sanitized” value in C2. I also did this for the [T.Year] token in cell D1.

With this small technique, I can develop my report as normal and keep it tokenized, instead of having to look at something like this:

This is what it looks like without “dev mode”

Again, it’s a small technique but I just started doing it and I have to say that it really smooths out the report development iteration cycle for me.

The Final Template

Now that things are dialed in formula-wise, the final steps to clean this up are pretty standard by now: hide rows and columns, turn off gridlines, and turn off row/column headers. These steps aren’t always performed but they make sense in this case. Now the template looks like this:

Cleaned up calendar template

A Refresher on Dodeca Comments

I’m going to skip showing the basic view options and the selectors for this report since I’ve covered that numerous times in the past and it’s pretty straightforward for this report. Instead, I’m going to jump straight in to the comment configuration for this view and going into detail about how and why I set it up the way I did.

First, let me do a very quick refresher on comments. In Dodeca, commentary is a first class citizen with extensive configuration options. Every comment in Dodeca is associated with an arbitrary set of key/value pairs of our own choosing. Let me strenuously emphasize this: comments in Dodeca are defined by a unique combination of keys and values (Scenario = Actual, Year = FY16, Account = 1001) – and notably the key/value pairing exists irrespective of a particular view or Essbase cube. Further, our comment address/intersection is not forced or limited in any way by the dimensionality of the cube. In fact, the view I’m building here doesn’t even have an associated Essbase cube at all.

That said, for this calendar example, the address (key/value pairs) I want to use for each comment is pretty straightforward:

• Year: the year the user chose
• Month: the current month
• Day: the day (cell in calendar)
• Row: the offset in a given calendar cell. The way I designed this template is that each cell has four rows, not including the row with the day of the month. Each cell, therefore, is actually four individual comments. We will add an arbitrary key named Row, with a value of either 1, 2, 3, or 4, to uniquely identify the row for a given day.

When we setup comment ranges in Dodeca, one of the critical things we setup is to generate the key value pair string. For example, the key value pair string for a comment in the row of a cell for December 7th, 2016 would be like this: "Year=2016;Month=December;Day=7;Row=1" (in other words, it’s Key=Value pairings, with a semicolon delimiter). It’s up to us (the report designer) to generate this string somehow, whether it’s with a formula or whether it’s in a cell that we then reference with a formula.

Very often when I setup comments (and as I did in a previous comments example), I just generated the key/value pair string in a column of cells using an Excel formula, then referenced those cells. I could certainly employ that approach here. How this might look would be to insert 7 columns (one for each day of the week), and then a number or rows to the template, put in the proper formulas, and then setup the comment range accordingly (and of course hide all of these rows/columns on the final view).

Determining Comment Addresses with OFFSET & Friends

I’d like to do something a little more elegant, though, without having to insert a ton of rows and junk. I’m going to use my good friend the Excel OFFSET function. Remember the extra row and column I put into the template earlier (shown here as row 4 and column J):

Understanding the “magic” areas of this layout

I’m going to use these to help me generate the address for each comment. Let’s think about this for a moment. We need a value to generate a unique address for any given cell in our calendar. For example, let’s say I put a comment into cell F11 (the third comment cell under December 1st). Relative to this particular cell, it’s very easy to determine what its year and month are (since these are located in fixed locations). Getting the number of the day of the month is a little trickier. For this cell, it’s three rows up and in the same column (the value of 1 in cell F8). Additionally, the unique Row number is 3, which in this case is 4 columns to the right.

Let’s start with how we can determine the day number for an arbitrary cell. Before we get to that, even, let’s start even more simply with how we can get the offset value (in the above screenshot, the pink colored cell containing 4, located at F4):

OFFSET(@ACell(), 0, @ColL(@CCol())$4

Say what? I just used a combination of native Excel functions and Dodeca functions. Dodeca has many functions that we can use in contexts like this and in Workbook Scripts, and we can mix them interchangeably in this context. The @ACell() function is very common, and it gives us a reference to the active cell being evaluated. Additionally, @ColL() gives us the column letter for a given column, and @CCol() gives us the numeric value of the current column.

Put all together, when this formula gets evaluated at, say, cell F11, we’re going to plug the current cell’s address into the OFFSET function, offset it by zero rows, and offset it by the number of rows indicated in the cell located above the current cell, in row 4. In other words, I’m using those helper values in row 4 to tell me how many columns to the right I need to offset for a given cell in the calendar. For example, in column F, the location of the row ID values is four columns to the right (in column J), and therefore I have a 4 in cell F4. In column G, the row ID values are only three columns to the right. It’s a little tricky but in essence it’s really not unlike the dynamic rolling quarters example I blogged about some time ago.

Now, nicely enough, the values in column J (the row IDs happen to also tell us how many rows up the day of the month is from a given cell. For example, in cell F11 (orange), the numeric day of the month is three rows up, in the same column. So we can use OFFSET again here to help us evaluate the day of the month for any given cell in the various comment ranges.

Now that we know how to generate the year/month/day/row for any given cell in the calendar (using a combination of OFFSET and intrinsic Dodeca functions), we have everything we need to generate a key/value pair string (remember, the format we want is something like this: "Year=2016;Month=December;Day=7;Row=1") for any given cell, which we will use in our comment configuration:

Formula to generate key value pairs for comment identity

The rest of the formula is just generic string concatenation and referencing named ranges I created for the year and month. I’ve said it before and I’ll say it again: OFFSET is powerful. And with respect to Dodeca, it’s generally essential, not esoteric.

My friend, the OFFSET function

Advanced Comment Range Options

Now that I’ve hopefully given you enough of a primer on the byzantine clever way of generating the key/value pairs, let’s take a look at the entire comment configuration for the sheet:

Comment Range configuration for dynamic comment calendar

Let’s walk through the options I have changed from defaults (bold) or that are otherwise important:

• Caption: this is just the identifier for the particular comment range on this view. We can have multiple comment range definitions in a view; in this case, due to our very clever setup, we are going to get all of our comment functionality defined in terms of a single defined comment range (not to be confused with the actual named range on the sheet itself).
• AllowAddString/AllowDeleteString/AllowUpdateString: These options control whether comments can be added, deleted, and updated. For this view I want to be able to do all three things, so these are all True.
• InCellDisplayPolicy: this is MostRecent. Dodeca has support for threaded comments and options surrounding the most recent comment. For the sake of simplicity we are going to say just to show the most recent comment always (so that our comment implementation is more akin to “what you see is what you have” rather than worrying about revisions to comments and such).
• EditPolicy: Dodeca actually offers a dedicated editor for comments if we want it, but in this case I want to just edit them on the grid itself, so this is EditInCell.
• ThreadPolicy: Again, there are extensive features regarding threading comments (like a discussion forum) but we’re going to keep it simple here for now with OneCommentOnly.

Next up is this interesting SequentialRanges option. Recall that we can have multiple comment range definitions in a view. But what if I want to have one comment range definition that can handle multiple defined ranges in my worksheet? I can just turn this option on, then just define names on the sheet using the Address I define as a prefix.

In other words, check out where my first named range is on the sheet:

One of the comment named ranges on the calendar

Notice that it is called Comment.Range.1. Each row in the calendar table has a similar name (Comment.Range.2, and so on). By turning on the SequentialRanges option, I can have this single definition apply to all of the different named ranges, so long as I name them with the proper suffix.

Again, there are many, many ways that you can use Dodeca to setup a view such as this. But my goal for the way I did it here is that I wanted the simplest possible spreadsheet and simplest possible definition of comment ranges. So this is a really powerful option that saves me from having to make six almost identical comment range definitions.

Regarding the Indicator options, I decided to spruce things up a little bit instead of the normal Excel comment indicator (a red triangle in the corner of the cell). In honor of my college football playoff bound University of Washington Huskies, I thought it’d be fun to go with a nice shade of purple for comments. The other minor options regarding the indicator should be pretty self-explanatory.

Last up is the Linking category of options. by now you know that the Address field in this case contains my prefix for named ranges (if SequentialRanges was set to False, then the name in Address should actually correspond to an exact name on the sheet). We’ve already covered the KeyItemsString value, so the very last option to talk about is this KeyItemsConditionString.

The KeyItemsConditionString is a chance for us to define a formula similar to the KeyItemsString, but it’s evaluated to determine if the comments should be enabled on a given cell at all. This is entirely optional but it’s very handy here. You may have noticed that due to the nature of a calendar, there are several cells at the beginning and end that are likely to be blank. If we just made it so everything in a given comment range was  a comment, it’d be bad, because we would have errors for cells without a numeric day. This option let’s us conditionally turn off comments. Check this formula out:

Our condition string for seeing if comments should be active for a cell

It’s pretty similar to part of the other formula for the comment itself, but we’re just checking to see if a given cell has a number for the day of the month. If it does, then we’ll allow comments in the four cells below the number. If not, it’s blank, this formula will evaluate to False, and the user will not be able to enter comments in.

Putting It All Together

Alright, we have a view, a template, selectors, magic formulas from the internet, and more. Let’s build the view and see what happens:

Running the view after having selected December/2016 and building

Things are looking good! Let’s type in a few comments to test things out, like on Christmas and New Year’s Eve. Based on the view configuration, as soon as I hit enter or otherwise leave a cell, the comments are sent back to the Dodeca repository, so there’s no Save button or anything we need to press. Now to test out the selectors, let’s change to the next month/year (January 2017) and take a look that the calendar updates properly:

Change to another month/year

Sure enough, everything looks to be in the proper place and was updated. When I flip back to December 2016, I see that my comments have loaded in as expected.

Summary

This was a very technical post that covered a lot of ground. It focused on a bit of an unorthodox report type,  but it was a really (in my opinion) fun example of the power and flexibility you can get with Dodeca and its spreadsheet-based paradigm. Besides getting to use some everyday Excel functions to build a dynamic calendar and make it truly dynamic with some selectors, we’re able to use the extensive comment range options to setup a commenting system on this sheet that works exactly how we wanted it to, and didn’t even have to write a single line of SQL code. These techniques can and are used every day by many others to facilitate business processes around the globe.

As always, please don’t hesitate to reach out if you have any questions or comments!