Hi,

I just need to display a % symbol for one particular row value while generating the tabular report

I need generate the report like below.

  
CAR   ENGLAND RUSSIA JAPAN USA
BMW     10      20    30    50  
BMW1    10      20    30    50 
BMW2    10%     20%   30%   50%
BMW3    10      20    30    50 

If you see in the about report, I put CAR in the BY field and COUNTRY as across field.

My requirement is for BMW2 I need to show the '%' symbol only for this row specifically and other rows should come as it is with out percentage symbol...

How can we generate the percentage symbol for one particular row.

Thanks!

Maybe something like this?

DEFINE FILE CAR
PER2/D9 = SALES/RETAIL_COST;
PER3/A13=FTOA(PER2, '(D9)',PER3);
PER4/A15=IF CAR EQ 'BMW' THEN (PER3 || '%')  ELSE PER3;
END
TABLE FILE CAR
SUM  PER4
BY CAR
ACROSS COUNTRY
END  

The functionality you are looking for is called dynamic reformatting.

  
DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
END

TABLE FILE CAR
SUM SALES/CFORMAT DEALER_COST/CFORMAT
BY COUNTRY
END

I forgot about that...

Hi Selph,

Thanks! for the technique given, for the ACROSS columns based on the BY fields using the technique DECODE I could able to change the format dynamically but how can we achieve the same for the computed or calcuated columns. For instance

  

TYPE   COL1   COL2  COL3  COMCOL1 COMCOL2

ABC     10    20     30    20.00   20%
DEF     10    20     30    22.00   10.00 
GHI     10%   20%    30%   10 %    30.00
IJK     10    20     30    10      40.00

The row "ABC" and its computed columns COMCOL1 COMCOL2 has the format "D12.2" and "D12%"

How can we achieve this specfically for computed values.

My code looks like below

  

TABLE FILE FILENAME

SUM VALUE
  BY TYPE  
  ACROSS COLUMNS
  COMPUTE COMCOL1/? = SOME CALUCALATION
  COMPUTE COMCOL2/? =  SOME CALUCALATION

END

The computed columns COMCOL1,COMCOL2 values format should come dynamically as per the mockup given above.

I have added "?" for the format becz the format should come dynamically based on the by TYPE Column.

Try this:

DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
CFORMATB/A8=DECODE COUNTRY('ITALY' 'D10.2' 'JAPAN' 'D10%' ELSE 'D10.2%');
END

TABLE FILE CAR
SUM SALES/CFORMAT DEALER_COST/CFORMAT
COMPUTE BLAH/D20.2 = SALES/DEALER_COST; NOPRINT
BLAH/CFORMATB 
BY COUNTRY
END

(What is "becz"?)

See if this works:

Insert a COMPUTE (with NOPRINT) for the desired output format first, and reference it as the format of the displayed COMPUTE column.

quote:

The functionality you are looking for is called dynamic reformatting.

Very nice, N Selph. I learned a lot here.
This dynamic reformatting only works with the appearance of the report, not the internal storage. COMPUTE won't work, hold them to temp file won't work.

DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
CFORMAT1/A8=DECODE COUNTRY('ITALY' 'D10%' 'W GERMANY' 'D10' ELSE 'D10.2B');
CFORMAT2/A8=DECODE COUNTRY('FRANCE' 'D10B' 'JAPAN' 'D10%' ELSE 'D10.2-');
 NET1/D12.2 = SALES - DCOST;
 NET2/D12.2 = SALES - DCOST;
END

TABLE FILE CAR
SUM SALES/CFORMAT DEALER_COST/CFORMAT
NET1/CFORMAT1 NET2/CFORMAT2
BY COUNTRY
-*ON TABLE HOLD AS H1 FORMAT FOCUS
END
-*TABLE FILE H1
-*PRINT *
-*END  

Thanks.

Hua

Keep in mind that the FORMAT field MUST have a format of A8. If you define as A5 (for example to hold 'D10.2') it will not work.

Hi Francis,

I really understand the logic but I'm using BY and ACROSS and creating the computed or calculated values.
Here is what I modified the code a little to show my requirement and this is throwing error

DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
CFORMATB/A8=DECODE COUNTRY('ITALY' 'D10.2' 'JAPAN' 'D10%' ELSE 'D10.2%');
END

TABLE FILE CAR
SUM SALES/CFORMAT DEALER_COST/CFORMAT
BY COUNTRY
ACROSS CAR

COMPUTE BLAH/D20.2 = SALES/DEALER_COST; NOPRINT
BLAH/CFORMATB
END

How can we change the computed column values formats dynamically at run time when we call the computed columns after ACROSS (reason I'm creating the computed columns after ACROSS stmts is I need to generate columns at the end of the report) ?

Error Message : BLAH Field/word not recognized.

Thanks!

Consider this:

-* Douglas Lee: File ID: BY_ACROSS_Format.fex
-* http://forums.informationbuild...71057331/m/569109545
DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
CFORMATB/A8=DECODE COUNTRY('ITALY' 'D10.2' 'JAPAN' 'D10%' ELSE 'D10.2%');
END
TABLE FILE CAR
SUM 
SALES/CFORMAT
DEALER_COST/CFORMAT AS 'DCOST'
BY COUNTRY
ACROSS CAR
-*COMPUTE BLAH/D20.2 = SALES/DEALER_COST; NOPRINT
-*BLAH/CFORMATB
-IF COUNTRY EQ 'JAPAN' THEN GOTO D10PCT ELSE GOTO D102 ;
-D102
COMPUTE BLAH/D20.2 = SALES/DEALER_COST; 
-GOTO FORMATDONE
COMPUTE BLAH/D20% = SALES/DEALER_COST; 
-FORMATDONE
WHERE COUNTRY IN ('ENGLAND' 'JAPAN')
END

Hi Doug,

I don't think the solution that you gave will satisfy my requirement. I can't show the formated values(%,D12.2 something like for each row) for different country, car combination differently.

The issue is for Computed columns dynamic formating. Any new solution?

I'm posting the same message again...
----------------------

Hi Francis,

I really understand the logic but I'm using BY and ACROSS and creating the computed or calculated values.
Here is what I modified the code a little to show my requirement and this is throwing error



DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
CFORMATB/A8=DECODE COUNTRY('ITALY' 'D10.2' 'JAPAN' 'D10%' ELSE 'D10.2%');
END

TABLE FILE CAR
SUM SALES/CFORMAT DEALER_COST/CFORMAT
BY COUNTRY
ACROSS CAR

COMPUTE BLAH/D20.2 = SALES/DEALER_COST; NOPRINT
BLAH/CFORMATB
END



How can we change the computed column values formats dynamically at run time when we call the computed columns after ACROSS (reason I'm creating the computed columns after ACROSS stmts is I need to generate columns at the end of the report) ?

Error Message : BLAH Field/word not recognized.

This correction makes the program run without error, check the results and let us know if it solves your format problem:

-* File subbu1.fex

DEFINE FILE CAR
CFORMAT/A8=DECODE COUNTRY('ENGLAND' 'D10%' 'JAPAN' 'D10' ELSE 'D10.2');
CFORMATB/A8=DECODE COUNTRY('ITALY' 'D10.2' 'JAPAN' 'D10%' ELSE 'D10.2%');
END

TABLE FILE CAR
SUM SALES/CFORMAT DEALER_COST/CFORMAT
BY COUNTRY
ACROSS CAR

PRINT
COMPUTE BLAH/D20.2 = SALES/DEALER_COST; NOPRINT
BLAH/CFORMATB
END

It might be easiest to calculate your computes as a separate query and combine the output with your initial results before producing the final output. Here's an example using the EMPDATA file:

SET ASNAMES   = ON
SET HOLDLIST  = PRINTONLY

APP FILEDEF EMPHOLD DISK EMPHOLD.FTM (APPEND

DEFINE FILE EMPDATA
 AREAX/A15     = AREA ;
 LAST_AREA/A15 = 'ZZZZZ';
END

TABLE FILE EMPDATA
 SUM SALARY
 COMPUTE AREA_TEXT/A15 = AREA ;
 BY AREAX
 BY JOBCLASS
 ON TABLE HOLD AS EMPHOLD
END

TABLE FILE EMPDATA
 SUM SALARY
 COMPUTE AREA_TEXT/A15 = 'TOTALS';
 BY LAST_AREA
 BY JOBCLASS
 ON TABLE SAVB AS EMPHOLD
END

DEFINE FILE EMPHOLD
 SFORMAT/A8 = IF (JOBCLASS CONTAINS 'S') THEN 'D10.1%' ELSE 'D12.2M';
END

TABLE FILE EMPHOLD
 SUM SALARY/SFORMAT AS 'Salary'
 BY JOBCLASS
 ACROSS AREAX NOPRINT
 ACROSS AREA_TEXT AS ''
 ON TABLE SET PAGE NOPAGE
 ON TABLE PCHOLD FORMAT HTML
END

Hi Francis,

I think I need to bring my code what I did for better understanding...

  

DEFINE FILE SUMDATA

RFMTS/A30=DECODE METRIC('CLAIM' 'D10' 'UNIQUE_GUEST_COUNT' 'D10' 'NEWNT' 'D10'  'PMU' 'D12.2%' 'BRD' 'D12.2%' 'GEN' 'D12.2%' ELSE 'D10.2');
END

TABLE FILE SUMDATA

SUM
	TOTVALUES NOPRINT
    VALUES/RFMTS AS ''
BY STORE
BY ORD NOPRINT
BY MNAME AS 'METRIC'
BY METRIC NOPRINT
ACROSS FN AS '' NOPRINT
ACROSS FDESC AS ''
ACROSS WEEK_END AS ''

-SET &K = 1;

-REPEAT ENDREPEAT3 FOR &I FROM 1 TO 3
-IF &XREGION.&I EQ 'PRECMM' THEN AVGPRE1 ELSE ALL1;

-AVGPRE1
COMPUTE PCINC&I/D12.2% = IF (METRIC EQ 'CLAIM' OR 'UNIQUE_GUEST_COUNT' OR 'NEW_GUEST_COUNT') THEN &MSTR
ELSE IF METRIC EQ 'SALES_PER_CLAIM' THEN &SLSTOT/&CLMT
ELSE IF METRIC EQ 'PROFIT_PER_CLAIM' THEN &PRFTOT/&CLMT
ELSE 999
-ENDREPEAT3
END

in the above code, I have called the RFMTS field to apply dynamic formating and what happens is for the computed columns the d12.2% format is not applying instead it is taking d12.2 as a new format even though I have called d12.2% the % is disappearing. Another problem is I have applied the style for the computed column by using the column notation and it is not properly applying when dynamic formating is applied for the called columns

The question is when we do dynamic formating on the pring fields can the computed field creates something new columns with different formats (like here d12.2 as default ) instead of the defined format D12.2% ?

second one is you can see the loop I have created after the across how can we called the defined column RFMTS for the computed columns.
LIKE BELOW. Any thoughts ?

  
DEFINE FILE SUMDATA

RFMTS/A30=DECODE METRIC('CLAIM' 'D10' 'UNIQUE_GUEST_COUNT' 'D10' 'NEWNT' 'D10'  'PMU' 'D12.2%' 'BRD' 'D12.2%' 'GEN' 'D12.2%' ELSE 'D10.2');
END

TABLE FILE SUMDATA

SUM
	TOTVALUES NOPRINT
    VALUES/RFMTS AS ''
BY STORE
BY ORD NOPRINT
BY MNAME AS 'METRIC'
BY METRIC NOPRINT
ACROSS FN AS '' NOPRINT
ACROSS FDESC AS ''
ACROSS WEEK_END AS ''

-SET &K = 1;

-REPEAT ENDREPEAT3 FOR &I FROM 1 TO 3
-IF &XREGION.&I EQ 'PRECMM' THEN AVGPRE1 ELSE ALL1;

-AVGPRE1
COMPUTE PCINC&I/RFMTS = IF (METRIC EQ 'CLAIM' OR 'UNIQUE_GUEST_COUNT' OR 'NEW_GUEST_COUNT') THEN &MSTR
ELSE IF METRIC EQ 'SALES_PER_CLAIM' THEN &SLSTOT/&CLMT
ELSE IF METRIC EQ 'PROFIT_PER_CLAIM' THEN &PRFTOT/&CLMT
ELSE 999
-ENDREPEAT3
END

You cannot have COMPUTE statements after the verbs ACROSS or BY. Adding the PRINT seemed to make it work.

quote:

You cannot have COMPUTE statements after the verbs ACROSS or BY

Yes, you can.
But, this compute will not take the dynamic format from the field, its format is fixed.
Consider this example:

DEFINE FILE CAR
RFMTS/A8 = DECODE COUNTRY('ENGLAND' 'D10.1' 'JAPAN' 'D10.2' 'ITALY' 'D10M'  'FRANCE' 'D12.2%' ELSE 'D10.2');
END

TABLE FILE CAR
SUM   DCOST NOPRINT
      RCOST NOPRINT
BY    COUNTRY
SUM   DCOST/RFMTS AS ''
      RCOST/RFMTS AS ''
BY    COUNTRY
ACROSS SEATS AS ''

-REPEAT ENDREPEAT3 FOR &I FROM 1 TO 3
COMPUTE PCINC&I/D12.2% = (C2 - C1) / &I;
-ENDREPEAT3
END

It will give you the dynamic formats for the separate rows, it shows the compute after the across, but when you change the format for the computes to RFMTS, then it will throw an error message.

Thanks for the correction - I've not had the need for COMPUTES after a verb, I thought it wouldn't work.

Does this help?

DEFINE FILE CAR
RFMTS/A8 = DECODE COUNTRY('ENGLAND' 'D10.1' 'JAPAN' 'D10.2' 'ITALY' 'D10M'  'FRANCE' 'D12.2%' ELSE 'D10.2');
END

TABLE FILE CAR
SUM   DCOST NOPRINT
      RCOST NOPRINT
BY    COUNTRY
SUM   DCOST/RFMTS AS ''
      RCOST/RFMTS AS ''
BY    COUNTRY
ACROSS SEATS AS ''

PRINT
-REPEAT ENDREPEAT3 FOR &I FROM 1 TO 3
COMPUTE PCINC&I/D12.2% = (C2 - C1) / &I; NOPRINT
PCINC&I/RFMTS
-ENDREPEAT3
END

It appears you cannot use dynamic formatting in a COMPUTE, but you can use it in a subsequent reference to the newly computed field.

You can achieve what you need by using FML.

T

Subbu,

Have you tried Formatting data based on row numbers?