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.