Grouping of the same column in to two groups based on condition

[Former Member]

Hi All I need to group the data of a table for the same column values into two groups. I'll explain my requirement with a small data as below

Person	Pet	Cost	Count
John	dog	20	1
John	cat	10	1
John	fish	10	1
John	fish	20	1
Joseph	dog	30	1
Joseph	dog	20	1
Joseph	cat	10	1
Joseph	fish	10	1
Robet	dog	30	1
Robet	cat	10	1
Robet	cat	20	1
Robet	fish	15	1

Now my report should first group the data based on peson and then on pet, but when grouping with person all terrestrial animals in one set and fish alone in seperate set. so the report should look like this

Person                             Pet                  sum (cost)                                 sum( count)

john

                                          dog                   20                                               1

                                          cat                    10                                               1

                                       cost                   30                                                2

                                          fish                   30                                                2

                                         cost                   30                                                2

joseph

                                          dog                   50                                               2

                                          cat                    10                                               1

                                       cost                   60                                                3

                                                                                  fish                   10                                                1

                                         cost                   10                                               1

Robet

                                          dog                   30                                               1

                                                                                  cat                    30                                               2

                                       cost                   60                                                3

                                          fish                   15                                                1

                                         cost                   15                                               1

[Former Member · Accepted solution]

Hi Ayyappa

You need another column in your data that identifies the Pet Type (Terrestrial, Aquatic).  Is this field available in your dB?  If not, you can add a formula in your report:

@Pet_Type
Select [PET]
Case “dog”:”T”
Case “cat”:”T”
Case “fish”:”A”
Default:”X”

You can then use the @Pet_Type formula in the group expert.

[Former Member · Accepted solution]

Thank you that worked exactly