Help with formatting numeric report field, please.

[Stella740pl]

Hello, everyone!

My report has a numeric field that is currently formatted as #,###,###, and another one formatted as 9999.9, so they show minus signs for negative numbers.

Now I have a request to show also a plus sign for the positives. Short of making the whole field character, or putting the plus into a separate field, is there a format mask or a trick to easily achieve it?

Thanks.

 

[guma2go]

Hi Stella

You Can insert an expression but this will be not given
thousand sign.

iif(sign(your_field)=1,'+','')+alltrim(str(your_field,15,2))

I hope so this will be for You ok.

Best Regards

 

[Mike Lewis]

Hi Stella,

I haven't tried this, but what about something like:

IIF(MyField > 0, "+", "") + :
  TRANSFORM(MyField, "99999.99")
[code]

You can, of course, change the second param of the TRANSFORM to match the desired format.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

[url=www.ml-consult.co.uk] My Visual FoxPro site: www.ml-consult.co.uk[/url]

 

[Stella740pl]

Well, thanks, I know this is an option, but I do need to separate thousands, and I didn't want to convert the field to character type. I would prefer (if possible, of course) not to mess with the expression at all, as I would have to do it in 100 (25*4) fields of detail, group footers, and summary bands.

I was thinking more along the lines of some format mask symbol I am not aware of, or some other neat trick, which would take much less effort and testing than changing all those expressions.

Does anyone know of a nice way to do it?

Also, I am using VFP6.
Can this be easily done in VFP9?

Thanks.

 

[Stella740pl]

Mike, didn't see your reply when I posted mine.
I will try.
It looks like I might have to change the expressions and field type after all.
Must say, this doesn't excite me at all.

 

[guma2go]

HI Stella

MIke have an better idea with TRANSFORM so you can match eg "999,999.99" format
Only + sign will be at start of format
eg. + 2.2
so only small correction
iif(sign(your_field)=1,'+','')+alltrim(transform(your_field,"999,999.99"))

Best regards
George

 

[Mike Lewis]

Stella,

I don't know of any formatting symbol that would do this. I wish there was. I'm sure I would have found a use for it by now.

As for the thousands separator, with my solution, that's easy to achieve. Just use "999,999.9" as the second TRANSFORM parameter.

But I take your point about not wanting to convert it to a string.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Thanks, Mike and George!

TRANSFORM() with the correct formatting, coupled with SIGN() and ALLTRIM(), work nice from the command window, so will probably work in the report setting.

It's a pity that there is no little nice format thingy to add.

I am so not looking forward to going into each of 100 fields, but it looks I don't have a lot of options.

Thanks again.

 

[jimstarr]

Hi Stella,

Though I haven't done this myself there may be some way to use the Code References tool to do a search and replace for all occurrences of #,###,### and 9999.9 in your report.

Jim

 

[teresas]

I am so not looking forward to going into each of 100 fields, but it looks I don't have a lot of options.

Stella,

I'm not familiar with the Code References tool that Jim mentioned, but maybe you could hack the frx? (Look for objtype = 8.)

Teresa

 

[Stella740pl]

Jim, Teresa,

I can hack the .frx, it's just a table, but I don't think it is going to help that much.

That's exactly what I was going to look into - to replace occurrences of particular format by adding/changing some little format symbol - IF it existed, as I hoped.

But apparently it doesn't.

So now I have to change expressions in each of them, and they are different, and more complex than your_field in the example above. At the same time, I will still have to change the format mask after all - to clean out that #,###,### completely and replace it with @R, for right-justifying the character field. I will probably have to write a little more elaborate program to do that for me.

Another thing, not all columns in the report should be changed that way; only 4 out of about 10 (it's 25 lines * 4 columns, including group footers and summary band, with multiple report variables used there), so I have to correctly identify them somehow; I can't go just by looking for #,###,### in the format.

I am not sure which is better, just dive in and do it (which still may be the fastest way) or to write a program for that (which is much more satisfying). I hope they don't change their mind after I am done (of course I will keep backups!).

 

[Mike Lewis]

Stella,

Sounds like you've got a boring few hours work ahead of you. I hope your boss appreciates the efforts you are going to.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Thanks, Mike. I hope so, too.

 

[teresas]

so I have to correctly identify them somehow

Maybe you've thought of this already, too, but on occasion I have used HPOS and VPOS to filter the records in an frx to the ones I'm interested in.


Teresa

 

[Stella740pl]

Thanks, Theresa,

Yes, I did think of the column positions already (even though I am thinking of using more criteria than that). As a matter of fact, I've already started writing a program for that. Most likely, when I am done, the program will be reused once, with slight changes, on another report by another person for similar changes. This program and the changes to the report are now on the second and third positions of my priority list, so I have first to finish something else.
When I am done with the program, I will post it here.

Thanks again.
Stella

 

[Stella740pl]

OK, I wrote a program, and it turns out to be much shorter and simpler than I expected (basically, a REPLACE in a loop), and it works (when I try it on simpler reports).

Now comes the problem:
on a real report, the newly edited Expr field gets too long, and I get an error "Report File Invalid".

I don't see reasonable ways out. Well, changing the program where the cursor/table for reporting is created would be more than I bargained for, but it seems to be the only way, and I am not sure it would be all it takes, also.

* Can something be done to the report only?

* How long can be report expression?

* Did that size increase in VFP9 (if it does, the people in charge of it may at last decide to buy us VFP9)?

Here is the code:

rptName=[f:\revenue\ridershp\report\rid_orig_totals_pluses.frx]
nColumns=4

DIMENSION arColumn(nColumns), arFormat(nColumns)

arColumn(1)=33854.167
arColumn(2)=42187.500
arColumn(3)=64062.500
arColumn(4)=71354.167

arFormat(1)=[999,999,999]
arFormat(2)=[9999.9]
arFormat(3)=[999,999,999]
arFormat(4)=[9999.9]

cPart1=[IIF(]
cPart2=[>0,'+','')+ALLT(TRAN(]
cPart3=[,"]
cPart4=["))]

*==========================================

USE (rptName) IN 0 EXCLUSIVE ALIAS Report

SELECT Report

FOR i=1 TO nColumns
   REPLACE   Picture   WITH "", ;  
             FillChar  WITH "C", ;
             Offset    WITH 1, ;
             Expr      WITH cPart1+ALLTRIM(Expr)+ ;
                            cPart2+ALLTRIM(Expr)+ ;
                            cPart3+arFormat(i)+cPart4 ;
      FOR ObjType=8 AND hPos=arColumn(i) AND ;
         ('9'$Picture OR '#'$Picture) AND !"TRAN"$UPPER(Expr)

   && With this, we replace the numeric expression with a character one, 
   &&   blank out the numeric format, and set up right justification.

NEXT

 

[jimstarr]

Stella, in VFP9 and earlier versions "Expr" is a memo field.

Jim

 

[Stella740pl]

Yes, it is a Memo field (what do you think I replaced above?), but try to type/paste an expression longer than (I am not sure how many - 254?) characters in the expression builder - you just can't.

Or type/paste them straight into the Memo field, and you will get what I got - invalid report file.

There is a limit, even though it's a Memo field.

 

[jimstarr]

I don't seem to get the behavior you describe. I created a report named dummy1 with nothing in it, and saved it. Then ran the following code:

USE dummy1.frx
REPLACE Expr WITH REPL('x',9999) && no problem
USE
MODI REPO dummy1 && no problem here either

This was using VFP5.

Jim

 

[Stella740pl]

Hm... It looks like VFP9 can have expressions over 255 characters, but previous versions can't:

http://msdn.microsoft.com/library/d...html/585ebea6-d673-4bbd-acaf-12f36b60c977.asp

Can someone who has VFP9 please tell, would it work if I SET ENGINEBEHAVIOR 90 right before running the reports, and then back right after, since not all SELECT-SQL will be compliant at once (I currently have VFP6, but some applications are converted from some 2.x versions)?

Thanks.