Some values not appearing in CR field.
Good Afternoon,
I am using Crystal Reports v12 with a spreadsheet as my data source.
I have one field within my CR design that is only displaying some of the values of a column in my spreadsheet.
Out of the 43 records CR is pulling from this column in my spreadsheet, only 9 of these are coming up blank within CR.
Please note that values are in all 43 records of this column within my spreadsheet and they are all the same field length of 9 characters.
None of my suppress options are enabled for this field in CR and enabling them do not fix the problem.
Is someone able to assist and advise what could be the problem?
Kindly advise.
Regards,
N
I am using Crystal Reports v12 with a spreadsheet as my data source.
I have one field within my CR design that is only displaying some of the values of a column in my spreadsheet.
Out of the 43 records CR is pulling from this column in my spreadsheet, only 9 of these are coming up blank within CR.
Please note that values are in all 43 records of this column within my spreadsheet and they are all the same field length of 9 characters.
None of my suppress options are enabled for this field in CR and enabling them do not fix the problem.
Is someone able to assist and advise what could be the problem?
Kindly advise.
Regards,
N
ASKER
Column in Spreadsheet
* Cells are formatted as General.
* All values are numeric.
* All values have a character length of 9.
CR Field
* Field Data Type is a String
* No the field is not being hidden by anything, I have even made the field big in case something was getting cut off.
* Yes, if I create another report with just that field, the same thing will happen of only 9 values not showing out of the 43.
Appreciate the help.
Kind Regards,
N
* Cells are formatted as General.
* All values are numeric.
* All values have a character length of 9.
CR Field
* Field Data Type is a String
* No the field is not being hidden by anything, I have even made the field big in case something was getting cut off.
* Yes, if I create another report with just that field, the same thing will happen of only 9 values not showing out of the 43.
Appreciate the help.
Kind Regards,
N
WHat are the values?
Can you upload the spreadsheet?
mlmcc
Can you upload the spreadsheet?
mlmcc
ASKER
Sorry but the spreadsheet contains sensitive information so I cannot upload it.
Below is a list of values from the affected column though, all of the data in this column looks like this.
223012538
223012674
130830000
223013509
I came across the below posting which appears to be similar to the problem I am having.
http://kenhamady.com/cru/archives/2461
However this post is saying that the excel driver will use the majority type of the values in the first 8 rows to determine the data type of the column. This column in excel I am having problems with is all numeric, except for the heading. So really, there shouldnt be any guessing for CR to determine the data type of this column.
I'm going to try the solution they have on there and report back later. But the issue they are describing is exactly the problem I am facing. Where the majority of the cells are being printed on the CR report, and the other nine are not being printed (null).
Do let me know if you can think of anything else.
Regards,
N
"
Below is a list of values from the affected column though, all of the data in this column looks like this.
223012538
223012674
130830000
223013509
I came across the below posting which appears to be similar to the problem I am having.
http://kenhamady.com/cru/archives/2461
However this post is saying that the excel driver will use the majority type of the values in the first 8 rows to determine the data type of the column. This column in excel I am having problems with is all numeric, except for the heading. So really, there shouldnt be any guessing for CR to determine the data type of this column.
I'm going to try the solution they have on there and report back later. But the issue they are describing is exactly the problem I am facing. Where the majority of the cells are being printed on the CR report, and the other nine are not being printed (null).
Do let me know if you can think of anything else.
Regards,
N
"
That is why I asked about the data. Generally that happens if say the first 10-20 rows make it look like a number field but some of the later values have alpha characters in them.
mlmcc
mlmcc
I know this is obvious, but do the missing values have anything in common? Either something about the values, or where they're located in the spreadsheet?
It does sound like that problem where CR doesn't interpret a spreadsheet column correctly, but from everything else you've said, it doesn't seem like that should be happening in this case, so I don't know.
If you don't find another solution, you could try the following:
Create some dummy rows of data for your spreadsheet -- I'd say around 20 or 30, just to play it safe -- with something like 0 in any columns that should be numbers, and "dummy" in any columns that should be strings. Insert those rows at the beginning of the sheet, to try to force CR to interpret the columns the way that you want, and try the report again. See if those values are still missing. You can have the report ignore the extra lines by checking for the 0 or "dummy" values.
That may not be a long-term solution, but it should let you see if the interpretation of the column is the problem.
FWIW, I had this problem with a report, and it was a particular problem because the data changed, so one time CR might interpret a column as a string, and the next time it might interpret it as a number, so sometimes the report just wouldn't run, because the (perceived) data type for a column had changed. Fortunately, that report was only run once per month, so I worked around the problem by creating a file with some dummy rows, like the ones I described above, and those would be copied and inserted into the sheet with the data before the report was run, to get CR to interpret the columns the same way every time. Of course that may not be practical in your case.
James
It does sound like that problem where CR doesn't interpret a spreadsheet column correctly, but from everything else you've said, it doesn't seem like that should be happening in this case, so I don't know.
If you don't find another solution, you could try the following:
Create some dummy rows of data for your spreadsheet -- I'd say around 20 or 30, just to play it safe -- with something like 0 in any columns that should be numbers, and "dummy" in any columns that should be strings. Insert those rows at the beginning of the sheet, to try to force CR to interpret the columns the way that you want, and try the report again. See if those values are still missing. You can have the report ignore the extra lines by checking for the 0 or "dummy" values.
That may not be a long-term solution, but it should let you see if the interpretation of the column is the problem.
FWIW, I had this problem with a report, and it was a particular problem because the data changed, so one time CR might interpret a column as a string, and the next time it might interpret it as a number, so sometimes the report just wouldn't run, because the (perceived) data type for a column had changed. Fortunately, that report was only run once per month, so I worked around the problem by creating a file with some dummy rows, like the ones I described above, and those would be copied and inserted into the sheet with the data before the report was run, to get CR to interpret the columns the same way every time. Of course that may not be practical in your case.
James
ASKER
Ok that solution from that page didnt work.
I'm noticing that on the spreadsheet the cells with the little green arrow are coming across to CR. But the ones that dont have the little green arrow dont come across to CR.
Below is a screenshot of some of the data.
When i look at the format of all of these cells in this column the format is set to General. Clicking on the exclamation mark of one of these green arrowed cells its telling me that my number is stored as text.
The ones without the green arrow must be stored as a number I guess because they dont have a green arrow, but i dont know how they got that way, because i didnt change anything on the spreadsheet. These cells without the green arrow are the ones not coming in to CR.
The majority of the cells in this column have the green arrow. So I guess the majority format that was used when CR decided what format to choose was text, this would explain why my CR field for this column is set to string.
So how can i fix this with the least amount of work. As of April i am going to get these on a daily basis with 1000's of records and I really want to avoid doing anything one by one.
BTW, I tried formatting the ones without green arrows to "text" by right clicking the cell and going to "format cell" as text. But it did not fix the problem and i am getting the same results.
Can any of you provide a step by step?
Kindly advise.
Regards,
N
I'm noticing that on the spreadsheet the cells with the little green arrow are coming across to CR. But the ones that dont have the little green arrow dont come across to CR.
Below is a screenshot of some of the data.
When i look at the format of all of these cells in this column the format is set to General. Clicking on the exclamation mark of one of these green arrowed cells its telling me that my number is stored as text.
The ones without the green arrow must be stored as a number I guess because they dont have a green arrow, but i dont know how they got that way, because i didnt change anything on the spreadsheet. These cells without the green arrow are the ones not coming in to CR.
The majority of the cells in this column have the green arrow. So I guess the majority format that was used when CR decided what format to choose was text, this would explain why my CR field for this column is set to string.
So how can i fix this with the least amount of work. As of April i am going to get these on a daily basis with 1000's of records and I really want to avoid doing anything one by one.
BTW, I tried formatting the ones without green arrows to "text" by right clicking the cell and going to "format cell" as text. But it did not fix the problem and i am getting the same results.
Can any of you provide a step by step?
Kindly advise.
Regards,
N
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's strange that, for example, 223015428 is formatted as a number, but 223015214 is not. Obviously the best thing would be if you could get the original format to be consistent, but you may not have any control over whatever is putting the values in the spreadsheet.
I think the formatting is going to have to be consistent (string or number) for CR, and if you can't fix the initial formatting, I guess the next best thing would be to "format" (convert) the entire column at one time. There are ways to do that. If the values are always numeric, I'd probably convert the string values to numbers, but you can also go the other way.
Do a Web search for "excel convert column number text" (without the quotes), and you'll find numerous articles about it, including some from MS.
Once you decide on a procedure, you might even be able to create a macro to do it for you.
James
I think the formatting is going to have to be consistent (string or number) for CR, and if you can't fix the initial formatting, I guess the next best thing would be to "format" (convert) the entire column at one time. There are ways to do that. If the values are always numeric, I'd probably convert the string values to numbers, but you can also go the other way.
Do a Web search for "excel convert column number text" (without the quotes), and you'll find numerous articles about it, including some from MS.
Once you decide on a procedure, you might even be able to create a macro to do it for you.
James
ASKER
Thanks for the additional info James. Yes I find it really strange too, driving me crazy. The extra quote "band-aid" mlmcc suggested seems to have helped but its a bitch doing it for each cell.
Will look to see if i can get any further with the excel converting as you suggested.
Thanks again.
Nobushi
Will look to see if i can get any further with the excel converting as you suggested.
Thanks again.
Nobushi
It should be possible to "convert" the whole column at the same time. I've done it before, using one of the methods you can find online (I can't remember which one).
James
James
If text are some of the values just numbers?
In Crystal what does it see as the data type for the field?
Is the field perhaps being "hidden" by a field above it or next to it?
If you create a report with just that field on it does it behave the same way?
mlmcc