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.

Store Reorganization using Dodeca

The idea behind this report is that a company is growing quickly and a new division must be created. This report was originally created by Michael McLaughlin. You should visit is excellent Dodeca Blog. I've taken his original report and made it completely dynamic and solely using MDX to return data. There are no sheet formulas or Essbase Retrieve Ranges. As with most things in Dodeca, there are several ways to accomplish the same task. This is merely an exercise to show the power of using MDX.

The outline is shown here with emphasis on the Location dimension. We'll use this dimension to allow the user to rearrange the divisions.

The first step is to set up the selectors. I created selectors for Departments, Location, Measures, Periods and Scenario.

Then I created the Selector Lists. Most of them are straightforward. For the Scenario dimension I allow users to select Actual, Budget or Forecast.

For the Locations list I use MDX to retrieve the list of stores. I also changed the TokenValueItemFormat to [{0}]. This will allow each value to be wrapped by square brackets for use in an MDX script.

The store list MDX is shown here.

I use an outline UDA to create the list of Measures that the users may include in their report.

The Measures list MDX is shown here.

I used MDX to pull back the list of Years as well. All MDX, all the time...

Next I configured my selectors in the Reorganization view properties.

I added my Selectors and assigned them their appropriate Selector List. I set the Location selector as optional. I also set it to allow multiple selections.

Then I created a very simple template. I included several tokens for a report heading and added a few formatted cells that I will use later to copy into the report.

I set up Excel named ranges for the starts cells I will use to place data, formatted cells and the columns being used in the report.

Since there are no Essbase retrieval ranges in this report, the majority of the work will be done by the Workbook Script.

 

I first add the AfterWookbookOpen event and OnAfterWorkbookOpen Procedure.

 

I then added a AddDataCache method. This method will run MDX to retrieve the list of divisions in my Essbase outline.

Next I added an AddProperty method to set a DivisionOffset property. This will act as a variable to keep track of where the latest division was added.

Now I created a ForEach method which will use the DivisionList DataCache from the earlier method. The BuildDivision procedure will be called for each Division in the list.

The BuildDivision Procedure starts out by running an MDX script.

This script is how most of the data on the report will be created.

 

Notice the WITH MEMBER statements. These create members on the fly. In this case the measures will be dynamic based upon what the user selects. Locations selected by the user will be removed from the report.

Tokens and the CurrentLocation property value will be automatically replaced by Dodeca at runtime.

The DivisionOffset property will be adjusted based upon the size of the data returned in the MDX query.

The RowCount property will also be adjusted based upon the size of the data returned in the MDX query.

Next I added six CopyRange methods. These handle the copy of formatted cells in our hidden rows to the proper place on the sheet. I do this using the Excel Offset function and the property values I've been keeping track of.

Once the BuildDivision procedure has been run for all of the divisions in our DataCache, we can then run the MDX Query for the new division.

This MDX script uses the WITH MEMBER statement along with Location tokens from the selector to pull in the locations for the new division and total.

Now that all the data has been retrieved on the sheet, I used the CopyRange method to format the data for the new division.

Finally, I set the Column Width on the sheet to AutoFit since there are no Essbase Retrieve Ranges to handle that for us.

By default, the report doesn't select any locations for the new division.

Once the report is run, the user can see the existing divisions with their changes as well the new division. This report can help a user quickly see the impact to changes in the organizational structure. This methodology could be implemented in a number of places to see the impact of changes before they happen.