Count Criteria In Multiple Groups 3

[govmule]

Hello Everyone,

I'm trying to group by five districts in our County and then subgroup by city. Here is the formula:

***************************************************
If {act_youth.app_city} in ['GARDEN GROVE', 'SANTA ANA', 'WESTMINSTER']
Then "District 1"
else
If {act_youth.app_city} in ['COSTA MESA', 'CYPRESS', 'FOUNTAIN VALLEY', 'GARDEN GROVE', 'HUNTINGTON BEACH',
'LA PALMA', 'LOS ALAMITOS', 'NEWPORT BEACH', 'SEAL BEACH', 'STANTON', 'BALBOA', 'CORONA DEL MAR',
'LIDO ISLE', 'ROSSMOOR', 'SANTA ANA HEIGHTS', 'SUNSET BEACH', 'SURFSIDE']
Then "District 2"
else
If {act_youth.app_city} in ['ANAHEIM', 'BREA', 'IRVINE', 'ORANGE', 'TUSTIN', 'VILLA PARK',
'YORBA LINDA', 'COWAN HEIGHTS', 'EL MODENA', 'LEMON HEIGHTS', 'OLINDA', 'OLIVE', 'ORANGE HILLS',
'ORANGE PARK ACRES', 'TUSTIN FOOTHILLS']
Then "District 3"
else
If {act_youth.app_city} in ['ANAHEIM', 'BUENA PARK', 'FULLERTON', 'LA HABRA', 'PLACENTIA']
Then "District 4"
else
If {act_youth.app_city} in ['ALISO VIEJO', 'DANA POINT', 'LAGUNA BEACH', 'LAGUNA HILLS',
'LAGUNA NIGUEL', 'LAGUNA WOODS', 'LAKE FOREST', 'MISSION VIEJO', 'RANCHO SANTA MARGARITA',
'SAN CLEMENTE', 'SAN JUAN CAPISTRANO', 'LADERA RANCH', 'COTO DE CAZA', 'LAS FLORES', 'NEWPORT COAST',
'TRABUCO CANYON']
Then "District 5"
*****************************************************

My problem is that two Cities "Garden Grove" and "Anaheim" are in multiple districts. I'd like to include records for these cities in both districts.

Does my request even make sense?

Thanks In Advance!

 

[synapsevampire]

The request makes sense, but your data probably doesn't support it as you'll only have one row of each, you'd need to create extra rows in the data to support this, a grouping is considered a unique value, whereas yours is not.

One solution might be to create an additional table which has the group names and their respective cities and join it to your table based on the city name, in which case you'll get 2 rows for those cities, and already have the group names.

-k

 

[kenhamady]

The only way to have CR put one record into two different places is to use subreports. Have each subreport select the cities for that district.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

Maybe you could add an alias table (act_youth_1) and then in the record selection limit records from this second table to the two cities in question. Then in your if-then statement, for the second instance of each city in question, you could say, e.g.,:

If {act_youth.app_city} in ['BUENA PARK', 'FULLERTON', 'LA HABRA', 'PLACENTIA'] or
{act_youth_1.app_city} = 'Anaheim'
Then "District 4"

I think you would need to create a left join from a main (third) table to the alias table, not from the act_youth table. Not sure this would work, but it might be worth a try.

-LB

 

[govmule]

My thanks to all of you. I evaluated your suggestions and decided to create five seperate subreports which has worked out fine. All three solutions seemed logical I just didn't want to create any tables on the Informix side.

Once again, I must say that you have been the best resource possible for solutions regarding Crystal Reports. My hats off to each of you.

Cheers,

Jack Luster
Systems Analyst
County of Orange, CA