Age Bands Report in V10 1

[peterb58]

I have been asked to modify an existing report to allow the user more control. The existing report displays Ages of people grouped in Specified order(e.g <30, 31-40, 41-50 etc ). Done in Group Expert at the moment.The results show the summary total for each age group and drills down to names an addresses.

The users would like to specify the banding range so we need a way of getting a parameter to control the Specified order of a group.

Can it be done

Pete

 

[lbass]

If you establish the specified order based on all age ranges when you design the report, then adding a parameter to specify a subset of ranges would not require any adjustment to the specified order. You would just add the age parameter into the record selection formula, e.g.,:

{table.age} in {?ages}//where {?ages} is a range, e.g., 20 -40

-LB

 

[peterb58]

lbass,

thanks for that, but I don't thnk it does what I want.

The report needs to show the complete database of ages banded into groups defined by the customer. They will get a starting age of <30yrs old and >100yrs. The age band between that will be defined by user input. They may choose to use 10yr bands or 5 yr bands or even every year. They may want further enhancements, but the basis is to get the banding under their control.

The current fixed report displays a graph based on 10yr bands and can be 'drilled'. I need to improve this for my custmers.

thanks

Pete

 

[MrBillSC]

I think the following may work for your AgeGroup formula.
You will have a parameter {?BandYrs} for your number of years per group. Your {AgeGroup} formula will end up with values between 0 and 71 depending on the value of
{?BandYrs}. Ages less than or equal to 30 will always have an {AgeGroup} value of zero.

The formulas.

// AgeGroup101

If Remainder(70, {?BandYrs}) > 0 then
Truncate(70, {?BandYrs}) + 2 else
(70 / {?BandYrs}) + 1


// BandGroup

If {table.age} <= 30 then 0 else
If {table.age} >= 101 then {@AgeGroup101} else
If Remainder({table.age} - 30, {?BandYrs}) > 0 then
Truncate(({table.age} - 30) / {?BandYrs}) + 1 else
(({table.age} - 30) / {?BandYrs})

Group report on {@BandGroup}

Formula for {@BandGroup} description.
// BandGroupDescr

ToText(({@BandGroup} * {?BandYrs}) + 31 - {?BandYrs}) +
" to " +
ToText(({@BandGroup} * {?BandYrs}) + 30)

I have only tested this on paper, but I think it will get you close.

MrBill

 

[Turkbear]

Hi,
I tested it and think the AgeGroup101 may have a typo..

It works well if modified to replace a comma with the division sign:

If Remainder(70, {?BandYrs}) > 0 then
   Truncate(70/?BandYrs}) + 2 else
   (70 / {?BandYrs}) + 1

Works great...!!

 

[Turkbear]

Hi again,
One more enhancement to MrBill's great code will handle any negative Group Name values when large Age Bands are specified:

(If ({@BandGroup} * {?BandYrs}) + 31 - {?BandYrs} < 1 then ToText(0) else
ToText(({@BandGroup} * {?BandYrs}) + 31 - {?BandYrs})) +
" to " + 
ToText(({@BandGroup} * {?BandYrs}) + 30)

 

[peterb58]

Thank you all for that, I will look and test this weekend.

regards

Pete

 

[peterb58]

Once again, thanks for all who responded. Just a couple of tweaks to take into account null age values stored in the database. Everything now back on track

thanks

Pete

 

[peterb58]

Just to let you know, I delivered the report to the customer and they think it is the Bee's Knees.

Thanks to you all

Pete

 

[dgillz]

Peter,

Bee's knees? Thats a new one. I guess that is good not bad right? Where are you from?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[peterb58]

That is one of the strange phrases we use in the north of England. No idea where it came from or why it is used, but I throw it in now and then. More polite than dogs b*******s

Pete

 

[MrBillSC]

I believe the "bee's knees" was popular in the U.S. also in the early part of the 20th c. 1920's era.

MrBill