How to add a summary formula of group footer in the crosstab

[info12suchi]

Hi,

I have a formula in the Group footer, which calculates the total hours worked by the employees for a work date.
In report footer I have added the Cross tab to show the total hours worked by the employees for each day.
When I try to add the formula in the Cross tab summarized field. I feel the crosstab is treating the formula as a variable and just displaying the value of the time worked for the last day.

Please help me in getting the appropriate value.

 

[lbass]

You need to show the content of the formula, identify the fields you are grouping on in the main report, and also identify the row and column fields used in the crosstab.

-LB

 

[info12suchi]

Fields in the Main report

1) Work Date
2) Billing Type
3) Time(In hours)
4) Name of the Employee

Grouping Fields
1) Work Date
2) Billing Type(Billable/Non Billable)

Group Summary
1) Final_Time (Formula for the Sum of Hours)

Details of the formula:

Total_Mins := sum(Split(Cstr(Time),".")[2])
Remaining_Mins := Total_Mins mod 60
Total_Hrs := sum(Split(Cstr(Time),".")[1])+
((Total_Mins -Remaining_Mins)/60)

Final_Time := Tonumber(Totext(Total_Hrs,0)+"."+ Totext(Remaining_Mins,0))

 

[lbass]

I don't believe that could possibly be your actual formula. You are using a reserved word for a field name, and you can't sum a string. How about showing the actual formula?

What is the formula intended to do? Why not just use the original field (time in hours? You didn't explain the crosstab set up either.

-LB

 

[info12suchi]

Table name - WOMNT_LABOR

Fields in the Main report

1) WOMNT_LABOR.Work_Date
2) WOMNT_LABOR.Billing_Type
3) WOMNT_LABOR.Time(In hours)
4) WOMNT_LABOR.Name

Grouping Fields
1) WOMNT_LABOR.Work_Date
2) WOMNT_LABOR.Billing_Type(Billable/Non Billable)

Group Summary
1) Final_Time (Formula for the Sum of Hours)

Details of the formula:

Total_Mins := tonumber(sum(Split(Cstr(Time),".")[2]))
Remaining_Mins := Total_Mins mod 60
Total_Hrs := tonumber(sum(Split(Cstr(Time),".")[1]))+
((Total_Mins -Remaining_Mins)/60)

Final_Time := Tonumber(Totext(Total_Hrs,0)+"."+ Totext(Remaining_Mins,0))

The Row field in the Crosstab is WOMNT_LABOR.Work_date and Column field is Womnt_Labor.Billing_Type

The summary field i am trying to add to the cross tab is Final_Time.

Here is the data

Name Time Billing_Type Work_date
Priya 1.20 Billable 5/3/2010
Anu 2.20 Not Billable 5/3/2010
Chandra 3.30 Billable 5/4/2010
Ramki 4.10 Not Billable 5/4/2010
Rochelle2.10 Billable 5/3/2010
Bob 2.20 Not Billable 5/5/2010
Michelle3.40 Billable 5/4/2010
Yolanda 4.10 Not Billable 5/3/2010
Maria 2.10 Billable 5/5/2010
Fariba 2.20 Not Billable 5/4/2010
Shreesh 3.30 Billable 5/3/2010


Expected output I need would be as follows

Billable Non Billable

5/3/2010 7.00 6.30

5/4/2010 7.10 6.30

5/5/2010 2.10 2.20

 

[lbass]

The following formula would not compile without error for several reasons:

Total_Mins := tonumber(sum(Split(Cstr(Time),".")[2]))Remaining_Mins := Total_Mins mod 60Total_Hrs := tonumber(sum(Split(Cstr(Time),".")[1]))+((Total_Mins -Remaining_Mins)/60)Final_Time := Tonumber(Totext(Total_Hrs,0)+"."+ Totext(Remaining_Mins,0))

I don't understand why you are trying to convert it to a string and then back again. Why not just set up the crosstab with date as the row field, billing_type as the column field, and sum of time (the field, not your formula), as your summary field?

-LB

 

[info12suchi]

Hi,

When we do a normal sum of the time field it will show only the mathematical sum.

For example

Mathematical sum of 2.30+3.40 = 5.70
Where as the time sum is 6.10.

That is the reason why i am doing this in a formula instead of the direct sum.

I hope this helps.

 

[lbass]

So you are saying that the time (in hours) is really:

hour.minutes

Not hours with a decimal.

You should convert this to an actual number with decimals by using a formula like this:

stringvar array x := split(cstr({table.time}),".");
val(x[1])*60+val(x[2])

Use this formula in the crosstab, using a sum as the summary. Then right click on the cell in preview mode->format field->display string->x+2 and enter:

totext(truncate(currentfieldvalue/60),0,"")+"."+
totext(remainder(currentfieldvalue,60),0,"")

This will convert it back into the hour.minute format.

-LB

 

[info12suchi]

Hi ,

Thank a lot. Its working now. Gr8!