We help IT Professionals succeed at work.

Crystal reports formatting issue when linked to excel

Maliki Hassani
on
Medium Priority
213 Views
Last Modified: 2012-08-13
Experts,

I have a excel spreadsheet that has values in a few different formats.
My crystal report is linked to the spreadsheet.  
The issue is when I run the report only the values in general format show up, and the other formats are left blank.  How can I simply show exactly what is on my excel spreadsheet in my report?
Here is an example of the values of the excel spreadsheet.  Notice the different value formats

8:57PM
3.03
:00:00
:00:00
:00:00
:00:00
:00:00
:00:00
6:33AM
4
1

Open in new window

Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are these all in the same column?

Crystal treats a column as a field and the field can have only 1 datatype which in this case I suspect is String or Text.

mlmcc

Author

Commented:
Yes it is in a column, but set to number.  What can I do to change it to string or text in Crystal?

Author

Commented:
totext wont work..  Do you know of a crystal format that will show it like excel?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Crystal considers that column a number?

mlmcc

Author

Commented:
I can change it to a string but still values that are like 0.00, 0.05, 0.08 etc only show up. I have other values in my excel like:12:54, that aren't showing..  Is it excel that is causing the problem?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
How do you change it to a string?

You would have to do that in Excel.

I think the issue is in Excel you are seeing the formatted value while Excel is storing them all as numbers.  Time number of seconds since some given datetime but you formatted it to be just the time.

Crystal expects an Excel sheet to have 1 datatype in a column.  Crystal looks at the first 10-20 rows to determine the type.  Not sure how it does it.

Do the numbers represent values for the same thing or is the data just somewhat random.

mlmcc

Author

Commented:
Here is the spreadsheet.

I am not sure what my options are here.  I will continue to think about this..
CMS-Report.xlsx
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Actually, for each column header there are individual names for each indicator.  Does that change your thoughts on how it is setup now?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I am not sure what you mean.  I don't see any name for the columns.

mlmcc

Author

Commented:
Let me see what I can do to arrange the data.  Thanks
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.