There are a lot of reasons one might loop through children in a Groovy Calculation. On my journeys with Groovy, I have run into a few roadblocks where this has been helpful. Some of these were related to limits in PBCS. Looping through sets of members allowed us to get around some of the limitations. Read more
Tag Archive for: loop
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.
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