[Solved] User Functions giving wrong Answers

[GowerMick]

I am using LibreOffice 3.5.3.2 and Oracle Report Builder, and have hit a problem with user defined Functions.

In a Report, I have an Accumulation of Durations in Minutes, which I need to convert to Hours and Minutes.

The report correctly reports the Accumulated Minutes, but the conversion functions appear a bit Hit and Miss and don't always give correct answer.

The functions are
1: AccumulatedDuration (works as expected)
2: Hours = MOD([AccumulatedDuration];60)
3: Minutes = INT([AccumulatedDuration]/60)

The report is grouped by company, and all function are used within the Company Footer of the report.

The first symptom was the Hours and Minutes did not match the AccumulatedDuration, and appeared to miss the Duration of the last record in Detail section!
A work round of sorts, was to modify the Hours and Minutes functions to include the duration of last record.
This seemed to work for most Companies, but not for a Company with only record, where the Minutes reported were Double those expected.

Question: If the report accurately Accumulates the minutes, why aren't the Hour and Minute calculated correctly.

Is it me?

[gerard24]

1: AccumulatedDuration (works as expected)
2: Hours = MOD([AccumulatedDuration];60)
3: Minutes = INT([AccumulatedDuration]/60)

And if you reverse 2 & 3 ?

[GowerMick]

Oops.

As you say, I wrote them down wrong in Post!! Duh...

I did do them correctly in the report, honest. So posted question is valid, and still needs an answer.

Mike

[chrisb]

Hello GowerMick,

Assuming the function '[AccumulatedDuration]' is an accumulation of minutes then the formulas below as pointed out by gerard24 are sound.

Hours = INT([SummedMinutes]/60)
Minutes = MOD([SummedMinutes];60)

To convince yourself that the formulas are accurate I suggest that in the 'Company' footer you drag out 3 text boxes and where there is doubt manually check the results.

The content of Box1 may not be what was expected. However Box2 * 60 + Box3 will undoubtedly match with Box1 thereby proving the accuracy of the formulas.

Box1 = '[AccumulatedDuration]'.
Box2 = 'INT([AccumulatedDuration]/60)'.
Box3 = 'MOD([AccumulatedDuration];60)'.


Your statement below clearly indicates other failures:-

GowerMick said:-
The first symptom was the Hours and Minutes did not match the AccumulatedDuration, and appeared to miss the Duration of the last record in Detail section!
A work round of sorts, was to modify the Hours and Minutes functions to include the duration of last record.
This seemed to work for most Companies, but not for a Company with only record, where the Minutes reported were Double those expected.

I don't have access to your database.
My suspicion is that your report is based on a query which utilises data from more than one table.
(Report Builder is not at fault but unexpected results may be produced when functions are applied).

NULL values may also be a factor.

You are unlikely to receive any further replies as the topic 'User Functions giving wrong Answers' is a dead end. The problem lies within the database.

If you really are stuck then consider uploading a cut down version your database to this forum with any sensitive data removed of course.

[GowerMick]

Your implication about the database being at fault doesn't hold water, and I am also aware that the formulae are sound, so why the wrong answers?

I already have three text boxes in Company Footer (as you have suggested), and it is how I spotted problem in first place

Box1 = '[AccumulatedDuration]'.
Box2 = 'INT([AccumulatedDuration]/60)'. i.e INT(Box1/60)
Box3 = 'MOD([AccumulatedDuration];60)'. i.e. MOD(Box1;60)

If I mis-stated the problem I apologise, but it is a fact that although Box 1 gives the correct answer, and Box2 and Box3 are both derived from Box1, their answers are incorrect!

i.e Box2*60 + Box 3 DOES NOT Equal Box1, which is my complaint!

Mike

[RoryOF]

What happens if you replace AccumulatedDuration in each of the above cases by the value instead of using a variable?

[GowerMick]

RoryF

Re-reading my last post, I may have mislead you into thinking I wrote the formulae directly into Boxes 2 & 3. What I meant to say that my User-Defined formulae produced the equivalent entries for the Boxes.

Following your suggestion, however, I entered the formulae directly into the boxes as follows:

1 Create Box1: Type 'Function' - Data Field 'Duration' - Function 'Accumulation' - Scope 'Group: CompanyName'
This created a function under CompanyName called 'AccumulationDurationCompanyName'

2 Created Box 2: Type 'Field or Formula' - Data Field 'INT([AccumulationDurationCompanyName]/60)'
3 Created Box 3: Type 'Field or Formula' - Data Field 'MOD([AccumulationDurationCompanyName];60)'

i.e I typed formulae directly into Data Fields of Box 2 & 3, rather than produce them via User Defined Functions.

This method worked! so many thanks.

This still leaves the original problem of why using the 'User Defined Function' methods failed, which I'll leave others to ponder.

COMMENT
Sun Report builder is not error free, and users should tread carefully when creating reports. There is always more than one way of producing answers, and if one doesn't work, another might or failing that ask this forum!

Mike

[chrisb]

Hello GowerMick,

We should not confuse function with formula.

Function:-
A named and stored basic operation which yields a single result when invoked.

Formula:-
An invariable way of doing something in order to achieve a particular end.

When we create a function then that function will process each and every row within the relevant group.
The end result of that funtion will not be known until the entire group has been processed.

We may then employ a formula in order to extract a desired result from a known value.

I hope this is clear.

[GowerMick]

Chrisb,

I am familiar with the difference between the two, but why should a formula work when applied directly to the text box, but not when applied via a function?

If I create function 'Hours' containing the formula 'MOD(SummationDurationCompanyName;60)' and apply it to a field. Surely it should give same result as applying exactly the same formula directly to the field,or am I missing something?

NB It used to work in the past, so something must have changed when OO updated.

Thanks for your help.

Mike

[chrisb]

Hello GowerMick.

A FUNCTION SHOULD ONLY BE USED WHEN NECESSITY DEMANDS A STORED RESULT.

A function will be invoked each time a row is processed within its group (Scope).

WHY I THINK YOUR USE OF FUNCTIONS DOES NOT WORK:-
You have three functions within the same group (scope).
Two of these functions reference and rely upon a value which is STORED by a third function.
It would therefore be imperative that the third function be executed before the other two.

JUST FOR INFORMATION:-
If we open the Navigator (F5) and click on a function then its Properties will be displayed in the Properties pane (F4 to toggle on/off).
If 'Pre evaluation' is set to 'Yes' then the function will be executed before the group is processed.
This is the default for the 'Maximum' and 'Minimum' functions and allows other functions to access to their STORED values.
If an accumulation function is Pre-evaluated then its STORED value will be an invariable sum total of field.
I have never had reason to Pre-evaluate an accumulation function.

Our objective is to display the sum total of the field 'Duration' in the form of HOURS and MINUTES within the group footer.
The only value we require in order to achieve this is THE SUM TOTAL OF THE FIELD 'Duration' as STORED within the function 'AccumulationDurationCompanyName'.

To extract our hours and minutes we simply apply formulas as you did in your fourth post:-
To calculate the hours we used the formula 'INT([AccumulationDurationCompanyName]/60)'
To calculate the minutes we used the formula 'MOD([AccumulationDurationCompanyName];60)'

In the 'Detail' pane:-
The exact same formulas could be used to display a running total.
The formulas when applied to the field 'Duration' will display the hours and minutes for each individual record.

TO SUM UP:
The three function notion will work if the function 'AccumulationDurationCompanyName' is Pre-evaluated.
HOWEVER:
We need only one STORED result in order to achieve our objective. NOT THREE.
Its inflexible and inefficient.
We could not have a running total.
It slows program execution.
For every necessary we make two unnecessary computations.
It's bad programming.
REMEMBER IF A STORED RESULT IS NOT REQUIRED THEN DO NOT USE A FUNCTION.

[GowerMick]

Chrisb,

I accept what you say about bad programming and inefficiency, and I shall remember it for the future. This was for a purely a personal database, so I could accept a delay of few extra milliseconds to compute. I was more concerned about why it did not work at all, which you have now explained.
(I did wonder what pre-evaluation was for).

Many Thanks

Mike