Dodeca Commentary Part Three

This is part three of three in my series on Commentary in Dodeca. In this part I will focus on the double-click event that opens an adhoc view. The action is done completely with workbook scripting.

First I created an event link to capture a cell being double clicked. The first method I added to the OnCellDoubleClicked procedure was ExitProcedure. I added this method first because I don't always want the double click event to open a view. I only want it to open the view when the double click happens in a valid cell. I defined that valid cell using an Excel formula and the Dodeca @ACell() function.

In this case the procedure will exit if the double-click happens anywhere but the CommentsColumn range. It will also exit if the cell is empty.

Next I added five methods to set Properties. These properties will correspond to the members in the row that was double-clicked. They will be shared with the adhoc view. Once again I'm using an Excel formula and the @ACell() Dodeca function. In this method, the Departments property is being set.

In this method, the Periods property is being set.

In this method, the Years property is being set.

In this method, the Measures property is being set.

In this method, the Location property is being set.

Finally, I called the OpenView method. I specified an AdhocEssbase View called PaloCommentsAdhoc.

In the PaloCommentsAdhoc Workbook Script I added one Event Link. The AfterBuild event will add the members to the sheet in this example.

Again, the first method I called was ExitProcedure. In this method I check to see if the view was opened from the PaloVarianceComments procedure. I checked the value of the variable using the @PVal Dodeca function. If the view is opened by a user, this procedure will exit and they will be able to use the view as a normal Essbase Adhoc View.

Next I used the ClearRange method to clear the sheet.

I then used the SetEntry method to populate the sheet with the member names from the properties I created in the previous Workbook Script. In this case I'm setting cells D2:F2 to the Years member.

Next I put the Periods value into cells D1:F1.

Next I put Actual into cell D3.

I put Budget into cell E3.

I put Variance into cell F3.

I put the Location into cell C4.

I put the Departments member into cell B4.

I put the Measures member into cell A4.

Since this is an adhoc view, I can't create a template with the formats I want. So I used the SetNumberFormat format the numbers the way I wanted.

I then set the font to the one I wanted.

Next I set an index color. This method allows you set the a number corresponding to the color you want. You can define the color using R/G/B values in the format shown here.

Next I set a color index using the ColorSelect property. Using an index on your views allows you to update multiple places that color value is used in a single place.

Next I set the font color on the header to be color index 21 which I set in the previous step.

Next I set the cell background color for the header to be blue.

The last method I called was EssbaseRetrieve. This will perform a retrieve on the adhoc sheet that was just set up and formatted.

We end up with an adhoc sheet that has the members we want and the formatting we want. There are probably several other ways of doing this but I chose the aforementioned methodology in this case.

That concludes this three part series. From entering the comments, to viewing all comments on a single sheet, to doing analysis on a line. Dodeca Does It!