ELCOMP but using a Subset

[Kaveenga]

Hi All,

I have a dynamic subset which filters the elements (Centre) by attributes (attribute IsCentreClosed <> Y).

What I want to do is, in a report enable a user to select a centre from the dimension using the filtered subset, and then display the children of that centre (if the centre is a consol) using the filtered subset.

In the image below, there's a SUBNM attached to where it displays "Business Markets" (Cell D10), and the rest of the report is driven by ELCOMPs.

Report.jpg (219.19 KiB) Viewed 8598 times

(numbers randomized and centre names changed)

However, since ELCOMP is based on the Dimension and not the Subset, centre "7573 Project Annihilate" which is closed appears on the report.

What I am now going to do is:

• 1. Write a VBA Function which gets the Index number of the SUBNM in cell D10
  2. Use SUBNM functions to generate the childrens' reports by simply incrementing the index number
  3. Stop generating childrens' reports when the level (ELLEV) of the element returned by the SUBNM function is higher than the level of the element in cell D10

However, what would be great is, if there is an equivalent for ELCOMP that runs off a subset.

Would appreciate any comments and suggestions, before I embark on this report.

Many thanks

Kaveenga

[David Usherwood]

Won't this work for you?

SUBNM

This is a TM1 worksheet function, valid only in worksheets.

This function returns the element of a dimension subset corresponding to the IndexOrName argument. When you double-click a cell containing a SUBNM function, the Subset Editor opens. You can then select a new element to place in your worksheet. The selected element becomes the return value of the SUBNM function, and the function automatically updates the IndexOrName argument to reflect the new element.

[Kaveenga]

Won't this work for you?

SUBNM

This is a TM1 worksheet function, valid only in worksheets.

This function returns the element of a dimension subset corresponding to the IndexOrName argument. When you double-click a cell containing a SUBNM function, the Subset Editor opens. You can then select a new element to place in your worksheet. The selected element becomes the return value of the SUBNM function, and the function automatically updates the IndexOrName argument to reflect the new element.

Hi David,

My objective is to allow the user to select one element from the subset and the report will show the cost breakdown of that element, and if the element is a consolidated element, I want to show the cost breakdown of the immediate children.

Therefore the
1. user clicks on one SUBNM formula (Cell D10),
2. this opens the subset editor,
3. user selects the element,
4. element is returned to the report,
5. report shows cost breakdown for selected element and
6. report shows cost breakdown for selected element's immediate children.


In order to generate the childrens' reports, I can't see an ease way like using ELCOMPs, so I have to
1. retrieve the index number of the SUBNM in Cell D10
2. create an array containing the immediate childrens' index numbers wrt to the dynamic subset.

I've done 1 and part way into 2 now.

Regards

Kaveenga

[Kaveenga]

Here's the code that does the job for me. The codes runs reasonably fast. However, appreciate any methods of making this more efficient.


--------------------------------------------------------------------------------------------------------------------
Function SplitFormula(sFormula As Range, ParameterID As Integer) As String
' Splits formula and returns value of the parameter ID seperated by a comma

Dim TempString As String
Dim x As Variant
Dim i As Integer

TempString = sFormula.Formula
x = Split(TempString, ",")
For i = 0 To UBound(x)
If ParameterID = i Then
SplitFormula = x(i)
GoTo Breakloop
End If
Next i


Breakloop:

End Function

--------------------------------------------------------------------------------------------------------------------

Function ELCOMPSUB( _
TM1Dimension As String, _
TM1DimSubset As String, _
TM1ParentPosition As Integer, _
TM1ChildPosition As Integer _
) As Integer

' v0.1
' This function emulates the TM1 Worksheet function ELCOMP.
' Function returns the subset index value of the immediate children
' of a consol element within a subset

Dim vSubsetIndex As Integer
Dim vParentLevel As Integer
Dim vChildLevel As Integer
Dim vParentElem As String
Dim vChildElem As String
Dim vParentIsParent As Integer
Dim vChildPosition As Integer


vParentElem = Run("SUBNM", TM1Dimension, TM1DimSubset, TM1ParentPosition)
vParentLevel = Run("ELLEV", TM1Dimension, vParentElem)

vSubsetIndex = TM1ParentPosition + 1

vChildPosition = 0
vChildElem = Run("SUBNM", TM1Dimension, TM1DimSubset, vSubsetIndex)
vChildLevel = Run("ELLEV", TM1Dimension, vChildElem)


Do While vChildLevel < vParentLevel

vParentIsParent = Run("ELISPAR", TM1Dimension, vParentElem, vChildElem)


If vParentIsParent <> 0 Then
vChildPosition = vChildPosition + 1
If vChildPosition = TM1ChildPosition Then
ELCOMPSUB = vSubsetIndex
GoTo ExitFunction
End If
End If

' Goto next element in subset
vSubsetIndex = vSubsetIndex + 1
vChildElem = Run("SUBNM", TM1Dimension, TM1DimSubset, vSubsetIndex)
vChildLevel = Run("ELLEV", TM1Dimension, vChildElem)

Loop

ExitFunction:

End Function

[paulsimon]

Kavee

You could try something complex with MDX where it gets the name of the selected element from a cube and then produces a subset of centres below that where the attribute Closed is N. The problem with that approach is that it needs to be user id specific.

Alternatively you could arrange for the Dim Updates routine to build a dimension from the existing centres dimension that just skips any that are closed, and then put the subnm on that. Then you can use ELCOMP as normal. You already have procedures that for example update a Dim from a Temp Dim, so you could just adapt one of those. The Dim Update only runs overnight, but so the dim might be out of date if a centre was closed during the day, but that wouldn't be the end of the world, and it would probably be simpler, than having to copy VBA to every sheet that lets the user pick a Centre.

Regards


Paul Simon

[lotsaram]

Hi Kaveenga (and Paul)

Not that your approaches aren't valid or won't work but this is quite a simple problem and these solutions are seriously overcomplicated for the problem at hand!

Don't forget that in this case your reporting interface is Excel which has a vast arsenal of tools which can be applied to the task of metadata validation. Why go with a "pure TM1" approach when you have Excel at your disposal? Attached is a very simple spreadsheet which solves Kaveenga's issue and uses nothing more than ELCOMP, DBRA and a VLOOKUP.
This took less than 5 minutes.

SimpleListFilter.JPG (92.73 KiB) Viewed 8538 times

SimpleListFilter.xls

(22 KiB) Downloaded 465 times

I for one would love to see a "SUBIX" function implemented BTW ...

[Kaveenga]

Thanks Paul and lotsaram.

I modified the custom ELCOMPSUB function to make it more efficient. However, since our centre hierarchy is quite deep it took a while when drilling up into higher level consolidated elements - eg. if I drilled up into All Centres, it took about 2 mins to calculate the immediate children of All Centre wrt to the subset. However, anything lower than All Centres took about 20 secs.

So now I moved to a spreadsheet based method of finding the immediate parent and immediate children of an element wrt to the selected subset.

The report works brilliantly and very fast now. Will post my final report and method soon.

Thanks again.

Regards

[paulsimon]

Lotsaram and Kavee

I would still consider the A_Centre_Open dimension idea. The problem with adding formula to the sheet is that you need to do this on every sheet. The need to only show open Centres, is probably quite a common requirement. If you use a dimension then is is re-usable on multiple sheets, since it is centralised in the server.

Regards


Paul Simon

[lotsaram]

I would still consider the A_Centre_Open dimension idea. The problem with adding formula to the sheet is that you need to do this on every sheet. The need to only show open Centres, is probably quite a common requirement.

I would agree this is a good option if this is a common requirement across a lot of reports, but if this is more of a once off then it is most likely not worth the effort (but it would still be effort much better spent than building custom TM1 VBA functions.)

[Kaveenga]

Hi Paul

Whilst having an Open Centre dimension is useful, the spreadsheet method and VBA function method helps to reuse the method/code with any subset.

Also, as you mentioned, if an attribute value is changed the reports are updated immediately without having to rerun a dim update.

Cheers

Kavee