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>,

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

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

Perhaps what you are looking for is OVER.

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,

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

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.

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,

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,

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,

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?

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