[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Exporting Crystal report Excel with 3 decimal places

Posted on 2010-01-08
Medium Priority
Last Modified: 2013-11-15
I have formatted number field in crystal report with 3 decimal places with no dollar sign but when publish it to infoview and schedule the report with excel format it always converts the formatted field to 2 decimal places with $ sign. Is there any way to keep the formatting the way I have in crystal report even after it gets exported in excel?
Question by:baralp
LVL 16

Expert Comment

ID: 26211989
try exporting it as excel data only.

if that doesnt work, force it to a text. I believe this is an issue with how excel is reading the export and not the report. totext(field,'0.000') this is not idea as it does require additional formulas.
LVL 101

Expert Comment

ID: 26212566
Is the field a CURRENCY field?

LVL 16

Expert Comment

ID: 26212600
currency is only valid in excel and if you apply currency as a format field properties. I would assume that is what you are talking about correct. Otherwise SQLsever and Oracle do not have currency data types.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 26212747
No its just a number data type field. Its weird why crystal does not have this feature of retaining the format when its exported to excel. But anyhow i was able to make it to 3 decimal places by converting it to text with formula totext({@billrate},3) but it still does not solve the issue because it wont allow us to do calculation on this field in excel as its a text field.
LVL 16

Expert Comment

ID: 26212778
right.. any field that is displayed will have to have this done to it.
for example
d - totext(fielda,3)
  gf totext(total of fielda,3)
rf totext(grand total of fielda,3)

so you would apply this after your calculations.
LVL 35

Accepted Solution

James0628 earned 2000 total points
ID: 26244908
I don't know why you're getting the "$".  I've got a number of CR 10 reports that are exported to Excel files and they don't get a "$" on the numeric fields.

 As for the decimal places, I've found that the Excel export uses the number of decimal places in the field value and ignores the formatting, so I use formulas like Round ({field}, 2) to round the value, and put those formulas on the report.  Then the exported field has the number of decimal places I want.  You might give that a try, although I don't know if it will work for you, since the "$"s that you're getting give me the feeling that something else may be going on there.

LVL 101

Expert Comment

ID: 26273496
What does the field explorer in Crystal say is the data type for the field?


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question