How to get rid of the duplication data?
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=4231
Printed Date: 07 May 2024 at 3:36am
Topic: How to get rid of the duplication data?
Posted By: Elisha83
Subject: How to get rid of the duplication data?
Date Posted: 11 Sep 2008 at 9:46pm
|
Hi there~
Im facing a problem on displaying my reports data accurately due to the duplication of data. Im creating a report whereby I need to compare Purchase Requisition data and also Purchase Orde Data. Below are my report output and also what I wish to get.
My current report output:
Req. No. Desc. Qty Req. Po No. Qty PO
001 Eraser 10 PO-01 12
001 Eraser 10 PO-01 20
001 Paper 30 PO-01 12
001 Paper 30 PO-01 20
002 Pencil 40 PO-02 40
002 Pencil 40 PO-02 20
002 Pen 20 PO-02 40
002 Pen 20 PO-02 20
Desired Output:
Req. No. Desc. Qty Req. Po No. Qty PO 001 Eraser 10 PO-01 12 001 Paper 30 PO-01 20
002 Pencil 40 PO-02 40
002 Pen 20 PO-02 20
So can anyone guide me on how or what I should do in order to get the desire output?
Thanks in advance~  ------------- 3Lish@ |
Replies:
Posted By: sign_seventh
Date Posted: 11 Sep 2008 at 10:57pm
|
your records duplicate bcoz of different value in qty po.
i think changing your query would be great. if you plan in getting 1 entry only disregarding what their qty po. try this
select tbl1.reqno,(select top 1 qtypo from tblname where reqno=tbl1.reqno)
from tblname tbl1
group by reqno HAVING COUNT(*) > 1 |
Posted By: Elisha83
Date Posted: 23 Sep 2008 at 2:15am
|
Im not using sql query to do. So, if using your query, where should i put this query to? ------------- 3Lish@ |
Posted By: Krishna Kumar
Date Posted: 23 Sep 2008 at 3:00am
|
Hi, Introduce a group on the report for Description and place all teh fields in the Group Footer. Also remember to sort the desierd field (EG: Q PO) in the desiered order. Krishna ------------- Thanks and regards Krishna |
Posted By: Yume
Date Posted: 29 Sep 2008 at 5:55am
|
Are you using more than one table, if so it could be the linking of the tables that's causing it |
Posted By: hilfy
Date Posted: 29 Sep 2008 at 1:23pm
|
It looks like this is a table linking problem. You're getting quantities for all PO numbers under each PO number.
What is your table structure and how are you currently linking them? Also, as a general rule of thumb - SmartLinking isn't smart. It causes problems because it links based on matching field names. I always turn it off and do my own linking.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: gavinkil
Date Posted: 01 Oct 2008 at 1:38am
|
I have a similar problem The basic problem is we generate reports from SAP over 6 month time periods. In this period people may have left and been reappointed and therefore come up twice in the output list with different figures for each time spent employed. Was just wondering if there is a way in Crystal to only produce one line for these people with combined figures. Therefore instead of Pers # Name Abs Spells Days 10001013 Stewart 1 2 3 10001013 Stewart 2 3 1 It reads Pers # Name Abs Spells Days 10001013 Stewart 3 5 4 This is currently been taking into Crystal via Excel and only using one table to do so. |
Posted By: Yume
Date Posted: 01 Oct 2008 at 1:46am
| You could sort the by the Pers # first and group by Pers #, inserting a sum in that group to get the total |
Posted By: gavinkil
Date Posted: 01 Oct 2008 at 2:17am
|
If I sort by Pers# then group by Pers# and sum the Pers# it will just count the amount of personnel numbers. Will I have to make the fields I want to total into groups and therefore change the template to display only totals? |
Posted By: Yume
Date Posted: 01 Oct 2008 at 2:27am
| Insert a summary against the Abs etc showing the sum in the group section. Right Click on the fields you want the summary for and insert in the group line |
Posted By: gavinkil
Date Posted: 01 Oct 2008 at 2:44am
|
Got it working :) Basically Do the sum's and group then hide the details line and add the required display fields into the group footer. Does exactly what I was after. Am I still able to sort by Department etc... after this without the sum's falling over? Or would that require a coded field? Perhaps slightly out thinking myself not worked with Crystal before today. |