Excel Field Seen as String
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14185
Printed Date: 28 Apr 2024 at 9:55pm
Topic: Excel Field Seen as String
Posted By: Angela H
Subject: Excel Field Seen as String
Date Posted: 24 Aug 2011 at 7:40am
|
I'm a relative newbie at Crystal. Simply because I don't use it enough. I am using an Excel spreadsheet for my data. I have two columns that hold dollar amounts, however Crystal is seeing one as a string and the other as Currancy. I've searched other posts but still can't figure out how to get Crystal to see the data in the column as currancy so that I can sum. ------------- Thanks for your help. Angela |
Replies:
Posted By: hilfy
Date Posted: 24 Aug 2011 at 7:54am
|
In the first row of your data in the spreadsheet, is the column that is being seen as a string blank? When connecting to an Excel file, Crystal determines what "type" a field is based on the data in the first row and I believe it sees blanks as strings.
Create a formula on the column that's shown as a string using the "ToNumber" function and use that instead of the field itself. You may have to strip out the "$" character from the string in your formula for this to work.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: Angela H
Date Posted: 26 Aug 2011 at 7:50am
Thank you. With your hint I figured out that my database had a blank in the first line for the debits (as all transactions are either a debit or credit). So I put a number in there... took out the $... and Crystal recognized the column as a number. Whew!  ------------- Thanks for your help. Angela |