formula size

[Guest_imported]

There r too many conditions to be checked to filter the
records from the database. When i tried to add some more conditions in record selection formula it gives an error message as formula too long.

 

[kenhamady]

I have never seen this, so I would like to know how many characters you have in your selection formula? Could you post it here? It must be a monster.

You can take part of the formula, put it into a separate formula field or SQL Expressiion field (which will be a boolean), and then simply use this field as part of the selection formula. This makes the selection formula shorter. I would start with the parts of the formula that use functions. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[stinker]

Ken,
I have run into this before when trying to get a record selection formula to pass the SQL to the database, I discovered there were limitations oddly enough on using more than one formula that referenced other formulas in the record selection. (If I did this It wouldn't pass the SQL.) So I tried simply copying the formula code into the record selection multiple times. This worked fine and passed the SQL great until I got the same error that Sapnay mentioned. I didn't get a total count of charactors but also discovered that if you eliminate all spaces (like at the end of the lines of code) you get a lot more formula for your money.

BTW: Printed out with default wordwrapping (no spaces at the end of the lines) it was over 11 pages long. I wasn't that interested in Counting. Wish I had now though for the trivy.

Paul Ellsworth
Sr. Crystal Reports Developer/Trainer
Pleasanton, Ca

 

[kenhamady]

That is why I suggested pulling out anything with function, because that won't go to SQL anyway. I would like to see an 11 page selection formula. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[stinker]

I used to think that functions couldn't be used to Pass SQL because thats what I've allways been told by Seagate(I haven't asked Crystal Decisions Yet) here is one of the formulas that I use for record selection against a SQL Server Datebase that passes SQL 100% and you'll notice there are several of those functions we've all been told won't but they do.

Paul Ellsworth

if IsDatetime({?EndDate}) then
DatetimeValue({?EndDate})+1
else
if uppercase({?EndDate}) = 'TODAY' then (currentdate+1) else
if uppercase({?EndDate}) = 'YESTERDAY' then (currentdate+1) - 1 else
if uppercase({?EndDate}) = 'TOMORROW' then (currentdate+1) + 1 else
if uppercase({?EndDate}) = 'FIRSTOFMONTH' then minimum(MonthToDate)+1 else
if uppercase({?EndDate}) = 'ENDOFMONTH' then DateAdd ("m", 1,minimum(MonthToDate)) else
if uppercase({?EndDate}) = 'FIRSTOFLASTMONTH' then minimum(LastFullMonth)+1 else
if uppercase({?EndDate}) = 'ENDOFLASTMONTH' then maximum(LastFullMonth)+1 else
if uppercase({?EndDate}) = 'FIRSTOFYEAR' then minimum(YearToDate)+1 else
if uppercase({?EndDate}) = 'ENDOFYEAR' then DateAdd ("yyyy", 1,minimum(YearToDate)) else
if uppercase({?EndDate}) = 'FIRSTOFLASTYEAR' then minimum(LastYearYTD)+1 else
if uppercase({?EndDate}) = 'ENDOFLASTYEAR' then minimum(YearToDate) else
if uppercase({?EndDate}) = 'SUNDAY' then
if DayOfWeek(currentdate+1) = 1 then (minimum(WeekToDateFromSun)-6) else minimum(WeekToDateFromSun)+1 else
if uppercase({?EndDate}) = 'MONDAY' then
if DayOfWeek(currentdate+1) <= 2 then (minimum(WeekToDateFromSun)-5) else minimum(WeekToDateFromSun)+ 2 else
if uppercase({?EndDate}) = 'TUESDAY' then
if DayOfWeek(currentdate+1) <= 3 then (minimum(WeekToDateFromSun)-4) else minimum(WeekToDateFromSun)+ 3 else
if uppercase({?EndDate}) = 'WEDNESDAY' then
if DayOfWeek(currentdate+1) <= 4 then (minimum(WeekToDateFromSun)-3) else minimum(WeekToDateFromSun)+ 4 else
if uppercase({?EndDate}) = 'THURSDAY' then
if DayOfWeek(currentdate+1)<= 5 then (minimum(WeekToDateFromSun)-2) else minimum(WeekToDateFromSun)+ 5 else
if uppercase({?EndDate}) = 'FRIDAY' then
if DayOfWeek(currentdate+1)<= 6 then (minimum(WeekToDateFromSun)-1) else minimum(WeekToDateFromSun)+ 6 else
if uppercase({?EndDate}) = 'SATURDAY' then
if DayOfWeek(currentdate+1)<= 7 then (minimum(WeekToDateFromSun)) else minimum(WeekToDateFromSun)+ 7 else
if uppercase(left({?EndDate},5)) = 'TODAY' then
if length({?EndDate}) > 6 and NumericText(right({?EndDate},length({?EndDate})-6)) then
if mid({?EndDate},6,1) = '+' then
(currentdate+1) + tonumber(right({?EndDate},length({?EndDate})-6))
else
if mid({?EndDate},6,1) ='-' then
(currentdate+1) - tonumber(right({?EndDate},length({?EndDate})-6))
else
(currentdate+1)
else
(currentdate+1)
else
if uppercase(left({?EndDate},8)) = 'TOMORROW' then
if length({?EndDate}) > 9 and NumericText(right({?EndDate},length({?EndDate})-9)) then
if mid({?EndDate},9,1) = '+' then
(currentdate+1) + tonumber(right({?EndDate},length({?EndDate})-9)) + 1
else
if mid({?EndDate},9,1) ='-' then
(currentdate+1) - tonumber(right({?EndDate},length({?EndDate})-9)) + 1
else
(currentdate+1)
else
(currentdate+1)
else
if uppercase(left({?EndDate},9)) = 'YESTERDAY' then
if length({?EndDate}) > 10 and NumericText(right({?EndDate},length({?EndDate})-10)) then
if mid({?EndDate},10,1) = '+' then
(currentdate+1) + tonumber(right({?EndDate},length({?EndDate})-10)) - 1
else
if mid({?EndDate},10,1) ='-' then
(currentdate+1) - tonumber(right({?EndDate},length({?EndDate})-10)) - 1
else
(currentdate+1)
else
(currentdate+1)
else
(currentdate+1)

 

[kenhamady]

I guess to be more precise I would have to say any functions applied to data fields. You aren't applying the functions to any data fields.

You are using the functions simply against your own input parameters and system function values. Since this can all happen without the database even being involved, (ie &quot;before reading records&quot you aren't affected.

However, it is still true that if there is a rule within the selection formula that uses a function to process a field, this rule would not convert to SQL but would be processed at the client.

The only exception I have seen in some cases is the IsNull function, which I have been surprised to see sneak into the SQL statement. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Guest_imported]

I found in a document that the limitation of the formula size is 64KB. My formula size is already 54 KB and if i add some more code to it, it gives that error message. Yes we can add add functions to the input paramters but not with the database fields. Is there any solution for this.

 

[kenhamady]

There is no way to raise the limit. The only answer is to create a formula for some of the criteria code, and then use the formula field in the selection formula instead of the code itself. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)