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,

  
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

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.

Nice understanding, and sharing, of DEFINE - Tom.

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,

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.

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.

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

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.

Hi Michele,
OK, Cool Beans!!!
Have a great week...
Tom

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.