Format EXL2K - How to Avoid 'Number Stored as Text' Warning.

[David Briars]

I have the following code:  

DEFINE FILE CAR
 REPORT_YEAR/I4 = 2024;
END
TABLE FILE CAR
 PRINT CAR
       SALES
 ACROSS REPORT_YEAR
 ON TABLE PCHOLD FORMAT EXL2K
END

When I EXecute and open in Excel I see a 'Number Stored As Text' warning for the ACROSS heading:  

Any ideas on how to avoid this warning?  

 

 

 

 

[Debra Waybright]

I suspect there are several ways to do that, but one is to use a template. I believe Patrick has a video for how to do that, and it may depend on which version you are running.

[David Briars]

Looks like adding a control character will work in my case:  

DEFINE FILE CAR
 REPORT_YEAR/A4 = '2024';
 REPORTYEAR/A10 = REPORT_YEAR | CTRLCHAR(CR);  
END
TABLE FILE CAR
 PRINT CAR
       SALES
 ACROSS REPORTYEAR
 ON TABLE PCHOLD FORMAT EXL2K
END

 

[David Beagan]

Good to know this.

[Patrick Huebgen]

@David Briars - The problem here is not really WebFOCUS but Excel - it trys to be smart. 

As the year is a horrizontal sort - WebFOCUS defines it as text because otherwise it my be considered in asubtotal if you manualy add it later in Excel

Your   CTRLCHAR works as this prevents the "Number as text"  - the only solution is what you did - make sure Excel does not "think" it could be a number.

Better solution - make sure your Date / Year is a Date not an integer.

Additional please use XLSX instead of EXL2K to prevent the error message in my screenshot - but the Date solution will work for both versions.

Here's a sample:

DEFINE FILE ibisamp/CAR
 REPORT_YEAR/I4 = '2024';
 REPORTYEAR/YY = REPORT_YEAR;

END
TABLE FILE ibisamp/CAR
 PRINT CAR
       SALES
 ACROSS REPORTYEAR
 ON TABLE PCHOLD FORMAT XLSX
END

 

 

  

[Martin Yergeau]

Also, the obvious choice is to change the Excel settings