Hi experts,
I have performance issue in calc script.
I had the next script to calculate a custom member called Impuestos (Taxes),
This is just a part of the whole script.
I hadn't the range accounts to calculated it, so I used the expression, account by account,
and so on for 30 members.
Sub Calculate()
.
.
.
'Calcula Impuestos
HS.Exp "IND#ER_IMPTOS.A#MA_7101.CC#CC_801 = IND#NA_INDICADOR.A#MA_7101.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7103.CC#CC_801 = IND#NA_INDICADOR.A#MA_7103.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7104.CC#CC_801 = IND#NA_INDICADOR.A#MA_7104.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7105.CC#CC_801 = IND#NA_INDICADOR.A#MA_7105.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7106.CC#CC_801 = IND#NA_INDICADOR.A#MA_7106.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7107.CC#CC_801 = IND#NA_INDICADOR.A#MA_7107.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7120.CC#CC_801 = IND#NA_INDICADOR.A#MA_7120.CC#CC_801"
HS.Exp "IND#ER_IMPTOS.A#MA_7101.CC#CC_851 = IND#NA_INDICADOR.A#MA_7101.CC#CC_851"
HS.Exp "IND#ER_IMPTOS.A#MA_7103.CC#CC_851 = IND#NA_INDICADOR.A#MA_7103.CC#CC_851"
HS.Exp "IND#ER_IMPTOS.A#MA_7104.CC#CC_851 = IND#NA_INDICADOR.A#MA_7104.CC#CC_851"
HS.Exp "IND#ER_IMPTOS.A#MA_7105.CC#CC_851 = IND#NA_INDICADOR.A#MA_7105.CC#CC_851"
HS.Exp "IND#ER_IMPTOS.A#MA_7106.CC#CC_851 = IND#NA_INDICADOR.A#MA_7106.CC#CC_851"
HS.Exp "IND#ER_IMPTOS.A#MA_7107.CC#CC_851 = IND#NA_INDICADOR.A#MA_7107.CC#CC_851"
HS.Exp "IND#ER_IMPTOS.A#MA_7120.CC#CC_851 = IND#NA_INDICADOR.A#MA_7120.CC#CC_851"
.
.
.
End Sub
But now I have the range for calculate the member, so I have the next script.
I use loops and if conditions for exist accounts.
The issue is that the second script takes 3+ hours for the whole script and the first script takes 30 minutes.
But I need the second script because I use a range of accounts,
So if I need add an account just I update the account dimension in the dimension library and the script takes this new account.
Sub Calculate()
vMayor = HS.Account.List("T005 RESULTADOS","[Base]")
vCostos = HS.Custom1.List("","[Base]")
.
.
.
For k = 801 To 801
For Each Citem in vCostos
If "CC_"&k = Citem Then
For j = 7000 To 7999
For Each Aitem in vMayor
If "MA_"&j = Aitem Then
HS.Exp "IND#ER_IMPTOS.A#MA_" & j & ".CC#CC_" & k & " = IND#NA_INDICADOR.A#MA_" & j & ".CC#CC_" & k
Exit For
End IF
Next
Next
End If
Next
Next
.
.
.
End Sub
What can I do to optimize this script and keep the range in the script for possible updates in the account dimension