I have made my first successful use of column equations. However, when I have attempted to be a little bit more adventurous, I have failed.
I have defined in user code the following VBScript function:
function notYetDone (Done, NotDone) 'Returns true if done remains null and not notDone
notYetDone = isnull(Done) and not notDone
end function
notYetDone = isnull(Done) and not notDone
end function
Figure 1 I include the function notYetDone as part of a column equation on taskDuration
Figure 1 point 1 highlights the IQ system field taskDuration. This column is given the label Nb Days, as in the standard InfoQube sample IQ base.
Figure 1 point 2 shows the column equation now associated with that IQ system field. The full text is: mysum(notYetDone (Done, NotDone)).
Figure 1 point 2 shows the column equation now associated with that IQ system field. The full text is: mysum(notYetDone (Done, NotDone)).
Figure 2 The column equation is no longer working
Figure 2 point 1 shows that the parent total number of days is now blank. If I remove my column equation in favour of a simple use of MySum, a figure of 50.5 days is calculated and displayed.
Can anyone suggest what I am doing wrong and how to correct it? Many thanks.
Can anyone suggest what I am doing wrong and how to correct it? Many thanks.
Comments
What you you suggest sounds plausible, but I can't get it to work.
So I define a new function:
Function sumNotYetDone(Done, NotDone, x) ' Calculates the sum of the array x if done remains null and not notDone
dim d, i, mySum
d=ubound(x)
for i=0 to d
if isnull(Done) and not notDone then mySum = mySum+NZ(x(i))
next
sumNotYetDone = mySum
End Function
To use that - and this is where I am not quite certain that I am doing the right thing - I set a column equation on TaskDuration which is:
sumNotYetDone (Done, NotDone, TaskDuration))
It is that third parameter TaskDuration which has me worried. This has to be the name of an array...
Do you have any further suggestions? Or Armando? Or any other benevolent contributor?
Your equation should then be: if isnull(Done(i)) and not notDone(i) then mySum = mySum+NZ(x(i))
sumNotYetDone (Done, NotDone, TaskDuration))
All that is actually needed is:
sumNotYetDone (Done, NotDone))
The third parameter is redundant and should be omitted.
However, as I posted yesterday, even with this change the user-defined hierarchy equation yields no result.
I have investigated further. Recall that VBScript can appear either in system code, user code or this database. Provided that the hierarchy equation appears in system code, it works. Since system code is now read-only, I cannot put my own code there. Any code which I include as a hierarchy equation in either user code or this database does not appear to be effective. So, for example, I established the very simple formula zSum based on a copy of the code of the system function sum:
Function zSum(x) ' Calculates the sum of the array x
dim d, i
d=ubound(x)
for i=0 to d
zSum=zSum+NZ(x(i))
next
end Function
Sum continues to work; zSum does not.