Grouping Different Groups

[rbh123456789]

Hello,

Here is an example of a column in my DB.

City:
Toronto
Toronto - East
Toronto East
Toronto - West
Toronto W
Montreal
Montreal E
Montreal West

When grouping the 'City' column in my report, and adding a subtotal count to each group, they will all appear with a subtotal of 1 for each group.

Is there a way to modify the group to include all the Toronto entries in one group, and all the Montreal entries in another?

This might not even be possible, but i thought i would ask the experts.

 

[lbass]

if {table.city} like "Toronto*" then "Toronto" else
if {table.city} like "Montreal*" then "Montreal" else
{table.city}

-LB

 

[TeyBrady]

You could write a formula that maps the cities and group on the formula. You'll have to make the call on how to write the formula.

This will work for your data but add in a Port Huron and a Port Arthur and you will be looking at one Port.

if instr({table.CITY}," ") = 0 then
{table.CITY}
else
left({table.CITY}, instr({table.CITY}," ") - 1)

 

[lbass]

That's a good point. To avoid hard coding the city name, you could use:

stringvar array x := split({table.city}," ");
if ubound(x) > 1 then
x[1] else
{table.city}

This wouldn't work correctly though if the name of the city had two words, as TeyBrady notes.

-LB