Among the many advantages of using Dodeca is the ability to easily copy comments. We already know that Dodeca is a great tool for entering and organizing commentary. Comments are stored in the Dodeca relational repository and you can align the comments with Essbase dimensions, SQL data or something else that might be unique to your report. The ability to copy commentary forward is additional feature that highlights Dodeca as an effective analysis tool for business users.
In this post I will describe a situation where copying commentary is useful and show how easily it can be done in Dodeca using the workbook script method CopyComments.
Please note, in this post I am not going to go into the details of setting up Comment Ranges in Dodeca. There already exists some great instructional information on this topic including YouTube videos and Jason Jones’ blog. See links here:
Now, onto the story. Imagine you need to enter comments every month on an Actual vs Budget report. You would like to see the current results and the comments you entered the previous month. Maybe you would like to actually start with the comments from the previous month, and add details for the current month. In the past perhaps you have even done something like this in other tools by copying and pasting, but that can be unfamiliar for users, unwieldy, and cumbersome for any reports larger than a few lines.
Wouldn’t it be great to simply push a button and have last month’s commentary copied in for this month, in the exact place it belongs? And from there you could update the comments for this month’s specific activity?
Dodeca provides a specific workbook script method for just this purpose: the CopyComments method.
We will start with a simple View that has a commentary range already configured. See screenshot:
This is a basic Actual vs Budget view. If the data looks familiar, it’s because it is a modification of the Sample Basic application that includes a dimension for years. This was added so we could ensure proper copying of comments between years, i.e. from December 2016 to January 2017.
In this report, comments can be entered for each measure to provide additional analysis on the results.
This is pretty standard stuff. The selectors on the view allow a user to select any market, product, month or year. The comments are stored based on these specific values, plus the measure listed down the first column of the view: Sales, COGS, Margin, etc. The key/value pairs defined for storing these comments are in a hidden column just to the right of the comments on the template. A quick peek under the covers to see them defined:
The key/value pairs utilize Tokens to ensure the comments are stored for the any value that is selected in the View. The value for the “Measures” key is picked up from the first column of the View. When the View is run for New York, Cola, Oct, and 2017, all the tokens are resolved and this is what is on the View, hidden of course:
We are going to keep the original lines with the Tokens handy. We will use them in the shortly when we set up the method to do the copy.
Now let’s take a look at the CopyComments method in the View’s workbook script (wbs):
Beyond the first eight properties of the method that are common to every method in Dodeca (and which we do not need to modify in this example), we see there are only three additional ones: Source, Target, and BackgroundExecute. We do not need to set BackgroundExecute, so all we need to do is correctly define the Source and Target fields for the method.
As we know about comments in Dodeca, they are stored via their key/value pairs. When we copy comments, we are taking comments that are stored with one set of key/value pairs, and then saving them with another, different set of key/value pairs. Within the CopyComments method, we simply need to specify the key/value pairs of the source comment, and the key/value pairs of the Target comment. In the above View for example, if wanted to copy a comment from September to October, the Source and Target fields could be listed as:
So far so good. One thing to keep in mind when copying comments is that the number of key/value pairs for the source comment must equal the number of key/value pairs for the target comment.
You can also copy multiple “sets” of comments at one time. This is done by separating each set listed in the Source and Target properties with a pipe delimiter. Following our example, if we have three measures (Sales, COGS, Margin) to copy from September to October, the Source and Target fields would contain this:
Great, so we know the underlying steps of the CopyComments wbs method. So how do I copy forward comments from the previous month to the current one regardless of the month currently displayed in the View? And also, be sure to have the right Product, Market and Year?
Well, we have a head start. We know exactly what needs to be defined in our Target field. It is all the key/value pairs as they are designed in the View! We are going to copy in the previous month comments to the month we have selected in the View, so we can use everything that is already defined. Recall how we wanted to keep these lines handy. Our Target field starts with copying the key/value pairs from the View Template:
We do need to do a bit of work on this and if you have text editor (like notepad++ or UltraEdit) it will be easier to do replaces. We are going to use the Dodeca @TokenValue function, @TVal(), to return the token values. We are also going to add the pipe delimiter between each set, and also remove the CRLF’s. After making these changes in our text editor we can paste them into the Target field of the method. So now the Target looks like this (although it is really all in one line; I separately them for readability):
A little messy, but stick with it. We are halfway there.
The next question is: how do we specify the Source for the CopyComments method? Well, we know we have three of the five defined: Market, Product, and Measure. These will be the same as the Target. That leaves us with figuring out the Year and Month (Period in our example). How can we ensure that when we are looking at the View for October, 2017, that we can copy in the comments from September, 2017? And consistently be sure we are always bringing in the comments from the previous month and year no matter where we are in the calendar?
For that we can let Excel help us. Excel has some great tools for figuring out dates. We can ask a question such as “If I am looking at a view with a particular month and year, can you tell me what the previous month is?” And inside Dodeca, we can utilize familiar Excel formulas to get information to use in our scripts.
We know the month and the year we are viewing in the report since they are specified in the View selectors and stored in the view tokens: [T.Years] and [T.Period]. We can then use these tokens in a formula to always return the previous month. We are actually going to use a few formulas. Here is the breakdown.
- Use the DATEVALUE formula to turn our tokens into an actual date.
- Use the EOMONTH formula to return the previous month of the date we find in step 1.
- Finally, use the TEXT formula to turn the date value from step 2 into month and year values we can use.
In the view, the tokens for Period and Year are in cells C9 and C10. Let’s use Oct, 2017 as examples. Also keep in mind that Excel stores dates as a number representing the number of days since Jan 0, 1900 (weird, I know, but just think of 1/1/1900 as the date 1 and go from there).
- DATEVALUE ($C$9&” 15, “&$C$10) = 43023. This formula is asking “What is the date value of Oct 15, 2017?” We use 15 arbitrarily as every month has a 15th.
- EOMONTH (43023, -1) = 43008. EOMONTH stands for End Of Month. You can enter any date and it will return the last day of the month. The second parameter will let you count forward or backward months. The “-1” in our formula means go back one month. So this formula is asking “What is the last day of the month previous to Oct 15, 2017?” The value that is returned is 43008. This is Sept 30, 2017.
- Text (43008, “mmm”) = Sep. The Text function turns numbers into text using specific format codes. The code “mmm” means turn it into a three character standard month abbreviation.
Altogether then, I am going to put the formula: =TEXT(EOMONTH(DATEVALUE($C$9&” 15, “&$C$10),-1),”mmm”) into a cell on the template and give the cell the name PrevMonth.
Then I am going to put the formula: =TEXT(EOMONTH(DATEVALUE($C$9&” 15, “&$C$10),-1),”yyyy”) into a cell on the template and give the cell the name PrevYear.
Then we can tell the workbook script to pick these values up from the template, and use them in our CopyComments method. We do this by way of the @ValueText() Dodeca function.
Now we have what we need to define our Source field. I am going to start with what we have defined for the Target, and replace the Year and Period values with the values of the previous month and year on the template. To illustrate, let’s look at the first set of the Target:
The Market, Product, and Measures key/values pairs are all set. We are going to use what’s here. We just need to update the Year and Period values.
Picking up the data from the template, the source Year will be @ValueText(PrevYear) and the source Period will be @ValueText(PrevMonth). So this line in the Source will look like:
Again, this is more easily done in a text editor, but all of the sets of the Source field will appear as thus:
That’s it. We are done with the CopyComments method! We are going to add one more method into our Procedure (which I have called CopyComments as well.) That is the RefreshView method. Refreshing the View will ensure we display the comments that were just copied. The entire procedure with the two methods looks like this:
My last job is to set up a button on the toolbar to call this Workbook Script. For information on how to set up button on a toolbar to call a WBS, please see the next blog entry: Adding a Tool to a Toolbar in Dodeca
There we will put the tool on the View and test our update.