Need Immediate Help With Crystal Throwing An Error -2147191673
HELLO...
When running my Crystal Report...the report will run (for various data)...but then when I go to PAGE-#2 (or subsequent pages) of the Report's output, Crystal is throwing the following error:
Error -2147191673 A string can be at most 65534 characters long.
I have (2) Formula Fields in this report. The first field is as follows:
WhilePrintingRecords;
StringVar Current := {AA_OCTA_SERIALS_STATUS_AV
StringVar Prior := if not onFirstRecord then Previous ({AA_OCTA_SERIALS_STATUS_A
NumberVar CurrentX:= Length(Totext (Val(StrReverse(Current & '1')),0,''));
NumberVar PriorX:= Length(Totext (Val(StrReverse(Prior & '1')),0,''));
StringVar CurrentA := if CurrentX > Length (Current) then "" else Current [ 1 to - ( CurrentX )];
StringVar CurrentB := Current [ -(CurrentX-1) to -1];
StringVar PriorA := if Priorx > Length (Prior) then "" else Prior [ 1 to - ( PriorX )];
StringVar PriorB := Prior [ -(PriorX-1) to -1];
StringVar Output;
If OnFirstRecord
then Output:= Current
else
if Val (CurrentB) - 1 <> val (PriorB) or CurrentA <> PriorA //This line now does two separate tests
then Output := Output + ' -to- ' + prior + CHR(13) + current;
output
The Second formula field is as follows:
WhilePrintingRecords;
StringVar Max := Maximum ({AA_OCTA_SERIALS_STATUS_A
StringVar Output;
if output = max
then output
else output + ' -to- ' + Max
PLEASE HELP! I need to get this data reported on today. I truly appreciate anyone who can chime in. Thank You in advance!...Mark
When running my Crystal Report...the report will run (for various data)...but then when I go to PAGE-#2 (or subsequent pages) of the Report's output, Crystal is throwing the following error:
Error -2147191673 A string can be at most 65534 characters long.
I have (2) Formula Fields in this report. The first field is as follows:
WhilePrintingRecords;
StringVar Current := {AA_OCTA_SERIALS_STATUS_AV
StringVar Prior := if not onFirstRecord then Previous ({AA_OCTA_SERIALS_STATUS_A
NumberVar CurrentX:= Length(Totext (Val(StrReverse(Current & '1')),0,''));
NumberVar PriorX:= Length(Totext (Val(StrReverse(Prior & '1')),0,''));
StringVar CurrentA := if CurrentX > Length (Current) then "" else Current [ 1 to - ( CurrentX )];
StringVar CurrentB := Current [ -(CurrentX-1) to -1];
StringVar PriorA := if Priorx > Length (Prior) then "" else Prior [ 1 to - ( PriorX )];
StringVar PriorB := Prior [ -(PriorX-1) to -1];
StringVar Output;
If OnFirstRecord
then Output:= Current
else
if Val (CurrentB) - 1 <> val (PriorB) or CurrentA <> PriorA //This line now does two separate tests
then Output := Output + ' -to- ' + prior + CHR(13) + current;
output
The Second formula field is as follows:
WhilePrintingRecords;
StringVar Max := Maximum ({AA_OCTA_SERIALS_STATUS_A
StringVar Output;
if output = max
then output
else output + ' -to- ' + Max
PLEASE HELP! I need to get this data reported on today. I truly appreciate anyone who can chime in. Thank You in advance!...Mark
ASKER
Hi MLMCC...
Both formulas are part of a SubReport. That first formula, is in the SubReport's DETAILS section, suppressed (intentionally). That second formula, is in the SubReport's REPORT_FOOTER section, and it is not suppressed.
There are definitely *NO* strings in the database (MSSQL 2012) anywhere near that long; this is occurring ONLY within the Crystal Report output.
This is for a Ticketing Agency...who sells Bus Passes...Disneyland Tickets...Dodgers Stadium Admission Tickets...and so forth. The purpose of this report, is to provide an "Available Serial-#'s" listing, daily, for Management. BUT, these Serial-#'s, could easily go into the 100's of thousands (sequentially)...especiall
If need be...I can upload the Crystal Report Object. Please let me know if you need it :-) Thanks!...Mark
Both formulas are part of a SubReport. That first formula, is in the SubReport's DETAILS section, suppressed (intentionally). That second formula, is in the SubReport's REPORT_FOOTER section, and it is not suppressed.
There are definitely *NO* strings in the database (MSSQL 2012) anywhere near that long; this is occurring ONLY within the Crystal Report output.
This is for a Ticketing Agency...who sells Bus Passes...Disneyland Tickets...Dodgers Stadium Admission Tickets...and so forth. The purpose of this report, is to provide an "Available Serial-#'s" listing, daily, for Management. BUT, these Serial-#'s, could easily go into the 100's of thousands (sequentially)...especiall
If need be...I can upload the Crystal Report Object. Please let me know if you need it :-) Thanks!...Mark
SO are you showing this as separate strings like this or is it one big string?
Ticket-1111 through Ticket-2222
Ticket-3333 through Ticket-4444
mlmcc
Ticket-1111 through Ticket-2222
Ticket-3333 through Ticket-4444
mlmcc
ASKER
Yes...as you specified...UP TO the point where there is a BREAK in the sequences. For example, say they have the following Serial-#'s on hand, for a given Item-#:
123
124
125
139
140
ABC123
ABC124
ABC126
XYZ240003
XYY240004
In my example above, the output would be displayed as follows:
123 -to- 125
139 -to- 140
ABC123 -to- ABC124
ABC126 -to- ABC126
XYZ240003 to XYZ240004
In total, there would be 10 Serial-#'s...but since we're outputting the *sequential ranges*...only 5-lines of output would be necessary. Does this make sense? Please let me know...and, again, THANK YOU!...Mark
123
124
125
139
140
ABC123
ABC124
ABC126
XYZ240003
XYY240004
In my example above, the output would be displayed as follows:
123 -to- 125
139 -to- 140
ABC123 -to- ABC124
ABC126 -to- ABC126
XYZ240003 to XYZ240004
In total, there would be 10 Serial-#'s...but since we're outputting the *sequential ranges*...only 5-lines of output would be necessary. Does this make sense? Please let me know...and, again, THANK YOU!...Mark
I'm not sure about all of those negative (or potentially negative) subscripts, but CR does allow them in some cases and you're not getting a subscript error, so I'll assume that they're not the problem for the time being.
Maybe your data just isn't what you think it is and you're getting a lot of individual values that don't fall in ranges, or your tests aren't correctly identifying the ranges. I'd start by unsuppressing the detail section in the subreport and seeing what you're getting from the first formula.
James
Maybe your data just isn't what you think it is and you're getting a lot of individual values that don't fall in ranges, or your tests aren't correctly identifying the ranges. I'd start by unsuppressing the detail section in the subreport and seeing what you're getting from the first formula.
James
Do you have the data as a recordset. For example
123
124
125
139
149
ABC123
ABC124
as separate records ?
123
124
125
139
149
ABC123
ABC124
as separate records ?
Can you upload the rpt file?
mlmcc
mlmcc
ASKER
Uploading the Crystal Report Object as requested. If you simply run the report with the "Saved" Data, and try to go to Page-#2, you'll see what I'm dealing with. Thanks everyone for chiming in...it's much appreciated!...Mark
EExch-2015-06-19-Serials-Report-Passes.r
EExch-2015-06-19-Serials-Report-Passes.r
ASKER
...and another version..."zipped"...
EExch-2015-06-19-Serials-Report-Passes.z
EExch-2015-06-19-Serials-Report-Passes.z
I unsuppressed the field (and made some other tweaks), so I could check the values coming from your first formula (Serial Accum).
It seems that my guess was basically correct. You have a string field (SER_NO) that you're trying to treat as a number. The problem is that, because it's a string field, the values aren't sorted the way you'd expect. Say you have strings with values between "1000" and "9999", and between "10000" and "99999". Those would be sorted as
1000
10000
10001
10002
...
10009
1001
10010
10011
and so on.
1000 and 1001 are not together. 10009 and 10010 are not together. So, you end up with a lot of small ranges for one number (eg. 1000 - 1000), 10 numbers (eg. 10000 - 10009), etc. The beginning of the list for item 5151 and location 400 looks like this:
0000021 -to- 0000100
100099 -to- 100099
1001 -to- 1001
10010 -to- 10010
100100 -to- 100109
10011 -to- 10011
100110 -to- 100119
10012 -to- 10012
100120 -to- 100129
10013 -to- 10013
100130 -to- 100139
10014 -to- 10014
100140 -to- 100149
10015 -to- 10015
The list ends up exceeding the maximum string length.
The solution is presumably to get the records sorted in the desired order.
Is SER_NO always a number, or could it contain other significant characters (eg. "123A" and "123B")?
If it's always a number, you could create a formula similar to the following in the subreport (I called it Serial_sort), and sort the subreport by that formula:
Val ({AA_OCTA_SERIALS_STATUS_A
If SER_NO could be a string, you could use a formula like the following for the sort. The idea is to convert the numeric values to a consistent format, with leading zeros, but just leave any non-numeric values as they are. I used 12 digits ("000000000000"). If the numbers in SER_NO could be longer than that, you can add more "0"s to the format string in the CStr function.
if NumericText ({AA_OCTA_SERIALS_STATUS_A
CStr (Val ({AA_OCTA_SERIALS_STATUS_A
else
{AA_OCTA_SERIALS_STATUS_AV
Another option would be to use 2 sort formulas, one for the numeric values and one for the string values, and sort by both of them. For the numeric formula, if SER_NO is numeric, convert it to a number, otherwise output 0. For the string formula, if SER_NO is not numeric, output SER_NO, otherwise output "".
When I changed the subreport to sort by that second formula, item 5151 and location 400 gave me this list:
0000021 -to- 999
1001 -to- 9999
10010 -to- 99999
100099 -to- 101649
101651 -to- 101980
101983 -to- 122145
122147 -to- 122149
122155 -to- 150100
3210001 -to- 3260000
66553322 -to- 000066553821
You also need to change the {@Serial Display} formula in the subreport. It uses Maximum (SER_NO), but, again, that's the maximum _string_ value. For example, for the last range shown above, your original {@Serial Display} gave me 66553322 -to- 99999 ("99999" being the maximum string value).
Change it to use the Maximum of the sort formula. That gave me the 66553322 -to- 000066553821 range shown above. You could remove the leading zeros that the sort formula added if you like.
For the record, I also noticed that your subreport wasn't actually sorting the records at all (before I added a sort). I wouldn't normally just assume that the records are going to be in the desired order when you're reading from a table like that. I did try sorting by SER_NO, but, as expected, that didn't work (for the reasons I mentioned earlier).
Also, after I made those changes, I got a subscript error later in the report, so you may still have another problem to deal with (I thought those negative subscripts might be trouble :-).
James
It seems that my guess was basically correct. You have a string field (SER_NO) that you're trying to treat as a number. The problem is that, because it's a string field, the values aren't sorted the way you'd expect. Say you have strings with values between "1000" and "9999", and between "10000" and "99999". Those would be sorted as
1000
10000
10001
10002
...
10009
1001
10010
10011
and so on.
1000 and 1001 are not together. 10009 and 10010 are not together. So, you end up with a lot of small ranges for one number (eg. 1000 - 1000), 10 numbers (eg. 10000 - 10009), etc. The beginning of the list for item 5151 and location 400 looks like this:
0000021 -to- 0000100
100099 -to- 100099
1001 -to- 1001
10010 -to- 10010
100100 -to- 100109
10011 -to- 10011
100110 -to- 100119
10012 -to- 10012
100120 -to- 100129
10013 -to- 10013
100130 -to- 100139
10014 -to- 10014
100140 -to- 100149
10015 -to- 10015
The list ends up exceeding the maximum string length.
The solution is presumably to get the records sorted in the desired order.
Is SER_NO always a number, or could it contain other significant characters (eg. "123A" and "123B")?
If it's always a number, you could create a formula similar to the following in the subreport (I called it Serial_sort), and sort the subreport by that formula:
Val ({AA_OCTA_SERIALS_STATUS_A
If SER_NO could be a string, you could use a formula like the following for the sort. The idea is to convert the numeric values to a consistent format, with leading zeros, but just leave any non-numeric values as they are. I used 12 digits ("000000000000"). If the numbers in SER_NO could be longer than that, you can add more "0"s to the format string in the CStr function.
if NumericText ({AA_OCTA_SERIALS_STATUS_A
CStr (Val ({AA_OCTA_SERIALS_STATUS_A
else
{AA_OCTA_SERIALS_STATUS_AV
Another option would be to use 2 sort formulas, one for the numeric values and one for the string values, and sort by both of them. For the numeric formula, if SER_NO is numeric, convert it to a number, otherwise output 0. For the string formula, if SER_NO is not numeric, output SER_NO, otherwise output "".
When I changed the subreport to sort by that second formula, item 5151 and location 400 gave me this list:
0000021 -to- 999
1001 -to- 9999
10010 -to- 99999
100099 -to- 101649
101651 -to- 101980
101983 -to- 122145
122147 -to- 122149
122155 -to- 150100
3210001 -to- 3260000
66553322 -to- 000066553821
You also need to change the {@Serial Display} formula in the subreport. It uses Maximum (SER_NO), but, again, that's the maximum _string_ value. For example, for the last range shown above, your original {@Serial Display} gave me 66553322 -to- 99999 ("99999" being the maximum string value).
Change it to use the Maximum of the sort formula. That gave me the 66553322 -to- 000066553821 range shown above. You could remove the leading zeros that the sort formula added if you like.
For the record, I also noticed that your subreport wasn't actually sorting the records at all (before I added a sort). I wouldn't normally just assume that the records are going to be in the desired order when you're reading from a table like that. I did try sorting by SER_NO, but, as expected, that didn't work (for the reasons I mentioned earlier).
Also, after I made those changes, I got a subscript error later in the report, so you may still have another problem to deal with (I thought those negative subscripts might be trouble :-).
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good Morning Vasto...
So...you're saying...that at the *MSSQL* level...if I created a VIEW with the logic examples from the TechNet URL...then my work is essentially done...and I can then just apply that to Crystal for reporting? *BRILLIANT*! I'll give that a try today, and let you know how it turns out. It's Father's Day, so "work" is off limits for a while...as the family will dictate otherwise :-)
I'll let you know the outcome. Thanks so much for chiming in!...Mark
So...you're saying...that at the *MSSQL* level...if I created a VIEW with the logic examples from the TechNet URL...then my work is essentially done...and I can then just apply that to Crystal for reporting? *BRILLIANT*! I'll give that a try today, and let you know how it turns out. It's Father's Day, so "work" is off limits for a while...as the family will dictate otherwise :-)
I'll let you know the outcome. Thanks so much for chiming in!...Mark
ASKER
Sorry for not posting these points sooner...you were right...the view was a Gaps and Islands issue. Thanks!
WHat is it trying to do?
Are any of the strings in the database that long?
mlmcc