Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2012Report 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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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, 2012Report 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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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, 2012Report This Post
Gold member
posted Hide Post
so there is no way!


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Report 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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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, 2012Report 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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
Smiler Thank you MartinY


8202, 8105M, 7.7.03
 
Posts: 79 | Registered: June 12, 2012Report 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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders