When to use Cross Tab?

[lucy76]

We have received a report that the developer has written in Cross Tab it is to show an employees hours work. For example

Mon Tue Wed Thur Fri
John Smith 7.6 7.6 7.6 7.6 7.6
Jane Doe 5.6 5.6 5.6 7.6 5.0

I understand from the developer that because it is written using the cross tab we have to have these hours/mins in decimal as there is no way to convert them back to time.

Is Cross Tab the best way to write this report?
Couldn't it be written without the use of Cross Tab and therefore allow to conversion from decimal to time?

Thanks Lu

 

[lbass]

I think you should be able to convert the decimal version to hours and minutes. For your summary field in the crosstab, you would use the conversion formula instead of the current {@hoursworked} formula.

SynapseVampire has written an FAQ on this conversion, and the following adapts his formula to round seconds to minutes:

numbervar hrswrk := {@hoursworked};
numbervar hours;
numbervar mins;
stringvar hhmm;

hours := truncate(truncate(hrswrk/60)/60);
mins := round(remainder(truncate(hrswrk)/60,60));
hhmm := totext(hours, "0&quot + ":" + totext(mins, "00"
hhmm;

Since this is a text result, you would need to ensure that there was only one unique value per row/column and then choose Nth Most Frequent N = 1 for your summary operation. This also means you can't do further calculations, e.g., total for the week, using this formula. You could do a manual crosstab, though, and then you could add a different formula to get the total and convert it to hours:minutes format.

-LB

 

[lucy76]

Thank you very much for that, it was very useful

Lucy