VFP reporter. Subtotals in converted format

[AndrewMozley]

(Simplified)

I have a report which shows <Employee> <Job> <Time spent>

The time is stored in minutes, but I like to display it as hh.mm

So a few of lines of the report might have :
[tt]
John Smith J1234 7.00
X0045 2.30

Will Young J1234 11.00
[/tt]
I have a function Convhrs() which will convert a number of minutes to the display format shown above (e.g. 150 minutes shows as 2.30 - two and a half hours). So the expression for that field on the report is Convhrs(detail.minutes)

That works fine. However I would also like to show subtotals and a grand total on the report. However I know that I cannot specify the total field for time by specifying the field as Convhrs(detail.minutes) and specifying that it has to be calculated as Sum, because the way the reporter then works is to evaluate the individual Convhrs() results - which are character strings - and then try to add them together - which does not work. What I had wanted is to add them up, and then apply the Convhrs() function.

Thanks in advance

 

[JRB-Bldr]

Unfortunately, you would have been better off if you had done the Conversion-for-Display in the data table BEFORE sending it to the Report Form.

To do that you would have the original data fields, plus add a few more to support the DispTime(s). Then you would put your Converted values into the DispTime fields and still have the original field values to use for the VFP Report Form SUM.

Something like...

SELECT Details.*,;
     SPACE(10) AS DispTime,
    FROM Details;
    INTO CURSOR RptData READWRITE

SELECT RptData
REPLACE ALL DispTime WITH Convhrs(Minutes)
GO TOP
REPORT FROM MyReport

If you had done that, then the Report Form's SUM would work on the original values in the Minutes field(s) in the RptData data table while you can still display the DispTime in the Report Form.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Andrew,

I agree with JRB-Bldr that it would be better to do the conversion at the pre-report stage.

However, another option would be for the report to include both items, that is, the raw number of minutes and the formatted string. For the number of minutes, you would set the Print When to a logical false, so that it won't actually get printed. But you can still use it in the expression of a calculated field, to get the totals that you need.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

By creating a RptData data cursor (or whatever you want to name it) as I have shown above you end up with: another option would be for the report to include both items since both the DispTime field and the Minutes fields would be included in the data cursor.

But you would not need to set the Print When to a logical false, so that it won't actually get printed. Instead just do not include Minutes as one of the Form's text object expressions.
In that way the data will be present within the data record for the Report Form calculations to use, but it will not be displayed in report in its 'raw' form - only the Converted Display format (as DispTime).

Then within the Report Form you can create a variable which will summarize the Minutes (such as TotalMins) and then, if desired, make the textbox expression in the Group Footer or Summary band be: Convhrs(TotalMins)

Also - oops on the typo above...

REPORT [B]FORM[/B] MyReport

Good Luck,
JRB-Bldr

 

[AndrewMozley]

Thank you for both of your prompt replies. But, with respect JRB-Bldr, the method described in your first reply does not work.

I can indeed do the conversion into an extra field, disptime, in the cursor, and I can display this disptime field in the detailed record on the report.

But I have nothing from which to display the first level subtotal – in the above example, 9.30 (hrs.mins). I cannot total the disptime field because it is a character field, not numeric, and if I total my original mins field, I am left with the original problem that I cannot convert it from 570 minutes to 9.30 (hrs.mins).

But let me know if I am being rather dense!

I am thinking about the suggestion in your second email of have a report variable. I am not entirely familiar with these. I imagine I would need to include some code in the form to increment and reset the total, and then display it using my convhrs() function.

Thanks again

 

[JRB-Bldr]

"But I have nothing from which to display the first level subtotal". I cannot total the disptime field because it is a character field, not numeric, and if I total my original mins field, I am left with the original problem that I cannot convert it from 570 minutes to 9.30 (hrs.mins)./B]

Of course you can total the original mins - do so into a new Report Form variable - maybe something like TotalMins (see 2nd posting). Then when you want to display the subtotal in the modified format, you put the ConvHrs(TotalMins) into the Expression of the Report Form's subtotal textbox.

And, as far as Resetting the Report Form's variable, you set that when you create the variable - you tell it when to reset (such as when the Group changes).

You may want to look at the tutorial videos about Reporting (Part 1 & 2) at:

http://www.garfieldhudson.com/FreeVideos.aspx

Good Luck,
JRB-Bldr