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     [CASE-OPENED] How to Display Different Format Data in One Column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] How to Display Different Format Data in One Column
 Login/Join
 
Member
posted
Hello,

We have a requirement to display both Integer value and Decimal value in one column.

To simplify, for example, how can we display 1 row (suppose has only 1 column) with the integer value 10 (the count of quantity), and the second row showing the decimal value 98.99 (the total sales amount $).

Is there a way to do that in one TABLE command (request)?


Best Regards,

William

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.7.03 / 7.6 / 7.1 / 5.3;
HTML / Excel / CSV;
 
Posts: 9 | Registered: December 06, 2012Report This Post
Platinum Member
posted Hide Post
Hi

Here's an example:

DEFINE FILE CAR
MYFORMAT/A10=IF COUNTRY EQ 'JAPAN' THEN 'D12.2M' ELSE 'I9C';
END
TABLE FILE CAR
SUM RCOST/MYFORMAT
BY COUNTRY
END


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Report This Post
Member
posted Hide Post
Thanks,

Your solution works on detail data.

I'm trying to also apply the same technical to ACROSS-TOTAL or ROW-TOTAL, but it's not working.
It display ACROSS-TOTAL or ROW-TOTAL data in decimal format for I9C detail data.

I'm still trying to make sure ACROSS-TOTAL or ROW-TOTAL data in correct format.

Best Regards,
William


WebFOCUS 7.7.03 / 7.6 / 7.1 / 5.3;
HTML / Excel / CSV;
 
Posts: 9 | Registered: December 06, 2012Report This Post
Virtuoso
posted Hide Post
Perhaps what you are looking for is OVER.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Silver Member
posted Hide Post
Jack Gross is correct.
FML (Financial Modeling Language, AKA EMR and FRL) allows you to use the FOR and OVER commands to mix and match formats. I have done this, but I do not have an example readily available. I'll search my archives.

Could not fin in my archive so I put this together.
Master File ----------------------
FILENAME=TESTFML, SUFFIX=FOC,
SEGNAME=BUDGET, SEGTYPE=S1, $
FIELDNAME=CODE, ALIAS=CODE, USAGE=A12, ACTUAL=A12, $
FIELDNAME=BUDGET, ALIAS=BUDGET, USAGE=D8.2, ACTUAL=D8, $
Data -----------------------------
POLES.......25.00
A_CABLE....125.80
MANHOLES.....5.00
U_CABLE....502.80
FEX ------------------------------
TABLE FILE TESTFML
SUM BUDGET
FOR CODE
POLES LABEL DPOLE NOPRINT OVER
RECAP IPOLE/I5 = DPOLE ; AS 'POLES' OVER
A_CABLE OVER
MANHOLES LABEL DMANHOLE NOPRINT OVER
RECAP IMANHOLE/I5 = DMANHOLE ; AS 'MANHOLES' OVER
U_CABLE
END
Results --------------------------
POLES...........25
A_CABLE.....125.80
MANHOLES.........5
U_CABLE.....502.80

In the reports, the numerical values are aligned properly. This posting removes excess spaces, so I inserted dot leaders to help reading.

This message has been edited. Last edited by: John W Price,



WebFOCUS 8.0.2, FOCUS since 1977 - John@Aviter.com
PDF , Excel, FOCUS, Author of the Keysheets and Dates book.
www.Aviter.com
 
Posts: 40 | Registered: April 19, 2013Report This Post
Member
posted Hide Post
Hi,

Our requirement is like this (similar, to simplify): A matrix report, COUNT(products sold), BY Product Type, ACROSS Country, ACROSS City, It also has ACROSS (City) Sub-total, and ROW-TOTAL, SUB-TOTAL.

There is a bottom line "Total Sales ($)", which SUM (sales amount), ACROSS Country, ACROSS City (has also ACROSS (City) Sub-total, and ROW-TOTAL).

"COUNT(products sold)" (including sub-total & row-total) should display INTEGER values, "Total Sales ($)" should display DECIMAL (2 decimal points) values.

In this complicate case, how can we display correctly?

Thanks,
William


WebFOCUS 7.7.03 / 7.6 / 7.1 / 5.3;
HTML / Excel / CSV;
 
Posts: 9 | Registered: December 06, 2012Report This Post
Platinum Member
posted Hide Post
Why can't you set the format or "products sold" to "I11" and you "sales $" to "D12.2". You can do tha in the Master or in the FEX in a DEFINE or a COMPUTE or directly by adding "/[format]"after the field declaration.


WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
 
Posts: 175 | Location: Pomona, NY | Registered: August 06, 2003Report This Post
Member
posted Hide Post
Yes, you are right if we are using two columns to display "product sold" and "sales ($)" separately.

But in this requirement, the report use only 1 column (for each ACROSS value) to display "product sold" (in some rows) or "sales ($)" (in the last row of the report).

Best Regards,

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


WebFOCUS 7.7.03 / 7.6 / 7.1 / 5.3;
HTML / Excel / CSV;
 
Posts: 9 | Registered: December 06, 2012Report This Post
Silver Member
posted Hide Post
You can use the FPRINT() function to display the data in one field as both formats. It will convert them to an alpha, but keep the format you specify.

This is code we used to show a 401(k) deferral as either a percent or dollar amount.

 _401K_DEFERRAL/A15 MISSING ON = IF 401K_AMT IS MISSING THEN ''
     ELSE IF 401K_HOW EQ '%' THEN FPRINT(401K_AMT,'D12.2%',_401K_DEFERRAL)
     ELSE FPRINT(401K_AMT,'D12.2M',_401K_DEFERRAL); 


Hope that helps,


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Member
posted Hide Post
Thanks for everyone's kind inputs.

Below is answer from IBI:

"
The Dynamic format would be the way to go, but it currently does not work with either ROW-TOTAL or ACROSS TOTAL. Basically this would have to be a new feature request.
"

Best Regards,


WebFOCUS 7.7.03 / 7.6 / 7.1 / 5.3;
HTML / Excel / CSV;
 
Posts: 9 | Registered: December 06, 2012Report This Post
Gold member
posted Hide Post
quote:
Originally posted by WF_IL:
Hi

Here's an example:

DEFINE FILE CAR
MYFORMAT/A10=IF COUNTRY EQ 'JAPAN' THEN 'D12.2M' ELSE 'I9C';
END
TABLE FILE CAR
SUM RCOST/MYFORMAT
BY COUNTRY
END


Is there a similar solution for rows?


WebFOCUS 8010,8204
Windows
 
Posts: 59 | Registered: June 26, 2015Report This Post
Virtuoso
posted Hide Post
Kartik,

What do you mean by "similar solution for rows" ? What are you trying to perform ?

In a sense, each data displayed on a row is part of a column.

Tell me if I'm right and if I understand properly your question ?

Let say we have a report with 2 rows and 3 columns. What you want is per example:

row-1 for all 3 columns display alpha data
row-2 for all 3 columns display number in numeric format

I think that the answer maybe in MacGyver technique. Lot of post and info in technical library.

Otherwise you will have to perform one HOLD file per row types where you will use the FPRINT for every column of that row type and have each corresponding column of each row with the same output format (alpha same length). Finally merge all the HOLD files.

Sample
TABLE FILE CAR
PRINT COMPUTE COL1 /A20 = FPRINT(CAR,   'A16', 'A20');
      COMPUTE COL2 /A40 = FPRINT(MODEL, 'A24', 'A40');
BY COUNTRY
ON TABLE HOLD AS TMP1 FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM   COMPUTE COL1 /A20 = FPRINT(SEATS, 'I3',  'A20');
      COMPUTE COL2 /A40 = FPRINT(SALES, 'I6',  'A40');
BY COUNTRY
ON TABLE HOLD AS TMP2 FORMAT FOCUS
END
-RUN

TABLE FILE TMP1
PRINT COL1
      COL2
BY COUNTRY
MORE
FILE TMP2
END
-RUN


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: 2410 | 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     [CASE-OPENED] How to Display Different Format Data in One Column

Copyright © 1996-2020 Information Builders