Modify Numeric field formatting in reports dynamically 1

[Rajesh Karunakaran]

Hi Friends,

I have a report form for Stock Reporting. This same report form is used for different industry requirements. So, some needs decimals in the stock figures (say, any item measured in liters or grams etc which falls in fractions frequently, eg sale of oils or pulses) while some not (say, any item measured by whole numbers, e.g: Garments).

My stock deriving program has decimals. However, how can I modify the field formatting (say the '99999.999' we put in 'Format' of 'Field Property') of the stock figures in the report at run time according to how many decimals need to be shown.

Thank you in advance,
Rajesh

 

[Mike Lewis]

Instead of trying to do this within the report, do the formatting in the underlying table or cursor.

To do that, add a new field to the cursor. Make this a character field, not a numeric. Then, before printing the report, populate this new field with a character representation of the number that you wish to display. Use TRANSFORM() to format the number, varying the format code (in the second parameter) according to how many places of decimals you want.

Then use that character field in your report rather than the numeric one.

So, in outline, your code might look something like this:

SELECT TheTable
REPLACE ALL StockChars WITH ;
  ICASE ( ;
    <stock field is a whole number>, TRANSFORM(StockNum, "9999"), ;
    <it has 2 decimal places, TRANSFORM(StockNum, "9999.99"), ;
    <it has 4 decimal places, TRANSFORM(StockNum, "9999.9999"), ;
    < and so on > )

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Hi Mike,

Thank you so much for that idea!

I realize now that there is much more than this I need to take care. I have to consider the number of decimals not just while reporting but right from when the calculations being done. However, I think I would not go for storing the values as chars as I am using the same underlying table to generate excel files also (optionally). But then the problem is, if I give max decimals (say 6) in report form, my shown report will unnecessarily show the full decimal places (eg 6 as in report form) even it actually need a less number (say 2).

Let us think of other ways also so that we can finalize the most appropriate method.

Thanks,
Rajesh

 

[Mike Lewis]

Rajesh, using a character field wouldn't stop you using the numeric field as well. Under my plan, you would keep the numeric field for calculations and sending to Excel. You would only use the character field for reporting; you would populate it on the fly, just before you print the report.

But I agree that, if there is a better approach, by all means go for it. Let's see what the others come up with.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads