Dodeca Commentary Part One

This post is part one of three in a series on using commentary in Dodeca. In this post I will be showing how to set up a simple view that allows user comments.

First I set up the view template to include an Essbase retrieve range.

 

I then added a column for users to enter their comments. I chose Column F for this example.

Next I created three named ranges in the template.

 

Comments.Range is the area in which users will enter their comments.

 

ConnectionID will hold the ConnectionID for the view and will be hidden from the end user.

 

Ess.Retrieve.Range.1 is the range into which Essbase data will be retrieved.

The next part involves writing an Excel formula so that Dodeca knows how the commentary data should be stored in the repository.

 

Dodeca uses Key,Value pairs in order to store the comments.

 

For example, "Years" would be a key while "2019" would be the value. You can add as many or few Key,Value pairs together as you want to represent a single comment. In this case I'm going to add a Key,Value pair for each dimension in addition a Key,Value pair for my view's ConnectionID. That way comments from this cube will not interfere with comments from another cube if the dimensionality is identical.

The string related to Years for 2019 should look like this:

Years=2019;

This Key,Value pair will be concatenated to the rest of the Key,Value pairs that make up an entry. The semi-colon is used as a delimiter.

 

That formula is pasted into cell G9 and copied down the sheet. The formula will pick up the correct value for each key from the view.

 

Column G will eventually be hidden from the user. The user needs no understanding of how Dodeca manages comments.

Next I hid rows and columns, headings and gridlines.

In my view properties, I selected the ellipses on the CommentRanges property.

Next I clicked "Add".

 

Then I set various properties for my comment range. Notice the properties in bold have been changed from the default value.

 

Note two critical properties on this screen. The first is Address. This is where I entered the named range from my view template. This is the range in which the user will be entering their comments.

The next critical property is KeyItemsString. This tells Dodeca where to look for the Key,Value pair string related to a given comment. In this case I'm using the Offset() Excel function to find the cell immediate the right.

 

 

Finally, I did add one method in a Workbook Script. This handled putting the ConnectionID on the view so it could be used in a Key,Value Pair.

I added the @ViewPVal("EssbaseConnectionID") function in the SetEntry method. It populates the ConnectionID named range I set up in an earlier step.

Now when I run the view, I can enter comments in the Variance Explanation column. Once I type a comment and press enter, the comment is immediately saved to the Dodeca repository.

 

In Part Two, I will show how to create a view that shows all the comments for a cube on a single screen.