Whether you play a technical role or are a financial analyst, Excel is likely a major asset in your toolbox. Whether it is the SUM function, the VLOOKUP function, or one of the many others, we have all used Excel functions for a plethora of reasons. 

There is a lot of potential hidden in Excel that you may not be aware of.  Excel offers the ability to create your own user defined functions, and it’s not hard to create them.  With a little ingenuity and strategic thinking, custom Excel functions can be a huge asset.  

Below are two examples.  Neither is difficult, but they will provide you with a taste of what you can do with custom functions.  The first example calculates a better/worse value based on three inputs (prior period, current period, and expense vs. revenue).  The second concatenates columns together with a user specified delimiter and the option to use quotes around the values.

Background on Custom Functions

Custom functions are Visual Basic for Applications (VBA) code snippets that are stored in modules in a workbook.  This is the same place macros are stored, so it may be familiar.  To open the VBA window, use ALT F11.  Once the window opens, right-click the workbook you want to add the function to in the VBAProject window and select Insert->Module.  A new window will open named Module1.  Custom functions have to be in a module to be accessed in a workbook.

Each function has a function name, input arguments that pass data to the function, and return a value.

A very simple example shows these pieces.  “Test” is the function name.  “Input” is one argument passed to the function.  The function returns a numeric value, which is the input value multiplied by ten.

Function Test(input as double) as Double
    Test=input * 10
End Function 

To use this function, return to your worksheet and enter “=Test(5)” in a cell.  This function can also be found in the Insert Function option by selecting User Defined in the Select A Category dropdown box.  The input parameter doesn’t have to be a value.  A cell reference can be used, just like any other Excel function.  The result should return 50.

Example:  Better(Worse) Calculation

For you finance folks, you will almost always have a better/worse calculation in a spreadsheet that compares two periods.  For revenue, the current period is subtracted from the prior period.  For expense, it is the inverse. 

To accomplish this, we will have a function with 4 parameters. 

1. Prior Period
2. Current Period
3. Whether the numbers being evaluated should be calculated as an expense or revenue
4. Whether the result returned is in the form of a dollar value or percentage change

Function BetterWorse(Prior_Period As Double, Current_Period As Double, Expense As Boolean, Return_Dollar As Boolean) As Double
    If Expense = True Then 'Calculate as an expense
        If Return_Dollar = True Then  'Return a dollar value
            BetterWorse = Prior_Period - Current_Period
        Else  'Return a percentage
            BetterWorse = (Prior_Period - Current_Period) / Prior_Period
      End If
    Else  'Calculate as a revenue
        If Return_Dollar = True Then  'Return a percentage
            BetterWorse = Current_Period - Prior_Period
        Else  'Return a percentage
            BetterWorse = (Current_Period - Prior_Period) / Current_Period
      End If
    End If
End Function

Below is an example of this function being used.  The result of the custom function resides in column D and E.  Revenue is lower in the current year, resulting in a negative variance.  Expenses are also lower, but result in a positive variance.

The formulas that exist in columns D and E are as follows.

Example:  Concatenation

The need to create a delimited file from Excel is very common.  The problem with doing this is that the entire worksheet is extracted.  If the worksheet had data in rows or columns that are now blank, Excel still exports those blank cells.  One way to overcome this is to create a function that concatenates a range into one cell.  Then, the concatenated values can be copied and pasted to a text file.  Many times this is very handy.  This can obviously be done with a cell formula, but gets time consuming to create when many cells are required.  It is further complicated when quotes around the fields are necessary. 

Function ConcatForExport(InRange As Range, Delimiter As String, UseQuotes As Boolean) As String
    Dim TheCount As Integer
    TheCount = 0
    For Each cell In InRange
        If TheCount = 0 Then
           If UseQuotes = True Then
              strString = Chr(34) & cell.Value & Chr(34)
           Else
              strString = cell.Value
           End If
        Else
           If UseQuotes = True Then
               strString = strString & Delimiter & Chr(34) & cell.Value & Chr(34)
           Else
               strString = strString & Delimiter & cell.Value
           End If
        End If
        TheCount = TheCount   1
    Next cell
    ConcatForExport = strString
End Function

To expand on the variance example above, an additional column has been added to show the use of this function.  Each row passes different parameters.  Columns B through E are concatenated together into one cell.  The delimiter is altered in row 5, and no quotes are around the value in row 4.

The corresponding formulas are below.

There are a wealth of opportunities that open up using custom functions.  Adding functionality and automating tasks like the examples above are just the start of what can be done. 

I attended many Hyperion Solutions conferences back in the day.  I really enjoyed the experience, but after the first couple of years, I didn’t feel like I was getting my money’s worth.  I started to wonder if I knew all there was to know about Essbase, as every presentation I attended seemed very basic.  Was I that good?  Did the benefit of attending these conferences shift from knowledge gain to networking? I decided to stop attending. 

Last year, Jake Turrell invited me to become be part of the Kscope12 Hyperion Planning track selection committee.  Being away from the conference scene for so long, I jumped at the chance to find out how presentations were selected.  I thought it would be a great opportunity to be part of a team that was trying to make the conference content better.  I was extremely impressed with the topics, but was still hesitant on how valuable attending the conference would be for me, outside of networking.

Two words express what I experienced in San Antonio.

HUMBLING and EXCITED

I was never involved in the abstract selection process for the Hyperion Solutions conference, so I’m unable to compare the marketing behind the process of collecting and selecting abstracts, but I can say with conviction that every presentation I attended far exceeded my expectations.

This year, I was asked to take on the role of committee chair for the Planning track.  My goals were simple – to set measureable and meaningful evaluation standards that a democratic group of experts could use to make the best decisions on the presentations for Kscope13 and continue its presentation excellence.

To provide you a little background, presentations were ranked and selected according to the content, regardless of presenter’s industry exposure.  Once the presentation was paired to the presenter, we verified that

1. No presenter dominated a track
2. No consulting company dominated a track
3. The consulting/customer ratio was reasonable
4. A presentation that was known to be presented at a national or regional conference was excluded, unless there was an overwhelming reason for it to be presented again
5. Presenters’ abilities were verified by interviews, or feedback by an industry contact that knew the presenter

There was great dialogue among the selection committee to select the abstracts that showed the most promise. Once the abstracts were ranked, a discussion surrounded each one on its merits and possibilities.  This discussion included the presenters’ backgrounds, the content and whether it was presented before.  The selection committee members were not immune to this degree of scrutiny either, as some of my own (the almighty track chairperson) topics were disregarded!

The bottom line is that the committee made every effort to showcase the best of the best, regardless of the presenter’s historical credentials and industry panache.

After the smoke cleared, the selected presentations were tweaked so no presenter or organization dominated the track. The presenters that were not known by a committee member were called and interviewed to ensure the most potential for a great presentation.

If you want to further your knowledge, improve your productivity, network with some of the best minds in our industry, and further your career, join us in New Orleans.  We think the result will be a conference well worth your time and investment.

You are sure to enjoy the experience.

The format of the data that is loaded to Essbase is often an after-thought.  But, should it be?  When requesting the data file from a source system, it is more important than you may think to have it sorted to mirror your outline.

Assume an outline has the following dimensions.

• Period [DENSE]
• Account [DENSE]
• Region [SPARSE]
• Category [SPARSE]
• Product [SPARSE]
• Organization [SPARSE]

The most efficient way to receive a data file would be to have it sorted by Organization, Product, Category, Region, and then Account.  Data files load faster when the columns that hold the sparse members are sorted in reverse order of the sparse dimensions that exist in the outline.

The reason the data loads faster is because it opens a block of data only one time.  If the data was sorted by the dense members first, then every block would have to be opened multiple times.  If the same sparse member combinations have 3,000 dense members with data, the block would be opened up to 3,000 times.

There are some more important benefits of doing this, however.  When the block is opened multiple times, the database becomes far more fragmented than it needs to be.   Fragmentation causes calculations to be slower and retrieving data can also be impacted, which can lead to frustrated customers.

By not sorting the data when loaded, every time a data load occurs, any performance issues that may exist are exacerbated.  So, anytime possible, sort the data load files by the last sparse dimension in the outline, the second to last sparse dimension in the outline, and so on.  You may be presently surprised at the benefits.

In2Hyperion has far surpassed any expectation I had for its success. The authors at in2Hyperion are very proud of its accomplishments. The feedback from our visitors has been encouraging and inspiring. Almost ¾ of a million visitors have read nearly 100 articles, and 1,500 people are viewing pages on www.in2hyperion.com every day. An estimated 20,000 people are using our Excel Addin Ribbon.

In an effort to take the next step at keeping more of the community informed about our updates, we are stepping up our social media efforts. We are excited to increase our viewership and now include more ways to follow In2Hyperion.

1. Like us (see above) on Facebook and get status updates every time a new article is published.
2. Sign up for the newsletter (just to the right of this article) and get emails every time a new article is published.
3. Join the In2Hyperion LinkedIn group.
4. Follow our tweets (see above), which include more than just our published articles.

As always, your information stays private and is never sold.  Thanks to all those who have made this such a huge success!

Do you want to be part of In2Hyperion?  Do you want your voice to be heard?  We are looking for guest authors.  If you are interested in participating, please send us an email with your name, organization, experience level, and topic you would like to write about.

If you are interested in learning more about a topic related to the Hyperion suite, let us know what that topic is.  Comment on this post or send us an email.  We will consolidate your requests, find an author, and post the article as soon as it is available.

You can now follow us on Twitter, Facebook, LinkedIn, and email.  You can chose one, or all of them, and we will make sure you are informed when new information is available.

Everybody knows the quickest way from point A to point B is a straight line.  Everybody assumes that the path is traveled only one time – not back and forth, over and over again.  I see a lot of Essbase calculations and business rules, from experienced and novice developers, that go from point A to point B taking a straight line.  But, the calculation travels that line multiple times and is terribly inefficient.

Here is a simple example of a calculation.  Assume the Account dimension is dense, and the following members are all members in the Account dimension.  We will also assume there is a reason to store these values rather than making them dynamic calc member formulas.  Most of these are embedded in a FIX statement so the calculation only executes on the appropriate blocks.  To minimize confusion, this will not be added to the example.

Average Balance = (Beginning Balance  Ending Balance)  / 2;
Average Headcount = (Beginning Headcount   Ending Headcount) / 2;
Salaries = Average Headcount * Average Salaries;
Taxes = Gross Income * Tax Rate;

One of the staples of writing an effective calculation is to minimize the number of times a single block is opened, updated, and closed.  Think of a block as a spreadsheet, with accounts in the rows, and the periods in the columns.  If 100 spreadsheets had to be updated, the most efficient way to update them would be to open one, update the four accounts above, then save and close the spreadsheet (rather than opening/editing/closing each spreadsheet 4 different times for each account).

I will preface by stating the following can respond differently in different version.  The 11.1.x admin guide specifically states the following is not accurate.  Due to the inconsistencies I have experienced, I always play it safe and assume the following regardless of the version.

You might be surprised to know that the example above passes through every block four times.  First, it will pass through all the blocks and calculate Average Balance.  It will then go back and pass through the same blocks again, calculating Average Headcount.   This will occur two more times for Salaries and Taxes.  This is, theoretically, almost 4 times slower than passing through the blocks once.

The solution is very simple.  Simply place parenthesis around the calculations.

(
Average Balance = (Beginning Balance  Ending Balance)  / 2;
Average Headcount = (Beginning Headcount   Ending Headcount) / 2;
Salaries = Average Headcount * Average Salaries;
Taxes = Gross Income * Tax Rate;
)

This will force all four accounts to be calculated at the same time.  The block will be opened, all four accounts will be calculated and the block will be saved.

If you are new to this concept, you probably have done this without even knowing you were doing it.  When an IF statement is written, what follows the anchor?  An open parenthesis.  And, the ENDIF is followed by a close parenthesis.  There is your block!

"East"
(IF(@ISMBR("East"))
    "East" = "East" * 1.1;
ENDIF)

I have seen this very simple change drastically improve calculations.  Go back to a calculation that can use blocks and test it.  I bet you will be very pleased with the improvement.

Almost every planning or forecasting application will have some type of allocation based on a driver or rate that is loaded at a global level.  Sometimes these rates are a textbook example of moving data from one department to another based on a driver, and sometimes they are far more complicated. Many times, whether it is an allocation, or a calculation, rates are entered (or loaded) at a higher level than the data it is being applied to.

A very simple example of this would be a tax rate.  In most situations, the tax rate is loaded globally and applied to all the departments and business units (as well as level 0 members of the other dimensions).  It may be loaded to “No Department”, “No Business Unit”, and a generic member in the other custom dimensions that exist.

If a user needs the tax rate, in the example above, they have to pull “No Department” and “No Business Unit.”  Typically, users don’t want to take different members in the dimension to get a rate that corresponds to the data (Total Department for taxes, and No Department for the rate).  They want to see the tax rate at Total Department, Total Business Unit, and everywhere in-between.

There are a number of ways to improve the experience for the user.  An effective solution is to have two members for each rate.  One is stored and one is dynamic.  There is no adverse effect on the number of blocks, or the block size.  The input members can be grouped in a hierarchy that is rarely accessed, and the dynamic member can be housed in a statistics hierarchy.

Using tax rate in the example above, create a “Tax Rate Input” member.  Add this to a hierarchy called “Rate Input Members”.  Any time data is loaded for the tax rate; it is loaded to Tax Rate Input, No Department, No Business Unit, etc.  Under the statistics/memo hierarchy, create a dynamic member called “Tax Rate”.  “Tax Rate” would be the member referenced in reports.  The formula for this includes a cross-dimensional reference to the “Tax Rate Input” member, and would look something like this.

“No Department”->”No Business Unit”->”Tax Rate Input”;

When a user retrieves “Tax Rate”, it always returns the rate that is loaded to “No Department,” “No Business Unit,” and “Tax Rate Input,” no matter what department or business unit the report is set to.  The effort involved in creating reports in Financial Reporting or Smart View now becomes easier!

There is an added bonus for the system administrators.  Any calculation that uses the rate (you know, the ones with multi-line cross-dimensional references to the rates) is a whole lot easier to write, and a whole lot easier to read because the cross-dimensional references no longer exist.

Before you move the application to production, make sure to set the input rates consolidation method to “Never.”  Don’t expect this change to make great improvements in performance, but it will cause the aggregations to ignore these members when consolidating the hierarchies.  A more important benefit is that users won’t be confused if they ever do look at the input rates at a rolled up level.  The ONLY time they would see the rate would be at level 0, and would be an accurate reflection of the rate.

Note:  It is recommended to create member names without spaces.  The examples above ignored this rule in an effort to create an article that is more readable.

1. SSL Offloading
2. 2-way SSL deployment
3. SSL termination at the web server

1. Additional information in migration status reports, including source and destination details.
2. Users must be provisioned with the Shared Services Administrator role to work with the Deployment Metadata tool.
3. The Calc Manager is supported, and has its own node under Foundation. As a result, business rules can now be migrated to classic HFM and Planning applications.

1. Manage Taskflows – This role allows users to create and edit a taskflow
2. Run Taskflows – This role permits users to view and run a taskflow, but they cannot create or edit taskflows

Working with people new to Essbase every three to six months, I am always looking for ways to show users their hierarchies effectively. Many of them don’t have access to Essbase administration services or EPMA.  So, I always fall back to excel as a distribution method, as well as documentation, to show hierarchies.

Expanding hierarchies to all descendants is a great way to show small hierarchies, but, I am always asked to make it a collapsible hierarchy using the Excel grouping feature. The challenge of doing this manually to a hierarchy with thousands of members is that it is extremely time consuming and very error prone.

The following script can be added to any workbook to automate this effort.

Sub CreateOutline()
    Dim cell As Range
    Dim iCount As Integer
    For Each cell In Selection
        'Check the number of spaces in front of the member name 
        'and divide by 5 (one level)
        iCount = (Len(cell.Value) - Len(Trim(cell.Value))) / 5
        'Only execute if the row is indented
        If iCount <> 0 Then cell.EntireRow.OutlineLevel = iCount
    Next cell
    MsgBox "Completed"
End Sub

Setup

First, this sub routine has to be added to a workbook.  Open up the visual basic editor. Right click on the workbook in the project explorer window and add a new module. Paste the code above in the new module.  The editor is in different places in different version.  In Excel 2007 and 2010, the Developer ribbon is not visible by default.  To make it visible, go to the navigator wheel and click Excel Options.  There is a checkbox named Show Developer Ribbon that will make this developer ribbon viewable.

How To Use

First, open the member selection option in the Essbase add-in or smart view and select the parent.  Add all its descendants.  Alternately, change the drill type to all descendants and zoom in on the member of the hierarchy.

Retrieve, or refresh, the data, and make sure the indent is set so the children are indented.  Now, highlight the range of cells that has the hierarchy/dimension that the grouping should be applied. This should include cells in one column of the worksheet.  Open the code editor and place the cursor inside the sub routine you added from above and click the green play triangle in the toolbar to execute the script.  When this is finished, go back to the worksheet with the hierarchy and it will have the hierarchy grouped.

Excel limits the level of groupings to eight. If the hierarchy has more than eight levels, they will be ignored. Now, the hierarchy can be expanded and collapsed for viewing.

Shortcut keys or toolbar buttons can be assigned to execute this function if it is used frequently. If you are interested in doing this, there are a plethora of how-to articles on this topic.  This Google search will get you started if you choose to go down that path.

So, the next time you need to explain a hierarchy in Essbase, or distribute it in a common format, hopefully this script will help.

Working with people new to Essbase every three to six months, I am always looking for ways to show users their hierarchies effectively. Many of them don’t have access to Essbase administration services or EPMA.  So, I always fall back to excel as a distribution method, as well as documentation, to show hierarchies.

Expanding hierarchies to all descendants is a great way to show small hierarchies, but, I am always asked to make it a collapsible hierarchy using the Excel grouping feature. The challenge of doing this manually to a hierarchy with thousands of members is that it is extremely time consuming and very error prone.

The following script can be added to any workbook to automate this effort.

Sub CreateOutline()
    Dim cell As Range
    Dim iCount As Integer
    For Each cell In Selection
        'Check the number of spaces in front of the member name 
        'and divide by 5 (one level)
        iCount = (Len(cell.Value) - Len(Trim(cell.Value))) / 5
        'Only execute if the row is indented
        If iCount <> 0 Then cell.EntireRow.OutlineLevel = iCount
    Next cell
    MsgBox "Completed"
End Sub

Setup

First, this sub routine has to be added to a workbook.  Open up the visual basic editor. Right click on the workbook in the project explorer window and add a new module. Paste the code above in the new module.  The editor is in different places in different version.  In Excel 2007 and 2010, the Developer ribbon is not visible by default.  To make it visible, go to the navigator wheel and click Excel Options.  There is a checkbox named Show Developer Ribbon that will make this developer ribbon viewable.

How To Use

First, open the member selection option in the Essbase add-in or smart view and select the parent.  Add all its descendants.  Alternately, change the drill type to all descendants and zoom in on the member of the hierarchy.

Retrieve, or refresh, the data, and make sure the indent is set so the children are indented.  Now, highlight the range of cells that has the hierarchy/dimension that the grouping should be applied. This should include cells in one column of the worksheet.  Open the code editor and place the cursor inside the sub routine you added from above and click the green play triangle in the toolbar to execute the script.  When this is finished, go back to the worksheet with the hierarchy and it will have the hierarchy grouped.

Excel limits the level of groupings to eight. If the hierarchy has more than eight levels, they will be ignored. Now, the hierarchy can be expanded and collapsed for viewing.

Shortcut keys or toolbar buttons can be assigned to execute this function if it is used frequently. If you are interested in doing this, there are a plethora of how-to articles on this topic.  This Google search will get you started if you choose to go down that path.

So, the next time you need to explain a hierarchy in Essbase, or distribute it in a common format, hopefully this script will help.