Calculations with Duration Field

denisv

I have a report that uses a duration attribute from a business object. I have 'summed' this balue in the report.

I also have a cliet rate per hour attrribute from another business object.

In the report I can get to the hourly rate. What format is necessary to calculate the sum of duration * hourly rate.

Effectively how to I use a duration field in a calculation.

aware_support2

The formula will depend on the desired accuracy. The example below shows how to calculate an amount based on the number of full hours in the given duration. Assuming the result of duration multiplied by rate is a monetary value, you can use a tag like this in your report:

<<HOURS(SomeObject.Duration) * SomeObject.HourlyRate,#,##0.00>>

The following example uses a duration value rounded to nearest minute and converted to an hourly value:

<<MINUTES(SomeObject.Duration)/60 * SomeObject.HourlyRate,#,##0.00>>

denisv

The formulas given work but:

I have a duration field that would contain a value of say 2:30

a) The HOURS(TimeCard.TimeUsed) gives me the total hours = 2
b) The MINUTES(TimeCard.TimeUsed)/60 gives me the total mins in dec, 0.5
c) I have the hourly rate

I need to be able to multiply the hourly rate by the full fraction value of 2.5

I also need to be able to do the same calculation as a total for all entries in the report. Can you assist ?

denisv

CORRECTION:

I have worked out the per line item total as follows:

(MINUTES(TimeCard.TimeUsed)/60+HOURS(TimeCard.TimeUsed))*CallRecord.CompanyCall.HourlyRate

I need to be able to do it for the total on the report of all line items. I have totalled the time as follows:

SUM TimeCard.TimeUsed

The closest I have come to this working is:

(HOURS(SUM TimeCard.TimeUsed)+MINUTES(SUM TimeCard.TimeUsed)/60)*CallRecord.CompanyCall.HourlyRate

aware_support2

Function MINUTES returns the number of minutes within the last hour (0 - 59). Function HOURS returns the number of hours within the last day (0 - 23). Function DAYS returns the number of days within the last week (0 - 6). If the duration in your case may exceed one week, here is the complete expression to calculate the amount based on hourly rate:

(WEEKS(TimeCard.TimeUsed)724 + DAYS(TimeCard.TimeUsed)*24 + HOURS(TimeCard.TimeUsed) + MINUTES(TimeCard.TimeUsed)/60) * CallRecord.CompanyCall.HourlyRate

This assumes CallRecord is available in the context.

As you have noted, you can use the same expression in reports where instead of "TimeCard.TimeUsed" you would use "SUM TimeCard.TimeUsed " to calculate the total duration.

denisv

In the case of the line calculation, the formula I was using and the one you have given me (which includes the Week function) return the same correct values. This is becasue my duration field is onl hh:mm.

However, trying to sum will not work:

Manual total of all calculated daily rate * hours per line = 330

The following returns 370:

(WEEKS(SUM TimeCard.TimeUsed)724 + DAYS(SUM TimeCard.TimeUsed)*24 + HOURS(SUM TimeCard.TimeUsed) + MINUTES(TimeCard.TimeUsed)/60) * CallRecord.CompanyCall.HourlyRate

Times are: 2:50, 4:10, 1:15
Line item totals returned: 113, 167, 50
Rate = 40

aware_support2

Firstly, "MINUTES(TimeCard.TimeUsed)/60" in the expression above should be "MINUTES(SUM TimeCard.TimeUsed)/60".

Secondly, could you, please, add the following fields to your report, run the report and let me know the values that are printed:

<<HOURS(SUM TimeCard.TimeUsed)>>
<<MINUTES(SUM TimeCard.TimeUsed)/60>>

denisv

Corrected the SUM function - no resolution.

I had actually added the two other functions you requested to see what was happeningand the values look strange:

<<HOURS(SUM TimeCard.TimeUsed)>> RETURNS HH:MM9
<<MINUTES(SUM TimeCard.TimeUsed)/60>> RETURNS HH:MM1

aware_support2

Are you sure there are only 3 TimeCard instances in the report? Could you please add the following field to the report, run it and let me know the result:

<<COUNT TimeCard>>

denisv

I have tried to add the control you requested but get the followng message:

Attribute TimeCard is of type that cannot be used in aggregate expression

However, looks like you have found something. There are definitely only 3 time instances in the report and the total of time using

<<SUM TimeCard.TimeUsed>> is correct.

But there is a time card instance on another call record which does make up the balance for the total of the report. Matbe my query is wrong (although as I said the items on the report are correct.

Custom Query Used:
FIND TimeCard WHERE TimeCard.CallRecord=CallRecord

denisv

I have tried to add the control you requested but get the followng message:

Attribute TimeCard is of type that cannot be used in aggregate expression

However, looks like you have found something. There are definitely only 3 time instances in the report and the total of time using

<<SUM TimeCard.TimeUsed>> is correct.

But there is a time card instance on another call record which does make up the balance for the total of the report. Matbe my query is wrong (although as I said the items on the report are correct.

Custom Query Used:
FIND TimeCard WHERE TimeCard.CallRecord=CallRecord

aware_support2

It's strange you are getting the message since TimeCard is an object, not an attribute. Let's try this. In the report designer click the <<->> icon (Create a tag expression element) on the tool bar, click and drag to indicate a rectangle inside Summary band of the report, then enter "COUNT TimeCard", press Enter on the keyboard. The system should accept it and show it as "<<COUNT TimeCard>>". Right click on the field, select Properties, click Evaluation Details button, click "At the end of the report" and save the settings and the report. Run the report and let me know the result.

denisv

Sorry, message is still the same. I have entered the tag as you show and you can save&close the designer. It is at the point where it then needs to save the report itself.

Note: when you enter the count, even the system sees TimeCard as an object to select.

This aside, it is clear that the summary part of the report is picking up the other TimeCard instance against a different call record.

aware_support2

Could you please send your configuration to support@awareim.com and explain how/where to run the report.

aware_support2

The message complaining about "<<COUNT TimeCard>>" has been fixed starting from build 834.

When SUM is used inside another function in reports, you need to add necessary conditions to fully qualify the instances for SUM. In your case the condition would be the same as in the custom query used for the report. Then the complete calculation expression would look like this:

(WEEKS(SUM TimeCard.TimeUsed WHERE (TimeCard.CallRecord = CallRecord))724 + DAYS(SUM TimeCard.TimeUsed WHERE (TimeCard.CallRecord = CallRecord))*24 + HOURS(SUM TimeCard.TimeUsed WHERE (TimeCard.CallRecord = CallRecord)) + MINUTES(SUM TimeCard.TimeUsed WHERE (TimeCard.CallRecord = CallRecord))/60) * CallRecord.CompanyCall.HourlyRate