DOCUMENT:Q172849 11-DEC-1999 [foxpro] TITLE :PRB: TRANSFORM() Does not Format a Calculated Field Properly PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:2.6a,3.0b,5.0,5.0a,6.0 OPER/SYS: KEYWORDS:kbvfp kbvfp300 kbvfp500 kbvfp600 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 3.0b, 5.0, 5.0a, 6.0 - Microsoft FoxPro for Windows, version 2.6a ------------------------------------------------------------------------------- SYMPTOMS ======== The TRANSFORM() function does not properly format a calculated field in the report writer. However, this function properly formats a non-calculated field in a report. In FoxPro 2.6 for Windows, the calculated field appears, but the format is incorrect. In Visual FoxPro for Windows versions 3.x and 5.x, the calculated field appears as asterisks. CAUSE ===== The TRANSFORM() function returns a character value. Because numeric functions are applied to a calculated field, changing the field to a character value causes erroneous results. RESOLUTION ========== In FoxPro 2.6 for Windows, create a report variable to perform the desired calculations and then format the report variable. In Visual FoxPro for Windows versions 3.x and 5.x, just Sum the field and check the Currency check box in the Format dialog of the field (it appears under the Numeric category.) In the Format text box in the Report Expression dialog of the calculated field, use the following to format the output: 999,999.99 Of course, a report variable could also be used in Visual FoxPro. STATUS ====== This behavior is by design. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- Perform the following steps in FoxPro 2.6 for Windows: 1. Open the Detail table located in the \tutorial directory. 2. Set the order to the Ino tag. 3. Create a new report and add the Price field to the Detail line. 4. Add a Data Grouping to the report based on the Ino field. 5. Add the Ino field to the Group Header and add a second field referencing Price in the Group Footer band. 6. In the Price field in the Group Footer, click the Calculate check box, choose Sum and Reset on Detail.ino. 7. In the Price field in the Detail and Group Footer bands, add the following code to the Expression box: TRANSFORM(Price,"$$$$,$$$.99") 8. Add a Report Variable to the report, xtotal, and set the Value to Store to Detail.price, Reset to Detail.ino, and Calculate to Sum. 9. Add a new field to the Group Footer and use the Report Variable, xtotal, as the expression. Format as Currency if desired. 10. Preview the report and notice that the calculated field is not preceded with a dollar sign and it carries the number out to five decimal places. However, the report variable field has the correct format. Perform the following steps in Visual FoxPro versions 3.0b, 5.0x or 6.0: 1. Open the Orders table located in the \Samples\Data folder. 2. Create a report and add the orders table to the data environment. 3. Set the order of the table to the Cust_id field using the Order property of the cursor in the data environment. 4. Add a data grouping to the report based on the Cust_id field. 5. Place the Cust_id field in the Group Header band and place the order_amt field in the Detail band. Double-click the field to bring up the Report Expression dialog box and in the Expression box, add the following code: TRANSFORM(order_amt,"$$$,$$$,$$$.99") Click OK to close the Report Expression dialog box. 6. Copy this field into the Group Footer band. Double-click the field, choose the Calculations button, and select the button for Sum. Click OK to close the Report Expression dialog box. 7. Preview the report. Note the non-calculated fields appear formatted correctly. However, the calculated fields appear as a row of asterisks. For additional information, please see the following article in the Microsoft Knowledge Base: Q102704 FIX: Can't Give Numeric the Currency Format in Report Writer Additional query words: FoxWin VFoxwin ====================================================================== Keywords : kbvfp kbvfp300 kbvfp500 kbvfp600 Technology : kbVFPsearch kbAudDeveloper kbFoxproSearch kbFoxPro260a kbVFP300b kbVFP500 kbVFP600 kbVFP500a Version : WINDOWS:2.6a,3.0b,5.0,5.0a,6.0 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 1999.