Crystal Reports XI - convert csv input string parameter to number array for use in record selection formula

Question

I have a report which I'm working on which has an input parameter (strSchoolIds) that is a string of comma separated list of unique schoolIds. I'd like to convert this string into a number array which can then be used in the record selection formula. Here is what I've come up with so far. But whenever I view the report just the first schoolId gets used.

Example input string for strSchoolIds might be 1,3,63,237,281
The resulting SQL query I'd like would be similiar to this

Select name, phone, numStudents 
from schools
where schoolId in (1,3,63,237,281) and active =1

My current code is:

StringVar Array schools := Split ({?strSchoolIds}, ",");
Local NumberVar arrLen := UBound( schools );
Local NumberVar i := 1;

NumberVar Array schoolIdsArray;
While i <= arrLen Do
(
    schoolIdsArray[i] = ToNumber(Trim(schools[i]));
    i := i + 1;
);


{Schools.schoolId} in schoolIdsArray
and {Schools.active} = 1;

Answer 1

I've got code similar to the:

{Schools.schoolId} in schoolIdsArray

and it works properly.

Are you sure your array is getting populated properly?

You can test this quickly by adding a new unbound string field and placing this in the formula

ToText(schoolIdsArray[1]) + ", " + ToText(schoolIdsArray[2])

Answer 2

I figured it out after using Nathan Koop's suggestion and building upon it to debug with. Turns out I needed to Redim the schoolIdsArray with the arrLen of the string Array schools before adding values to it.

StringVar Array schools := Split ({?strSchoolIds}, ",");
Local NumberVar arrLen := UBound( schools );
Local NumberVar i := 1;
NumberVar Array schoolIdsArray;
Redim schoolIdsArray[arrLen];
While i <= arrLen Do
(
    schoolIdsArray[i] = ToNumber(Trim(schools[i]));
    i := i + 1;
);

{Schools.schoolId} in schoolIdsArray
and {Schools.active} = 1;

Answer 3

I have the similar issue and it does not work for me. The parameter {?Domain} get data from .NET apps as the string with values 1,4,9,25

In the selection criteria I try to type {vwAgregateRepByAPO_0.DOMAIN} in [{?Domain}] and crystal report return error. So I try the script: StringVar Array StrArray := Split ({?Domain}, ","); Local NumberVar arrLen := UBound( StrArray ); Local NumberVar i := 1;

NumberVar Array DomainArray; Redim DomainArray[arrLen]; While i <= arrLen Do ( DomainArray[i] = ToNumber(Trim(StrArray[i])); i := i + 1; );

{vwAgregateRepByAPO_0.DOMAIN} in DomainArray;

.......... No errors but report generated as a blank page. Any one has idea?

Thank you. Paul

Answer 4

in this part of the code, I added a colon and it worked...

schoolIdsArray[i] := ToNumber(Trim(schools[i])); 

Hope this helps...