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     Changing format of report variable

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Changing format of report variable
 Login/Join
 
MO Admin
Gold member
posted
I am developing a report based on DB2 SQL. I pull in a datetime stamp and use the SQL function DATE() to pull just that out. I then set up a date parameter that checks aginst this field.

My problem is that this field comes out as YYMD. I can change that format on the report to MDYY, but cannot figure out how to change the variable so that I users can type in the date ranges as MM/DD/YYYY, and also display in this format in the report header.

Any help is greatly appreciated.

Thanks,


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
Kevin,

Have the users specify the date in the MM/DD/YYYY format then use Dialogue Manager to switch the pieces around to create the variable that you use to do the WHERE clause. Do you know anything about DM? Hints: EDIT function to extract the date pieces, concatenation to put them together in the correct order.

Also, in your profile signature, please specify your product suite, releases, and platform(s) so we may better help you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
MO Admin
Gold member
posted Hide Post
I am familiar with the EDIT function, but not so much Dialogue Manager, especially when creating a report from SQL.

Would that go between the SQL and the report file?


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Tom Flynn
Expert
posted Hide Post
MO Admin,

This is just another idea:

The simplest way to return a date in the format you desire is to use the built-in column function CHAR. Using this function you can convert a date column into any number of formats. The specific format you request, MM/DD/YYYY, is the USA date format. So, for example, to return the date in the format you requested for a column named START_DATE you would code the function as follows:

CHAR(START_DATE,USA)

OR

DATE(SUBSTR(START_DATE,5,2) || "/" || SUBSTR(START_DATE,7,2) || "/" || SUBSTR(START_DATE,1,4))


Hope this helps...


Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
MO Admin
Gold member
posted Hide Post
It is not the column that I am having a problem with. It is the &variable for the date range.

I want to type 05/01/2008 instead of 20080501. In the report heading, it shows the way it is typed in.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Tom Flynn
Expert
posted Hide Post
Kevin,

OK, Ginny gave you the answer, BUT, with only 7 posts, here's the code:

-SET &NEW_DATE = EDIT(&VARIABLE,'$$$$99/') || EDIT(&VARIABLE,'$$$$$$99/')|| EDIT(&VARIABLE,'9999');

OR

-SET &NEW_DATE = EDIT(&VARIABLE,'$$$$99/99/') || EDIT(&VARIABLE,'9999');

&VARIABLE is your incoming &NAME
&NEW_DATE now goes in the HEADING...

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
MO Admin
Gold member
posted Hide Post
Thanks Tom and Ginny.

I used NEW_DATE for the where statement by converting MM/DD/YYYY into YYMD format by way of the EDIT in Dialogue Manager, and left the origional (&VARIABLE) as what is keyed in and what is displayed in the report header.

Thanks for you time.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report 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     Changing format of report variable

Copyright © 1996-2020 Information Builders