Grouping problem
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21000
Printed Date: 14 May 2024 at 4:07am
Topic: Grouping problem
Posted By: jjthed
Subject: Grouping problem
Date Posted: 25 Aug 2014 at 2:21pm
|
Hello, I've working on this report for few days and I am still stuck on this grouping problem that I have and I really would hope somebody can help me solve this problem. My questions is, how can I design my report so that there are two big sections, the first section has 2 groups that are all about face items and any items other than face item would go to other section. it looks something like below: Face - Oil type item qty unit price eyeliner 5 $10 lip stick 4 $4 Total 9 $14 powder 2 $10 cream 2 $30 Total 3 $40 Face - dry type item qty unit price eyeliner 3 $10 lip stick 2 $4 Total 5 $14 powder 1 $10 cream 5 $30 Total 6 $40 other (the rest items that are not facial products) item qty unit price hand cream 1 $5 Shampoo 2 $6 Total 3 $11 Please advise how to make two big sections and the first section contain groups and the section section is the rest items. Thanks! |
Replies:
Posted By: kostya1122
Date Posted: 25 Aug 2014 at 3:11pm
|
formula for group like if not( item in[ "Face - Oil type","Face - dry type"]) then "other" else item |
Posted By: jjthed
Date Posted: 26 Aug 2014 at 7:21am
| Thanks, but where do I put that formula? Sorry I am very new here... |
Posted By: kostya1122
Date Posted: 26 Aug 2014 at 8:10am
|
you group on this formula instead of you current group |
Posted By: DBlank
Date Posted: 26 Aug 2014 at 8:11am
|
in the field explorer you create a formula field and use kostya's code.
Then in the grouping you select this formula field as teh field to group on.
EDIT: Sorry kostya - responding at the same time
 |
Posted By: jjthed
Date Posted: 26 Aug 2014 at 8:13am
| Thank you both so much. I will try that now and update :) |
Posted By: jjthed
Date Posted: 27 Aug 2014 at 5:03am
|
Thanks! I finally got the layout I wanted. How can I do totals within each section? And Where should the calculation go? Thanks! Below is the sample:
Face - Oil type item qty unit price eyeliner 5 $10 lip stick 4 $4 Total 9 $14 powder 1 $10 cream 2 $20 Total 3 $30 |
Posted By: DBlank
Date Posted: 27 Aug 2014 at 5:06am
|
assuming you do not have duplicate data...
just do a SUM of the QTY field and place it in the GF and repeat for the unit price field
|
Posted By: jjthed
Date Posted: 27 Aug 2014 at 6:45am
|
Thanks DBlank. But what I mean is how do I break the item into two separate sections and do a total per each section? ie: for Face-oil type group has two sections, and I need to total each section.
Thanks!! |
Posted By: DBlank
Date Posted: 27 Aug 2014 at 7:11am
|
how are you determining waht items appear in each "sub-grouping"? Is it based on another field value?
If so you can group on that field, hide the GH and do you sum on that group footer.
If you don't have the value you can make the value like you did with the oher group header.
You can also use Running Totals or Shared Variable formulas and some suppression techniques on a details B but I would try to avoid that.
|
Posted By: jjthed
Date Posted: 27 Aug 2014 at 8:34am
|
Hi DBlank,
Thanks a lot with the info. I did another group with a field value, but the total on the footer gives me a total on each row instead of at the end of the section. Any advise on that? Thanks! |
Posted By: DBlank
Date Posted: 27 Aug 2014 at 8:37am
| what did you group on? |
Posted By: jjthed
Date Posted: 27 Aug 2014 at 10:44am
| I group on Category, and then sort in specific order, and it turns out each category is a group... |
Posted By: DBlank
Date Posted: 27 Aug 2014 at 10:51am
|
You can hard code it as a formula like you did for the first grouping...
If Category in ["eyeliner","lip stick"] Then "Cat1" else "Cat2"
and group on that instead of category field.
The preferred method is to have some other value in a column in your data set that defines the grouping so that as your data set grows with different categories the report knows how to handle them.
Note you can use other fields to group on even if you are not displaying them in the report.
|
Posted By: jjthed
Date Posted: 27 Aug 2014 at 12:02pm
|
Ah... I see. I will try that and play around to see if I can get what I want.
Thanks DBlank!! |
Posted By: jjthed
Date Posted: 28 Aug 2014 at 7:03am
|
just an update, it worked!!!!!!!!!!!!!!!!!!
I am so new at this and this is my first time designing a report from scratch. Now the structural part is done, I will move on to calculations. Will come back and ask more tips on the sum and totals if I am stuck again. Thank you so much |
Posted By: jjthed
Date Posted: 28 Aug 2014 at 12:24pm
|
Here is a new questions:
In my detail section, I use the category field, my report now looks like this: Item QTY Price eyeliner 5 $7 eyeliner 3 $8 eyeliner 1 $6 I want it to look like this: Item QTY Price eyeliner 9 $7 (This is average price) I used below formula to suppress the duplicate names: {tempestimateitems.Category} = previous({tempestimateitems.Category}) Now I am stuck on how to sum up the QTY and put it in one line. And also need to average the unit price. Thanks in advance! |
Posted By: z9962
Date Posted: 28 Aug 2014 at 9:59pm
|
Create a group on
the item field, Then create a
sum summary on qty. Finally add an average summary on Price Place these in the
item group header.
|
Posted By: DBlank
Date Posted: 29 Aug 2014 at 3:52am
|
You would also want to move the "item field" into the GH or GF next to the sum and avg results and suppress the GH and details section. |
Posted By: jjthed
Date Posted: 29 Aug 2014 at 7:49am
| I have create a group and put the "item field" in the group footer. I used the insert summery on qty but the number returned is not right..... did I do something wrong? |
Posted By: DBlank
Date Posted: 29 Aug 2014 at 9:36am
|
it has to be on the 'correct' group footer, in this case the footer of the group using the Item field (inner most group).
It has to be set to the correct summary type, in this cas a SUM.
Alsl this process includes suppressed records. If you are doing any row suppression it will still include those records as part of the records to summarize.
|
Posted By: jjthed
Date Posted: 03 Sep 2014 at 5:13am
|
The group I am using is the 3rd group (most inner group) and I put the qty on the footer of this group. I used the SUM as summery type and there is no suppressed records so it should have all the records.
My crystal report looks like this: Group 1 (big categories) Group 2 (small categories) Group 3 (roll up all the same items and sums up the qty) |
Posted By: DBlank
Date Posted: 03 Sep 2014 at 10:13am
|
unhide the details and verify you have no duplicate values (rows).
A Group Sum will include all rows/values in that group.
|
Posted By: jjthed
Date Posted: 03 Sep 2014 at 11:21am
| I don't have anything in my details section. I have duplicate rows, but I need to include all these duplicate values into my sum. The problem I have is that the sum is less than what I expected :( |
Posted By: DBlank
Date Posted: 03 Sep 2014 at 11:23am
| examples with unhidden details and actual summary values please? |
Posted By: jjthed
Date Posted: 03 Sep 2014 at 12:53pm
| Is there anyway I can upload screen shot or attach a file here? |
Posted By: jjthed
Date Posted: 04 Sep 2014 at 6:35am
| Just an update, I figured out why I got the wrong numbers, somehow, it didn't count the duplicate qty. So I put the item number somewhere on the report and it shows the correct qty now :) |