Grouping in crystal reports

[Former Member]

Hi Team

I have a data like this and having some values for every row

Country	Values
Indiastate1	22
Indiastate2	1
Indiastate3	3
UsBay1	2
UsBay2	4
UsBay3	6
UKnnn	1
UKmm	2

Now I want summary for each country.Please suggest

Indiastate1

Indiastate2

Indiastate3

India Total :   26

UsBay1

UsBay2

UsBay3

US Total:      12

UKnnn

UKmm

UK Total:      3  

[abhilash_kumar · Accepted solution]

Hi Vissu,

Insert a group on the country field.

Go to the insert summary option > choose the Values field as the Field to Summarize > Select Sum as the summary operation.

Under 'summary location', choose the Group for Country.

Suppress the Group Header section.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash

Thanks for your response

I just gave sample data only

I need to group based on first name only

I have data like this

Department:

TTTaca

TTTbab

TTTcns

RRRads

RRRnsd

RRRdgs

sdsd

fdfd

sddfsd

dfsdf

The first name should never change for TTT and RRR

I need show total of TTT,RRR and remaining like below

What ever the data we have apart from TTT and RRR should consider as one group

TTTaca

TTTbab

TTTcns

Total for TTT

RRRads

RRRnsd

RRRdgs

Total for RRR

sdsd

fdfd

sddfsd

dfsdf

Total for remaning :

[abhilash_kumar · Accepted solution]

Do you have a field that gives the first name?

-Abhilash

[Former Member · Accepted solution]

hi abhiash

i have a field which gives names as i shown aboard only

not separate fileds

[abhilash_kumar · Accepted solution]

And on what basis are the first names supposed to be extracted?

Just the first three letters?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash

yes.

[abhilash_kumar · Accepted solution]

And do you only want to group based on the first three letters when they're equal to TTR or RRR?

-Abhilash

[Former Member · Accepted solution]

HI Abhialsh

yes.And remainng should be as one more group

[abhilash_kumar · Accepted solution]

Create a formula with this code:

If Left({Name field}, 3) = 'TTR' then 'TTR'

else if Left({Name field}, 3) = 'RRR' then 'RRR'

else 'Remaining'

Insert a group on this formula field.

Insert a summary on the value field as described in my first reply.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I have 3 remaining values in else condition.

How to write those.

Also I am getting data like tghis after adding group as you told above

TTT  total:

RRR total:

Iam missing individual values

I wrote like this but getting error A string is required

If Left({db_txt.ASD}, 3) = 'TTT' then 'TTT'

else if Left({db_txt.ASD}, 3) = 'RRR' then 'RRR'

else {db_txt.ASD} in ['fsd','hfgh','pljgh']

[abhilash_kumar · Accepted solution]

You don't need to mention the remaining values as they need to be grouped together.

If Left({Name field}, 3) = 'TTT' then 'TTT'

else if Left({Name field}, 3) = 'RRR' then 'RRR'

else 'Remaining'

When you insert a group using this formula field, CR should create three groups. It should put all records where the first three letters = 'TTT' into a group called 'TTT'.

Similarly all 'RRR' records will be in their own RRR group.

And the rest will be grouped together into a group called 'Remaining'.

After you insert the group, go ahead and insert the summary as stated in my first reply.

Then, suppress the Group Header and move the summary to the Group Footer if it isn't on this section already.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am getting the data as expected now.

But I am getting in  different order.

I took group sort in ascending order

I am getting like this

remaining

RRR

TTT

If i took desc order I am getting like this

TTT

RRR

remaining

But I need

RRR

TTT

remaining

Please help

[abhilash_kumar · Accepted solution]

Go to the Group Expert > Highlight the formula field > Options > Change the default sort from 'in ascending order' to 'in specified order'.

Use the Named Group drop-down to select 'RRR' first. Then select 'TTT' and then 'Remaining'.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Working fine.I need to show Group Footer summary only for RRR and TTT only.

I dnt need summary for remaining ones

[abhilash_kumar · Accepted solution]

Go to the Section Expert > Highlight the Group Footer section > Click the formula button beside 'Suppress' > use this code:

{@Formula Field} = 'Remaining'

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I did like this {@Formula Field} = '' but still the section is showing as blank

Also i checked suppress blank section

[abhilash_kumar · Accepted solution]

To suppress the section when the group = 'Remaining', you'd need to use:

{@Formula Field} = 'Remaining'

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

For custom sort I don't have production access to get the values in drop down.

So how can I get the values to do custom sort

[abhilash_kumar · Accepted solution]

Hi Vissu,

As the initial question about grouping and summary has been answered, please post the sorting question as a separate discussion per SCN's rules.

-Abhilash