Help with Customize Group Name 1

[eastwestk]

Hello Everyone,

I hava crystal report XI with a country field. I would like to group the report based on country field as follows:

Group Name
------------------
1. United States (which should include records from Peurtorico also)
2. Canada
3. Mexico
4. Others(I want to name this group as International)

Also please let me know is there a way to display the count of each group in group tree next to their group name. I don't want to display the count of each group in any section of the report.

Any suggestion is greaty appreciated.

Thanks in advance!

 

[lbass]

I don't know what else is involved in the report, but to use a summary in the groupname, you need to return an already-calculated summary to the report. You can accomplish this by using a command as your datasource, setting it up something like this (though not sure whether Puerto Rico is in your database as a "country" or not):

Select 'United States' as Grp,
(
select count(`ID`)
from table A
where A.`Country` in ('United States','Puerto Rico')
) as CountryCnt
from table

union all

Select 'Canada' as Grp,
(
select count(`ID`)
from table A
where A.`Country` = 'Canada'
)
from table

union all

Select 'Mexico' as Grp,
(
select count(`ID`)
from table A
where A.`Country` = 'Mexico'
)
from table

Union all

Select 'International' as Grp,
(
select count(`ID`)
from table A
where A.`Country` not in ('United States','Puerto Rico', 'Canada', 'Mexico')
)
from table

Insert a group on {command.Grp} and in the group expert, go to the options tab->customize groupname ->use a formula->x+2 and enter:

{command.Grp} + " " + totext({command.CountryCnt},0)

You should add the other fields you are working with into each section of the command and use it as your sole datasource.

-LB

 

[lbass]

PS. You would then remove the groupname from the group header and replace it with the field {command.grp}.

-LB