Is This Possible? using Formula in parameter 1

[tonygr]

Hi All,

I am using crxi2 R3 to SQL2000 DB

I have a report that uses a dynamic prompt for

selecting first a place then dynamic brings up list of values to select from that.

I would like to add an additional parameter based (not dynamic) but referencing a formular which in turn references anther formular taken from this forum Mr Hamadys I believe the two formulas are as follows:
the first converts DOB to Age:

WhileReadingRecords;
Datevar Birth:= DateTimeToDate ({viewdayslastvisit.DOB});
Datevar Ann:= currentdate;
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

the second uses the above to get groupings:

if {@age} < 5 then "0 to 4" else
if {@age} < 11 then "5 to 11" else
if {@age} < 16 then "12 to 16" else
if {@age} < 24 then "17 to 24" else
if {@age} < 34 then "25 to 34" else
if {@age} < 44 THEN "35 to 44" else
if {@age} < 54 then "45 to 54" else
if {@age} < 64 then "55 to 64" else
if {@age} < 74 then "65 to 74" else
if {@age} < 84 then "75 to 84" else
if {@age} < 94 then "85 to 94" else
if {@age} < 115 then "95 and above"

it is this formular I would like to add as a parameter?

Can this be done? Any help greatly appreciated.

Kind Regards

Tonyg

 

[lbass]

The groupings formula should be corrected to:

if {@age} < 5 then "0 to 4" else
if {@age} < 11 then "5 to 10" else
if {@age} < 16 then "11 to 15" else
if {@age} < 24 then "16 to 23" else
if {@age} < 34 then "24 to 33" else
if {@age} < 44 THEN "34 to 43" else
if {@age} < 54 then "44 to 53" else
if {@age} < 64 then "54 to 63" else
if {@age} < 74 then "64 to 73" else
if {@age} < 84 then "74 to 83" else
if {@age} < 94 then "84 to 93" else
if {@age} < 115 then "94 and above"

...although these are kind of odd intervals.

If you want to use a parameter that allows the user to select certain age groups, then set up a string parameter {?Age} with options like:

0 to 4
5 to 10
11 to 15 //etc.

Then write a record selection formula like this:

select {?Age}
case "0 to 4" : {@age} < 5
case "5 to 10" : {@age} < 11
case "11 to 15" : {@age} < 16 //etc.

-LB

 

[tonygr]

Hi LB,
Thanks for your response,

who are we to question customers requirements I said the same thing about the groupings strange I know.

Many Thanks

Problem Solved

 

[lbass]

Whatever the intervals are, you must change the formula so that the labels are accurate in relation to the age--and they weren't accurate in your initial post.

-LB

 

[tonygr]

Sorry LB,

I did correct as per your previous post and noticed my newbieness (Silly me!!!) Thanks for spotting it.

I also added the "[1]" to make the below work, in case anyone else finds this thread of some use.

select {?Age}[1]
case "0 to 4" : {@age} < 5
case "5 to 10" : {@age} < 11
case "11 to 15" : {@age} < 16

Thanks Again

Tonygr

 

[lbass]

You should not need the [1]--did you set this up to allow multiple values?

-LB

 

[tonygr]

Yes!

Tonyg

 

[lbass]

If you want to allow multiple ranges, change your record selection formula to:

whilereadingrecords;
numbervar i;
numbervar j := ubound({?age});
numbervar range array y;
redim preserve y[j];

for i := 1 to j do(
y := (
select {?age}
case "0 to 4" : 0 to 4
case "5 to 9": 5 to 9
case "10 to 14" : 10 to 14
default : 0
));
{@Age} in y

-LB

 

[tonygr]

Hi LB,
Thanks again for your input,

would the above work if I had other parameters included in the record selection? please see below original record selection based on your first solution:
====================================================
{viewDaysLastVisit.EXPIRY} >= currentdate() and
{viewDaysLastVisit.CENTRE} = {?Selections - CENTRE} and
{viewDaysLastVisit.TYPE} = {?Selections - TYPE} and
select {?Age}[1]
case "0 to 4" : {@age} < 5
case "5 to 10" : {@age} < 11
case "11 to 15" : {@age} < 16
case "16 to 23" : {@age} < 24
case "24 to 33" : {@age} < 34
case "34 to 43" : {@age} < 44
case "44 to 53" : {@age} < 54
case "54 to 63" : {@age} < 64
case "64 to 73" : {@age} < 74
case "74 to 83" : {@age} < 84
case "94 to 115" : {@age} < 116

I ask this because when i implement your second solution it appears to ignore the first few parameters.
Sorry if I didn't fully explain the original query!

Thanks Again

TG

 

[lbass]

whilereadingrecords;
numbervar i;
numbervar j := ubound({?age});
numbervar range array y;
redim preserve y[j];

for i := 1 to j do(
y := (
select {?age}
case "0 to 4" : 0 to 4
case "5 to 9": 5 to 9
case "10 to 14" : 10 to 14
default : 0
));
{@Age} in y and
{viewDaysLastVisit.EXPIRY} >= currentdate() and
{viewDaysLastVisit.CENTRE} = {?Selections - CENTRE} and
{viewDaysLastVisit.TYPE} = {?Selections - TYPE}

-LB

 

[tonygr]

Excellent, Thanks again LB

Works a treat!

TG