Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Closed] date formatting issue in Excel using HTMTABLE
Go
New
Search
Notify
Tools
Reply
  
[Closed] date formatting issue in Excel using HTMTABLE
 Login/Join
 
Gold member
posted
Hi,

I have a date formatting question - we have date column contains mins and seconds (Ex: 2017/08/07 00:00:00.000 Format-HYYMDs). For Displaying purpose we modified the date format it in to "07-Aug-2017" using HCNVRT and removed the unwanted values using EDIT. If I run the report normally I am getting the output is;
ENGLAND | 2017/08/07 00:00:00.000 | 07-Aug-2017 00:00:00.000 | 07-Aug-2017

But if I run through HTMTABLE why am not getting the exact date value like in normal report(Column DAT_CNV)
ENGLAND | 00:00.0 | 00:00.0 | 7-Aug-17

If I click on the cell am seeing the un formatted column in Excel " 8/7/2017 " instead of " 07-Aug-2017 "

Is there any solution? why it is behaving differently!

TABLE FILE CAR
SUM
     COMPUTE DAT/HYYMDs = &YYMD; AS ''
     COMPUTE DAT_CNV1/A30 	= HCNVRT(DAT, '(HDMtYY-s)', 30, 'A30'); AS ''
     COMPUTE DAT_CNV/A12 	= EDIT(DAT_CNV1,'99999999999$$$$$$$$$$$$$$$$$$$'); AS ''

BY  CAR.ORIGIN.COUNTRY
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT HTMTABLE
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     ORIENTATION=PORTRAIT,$
TYPE=REPORT,     GRID=OFF,     FONT='ARIAL',     SIZE=9,$
ENDSTYLE
END
SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END
 

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


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I don't understand why you are doing so much transformation when only one command can be used.
TABLE FILE CAR
SUM  COMPUTE DAT/HYYMDs = &YYMD; AS 'DAT'
     COMPUTE DAT_CNV1/A30 = HCNVRT(DAT, '(HDMtYY-s)', 30, 'A30'); AS 'DAT_CNV1'
     COMPUTE DAT_CNV/A12  = EDIT(DAT_CNV1,'99999999999$$$$$$$$$$$$$$$$$$$'); AS 'DAT_CNV'
     COMPUTE DAT/HDMtYY-  = '&YYMD.EVAL'; AS 'Simplified'
BY  CAR.ORIGIN.COUNTRY
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT HTMTABLE
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     ORIENTATION=PORTRAIT,$
TYPE=REPORT,     GRID=OFF,     FONT='ARIAL',     SIZE=9,$
ENDSTYLE
END

SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END


As for Excel it will always have the date format in the cell itself, 07-Aug-2017 is only a display format but to show that way, Excel need the real date format.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2346 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
For an example am giving as &YYMD. Real time column format is HTTMDS.

If you remove the HTMTABLE and try with "ON TABLE PCHOLD FORMAT EXL2K" we can see the real date format from WebFOCUS to excel,
Only issue is if we use HTMTABLE the real date format is changed in Excel!

Hope now you will understand !


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If you do pay attention to both result cell format you will notice some differences.

Using EXL2K or XLSX format the resulting format is "General" and not evaluated as a date since Excel has received a char string (A12 from your fex).

Using the HTMLTABLE with XLS the Excel cell format is : Custom dd-mmm-yy. What I think is that Excel do receive a data and "changing" it into a real date format to be then able to display as the requested custom format.

Try the following.

Generates you data as XLSX or EXL2K and then in Excel select the cell where the data is displayed. Using the bottom right corner of the cell (where the + sign appear) drag it a few cell bellow.
The result is that you will still have "07-Aug-" but the year will be increased. This is a sign that it's not a date.

Do the same but using the HTMLTABLE Excel output. The dragged cell will increase by 1 day from the above cell : meaning that the cell IS a date.

In conclusion, I would say that there is no bug with HTMLTABLE : it send to Excel as a date which is okay, since using Excel directly, it send it as an alpha date string, which is also okay since it's the defined format.

Two different way, tow different result.

Yours to choose


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2346 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
I love it Smiler.

I have two different outputs;
1. Normal EXL2k report with more columns.
2. XLS through HTMTABLE format to display the titles & few rows in 90 degree text rotation using CSS. (Like to print the report in A4 landscape)
Both the final outputs column and formats are same; difference is the way we are printing in excel.

As a WebFOCUS team we can say it as a date format in HTMtable. But from business users they will expect the full date format!


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Reply With QuoteReport This Post
Gold member
posted Hide Post
so there is no way!


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
There is always a way and some time many ways :-)

It can be displayed with text month as RealDate in either format or RealTextDate in HTMTable format only.
Personally, I think that both solutions are good depending on your needs until you keep the date as a date.
That way you (your users) still have the flexibility to process date in Excel as they should.

I do prefer the solution for RealDate field since it does work with EXL2K, XLSX and HTMTable, kept as a date and displayed with text month (as you need). So one solution for multiple format.

Solution A
TABLE FILE CAR
SUM  COMPUTE REAL_DATE/HDMtYY- = '&YYMD.EVAL'; AS 'RealDate'
     COMPUTE DAT/HYYMDs = &YYMD; NOPRINT
     COMPUTE DAT_CNV1/A30 	= HCNVRT(DAT, '(HDMtYY-s)', 30, 'A30'); NOPRINT
     COMPUTE DAT_TXT/A12 	= EDIT(DAT_CNV1,'99999999999$$$$$$$$$$$$$$$$$$$'); AS 'RealTextDate'
BY COUNTRY
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD FORMAT HTMTABLE
END

SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END


Solution B
TABLE FILE CAR
SUM  COMPUTE REAL_DATE/HDMtYY- = '&YYMD.EVAL'; AS 'RealDate'
BY  COUNTRY
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE PCHOLD FORMAT XLSX
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2346 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
How to show the full year '08-Aug-2017' using HTMTABLE?


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I think that falls under MS-Excel setup or you may need to use a Excel macro to change Custom display from "dd-mmm-yy" to "dd-mmm-yyyy".

Since the date is passed as 07-08-2017 to Excel from the HTMTable it is the custom display that has to change, and this (I think) has to be performed into Excel.

Using the output as XLSX instead of HTMTable generates a Custom display of "dd-mmm-yyyy" using RealDate field (not with RealTextDate since it's a character string).

Now it's all a question of choices. Each solution (XLSX or HTMTable) have is pros and cons.
Personally I prefer the XLSX format since it keep it closer to the Excel format and functionalities.
I understand that users may want to have nice colored/formatted layout, but damn it's an Excel file where they will probably plays with numbers and doing their own formulas and styling.

Is it that much important to use the HTMTable to be able to produce an Excel file exactly colored/formatted (and whatever else) as they want ?
This is going to be your battle. Sometime you can't have "the butter and the money for the butter". Choices have to be done.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2346 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
Smiler Thank you MartinY


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
My pleasure.

If solved, edit your first post then add [SOLVED] at the beginning of the subject.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2346 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Closed] date formatting issue in Excel using HTMTABLE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.