One of the many reasons why Dodeca is the best tool on the planet for working with Essbase is how easily it integrates with your relational data. It allows you to bring together the domains of both Essbase and relational data to create the exact system you need.
Often when people hear the words “Essbase” and “relational data” together they think of drill-through. And yes, Dodeca makes it extremely easy to drill through to the relational source of your Essbase data. But that is just tip of the iceberg. Dodeca also provides an easy interface for WRITING back to your relational data systems. This creates many more possibilities for your Planning, Reporting and Analysis systems that use Essbase.
There is some great information on the web describing the use of Relational data within Dodeca. There are the Applied OLAP videos on their YouTube channel. There are also some great blog posts by Tim Tow, Jason Jones, and others on this topic.
Here are some of great sources of SQL in Dodeca information:
Please note: The post by Jason Jones on Relational Data Input does an excellent job of highlighting the game-changing nature of the ability to combine Essbase with relational data. If this is an interest to you, and you haven’t read it already, I would urge you to have a look.
Into these waters I wade. This blog will attempt to add to this fine collection of information with three posts on using SQL in Dodeca. The first is a quick post on the SQL Data Block range. The second will be a post on extending the functionality of the Data Block Range to validate SQL data before doing an insert. The third will take a look using some Workbook scripting to actually cancel an SQL insert event if there are any validation issues.
Also note, I will not discuss the basics of setting up a SQL View in Dodeca. Those basics are well covered in the information links above.
The SQL Data Block Range
What exactly is a SQL Data Block Range?
There is a property in the DataTable Range Editor called DataBlockSheetRangeName. In the editor, in the help section, the property is described as:
The name of an optional sheet range that contains the data range and additional columns that contain formulas that refer to cells within the data range. The DataBlockSheetRangeName must be the name of a sheet-level defined range.
The data block range is used to define formulas within cells on a data row that refer to cells within the data range on the same row. When the range is built and when rows are added, inserted, or deleted, the columns within the data block are maintained along with the data range.
Simply put, this range defines formulas that can tag along with each row of data in the SQL data range.
For example, if you are retrieving monthly data in your SQL data range, you could sum the months into quarter and year values using formulas in the data block range. If you brought back 3 rows of data or 3000 rows, the data block would follow along with the data range and calculate values for as many rows of data exist.
The data block range is an extension of the data range. It contains the data range, plus additional columns. See figures below. The first is the sheet defined Data.Range and the second is the defined DataBlock.Range. These are defined on the View Template in the View Template Designer.
You can see that the DataBlock.Range actually contains the Data.Range.
The SQL Passthrough Dataset for this View retrieves data for the first three months of the year. These values will be entered in columns B, C, and D, as defined in the data range. Column E is part of the data block and contains a formula to sum the months.
When the View is opened, we see that the data is retrieved, and the formula is there to sum the months for all the rows that are returned.
That’s it. Using a Data Block range with your relational data in Dodeca comes in very handy. In the next post, we will discuss using the Data Block range to do something a bit more sophisticated that adding columns together.
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.
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.
Once those options are set to true, only rows with data will appear in the View.
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:
Add a formula to the worksheet template to determine if a row contains all zeroes.
Hide the zero rows by default using a Workbook Script (WBS) procedure.
Create another Procedure in the Workbook Script to unhide the zero rows.
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:
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
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:
Let’s have a look at this method.
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.
Address. We are going to use the range name we created in previously called Hide.Range to determine the hide condition.
CellByCell. We will check each row, so we need to go CellByCell in our range.
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.
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.
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:
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.
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:
Our user can now easily manage the button for the zero rows with a simple push of a button.
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.
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.