Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: How to Consolidate Records Post Reply Post New Topic
Author Message
Luis2101
Newbie
Newbie


Joined: 14 May 2008
Online Status: Offline
Posts: 18
Quote Luis2101 Replybullet Topic: How to Consolidate Records
     Posted: 16 May 2008 at 6:12am
Hello again everyone. First I'd like to thank those guys who helped me out last time I posted. I got another question for you guys.
 
I'm making a cross tab that will show different categories of products and their total sales numbers through a few years. The issue is that many of the "Category" records are redundant and I would like to consoldate several of them into one record and then just add up all the values and store them into one.
 
For example, I have several "Compac" categories, ranging from Compac I to Compac IV. I would like to make all those into ONE Compac record and have my cross-tab show the sales totals for all the Compacs in that one compac record.
 
This sounds simple enough, but I can find anything in the user's guide about it.
 
Any help would be appreciated.
Many thanks.
IP IP Logged
Tim Wise
Newbie
Newbie
Avatar

Joined: 29 Apr 2008
Location: United States
Online Status: Offline
Posts: 31
Quote Tim Wise Replybullet Posted: 16 May 2008 at 12:17pm

Be forewarned: I'm a noob to CR.

I would add a formula field that returns the category you want to group on. For example:
 
    if (category starts with "Compac") then
        return "Compac"
 
Then create a cross-tab or group using your new field.
 
This does require you to know all possible categories you're going to see so that you know how to map them.
--
Tim
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 19 May 2008 at 4:21am
As a note, it actually doesn't necessarily require you to know all possible categories.  You just have to write an intelligent "ELSE" clause.

IF Left({MyReport.Category},6) = "Compac" THEN
    "Compac"
ELSE
    {MyReport.Category}

That style will collapse all the Compac entries into "Compac," but leave all the others in their original form.

IF Left({MyReport.Category},6) = "Compac" THEN
    "Compac"
ELSE
    "Other"

That style will take any record that is not matched with one of the other IF..THEN tests, and group them all up into one miscellaneous category.

IF Left({MyReport.Category},6) = "Compac" THEN
    "Compac"
ELSE
    "Other - " + {MyReport.Category}

That style will list all of the categories not accounted for.  But, by putting "Other - " at the front, it will group them all together in the report, alphabetically.
IP IP Logged
Luis2101
Newbie
Newbie


Joined: 14 May 2008
Online Status: Offline
Posts: 18
Quote Luis2101 Replybullet Posted: 19 May 2008 at 5:34am
Great ideas. Thanks for the help.

Just as a note, I would make that formula field run WhileReadingRecords, correct?
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 19 May 2008 at 6:21am
Yes.  You probably don't need to specify it, strictly speaking.  But, it rarely hurts.  And, you would definitely want it to happen while reading, so that Crystal can group on it.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.