BusinessObjects Board

SELECT CASE formula problem in CR2008

building reports crystal reports legacy
#1

I want to use the BirthDate of employees and print their Ages and AgeRanges in a report.

I created a formula called Age:

(Today-{Employee.Birth Date})/365

This formula is working fine.

Using the Age formula – I created another formula named AgeRange:

SELECT {@Age}
CASE IS < 19 : "0-19"
CASE IS > 19, IS < 34 :"20-34"
CASE IS > 34, IS < 49 : "35-49"
CASE IS > 50, IS < 64 : "50-64"
CASE IS > 64: "65+"

The AgeRange formula shows no errors in formula editor.
But, takes only the second condition for any age.

Eg: If a person is of age 36, AgeRange would be 20-34.

Thank you.

mpattuj (BOB member since 2012-06-12)

#2

I haven’t used “Select Case”, instead I generally use Switch which does essentially the same thing. It would look like this:

switch(
{@Age} <= 19, “0-19”,
{@Age} in 20 to 34, “20-34”,
{@Age} in 35 to 49, “35-49”,
{@Age} in 50 to 64, “50-64”,
{@Age} >= 65, “65+”,
“Unknown”)

If there is a way to use “in” with Select Case, that would work for you as well.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#3

Thanks a lot Hilfy for the quick reply.

But it says,

mpattuj (BOB member since 2012-06-12)

#4

I think there as to be a balance between tests and results.

switch(
{@Age} <= 19, “0-19”,
{@Age} in 20 to 34, “20-34”,
{@Age} in 35 to 49, “35-49”,
{@Age} in 50 to 64, “50-64”,
{@Age} >= 65, “65+”,
True, “Unknown”)

kevlray :us: (BOB member since 2010-06-23)

#5

This code works but there is a small problem…

Check the image,
1.JPG
there seems to be a problem at the end values of a set.

I made some cosmetic changes to the code

SWITCH( 
{@Age} <= 19, "0-19", 
{@Age} IN 20 TO 29, "20's", 
{@Age} IN 30 TO 39, "30's", 
{@Age} IN 40 TO 49, "40's", 
{@Age} IN 40 TO 49, "Oldies", 
TRUE, "Unknown")

mpattuj (BOB member since 2012-06-12)

#6

If you copied and pasted your formula and it’s not a typo - take a look at the “Oldies” line. The ages duplicate between it and the next line. That may be causing your problem.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#7

When you need to include data from multiple tables or look at multiple differences, you should look at using a Switch formula. The format for Switch is something like this:

Switch (
{MyTable.Field1} = ‘a’, {MyTable.Field2},
{MyTable.Field1} = ‘b’, {MyTable.Field3},
{MyTable2.Field1} = ‘c’, {MyTable.Field4},
true, ‘Unknown’)

You have condition/result pairs where the condition always evaluates to True or False. The last line should always be a final true with what you want returned if none of the other conditions are met.

-Dell

hilfy :us: (BOB member since 2007-04-16)