Dodeca Workflow

First things first.  Dodeca does not come with built in workflow or process management.

But what you CAN do is build process management, or workflow, into your Dodeca application.  And you can build it the way you want it, and it can be done relatively easily.  The key to this is that Dodeca is really an Application Platform for building the exact application that meets your requirements.

It is powerful and flexible.  Powerful because there is an incredible amount of functionality that can be added to your application.  Flexible because the functionality can be adapted to specific users and specific situations.

In this post, we will have a look at a sample application called Outlook.  This is a company forecast application that is used multiple times throughout the year.  It uses a version of the Sample Basic Essbase application.

The Outlook Application dashboard

This is going to be a basic example of Process Management that only involves the Submission and the Approval or Rejection of the entire scenario.  We will explore the use of Planning Unit Hierarchies in a later post.

We will use Roles in Dodeca for this example.  For more information on Roles in Dodeca, please see the previous posts:  Roles in Dodeca and More on Roles

We will continue with the Planner and Reviewer roles that were demonstrated in those posts.  As in the other post, I am using Andy Dwyer (userid = dwyera) as the test Planner, and Leslie Knope (userid = knopel) as the test Reviewer.

We will also use a relational table to hold information about the scenario status.  It is one table structured like this:

The Scenario table

So, we have our Essbase application, Dodeca and a single SQL table.  With only these ingredients, we can demonstrate a basic forecasting submission workflow.

As shown in this previous post, we have assigned View Hierarchies based on Roles.  Only users with the role of Planner will have access to the Views that allow data input.  Here are the Input Views:

The Input Views in the Outlook application

Roles are important in this process.  Here are the specific actions permissible by Role.

A Reviewer can:

  • Open a scenario for Input.  This action will notify (via email) the Planning team and open the scenario for input on the Planning Views.
  • Review a scenario.
  • Accept a Scenario that has been submitted.  This action will notify the Planning team of acceptance and prevent any more input.
  • Reject a Scenario that has been submitted.  This action will notify the Planning team and reopen the scenario for input.

A Planner can:

  • Enter input to an open Scenario.
  • Submit a Scenario to the Review team.  This action will notify the Review team that the scenario has been submitted and prevent any more input.

After data for a scenario has been entered, Planners can to the “Review, Submit, Accept” View to submit the scenario for review,  In an attempt to keep things simple, we will focus most of this post on this “Review, Submit, Accept” View.  This is where the majority of the process management functionality takes place.

The Review, Submit, Accept View

We have three buttons on the toolbar for this View and they are only enabled under certain conditions.

Process Management buttons on the View Toolbar

The Submit for Review button is enabled if:

  • User has role of Planner
  • Status of the Scenario is Open or ReOpened

The Accept Submission button is enabled if:

  • User has role Reviewer
  • Status of the Scenario is Submitted.

The Reject Submission button is enabled if:

  • User has role Reviewer
    Status of the Scenario is Submitted.

This is all controlled through the extensibility that Dodeca provides with Dodeca Workbook Scripts.  We will not look closely at workbook scripts in this post, since the purpose of the post is to provide an overview of possible functionality as opposed to step by step instructions.

The Workbook Script for the Review, Submit, Accept View

The buttons on the View toolbar are tied to specific functions in the Workbook script.  When a user clicks on the Submit for Review button, the actions (methods) defined within the Submit procedure will be performed.  If you would like more information on how a specific function is accomplished please let me know.

So let’s have a look at the Review, Submit, Accept View:

I am in the application as test user Andy Dwyer with a role of Planner, and the Status of the scenario is Open, so the Submit for Review button is enabled.  Once I hit submit, a few things happen.

1.       An email is sent to the Review team.  Here it is on my phone:

Email from Dodeca

(Quick note on testing user emails.  Jake Turrell has on post on setting up multiple email addresses with one Google Gmail account: Using Test email accounts.  I used that method here for testing multiple target emails.)

2.      The scenario is set to Submitted.  Since I am testing as a user with the role Planner, I cannot Accept or Reject the submission.  Those buttons are still not enabled.

All buttons disabled.

Now lets assume that Leslie Knope, as part of the Review Team, received the email and is eager to check it out.  She can open the application and go to the same View.  Since Leslie does have a role of Reviewer, and the status of the Scenario is Submitted, she will see the Accept and Reject buttons enabled.

Reviewer Buttons enabled

Something is not quite right, so she will reject the Submission.

Upon this action, the Status is set to Reopened.  And now, for Leslie, all three buttons are disabled because as a Reviewer, she cannot submit an Open or Reopened scenario.

When Leslie rejected the Submission, Andy, who is on the Planning team, received this email:

Email indicating Submission has been rejected

Andy was able to make some updates on the Planning input Views that increased the total year Profit margin and is ready to submit again.  When he opens the Review, Submit, Accept view, he will see his new totals and can Submit again.  Now Leslie can open the View and Accept the submission.

Once it is accepted, all three buttons are disabled for both Andy and Leslie as no further actions can take place until the Scenario is opened again during next year’s cycle.

So that’s it at a high level.  The goal of the post was simply to highlight some things that can be done in Dodeca for Process Management.  I did not want to delve into to specifics of updating the SQL table, sending the emails, setting the logic for enabling buttons, etc, but I certainly can describe those steps in more detail in a later post.

The key is that although Dodeca does not come with predefined workflow functionality, you can build it with Dodeca.  And it can be built the exact way you want it.

 

Cancel the Save Event in Dodeca

This is the third of three posts on using relational data with Dodeca, specifically on the Data Block Range that can be used with your SQL ranges in Dodeca.  It is another example of how Dodeca makes it easy for you to customize your Reporting, Planning and Analysis application to deliver the exact solution for you.

In the first post on the SQL DATA BLOCK range, we examined how it works with your SQL data range and how it can be utilized in your Dodeca Views.  In the second post, we added more functionality to perform validations on your SQL data.  In that example, we only provided feedback to the user.  We indicated that there was an issue with the data the user was trying to save.  However, we did not prevent the save action from occurring if the user went ahead and hit Save.  If the user did hit save with these Validation issues still applicable, they would still receive an error on the View.

In this post, we will go one step further and actually stop the Save process, and also provide a message as to why the event was cancelled.  Sometimes you really need to hit users over the head to get the message across.

So how will we do that?  One of the many reasons Dodeca is the best tool on the planet for your Planning and Reporting systems is the amount of control developers have with what happens in a Dodeca View.  The key to this control is what the Dodeca architect team refers to as the “event driven extensibility” of Dodeca.  This event driven extensibility is defined in Dodeca using what are called Workbook Scripts.

Do not let the term Script throw you though.  We do not actually write scripts filled with code.  That work has already been done by the Dodeca team.  They have already done all the heavy lifting.  We just need to define what we need to do, and then configure the workbook script to do it.

So here is what is happening in our example from the previous blog post.

  1. The user is attempting to perform an Insert.  Specifically, the user is attempting to Save a Data Set range, which will do the insert of the data to a relational table.
  2. We know the table is defined such that it does not allow null fields.  All the fields must have data in order to save the new row of data.
  3. Thus far, we have put a notification on the View to let the user know that all fields should be there before saving.
  4. If the user attempts to save anyway, the user will get an error.  We want to stop this error.

Here is our mission:

  1. If the user attempts to save while there are validation errors, we will stop the “save” process to avoid the error.
  2. We will present a message box to let the user know that the data cannot be saved, and why.

So here is where the Workbook scripts (WBS) enter the picture.  We will use a workbook script to accomplish our mission defined above.

Before we get into the WBS however, let’s create an easy way to indicate that there are Validation issues.  Recall from the previous post how we set a formula in column G to evaluate to TRUE if there is an issue.  We know where we need to check.  Let’s do two additional things on our template.  We will create a named range for the column G rows we need to check, and then use another cell to count the number of TRUE’s.

We will name the range in column G CheckFields:

Named range “CheckFields”. Where to check for problems

And add a formula to cell G2 to count the number of TRUE’s in the range.  And we will name this field ErrorCount:

Count the number of issues

Now we have an easy way to know if there are issues.  If the value in the ErrorCount cell is > 0.

Back to the Workbook Script.

Remember how I said the Dodeca team has already done all the heavy lifting?  Well, there are many things that can happen in a Dodeca view.  These are called Events (hence, event-driven extensibility), and Dodeca has already defined over 100 of these events for us to use.  Events are captured, and then Procedures are tied to the event to do get things done.  What event do you think we want to capture here?  That’s right, it is the Save event.  Specifically, it is the Save Data Set Range event.  And we want to capture the event *before* it happens.  So we will look for a Before Date Set Range Save event.

Let’s open the Workbook Script editor for this View.

The Workbook Script editor in Dodeca

There are four sections: Definition, Properties, Event Links and Procedures.  We are not concerned with the Definition or Properties sections today.

I can right click on “On Event…”, select New, and be presented with a list of the available events to use.  Scrolling down I find “BeforeDataSetRangeSave”.  Bingo.  That’s the one we want.

The event we want to capture

I can select that, then hit tab twice to automatically create a procedure called OnBeforeDataSetRangeSave.  The procedure name is automatically generated, but you could create one with another name for this if you want.

Now that I have the procedure, I need to specify the Methods within the Procedure.  The methods are the vehicles for getting things done.  We want to Cancel the Save event, so scrolling down I find the CancelEvent method:

Select the Method within the Procedure

Now to configure this event.  Here are the properties:

The CancelEvent method Properties

We only want this method to be in effect if there are validation errors.  And we know if there are validation errors if our ErrorCount field value is > 0.  So in the MethodCondition property we enter

=@ValueNumber(ErrorCount)>0

Also set the CancelProcedure property to TRUE

Setting the method properties

This method also provides a Property to enter the name of a procedure to run if the event is cancelled.  We can use this to display a message box informing the user of the cancelled event.  We will create a Procedure called ShowCancelMessage, and we can utilize the ShowMessageBox method there.  Let’s enter the message as:

“The Save operation has been cancelled due to Validation errors on the data. Please check the Validation messages and correct the data before saving.”

The ShowMessageBox method properties

The last bit of cleanup we need to do is reset the View status.  There is method for that, called, you guessed it, SetViewStatus.   The View status gets set to working when the Save button is clicked.  We want to reset this back to Ready.

Set the View Status back to Ready

That’s it.  One event, two procedures, and three methods used to get everything done.

Now when the user attempts to save date before it is ready to go, the Save will be cancelled, and the User will get a message as to why.  Let’s see it in action.  I have opened the View and entered some data.  It is incomplete however.  I try to save anyway:

The View showing Message box

I hit the okay button, and we are back to the View.  No errors.  Voila!

 

Hide or Show Zero Rows in Dodeca (without Suppressing)

The “suppress missing” and “suppress zero rows” ability of Essbase is quite useful for many cases of handling information from your Essbase application.  But let’s look at a case where you may want to have the option of easily hiding or unhiding these rows, as opposed to getting rid of them altogether, and how easy it is to do with Dodeca.

We will use a simple budgeting view, and a simple Chart of Accounts to demonstrate.

Leslie Knope-Wyatt has a simple Dodeca View to manage the budget for the year at her company.  This View is aptly titled Budget by Acct. The budget information is loaded at the beginning of the year for the appropriate chart of account members, and then updated throughout the year.

This is an example of what the Budget data looks like across all accounts for the year.  The list scrolls across multiple pages.

Figure 1. All rows in Budget View

This is a rather simple Chart of Accounts.  There are only about 45 or so level zero accounts in the Chart.  But the budget is only loaded to about half of those.  Since all the accounts are listed, the user is presented with more information than she needs.  It would be much easier on the user to present a streamlined View, of only the rows with data listed.

Simple, you say.  Turn on suppression of zero and missing rows in the View options and we are done.

Figure 2. View options for suppressing zero and missing rows

Once those options are set to true, only rows with data will appear in the View.

Figure 3. View with zero and missing rows suppressed.

Not so fast!  The accounts with zero or missing data are now gone, never to be seen again in the View.  But what if Leslie now needs to use one of those accounts during the year to correctly do the budget?  What if there is a new account that has been added during the year that needs to have budget?

Obviously, suppressing the rows in this case is not a viable solution.

A better option would be for the user to have the ability to hide or unhide zero rows at her discretion.  Then she would have a streamlined view for the most common updates, but also be able to get to and plan for any chart of account member that exists.

With Dodeca this is easy to accomplish with a simple formula on the sheet, and a bit of workbook scripting.

What we can do is tell Dodeca to have a look at the data after it has been returned from Essbase and hide the row if it contains all zeroes.  So, by default, the View will be streamlined to only show the rows with data.  If the user needs to use an account that currently has zero values, she can simply use a button on the toolbar to unhide the rows.

We will accomplish this with the following steps:

  1. Add a formula to the worksheet template to determine if a row contains all zeroes.
  2. Hide the zero rows by default using a Workbook Script (WBS) procedure.
  3. Create another Procedure in the Workbook Script to unhide the zero rows.
  4. Add a button to the toolbar to call the Unhide procedure.

Step 1:  Add a formula to determine the rows to hide.

We want a cell in each row to resolve to TRUE if it should be hidden, so we will enter a formula to check all the columns in each row.  Should we simply sum the values to check if they add to zero?  That would be a simple solution but it would not work if there were offsetting values, say 500 in June and -500 in July.  These would sum to zero but this would be a viable row the user wants to see.  We could check each cell (If B9=0, and C9 =0, and D9=0, etc) but that would be a cumbersome formula.

A better solution is the check the Min and Max of the range with a simple AND statement:

=AND(MIN(B9:M9)=0,MAX(B9:M9)=0)

This will resolve to TRUE when all the values in the range are zero.  Let’s enter this formula in Column O on our View template.  Once we have entered the  formula and copied it down for each row, lets select this range and give it a name:  Hide.Range

Figure 4. The Hide.Range range on the View Template

We will hide this column in the when setting up the View Template so it does not appear when the View is opened.

Step 2:  Use a workbook script method to hide the rows with zeroes.

There is a method just for such an occasion called “SetHidden”.  We can set it to hide the rows where the value of the formula we entered in step 1 is TRUE.  We can use this method the AfterBuild event in Dodeca.  The workbook script (wbs) looks like this:

Figure 5. The SetHidden WBS method.

Let’s have a look at this method.

  1. Set Overload to Rows.  This method has a number of overloads depending on what you want to hide: Columns, Rows, Worksheets, etc.  We will set the overload to Rows.
  2. Address.  We are going to use the range name we created in previously called Hide.Range to determine the hide condition.
  3. CellByCell.  We will check each row, so we need to go CellByCell in our range.
  4. CellCondition.  We will use the Dodeca wbs function ActiveCell for the cell condition.  This will either be TRUE or FALSE on the View.  If it is TRUE, the condition will be met and we will hide the row.
  5. Hidden.  Yes, we want this to be TRUE so that the row will be hidden.

So far so good.  We now have a View that will open presenting only the Accounts with data.  This will give the user a much more streamlined look at the data.  She will no longer need to scroll through the list of accounts; they can all be seen in one page.  See figure below.

Figure 6. The View with hidden rows.

The sheet headings are left on the View to highlight a couple of points:

1.        Column O is hidden.  This contains our formula to check for zero rows.

2.       The row numbers now indicate that a number of rows are hidden.  We see the same data that is present in Figure 3 above, but we know that more rows exist in this version, they are just hidden.

Here is what the View looks like with all rows and columns displayed:

Figure 7. All rows and columns unhidden.

We are almost done.  We now need a way to unhide these rows at the user’s request.  But instead of just unhiding rows, Dodeca gives us an easy way to “toggle” the rows with zeroes, so the user can hide or unhide with one button.  We will use the same method we used previously.

Step 3:  Add a procedure to toggle the hidden rows.

This will be easy.  Add a new procedure in the workbook script called ToggleHiddenRows.  Then add one method to this procedure, the same one used previously, SetHidden.  See below.

Figure 8. The toggle procedure and method to hide/unhide

The overload for this procedure will be ToggleRows.  The following line of help information describes the ToggleRows overload.

Overload: ToggleRows (Set Hidden of the specified row(s) to the opposite of the current setting.)

We still need to enter the Address and the CellCondition for this to work properly.  With these set, the method will focus the hiding/unhiding on the rows where the formula resolves to False.  Try setting the CellCondition to TRUE or FALSE to get an understanding of the difference in how the method works with these settings.

Step 4:  Add a button to the View toolbar to toggle zero rows.

Once we have the new procedure in place, we can add a button to the toolbar to call it.  We do not need to describe that process here as there is another blog post for that.  For more information on how to add a button to the toolbar, please see this post on the topic.

The button that is added to the toolbar will have the caption “Toggle Zero Rows” for brevity, with the understanding that the user knows that in this case, toggle refers to hiding and unhiding the zero rows.

Once the button has been added to the toolbar, our user can now easily toggle to unhide or hide the zero rows:

Figure 9. The View with the Toggle button added

Our user can now easily manage the button for the zero rows with a simple push of a button.

Figure 10. The View with rows unhidden.

All done!

If our user now enters and sends data on any of these zero rows, that row will now always appear when the View is opened.

Adding Message Boxes for Confirmation and Feedback

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.

Example message box

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.

Current Workbook Script for this view

We currently have one Procedure with two methods as were developed in the blog post:  Copy Forward Comments with Dodeca

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.

Scroll down to ShowMessageBox

Once we have selected the method, we see the following arguments:

Arguments for the ShowMessageBox method

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

Icon:                      Question

Buttons:               YesNo

See below.  The Icon and the Buttons arguments can be selected from the drop downs.

Arguments entered for our method

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

Arguments for the Property

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

=Not(@PVal(ContinueWithCopy))

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.

After clicking the Copy Previous Month Comments button

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:

Updated View, with comments copied and message displayed

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.

 

Adding a Button to a Toolbar in Dodeca

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:

  1. Create the button tool on the Toolbar Configuration used with the View.
  2. Place the new button on the toolbar.
  3. 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.

The View toolbar is specified in the View properties under UI

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”:

Right click property name for a shortcut to edit the toolbar

This will bring us to the Toolbars Configuration metadata editor with our toolbar already selected:

The Toolbar metadata editor

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:

Select the new copied Toolbar to use in the View

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:

Click on 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:

Click on the Tools tab, then click New…

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.

Click Add to add the toolbar, then click close.

Our list of tools now includes the one we just created:

Our newly created tool

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.

Update the DisplayStyle under SharedProps

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:

The toolbars listed in the Toolbar Designer tab

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:

Add the new tool to the Grid toolbar via drag and drop, then Begin a 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:

The new tool can be seen in the Toolbars Preview

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:

Click on Configure Tools to assign an action to the new button tool

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:

Select the tool, then click on the Tool Controller dropdown

Once selected, the Arguments window will be filled. We only need to enter the WorkbookScriptID and ProcedureName arguments. 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:

The View now has the updated toolbar, with the new button to Copy

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.

Copy Forward Comments with Dodeca

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:

Jason’s Blog on Dodeca Comments

Applied OLAP Video 1 on Comments

Applied OLAP Video 2 on Comments

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:

Dodeca View with Commentary

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:

Key/Value pairs defined on View template for Comment Fields. Tokens used will be resolved when the view is opened in Dodeca

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:

Key/Value pairs with tokens resolved when View is run

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):

The CopyComments method

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:

Source: Years=2017;Period=Sep;Market=NewYork;Product=Cola;Measures=Sales;

Target: Years=2017;Period=Oct;Market=NewYork;Product=Cola;Measures=Sales;

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:

Source:
Years=2017;Period=Sep;Market=NewYork;Product=Cola;Measures=Sales;|Years=2017;Period=Sep;Market=NewYork;Product=Cola;Measures=COGS;|Years=2017;Period=Sep;Market=NewYork;Product=Cola;Measures=Margin;

Target:
Years=2017;Period=Oct;Market=NewYork;Product=Cola;Measures=Sales;|Years=2017;Period=Oct;Market=NewYork;Product=Cola;Measures=COGS;|Years=2017;Period=Oct;Market=NewYork;Product=Cola;Measures=Margin;

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:

Defined 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):

Defined Target field for the CopyComments method

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.

  1. Use the DATEVALUE formula to turn our tokens into an actual date.
  2. Use the EOMONTH formula to return the previous month of the date we find in step 1.
  3. 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).

  1. 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.
  2. 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.
  3. 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:

Years=@TVal([T.Years]);Period=@TVal([T.Period]);Market=@TVal([T.Market]); Product=@TVal([T.Product]);Measures=Sales;

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:

Years=@ValueText(PrevYear);Period=@ValueText(PrevMonth); Market=@TVal([T.Market]);Product=@TVal([T.Product]);Measures=Sales;

Again, this is more easily done in a text editor, but all of the sets of the Source field will appear as thus:

Defined Source field for the CopyComments method

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:

The CopyComments Procedure, with two methods: CopyComments and RefreshView

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.