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     [SOLVED]How to rename the xlsx file dynamically

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]How to rename the xlsx file dynamically
 Login/Join
 
Guru
posted
Hi,

Iam trying to rename the Excel having the extension xlsx using the &RPTSTMP variable but when Iam trying to execute the report,it is getting opened in WFServlet.zip format but if I give as: ON TABLE PCHOLD FORMAT EXL07 FORMAT AS 'Report_name' ,(some static name) I get the report in xlsx format but I want to rename the xlsx dynamically (using &RPTSTMP or any other such variable).

 -SET &REPORT ='Car_report';
-SET &RPTSTMP = &REPORT || '_' || &TOD;
-TYPE RPT is &RPTSTMP

TABLE FILE CAR
PRINT CAR COUNTRY SALES
ON TABLE PCHOLD FORMAT EXL07 FORMAT AS '&RPTSTMP'
ON TABLE SET HTMLENCODE ON
ON TABLE SET STYLE *
TYPE=REPORT,FONT='ARIAL',SIZE=8,GRID=ON,BORDER=1,$
TYPE=TITLE,FONT='ARIAL',SIZE=8,STYLE=BOLD,COLOR='BLACK',JUSTIFY=CENTER,BORDER=1,BACKCOLOR=RGB(169 169 169),$
TYPE=DATA,FONT='ARIAL',SIZE=8,STYLE=NORMAL,BORDER=1,JUSTIFY=LEFT,$
END
-RUN
-EXIT 


Please let me know how to get this done.Thanks a lot in advance!

Thanks&Regards,
IP

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


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
quote:
ON TABLE PCHOLD FORMAT EXL07 FORMAT AS '&RPTSTMP'

Sould be
ON TABLE PCHOLD AS '&RPTSTMP' FORMAT EXL07


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
Also you can use '&RPTSTMP.EVAL' to get processed variable value.


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Report This Post
Guru
posted Hide Post
Hi Danny and Avinash,

Still Iam not getting the report with the name as specified in the &VARIABLE.It is still showing as WFServlet.zip...

Please let me know how to open it in xlsx format
with the different file name each time.

Thanks a lot in advance for all you help.

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Gold member
posted Hide Post
Hi IP,

The problem is &TOD value. hours, mins & seconds values are separated by "." . If you replace the "." with "_" then you may get the Excel file name.

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


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
 
Posts: 86 | Location: India | Registered: November 03, 2015Report This Post
Master
posted Hide Post
Info4pal, you need to adjust your redirection setting for that file type. I believe it needs to be set to "save" for that file type to save to the server first.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Guru
posted Hide Post
Hi All,

Maran's tips worked and thanks everyone also Smiler
But actually I have a drilldown report and once I click on the hyperlink, the renaming of the sheet works fine but again if I click on the same hyperlink, it gives me this pop up message saying :

A document with the name 'car_report_06_37_49.xlsx' is already open.You cannot
open two documents with the same name,even if the documents are in different folders.
To open the second document,either close the document that's currentky open,or rename one of the documents.

My end users do not wish to see this message if they click on the same hyperlink.

Is there any workaround for not showing up this message? Please let me know.

Thanks a lot in advance!

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Platinum Member
posted Hide Post
quote:
ON TABLE PCHOLD AS '&RPTSTMP' FORMAT EXL07



Hi IP,

You can use below code that will give dynamic excel name with no servelet.

-SET &REPORT ='Car_report';
-SET &RPTSTMP = &REPORT || '_' || &YYMD || '_'|| &TOD;
-SET &RPTSTMP = STRREP(&RPTSTMP.LENGTH, &RPTSTMP, 1, '.', 1, '_', &RPTSTMP.LENGTH, 'A&RPTSTMP.LENGTH');
-TYPE RPT is &RPTSTMP

TABLE FILE CAR
PRINT CAR COUNTRY SALES
ON TABLE PCHOLD AS '&RPTSTMP' FORMAT EXL07
ON TABLE SET HTMLENCODE ON
ON TABLE SET STYLE *
TYPE=REPORT,FONT='ARIAL',SIZE=8,GRID=ON,BORDER=1,$
TYPE=TITLE,FONT='ARIAL',SIZE=8,STYLE=BOLD,COLOR='BLACK',JUSTIFY=CENTER,BORDER=1,BACKCOLOR=RGB(169 169 169),$
TYPE=DATA,FONT='ARIAL',SIZE=8,STYLE=NORMAL,BORDER=1,JUSTIFY=LEFT,$
END
-RUN
-EXIT


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Report This Post
Expert
posted Hide Post
OR:
-SET &HHMMSS = EDIT(&TOD,'99$99$99');
... TABLE FILE...
ON TABLE PCHOLD AS 'Car_Report_&YYMD.EVAL_&HHMMSS' FORMAT EXL07
... END
"HHMMSS" is so useful...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
IP,

Are you generating excel output in drill down fex...?
If you want the current time values in excel name when you click the hyperlink..the &RPTSTMP variable should be refreshed to fetch the current time in the &TOD variable.

If you generate the &RPTSTMP variable in drill down fex..you will get the current time values in excel filename when you click the hyperlink.


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
 
Posts: 86 | Location: India | Registered: November 03, 2015Report This Post
Guru
posted Hide Post
Hi,

Avinash and Doug - Iam still getting the same error message/pop up message once I click on the already clicked hyperlink in the summary report.

Eric - The end users want to open the file rather than saving it everytime.

Maran - Iam trying to eliminate the pop up message and Iam trying to rename the excel sheet with the extension .xlsx dynamically.

Please let me know any other workaround for not getting the pop up message.

Thanks a lot in advance for all your help.

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Platinum Member
posted Hide Post
Hi IP,

Can you share your code via CAR sample file. Means both fexs - Main report and drill down report. Just wanted to see that what you are trying to achieve.

Below are the sample code and i clicked multiple time and it allowed me to open multiple excel file.

-*==============Main Report=========================
TABLE FILE CAR
SUM SALARY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=DATA,
COLUMN=N1,
FOCEXEC=IBFS:/WFC/Repository/Public/DrillDownReport.fex,
TARGET='_self',
$
ENDSTYLE
END


-*===============DrillDownReport================

-* File: IBFS:/localhost/WFC/Repository/Public/Procedure2.fex Created by WebFOCUS AppStudio
-SET &REPORT ='Car_report';
-SET &RPTSTMP = &REPORT || '_' || &YYMD || '_'|| &TOD;
-SET &RPTSTMP = STRREP(&RPTSTMP.LENGTH, &RPTSTMP, 1, '.', 1, '_', &RPTSTMP.LENGTH, 'A&RPTSTMP.LENGTH');
-TYPE RPT is &RPTSTMP


TABLE FILE CAR
SUM SALARY
BY CAR
ON TABLE PCHOLD AS '&RPTSTMP' FORMAT EXL07
ON TABLE SET HTMLENCODE ON
ON TABLE SET STYLE *
TYPE=REPORT,FONT='ARIAL',SIZE=8,GRID=ON,BORDER=1,$
TYPE=TITLE,FONT='ARIAL',SIZE=8,STYLE=BOLD,COLOR='BLACK',JUSTIFY=CENTER,BORDER=1,BACKCOLOR=RGB(169 169 169),$
TYPE=DATA,FONT='ARIAL',SIZE=8,STYLE=NORMAL,BORDER=1,JUSTIFY=LEFT,$
END
-RUN
-EXIT


I am hoping that it will solve your problem.


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Report This Post
Guru
posted Hide Post
Hi,

Thanks a lot everyone Smiler
It helped!

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report 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     [SOLVED]How to rename the xlsx file dynamically

Copyright © 1996-2020 Information Builders