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]Displaying a column that has multiple numeric formats

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Displaying a column that has multiple numeric formats
 Login/Join
 
Michele Brooks
Guru
posted
I created an Excel report that displays varying formats for column2. Column one is a category field and column 2 can be either a D20 or D12.2% or D20.2CM, depending on the category.
The table file request is as follows:
 
TABLE FILE FILENAME
SUM AMOUNT
BY CATEGORY
ACROSS MONTH

Report
"Car Activity"
                    Jan
Deductible              124
Co-pay                 3.2%
Retail           $24,000.00

In the above example I used the FPRINT function and that worked fine until one of the customers that receives the report could not copy and paste the numbers in the cells and use them as numeric because I had converted them to alphanumeric using the FPRINT function. Now, I need a way to make the one amount field a numeric format based on the category. Thanks.

This message has been edited. Last edited by: Michele Brooks,


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
  
DEFINE FILE FILENAME
  FMT/A8 = IF CATEGORY EQ 'Deductible' THEN 'D20'     ELSE
           IF CATEGORY EQ 'Co-pay'     THEN 'D12.2%'  ELSE
           IF CATEGORY EQ 'Retail'     THEN 'D20.2MC' ELSE D20;
END
TABLE FILE FILENAME
SUM AMOUNT/FMT
BY CATEGORY
ACROSS MONTH


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Michele Brooks
Guru
posted Hide Post
I will give this a try and let you know how I made out. Thank you so much for the quick response. I love Focal Point. Thanks again.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Doug
Expert
posted Hide Post
Nice understanding, and sharing, of DEFINE - Tom.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Michele Brooks
Guru
posted Hide Post
Tom, I tried your suggestion and it worked like a charm. I'm not going to give you 5 Stars, but 5,000 stars. Your suggestion saved me a lot of work and heartache. I wish I had Skype so that I can thank you face to face. I hope this post get lots of attention because your suggestion is AWESOME! Kudos

P.S. I had to add quotes around your last format of D20.

This message has been edited. Last edited by: Michele Brooks,


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
j.gross
Virtuoso
posted Hide Post
Define separate Amount variables for the respective categories, and use OVER to stack them:
DEFINE FILE FILENAME
  DEDUCTIBLE/D20      MISSING ON =  IF CATEGORY EQ 'Deductible' THEN AMOUNT ELSE MISSING;
  COPAY     /D12.2%   MISSING ON =  IF CATEGORY EQ 'Co-pay'     THEN AMOUNT ELSE MISSING;
  RETAIL    /D20.2MC  MISSING ON =  IF CATEGORY EQ 'Retail'     THEN AMOUNT ELSE MISSING;
END
TABLE FILE FILENAME
SUM
      DEDUCTIBLE AS 'Deductible'
 OVER COPAY      AS 'Co-pay'
 OVER RETAIL     AS 'Retail'
ACROSS MONTH
...

That should make it easier for WF to plunk the data values into Excel cells with numeric format.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Michele Brooks
Guru
posted Hide Post
j.gross, thanks for your suggestion but I only had to use three lines in my define when using Tom's suggestion. I have well over 50 categories that have 3 different numeric data formats. Thank you for your suggestion and replying to my post.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
Hi Michele,
Glad it worked for you. Hopefully, you didn't have to hard-code all those categories. If you did, maybe we can figure out an easier way...

Thanks for the kind words, much appreciated...
Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Michele Brooks
Guru
posted Hide Post
Your welcome Tom. I did have to hard code the categories. The categories are just text wording to describe the numbers that appear to the right of the category description in the first column. The report is actually a stacked OVER report. In 7.6 I was able to use the OVER statement along with the CTRAN function. I used the CTRAN function to assign a unique category description for each row. Using CTRAN allowed me to insert blank spaces in front of some of the category descriptions so that they would display when the report opened up in Excel. Unfortunately, I couldn't get the CTRAN function to work in 8.0 so I created a workaround by defining one common category field where I hardcoded a unique category description for each row and combined all of the categories into a hold file using the MORE statement. Not knowing about the FMT function, I used FPRINT in a define that allowed me to change the numeric formats. I'm just overjoyed that you gave me a 3 line define that fixed my problem.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
Hi Michele,
OK, Cool Beans!!!
Have a great week...
Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
j.gross
Virtuoso
posted Hide Post
Michele --

My response was posted before your edit. I only offered the alternative approach because you originally indicated Tom's suggestion failed, and I assumed the dynamic feature wasn't behaving with Excel output. Since all is well with dynamic formatting, that's the way to go.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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]Displaying a column that has multiple numeric formats

Copyright © 1996-2020 Information Builders