Solved

Summing and eliminate duplicates in crystal report 8.5

Posted on 2009-04-10
10
1,014 Views
Last Modified: 2012-05-06
Dear Experts,

I have a database table that store saledetails from an invoicing program. In that table I have the following fields:
- saleid
- item_code
- item_name
- quantity
- supplier_code
- customer_code

What I'm trying to do is to print a record which will show the amount of item a specific customer has ordered. (ie. customer A has made many purchases and he had ordered item A 3 times with quantity 5 each times, item B 2 times with quantity 3 each time and item C 1 time with quantity 1.)

I want my report to show

item_code      quantity
A                      15
B                       6
C                       1

so far, whenever i execute the crystal report, it will print the following:

item_code       quantity
A                         5
A                         5
A                         5
B                         3
B                         3
C                         1

I've used a group by item_code and subtotal field. With subtotal i can get the total quantity for each item, but the report still showed a particular item multiple times and the subtotal fields also showed in the group footer section, which made my report looks messy. Any idea how do i get the report i want experts? Thank you.
0
Comment
Question by:Slythie
  • 6
  • 3
10 Comments
 
LVL 17

Accepted Solution

by:
MIKE earned 80 total points
Comment Utility
Right click in the GRAY area out to the right of the DETAILS Section that you don't want to see...and choose SUPPRESS.

Incidentally all sections can be adjusted this way.....
0
 
LVL 17

Expert Comment

by:MIKE
Comment Utility
SORRY right click in the GRAY area out to the ***LEFT*** ...not the right....
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 45 total points
Comment Utility
That will suppress the detail section

To get the totals
Group by the item code
Right click the QTY field
Click INSERT --> SUMMARY
Set summary as sum
Set location as the group footer
Drag the field in the footer to the group header
Suppress the group footer

mlmcc
0
 
LVL 17

Expert Comment

by:MIKE
Comment Utility
Slythie:

According to your last comments,...it looks like you already have your Subtotal setup the way you want it, and so now you only need to SUPRESS the display of the DETAILS section in order to fix your issue.

At this point,...it appears to be a Formatting Display issue....of course you may need to do some other formatting, but that should be cosmetics only...

Hope it helps
0
 

Author Comment

by:Slythie
Comment Utility
thx for the response experts. I've tried all you've suggested but i can't get rid of the multiple entries for each items.

My reports still show:

item_code       quantity
A                         5
A                         5
A                         5
                           15 (subtotal)
B                         3
B                         3
                           6 (subtotal)
C                         1
                           1 (subtotal)

i want my report to print each item once with the subtotal on the same line as the item_code. Is there anyway to do it? thx
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 17

Expert Comment

by:MIKE
Comment Utility
What SECTION are these lines showing up in:

A                         5
A                         5
A                         5

Look out to the LEFT of those lines in the report design tab...in the gray area...what is shown there..
0
 

Author Comment

by:Slythie
Comment Utility
ohh ok nvm experts... i got what u guys are trying to say now... silly me, its just about playing with the group headers eh?? hehehe.. thx a bunch experts.. =)
0
 
LVL 17

Expert Comment

by:MIKE
Comment Utility
Just RIGHT CLICK whatever section is there...and SUPPRESS it....
0
 
LVL 17

Expert Comment

by:MIKE
Comment Utility
You may need to copy/paste the ITEM_CODE into the Group Footer to display it for your report...
0
 

Author Closing Comment

by:Slythie
Comment Utility
Thank you everyone.. =)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now