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_AVAILABLE.SER_NO};
StringVar Prior := if not onFirstRecord then Previous ({AA_OCTA_SERIALS_STATUS_AVAILABLE.SER_NO}) else 'x1';

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_AVAILABLE.SER_NO});
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
datatechcorpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
WHere is this formula?

WHat is it trying to do?

Are any of the strings in the database that long?

mlmcc
datatechcorpAuthor Commented:
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)...especially where the BUS PASSES are concerned.  So, I needed to draft a report, that outputted sequential *RANGES* of Serial-#'s...such that we don't produce reports that are tens of thousands of pages long...make sense?

If need be...I can upload the Crystal Report Object.  Please let me know if you need it :-)   Thanks!...Mark
mlmccCommented:
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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

datatechcorpAuthor Commented:
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
James0628Commented:
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
vastoCommented:
Do you have the data as a recordset. For example  
123
124
125
139
149
ABC123  
ABC124

as separate records ?
mlmccCommented:
Can you upload the rpt file?

mlmcc
datatechcorpAuthor Commented:
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
datatechcorpAuthor Commented:
...and another version..."zipped"...
EExch-2015-06-19-Serials-Report-Passes.z
James0628Commented:
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_AVAILABLE.SER_NO})


 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_AVAILABLE.SER_NO}) then
  CStr (Val ({AA_OCTA_SERIALS_STATUS_AVAILABLE.SER_NO}), "000000000000")
else
  {AA_OCTA_SERIALS_STATUS_AVAILABLE.SER_NO}

 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
vastoCommented:
It seems to me that the whole architecture of the report is wrong. You are trying to get the data in subreports, which makes it slow and complicate. This is a classic "Gaps and Islands" problem. Here is an explanation how to solve it:
http://social.technet.microsoft.com/wiki/contents/articles/18399.t-sql-gaps-and-islands-problem.aspx

The report will take seconds and no formulas are required. The example is for SQL server and for numeric data. Your data seems a little bit more complicate but still can be processed in similar way.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
datatechcorpAuthor Commented:
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
datatechcorpAuthor Commented:
Sorry for not posting these points sooner...you were right...the view was a Gaps and Islands issue.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.