Array Parameters

[terrym777]

I have very basic Crystal skills and I need help with an array parameter. We have a camp database where a child may attend camp for any number of 8 weeks. For example, a camper may attend weeks 1,2,3,7,8 stored in this format. We want the user to be able to select all weeks or multiple weeks in a report.If the parameter Week "Allow Multiple Values" is defined as False, then the below record selection works for 1 week.

If {?Week} = "ALL" Then
Not IsNull({CAMP_BILLING.CAMP_WEEKS})
Else
{?Week} in {CAMP_BILLING.CAMP_WEEKS}

However, I get the following error message if the parameter Week "Allow Multiple Values" is defined as True:
The array must be subscripted. For example: Array
We want the user to be able to select, for example, weeks 1 , 2 and 4 only. In this case, the record should be selected as the child attended weeks 1 and 2, even though the child did not attend week 4.

I know I will need to use variables and arrays but I have not reached that skill level yet so any detailed help would be greatly appreciated. I am trying to avoid having my DB manager create a view or a stored procedure in order for me to accomplish this.

Thanks!

 

[Turkbear]

Hi,
To have both a signifier ( like 'ALL') and a multi-value parameter, you must use the array style for both tests, try:

If {?Week}[1] = "ALL" Then
True
Else
{?Week} in {CAMP_BILLING.CAMP_WEEKS}

This will mean, of course, that users have to be instructed to pick ALL only, not ALL and some week number.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
OOPS! the formula should read:

If {?Week}[1] = "ALL" Then 
  True
   Else 
  {CAMP_BILLING.CAMP_WEEKS}  IN {?Week}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[terrym777]

The users are aware of selecting either just "ALL" or selecting certain week numbers. Reversing the Else clause as you indicated above to {CAMP_BILLING.CAMP_WEEKS} IN {?Week}did eliminate the error message but I am not getting any data results. The ALL will generate 14 camper records , 12 of which are attending both weeks 1 and 2. Selecting 1 and 2 in my ?Week parameter prompt returns no values instead of the 12 that is expected.

 

[Turkbear]

Hi,
Perhaps an = not an IN :

If {?Week}[1] = "ALL" Then 
  True
   Else 
  {CAMP_BILLING.CAMP_WEEKS}  = {?Week}

Or using IN with the brackets needed ( maybe)

If {?Week}[1] = "ALL" Then 
  True
   Else 
  {CAMP_BILLING.CAMP_WEEKS}  IN [{?Week}]

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[terrym777]

Using the brackets results in the array error message again. I really think that the solution is going to be much more involved from what I saw in some of the other array problem postings where loops and ubounds and variables and arrays are used. Thanks.

 

[Turkbear]

Hi,
What did the = sign produce?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[terrym777]

I assume you are referring to the ALL option:

If {?Week}[1] = "ALL" Then ...

This works fine with or without the [1].All 14 records get selected. The problem is only in trying to select multiple weeks for the report.

I made a mistake in my previous thread. I should have said that the user will be selecting multiple weeks but using an "OR" condition. So if the user selects 1 and then selects 2 in the parameter prompts for individual weeks, the results should still return all 14 records below as each camper is attending either week 1 or week 2. If the user selects weeks 2 and 3, then 12 records should get selected.

Sample Database:
ID Weeks
1 1,2,3,4,5,6,7
2 1,2,3,4,5,6,7
3 1,2,3,4,5,6,7
4 1,2,3,4,5,6,7
5 2,3,4,5,6
6 2,3,4,5,6
7 1,4,5,6,7,8
8 1,2,3,4
9 1,2,3,4,5,6,7,8
10 1,2,3,4,5,6,7,8
11 1,8
12 1,2,3,4
13 1,2,4,5,6,7,8
14 1,2,3,4,5,6,7,8

 

[Turkbear]

Hi,
Just to clarify, how many parameters and prompts are there?

If only 1 then you are correct , you will need to parse each element from the parameters muti-value array and, somehow that is not clear from your posting, determine the connecting logic ( AND or OR or some combination, if allowed)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[briangriffin]

I think it's often easier to create a formula to identify the records you want and then reference that formula in the selection criteria, rather than trying to do it all within the select.

Put this formula in the detail section:

whileprintingrecords;
stringvar array y := split(({WeeksField},",");
numbervar x;
booleanvar z := false;

for x := 1 to ubound({?Week})

do
(
if {?Week}[x] in y then z := true;
);

z

Then your selection can be:

if {?Weeks} = "All" then true else {@thisformula} = true

 

[lbass]

You would need to remove the "whileprintingrecords" and then Brian's suggestion should work.

-LB

 

[terrym777]

Hi Lbass or Brian,

I am still having trouble getting this to work for multiple weeks selected by the user. It works if they select ALL in my parameter prompt but does not work if they select multiple weeks in the parameter prompt (for example, 2 and then 3). I should get 2 records selected and I am not getting any records. Is there anything else you see that I can try? Thanks.


Formula Name = Weeks
stringvar array y := split(({CAMP_BILLING.CAMP_WEEKS}),",");
numbervar x;
booleanvar z := false;
For x := 1 to ubound({?Week})
Do
(
If {?Week}[x] in y then z := true;
);
z

Record Select:
(If {?Week} = "ALL" Then
Not IsNull({CAMP_BILLING.CAMP_WEEKS})
Else
{@Weeks} = True)

 

[lbass]

Try adding this directly in report->selection formula->record:

stringvar array y := split(({CAMP_BILLING.CAMP_WEEKS}),",");
numbervar x;
booleanvar z := false;
For x := 1 to ubound({?Week}) Do (
If {?Week}[x] in y then
z := true
);
If {?Week} = "ALL" Then
true else
z

-LB

 

[terrym777]

Thank you Brian and lbass. The formula within the record select did actually work. There was another hidden problem within the report which we could never figure out what it was that was preventing the data from being displayed. We started with a working version and added back in all our new group sorts and formulas one at a time and got it working again. I then added in your formula and record select and this time it worked! Thanks so very much for your help. This logic will come in quite useful for us in the future.