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.