In the two previous blog posts, we covered the implementation of the CopyComments workbook script (wbs) method in Dodeca and added a tool on the toolbar to perform the Copy Comments action. In this post, we will describe the steps to add some message boxes to the Dodeca View to improve the user experience. We will ask for confirmation and provide feedback when the action has been completed.
We are all familiar with message boxes, or dialog boxes. They are a secondary window that opens during a session that can be used for a number of purposes including the following:
1. Provide information to a user
2. Ask for confirmation on an action
3. Provide feedback on something that has happened
4. Perform a command
5. Ask for specific information
Message boxes in Dodeca can be customized for the Caption, Icon, Message and Buttons. See below.
We will use something like the above to ask the user for confirmation that would like to proceed with copying the previous months comments. In addition to providing the confirmation message, we can also very easily capture which of the two buttons was clicked by the user. If yes, we will continue with the operation. If no, we will cancel the operation.
Let’s have a look at the current wbs for this view.
We do not have any Event links in this wbs because the action is called from a button on the toolbar we developed in the last blog post: Adding a Button to a Toolbar
What we will do here is to insert a few more methods within this procedure to add the message boxes to the process. The first step is the confirmation message box. For this we will add two methods:
1. ShowMessageBox – to display the confirmation dialog per our specifications, asking the use to continue (Yes) or not (No).
2. ExitProcedure – to exit ONLY if the user selects the No button.
Let’s insert the first new method. Right click on the current CopyComments method, and then select “Insert a New Method Above the Selected Row”.
This will add a new line for you to select the appropriate method. You can start typing (ShowMess…) to go directly to the method we want, or you can simply scroll down to select it.
Once we have selected the method, we see the following arguments:
We can see that four of the arguments listed here are the same as the first image in this post: Message, Caption, Icon and Buttons. The Icon and Buttons arguments are drop downs that we can select from. Let’s enter the following for these arguments:
Message: “Are you sure you want to copy in the comments from the previous month? This will overwrite any comments currently displayed.”
Captions: Please confirm
See below. The Icon and the Buttons arguments can be selected from the drop downs.
Next, we will the last two arguments, PropertyName and PropertyDataType, to capture the user selection i.e. whether they have selected Yes or No.
We can name the result property anything we like. Let’s make it specific to what it means. In this case, Yes means please continue with the Copy, so lets name it ContinueWithCopy. Also, this a yes/no, true/false situation, so let’s make the PropertyDataType “Boolean”.
Great, we are done with this method. Next, we will add the ExitProcedure method which will check the property to decide whether to exit, or to continue with the copy. Do the same steps to add a new method (right click and insert) in the correct place:
For this method, we only need to enter the argument for the Exit condition.
There is a bit of logic to consider here. We asked the user if they want to continue. We did not ask them if they want to cancel. So, we want to exit if the user selects the No button. We do not want to exit if the user selects the Yes button. Our ContinueWithCopy will be set to true if they select Yes, and false if they select No.
We will use the Dodeca function PropertyValue to return the value of the property. We use the following syntax with this property @PVal(ContinueWithCopy).
If we are going to continue with the copy process, then we DO NOT want the exit the procedure. For this reason, our entry for the argument to EXIT will be
That’s it. We are done with the Confirmation message box and the result of the user selection.
Now let’s just add another simple message box after the other methods in the procedure to indicate that the copy has completed.
Right click on the last method and select “Add a New Method Below the Selected Row”. Select the ShowMessageBox method enter the appropriate arguments. I have selected the Information Icon and the OK button:
We’re done! Now let’s simply run the View and test.
I have opened the View and selected December, 2017. I know that there are comments that exist for November and I want to copy them in.
When I click on the “Copy Previous Month Comments” button I see the message box.
If I click on No, nothing happens. The Procedure has exited and no comments are copied. If I click on Yes, the comments are copied, and I see the confirmation message:
Tested and verified!
I hope this has been helpful in demonstrating how easy it is to add some simple message boxes in Dodeca which improve the user experience with the View.
In my last blog post, Copy Forward Comments with Dodeca I showed how you could use the CopyComments method within a Workbook Script (WBS) to bring forward comments from the previous month. In this post, we are going to create a button on the View’s toolbar which will call this WBS procedure. This way, if our user decides to bring forward the comments from the previous month, they can simply click a button and the current month’s comments will be updated.
Adding a button to a toolbar which will initiate a WBS method takes just a few steps:
Create the button tool on the Toolbar Configuration used with the View.
Place the new button on the toolbar.
Assign the button tool with the instructions we would like it to perform
That’s it from a high level. Down in the details there are a number of smaller steps to complete. For that reason, there are many pictures in this post to capture everything.
Please note that the Toolbar Configuration topic discussed here is also covered in the Dodeca Administrator’s Guide from Applied OLAP, under Section 3: Advanced Topics.
View toolbars can be used across multiple Views. The particular toolbar used in a View is specified in the Views metadata editor, under the View ToolbarsConfigurationID property. See figure below.
In our Actual Budget Analysis View, the toolbar we are using is titled “Essbase View Standard Limited”. Since this toolbar may be used in multiple Views, the very first thing we will do is copy this to another toolbar. That way we can make updates and use the new toolbar without affecting other, existing views.
Let’s go to the Toolbars Configuration metadata editor by right clicking on the ViewToolbarsConfigurationID property name and selecting Edit “Essbase View Standard Limited”:
This will bring us to the Toolbars Configuration metadata editor with our toolbar already selected:
We are going to Copy this toolbar to a new one, so click on Copy, and let’s give the new toolbar the name “Essbase View Standard Limited with CopyComments”. Now let’s go back to the Views list, and update our ViewToolbarsConfigurationID to use the new toolbar:
We are now ready to add the new tool to this new toolbar. Go back to the Toolbars Configuration editor, ensure our new toolbar is selected, then click on the “Toolbars Designer…” button:
This will start the Toolbars Designer for this toolbar In the Toolbars Designer, we have multiple tabs across the top. Click on the Tools tab, and then click New… at the bottom to create a new tool:
In the New Tool dialogue, select Button as the Tool type, and enter “Copy Previous Month Comments” in both the Caption and the Key fields. We can leave the Category field empty right now. Click Add and Close.
Our list of tools now includes the one we just created:
Before going further, we are going to make a quick update to the properties of this tool. Open the SharedProps hierarchy and scroll down to the DisplayStyle property. Click on the dropdown and select TextOnlyAlways. Setting this will ensure that the button will be displayed with the caption “Copy Previous Month’s Comments” on the toolbar.
Next click on the Toolbars Designer tab. Here we can add tools from our list to the toolbar so it shows up when the view is displayed. If you click on the Toolbar drop down box, you will see the 4 categories that exist on this toolbar: Essbase, Grid, Standard and View:
These are listed alphabetically, not in the order in which they actually appear in the toolbar. The Grid category is the rightmost category on the toolbar, so that is where we will place our new tool. Select Grid from the dropdown, then select our new tool and drag it to the left, just under the Sheets member of the Grid category. Let’s also add a divider also by right clicking on our new tool and selecting Begin Group:
Click on Close at the bottom of the dialog. In the Toolbars Preview, we should now see our new tool added to the toolbar, with a little divider separating it from the Sheets tool:
Click Commit to save the toolbar configuration.
Our last step is to now assign the proper actions to our new button. For that, we click on the Configure Tools… button in the metadata editor:
This will bring up the Configure Tools dialog. Click our new tool from the list of tools so we can map it to a Tool Controller. On the right side of the dialog, click on the tool controller drop down, then select WorkbookScriptToolController:
Once selected, the Arguments window will be filled. We only need to enter the WorkbookScriptID and ProcedureNamearguments. Select the dropdown for the WorkbookScriptID and select the correct workbook script ID, then select the ProcedureName. The only procedure we have in the script is CopyComments. Once those updates are made, click on the Commit Module, Toolbars Configuration, and/or Views button at the bottom center of the dialog. This will bring up a confirmation message:
Click Yes to confirm. This will bring up the following message box:
You must restart the application in order to use the updated toolbar. Click OK, then restart your Dodeca application. Now we are ready to test. Let’s open our application and run the view:
We see our updated toolbar with the “Copy Previous Month Comments” button. We are in the month of Sep when the view opens. Let’s update the selector to the month of Oct which has not had any comments added yet.
I click on the “Copy Previous Month Comments” button, and voila! Last month’s comments are copied in and I am ready to update and save the comments for the new month.
In the next post, I will add a confirmation message box to the process for the user to confirm the update of the comments for the month. We will also add a notification message box after the update is complete.
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:
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.
This blog will discuss Dodeca features and techniques to help streamline your Financial systems.
Dodeca is the best tool for working with Essbase on the planet. It is formally titled the Dodeca Spreadsheet Management System but it can really do much more than just manage spreadsheets. It is a platform for developing Finance applications the way you want them. If you can dream it, you can build it with Dodeca. Sound far-fetched? It’s true, just ask Dodeca users.