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

x
?
Solved

Crystal reports formatting issue when linked to excel

Posted on 2011-04-27
12
Medium Priority
?
205 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

0
Comment
Question by:Maliki Hassani
  • 6
  • 5
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35478393
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
0
 

Author Comment

by:Maliki Hassani
ID: 35478413
Yes it is in a column, but set to number.  What can I do to change it to string or text in Crystal?
0
 

Author Comment

by:Maliki Hassani
ID: 35478689
totext wont work..  Do you know of a crystal format that will show it like excel?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 101

Expert Comment

by:mlmcc
ID: 35478818
Crystal considers that column a number?

mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 35478849
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?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35478929
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
0
 

Author Comment

by:Maliki Hassani
ID: 35478999
Here is the spreadsheet.

I am not sure what my options are here.  I will continue to think about this..
CMS-Report.xlsx
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 35479190
Your spreadsheet seems to be setup with the rows having data for a field and the columns being a single record.  Crystal expects the data to be the other way where a row is one record and the columns are the fields.

Is there any chance you could pivot the data to put it in the format Crystal wants?

mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 35479412
Actually, for each column header there are individual names for each indicator.  Does that change your thoughts on how it is setup now?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35479999
I am not sure what you mean.  I don't see any name for the columns.

mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 35483074
Let me see what I can do to arrange the data.  Thanks
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 35490777
What mlmcc was saying is that you seem to have a "Log In" row, with a series of log in times in the columns, then an "AUX Time" row with a series of times in the columns, and so on.  So, CR would get one record with the "Log In" times, then a record with the "AUX Time" times, and so on.  Normally, each of those rows would be a column, so the first row/record would contain the first "Log In" time, "AUX Time" time, "Break" time, etc., the second row/record would contain the second set of values, and so on.

 James
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
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.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

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