There is a new, and often requested, option added to Smart View. If you use drill through to Data Management (PBCS) or FDMEE (On Prem), download the most recent release of Smart View. We, as users, now have the option to change where the result of our drill through queries is returned. Users can either be asked where the result should be displayed, have it displayed in your browser, or (drum roll) have another tab created that holds the results in Excel. Read more
Overview
If you have ever used custom functions in Excel, depending on the complexity of them, you have probably run into an issue where the accuracy of the results was sporadic. There is a quick solution. Read more
The newest release of Smart View (Release 11.1.2.1.102) includes many helpful, long-awaited features sure to both enhance the end-user experience and increase productivity. Many of the new features are examined below while a complete list and their descriptions can be found here.
Ad Hoc Operations in Multiple Cells
Previous versions of Smart View limited users to cell level ad-hoc operations. For example; say you wanted to ‘Zoom In’ on a member, Smart View limited this action to a single cell (single member). The newest version allows users to select a range of cells before performing an action allowing for quicker answers. Multi-cell actions such as Zoom In, Zoom Out, Keep Only and Remove Only are now permitted. Read more
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. Read more
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.
A lot of users like to see hierarchies in Excel and build groupings around these hierarchies so they can be collapsed and expanded easily. It is not a huge deal to do this for things that don’t change a lot, like months rolling to a quarter, but it can be extremely cumbersome to maintain for organizational or account hierarchies that are large or change frequently.
By adding some VBA code (a macro) to your workbook, managing groupings can be completely automated. This can be customized for a plethora of different scenarios. Below are 2 examples that Hyperion users will encounter. One caveat to this is that Excel limits the number of grouping levels to 8. If the worksheet has more than 8 levels, the following logic would not provide the expected result.
Creating a Hierarchy Based On Excel Indents
If a spreadsheet exists where the hierarchy is created with the indent (not multiple columns) feature of Excel, select the range for the groupings to be applied. Execute the following script. Basically, this loops through the cells you have selected and will create the groupings based on the number of indents in the cell.
Sub CreateGroupingsOnIndents() Dim cell As Range For Each cell In Selection If cell.IndentLevel <> 0 Then cell.EntireRow.OutlineLevel = cell.IndentLevel Else cell.EntireRow.ClearOutline End If Next End Sub
Creating a Hierarchy Based On SmartView/Excel Add-In Indents
When retrieving from Essbase, cells are indented by adding 5 spaces to the member name. By getting the length of the cell, subtracting the number of spaces preceding the member name, and dividing the result by 5, the level of the indent is identified. Select the cells with the member names and execute the following.
Sub CreateGroupingsOnSpaces() Dim cell As Range Dim iLength1 As Integer Dim iLength2 As Integer Dim iIndent As Integer For Each cell In Selection iLength1 = Len(cell.Value) iLength2 = Len(LTrim(cell.Value)) iIndent = (iLength1 - iLength2) / 5 If iIndent <> 0 Then cell.EntireRow.OutlineLevel = iIndent Else cell.EntireRow.ClearOutline End If Next End Sub
Setup a Module
If you are unfamiliar with adding custom code to an Excel workbook, follow the steps below.
Excel 2000 and below
- Select Tools/Macro/Visual Basic Editor
- Right click on the workbook in the Project window, and select Insert/Module
- Expand the module folder and open the new module (likely module1)
- Paste the example above in this window to the right
- Execute it by clicking F5 or the green play triangle in the toolbar
Excel 2003 and greater
- Select the Navigation Wheel, and check the “Show Developer tab in the Ribbon” checkbox in the Popular tab
- Select the Developer Ribbon and click Visual Basic
- Right click on the workbook in the Project window, and select Insert/Module
- Expand the module folder and open the new module (likely module1)
- Paste the example above in this window to the right
- Execute it by clicking F5 or the green play triangle in the toolbar
These can also be associated to a custom menu or toolbar if you choose to take the extra step!
Many processes need to write large volumes of data in Excel. The typical method is to loop through each cell and perform the action.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
CurrVal = CurrVal 1
Next CurrCol
Next CurrRow
Rather than writing the values out cell by cell, it is quicker to store the value in an array and write the array to a range of cells at one time.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
Dim TempArray() As Double
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Update the array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal
CurrVal = CurrVal 1
Next j
Next i
' Transfer temporary array to worksheet
TheRange.Value = TempArray
This same method can be used when altering data. By changing the following line
TempArray(i, j) = CurrVal
To this
TempArray(i, j) = TheRange(i, j) * 3
By using TheRange(i, j), the existing value can be altered
The process of writing values cell by cell took 3.16 seconds. Using the array method, it took .08 seconds, nearly 40 times faster
Many processes need to write large volumes of data in Excel. The typical method is to loop through each cell and perform the action.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
CurrVal = CurrVal 1
Next CurrCol
Next CurrRow
Rather than writing the values out cell by cell, it is quicker to store the value in an array and write the array to a range of cells at one time.
Dim CellsDown As Long
CellsAcross As Long
Dim CurrRow As Long
CurrCol As Long
Dim CurrVal As Long
Dim TempArray() As Double
' This can be replaced with the selected range and is just used to illustrate this example.
CellsDown = 1000
CellsAcross = 36
' Update the array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal
CurrVal = CurrVal 1
Next j
Next i
' Transfer temporary array to worksheet
TheRange.Value = TempArray
This same method can be used when altering data. By changing the following line
TempArray(i, j) = CurrVal
To this
TempArray(i, j) = TheRange(i, j) * 3
By using TheRange(i, j), the existing value can be altered
The process of writing values cell by cell took 3.16 seconds. Using the array method, it took .08 seconds, nearly 40 times faster
Working with finance and accounting professional the majority of my career, I see a lot of spreadsheet “templates” that are reused for multiple budget passes or monthly forecasting processes (any repatative process). When the workbooks have a number of worksheets, and they are large, it can be extremely tedious to clear out the old data and get back to a fresh, empty shell. The script below can be executed on any worksheet to clear out all the numeric values and cell comments. It ignores cells with dates, formulas, or text values.
'loop through each cell in the range of cells used in the worksheet For Each c In ActiveSheet.UsedRange.Cells 'If the cell value is null, don't do anything If Not IsNull(c.Value) Then 'Do not execute on formulas or non numeric values If Not c.HasFormula And IsNumeric(c.Value) Then 'If a cell comment exists and it is not equal 'to "KEEP", set the value of the cell to null If Not (c.Comment Is Nothing) Then If c.Comment.Text <> "KEEP" Then c.Value = Null ElseIf c.Comment Is Nothing Then c.Value = Null End If End If End If 'Execute on all cells in the range where the cell has a comment If Not (c.Comment Is Nothing) Then 'If the comment is equal to "KEEP", don't delete the comment If c.Comment.Text <> "KEEP" Then c.Comment.Delete End If Next c MsgBox "Complete"
Breaking It Down
The outside loop will loop through each cell using the ActiveSheet.UsedRange.Cells. This function will get the range of cells on the worksheet that has been used. UsedRange will take the equivalent range of using CTRL-HOME to get the upper left cell and CTRL-END to get the bottom right of the range.
For Each c In ActiveSheet.UsedRange.Cells
…
Next c
Each cell will be checked to verify that the value is not blank, is not a formula, and is numeric (not text). If this criteria is true, the value will be set to nothing.
If Not IsNull(c.Value) And Not c.HasFormula And IsNumeric(c.Value) Then
c.Value = Null
End If
If the cell has a cell comment, it will be removed as well.
If Not (c.Comment Is Nothing) Then
c.Comment.Delete
End If
In the full example, some additional lines are added to ignore clearing any cell with a cell comment of “KEEP”.
How To Use
To use this script, it must be added to a module. The easiest way to do this is to create a macro and associate a CTRL-? key to it.
In Excel 2007, select the Developer ribbon and click the Record Macro button. Immediately click the Stop Recording button. This will create a function in a new module for you. If the Developer tab is not visible, click the Office Button and click the Excel Options button. On the Popular tab, select Show Developer Ribbon.
In Excel 2003, select the Tools / Macro / Record New Macro menu. Immediately click the Stop Recording button.
After opening Visual Basic in Excel, expand the spreadsheet in the Project window. Expand the Modules tree and open the module. Inside the module will be a procedure that is empty. Paste the script inside the procedure. This can now be accessed by the CTRL-? that was assigned.
I will be posting more scripts like this. If you find this helpful, add your email to our mailing list near the top of the right sidebar. You will get an email any time we add a new article!