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!

Dodeca Commentary Part Two

This post is part two of three in a series on using commentary in Dodeca. In this post I will be showing how to set up a view that shows all the comments entered for an Essbase cube.

This report combines Essbase and relational data so I set it up as an Essbase Excel View.

The template is fairly straight forward.

 

I added a cell that will contain my ConnectionID as well as the scenarios I will use to retrieve Essbase data.

I set up four named ranges.

 

One range will define the column that will contain the comments.

 

The next range will tell Dodeca where the Essbase retrieve range will begine.

 

The Header.Row.1 range defines where the Essbase scenarios will be stored.

 

The SQL.DataRange.1 will tell Dodeca where to bring in the relational data.

In order to get the zebra striping on this report, I did export the template to Excel. I set up a conditional formatting rule to handle it. In this case I used the ISODD() and ISEVEN() Excel functions along with the ROW() function. This is a simple way to add this type of formatting to a view. Once I had set this conditional formatting up, I imported the template in Dodeca.

Next I created a SQL Passthrough Dataset and defined one query to bring back the comments.

The query I set up will only perform a Select statement.

This is the SQL used to bring back all the comments in the current Tenant with the ConnectionID for the cube.

 

This SQL is a bit tricky since each Key,Value pair in the Comment_Key_Items table is stored as a row. Essentially the rows need to be pivoted to the columns -- something not very straightforward in SQL.

 

If you do end up trying to reproduce this view in Dodeca and have trouble writing the SQL, drop me a note in the comments and I'll be glad to give you a hand with your specific dimensionality. There are several ways to write the SQL -- this is just one of many.

 

I added ConnectionIDs as a Key,Value pair just in case the dimensionality in this cube is the same as another cube using commentary. This practice will help to avoid any unintended conflicts.

The only view property of note in this case is DataSetRanges.

I set the SQLPassthroughDataSetID to PaloMartComments which I created in the prior step.

In the DataTable Range Editor I turned on AutoFiltering which will allow us to sort and filter on any of the fields in the view.

 

I also changed SetDataFlags to InsertCells, AllText. This will expand the named range when the SQL data is returned. So any formatting will be copied throughout the range.

Next I set two properties.

 

The first I called OpenedFromPaloVarianceComments. I'll use this in the next post when I create the adhoc sheet based upon a double-click. I set that value to true and allowed it to be shared between views.

 

The next I called IsDoneBuilding. I set this to false. I will use this to handle unwanted Essbase retrieves after the initial pull of data.

I ended up creating procedures that run upon four different Events being fired. I'll discuss all but the CellDoubleClicked. I'll handle that in part three of this series.

I used the AfterWorkbookOpen event to add a couple of tokens to the view.

I added one token for the Tenant. This was used when I set up the SQL query.

I also added a token for the ConnectionID. This was used in the SQL query and was placed on the sheet.

The first method in the OnAfterDataSetRangesBuild procedure is InsertRange. This will add a row at the top of the returned comments. This procedure gets called after the SQL query is run and the data is placed on the sheet.

The next method is CopyRange. I used this to copy in our Header.Row.1 named range to the empty line I just added. This will copy in our Essbase scenarios.

Once this is complete, we now have a well formed Essbase retrieval range with five of the dimensions in the rows and the Scenario dimension in the columns.

Now that we have a well formed Essbase retrieval range, we can create the defined range name by using the AddDefinedName method. In this case I'm using the Offset function along with the SQL.DataRange.1 named range to create the new range.

Once I had created an Essbase retrieve range all I had to do was perform the EssbaseRetrieve method using a RetrievePolicy of RetrieveRanges to get my data back.

In this view I only needed the scenario names at the top of my sheet for the Essbase retrieve. Once that was done, I no longer needed them. Hiding them wasn't a great option since they could possibly be unhidden during a sort or filter change. Deleting them was the easiest way to remove them.

Next I set the row height so multiple lines of comments were visible. I decided to show three lines of comments for this report.

Next I added some borders to make things a little easier to read.

And some more borders.

Next I set column widths to autofit the SQL.DataRange less a few hidden columns.

I wanted to make the comments column a bit wider so I added another SetColumnWidth method.

I wasn't happy with the way autofit handled some of the columns. It looks like the filter down-arrows don't count when it does the fitting. So I used the @ColWidth Dodeca function to increase each field by '4' which made the sheet look much cleaner and all headers readable. I also only ran the method on cells with a ColWidth not zero. That eliminated any hidden rows from being expanded.

Next I set the IsDoneBuilding property to true. No data needs to be retrieved after the initial build on this view. So I'll use this to cancel the BeforeSheetRetrieve event.

Finally, I created the OnBeforeSheetRetrieve procedure to check if the view is done building. If it is done, I cancel the event. I use the @PVal Dodeca function to check the IsDoneBuilding property value. Adding this method stops the view from updating unnecessarily. After the initial build, the only thing a user can do is sort and filter the data.

 

In the next step, I'll show how to use the double-click event to take the user to an adhoc sheet.