I have numeric values being returned from a database. Values whch are NOT to be displayed are returned as -999999.00 so I am setting these as MISSING in a DEFINE.

The requirement is then to show these values as '-' instead of blank, but to show the numerics as the actual values. This is easy enough when the output format is HTML (SET NODATA = '-'), but the output format is EXL2K.

I have tried every combination possible to effect this from DEFINES to COMPUTES to STYLESHEET settings.

I can't redefine the MISSING to an alpha because it is incompatible with the field format in the case where the numeric is valid.

I've used FTOA, but then the result is non-numeric in Excel and that is not desirable.

I am here as a last resort. Has anyone been able to display an ALPHA or a NUMERIC in the same DEFINE field and have the NUMERIC be treated as such in EXCEL?

Code sample:

DEFINE FILE MYFILE
FIELD1/D7.2 MISSING ON=IF FIELD1 LT -100 THEN MISSING ELSE FIELD1 * 100;
END

Now what?

Many thanks in advance.

This message has been edited. Last edited by: Kerry,

Look for "Applying Dynamically Formatted Virtual Fields to Report Columns" in the Creating Reports With WebFOCUS Language documentation. The format of a report field can be based on an if-then-else expression...

Francis, thanks for the suggestion, but a numeric can not be reformatted to a non-numeric using that method. Dynamic formatting allows you to specify different NUMERIC formatting to the same numeric field.

I need to say
NEWFIELD/[format]=IF [numeric-field] EQ -9999999 THEN '-' ELSE [numeric-field];

Use PTOA:

  
DEFINE FILE CAR
  XR1_COST/P9.2C = IF COUNTRY EQ 'JAPAN' THEN RETAIL_COST * (-1) ELSE RETAIL_COST;
  XR2_COST/A12   = IF XR1_COST LT 0 THEN '-' ELSE PTOA(XR1_COST,'(P9.2C)', XR2_COST);
END
TABLE FILE CAR
PRINT 
    CAR 
    DEALER_COST 
    XR1_COST 
    XR2_COST 
    RETAIL_COST
  BY COUNTRY
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K 
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=DATA,
     COLUMN=N5,
     JUSTIFY=RIGHT,
$
ENDSTYLE
END
 
-EXIT

hth

Tom, I've been down that road, too. The issue is that they want the NUMERICS in Excel treated as such so they can manipulate them using standard calculations but also want blanks represented as '-'.

This method treats the result as TEXT.

"You can not do this using WebFOCUS" is an acceptable answer.

Yes, you can... But, obviously, you have your answer. Thanks for sharing...

Tom, I do not post to this forum often because I research items thoroughly before I ask questions and am usually able to find the answers by either searching through Focal Point or by reading the manuals. I know that's a novel concept.

If you can show me how you are able to generate the column XR2_Cost as a NUMERIC and have it treated as such in Excel without manipulating it via that application, I will chalk your comments up to a miscommunication.

Thanks again.

quote:

"You can not do this using WebFOCUS" is an acceptable answer.

Ask the question, How do you do this in Excel?

Do you actually mean "You can not do this in Excel. Why should you expect WebFOCUS to do it"

try adding the following to an excel sheet and see what it does

1
2
3
-
4
=SUM(A1:A5)

add the following and see what it does

1
2
3
'-
4
=SUM(A1:A5)

Use a macro to manage it (EXL2K TEMPLATE)

Hey John,

Happy Holidays to you!

I wasn't even going to reply, but, since you responded to this, I wanted to say "Hey" to you. Hope all is well!

Tom

JG,
Thank you. It's been my experience that you can pretty much do anything with WebFOCUS if you code it properly. I wanted to make sure I wasn't missing something before turning my attention to the Microsoft side of things.

Thanks again for the input.

Tom's example works and can do numeric calculations on the XR2_COST field in Excel even with Excel thinking it is string format as long as it isn't the dash.

quote:

as long as it isn't the dash.

That's why you need a macro.

Tom, finish work on the 24th start again on the 27th. 1st Jan off, start again on the 3rd.

Wife and family not really happy but that's the job.

To one and all

                                            *
                                           ***
                                          *****
                                         *******
                                        *********
                                       ***********
                                      *************
                                     ***************
                                            **

This approach may produce the desired output, but raises other issues regarding the Excel grid lines.

SET NODATA = '-'
-*
DEFINE FILE CAR
 DL_COST/D12.2 MISSING ON = IF COUNTRY EQ 'FRANCE' THEN MISSING ELSE DEALER_COST ;
END
-*
TABLE FILE CAR
 SUM DL_COST
 BY COUNTRY
 ON TABLE HOLD FORMAT HTMTABLE
END
-*
SET HTMLFORMTYPE=XLS
-RUN
-*
-HTMLFORM BEGIN
!IBI.FIL.HOLD ;
-HTMLFORM END