Friday, March 1, 2019

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.
image


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.
image
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.
image


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.
image
The query I set up will only perform a Select statement.
image
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.
image


The only view property of note in this case is DataSetRanges.
image
I set the SQLPassthroughDataSetID to PaloMartComments which I created in the prior step.
image
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.
image
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.
image
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.
image


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


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


I also added a token for the ConnectionID. This was used in the SQL query and was placed on the sheet.
image
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.
image


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.
image


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.
image
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.
image


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.
image
Next I set the row height so multiple lines of comments were visible. I decided to show three lines of comments for this report.
image


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


And some more borders.
image
Next I set column widths to autofit the SQL.DataRange less a few hidden columns.
image


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


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.
image
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.
image


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.
 
image


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



No comments: