Suppress if duplicated makes error in total on CR 8.0

In Crystal report 8.0 when I make suppress if duplicated on some conditions it works fine for printing but when I put total on that field it counts (adds)  suppressed data too.

5
2
3
5 (invisible)
_________
total 15
but I need 10

regards
kirshanAsked:
Who is Participating?
 
MsLimCommented:
The qty above is your main problem , you can not using suppress if dublicate , actually what you must do is create a formula field and compare it with previous field if same , assign as zero otherwise assign as normal.
then you can suppress if zero so that it will not displaY OUT .
for the sum you must sum up this formula field.
hope you can try it
0
 
mlmccCommented:
Unfortunately I believe that is the way CR was designed.

One way to solve this may be to SELET DISTINCT records.

mlmcc
0
 
MsLimCommented:
mlmcc can work in the sense that the whole line do not want to display , BUT I am assumeing only this duplicate not diplay and calculate , others column in this line still displaying
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kirshanAuthor Commented:
Actually my report comes out from three table

table_Alldates
Dates, product_ID
table_Sale
dates , saleQTY ,product_ID
table_Purchase
dates, PurchaseQTy,product_ID

and may be in sale or purchase on one date two transitions take plce and then if I want to print prduct transitions like

date , saleQTY ,PurchaseQTY, it make duplicate values in report
querry looks like this
____________________________________
SELECT Alldates.Dates, Alldates.Product_ID, table_Purchase.purchaseQTY, Table_Sale.SaleQTY
FROM (Alldates LEFT JOIN table_Purchase ON (Alldates.Product_ID = table_Purchase.Priduct_ID) AND (Alldates.Dates = table_Purchase.Dates)) LEFT JOIN Table_Sale ON (Alldates.Product_ID = Table_Sale.Priduct_ID) AND (Alldates.Dates = Table_Sale.Dates);
___________________________
this is demo to illustrate real problem

I think Mr MsLim's comments to make a formula field may work.
0
 
MsLimCommented:
Yes , My way is working BUT look like very funny
based on your report reqirement
some things in my mind.
a) are you printing a list of all record in a period of date ? or
b) are you printing based on unique of date ?
if a is yes , then if duplicate of quantity do not count , how the report can show the right sum of total .
if b is yes , then you must print out the sumof sale and purchase on such date , do not to show detail , BUT one more thing is if still having duplicate , and do not show up how should the report show right figure of sum ?
Normally , people using duplicate is as below
date , prod id , sale_qty , pur_qty
if the date is same BUT prod ID is different , then date will be invisble so that you will not see too much things.
0
 
kirshanAuthor Commented:
A is yes.

 I want to print all transition of a product in

Product name(id) (group field)
date | saleQTY| PurchaseQTY

and data was as
for dale table
date    | product_ID | QTY
5-5-03 |1               | 50
5-5-03|1                | 20

for Purchase table
date | Product_Id | QTY
5-5-03 | 1           |25
6-5-03| 1            |15

the rusults looks like
Prodct 1
______________________________
date      |Sale qty | Purchase QTY
5-5-03 |  50        | 25
5-5-03 |  20        | 25 (duplicate)
6-5-03 |  15        | Nill or 0
               
0
 
MsLimCommented:
My way can be working in the sense that no duplicate purchase qty on the same date , if there is same qty on the same date but under different PO , then this will will causing wrong figure of sum.
if this is to match the date , and sales is from which po qty , then it will be work , but is better to put in sales invoice and PO Number, so that easy to understand and trace.
for my suggestion , this report is very dangerous , due to if happenning same sales qty but under different sales invoice , also will causing incorrect figure to sum up sales qty.
How about is it possible happenning of sales qty from 2 po qty ?
all the above must consider for doing report also
0
 
kirshanAuthor Commented:
thanks msLim
for u r help
0
 
MsLimCommented:
Thanks
Glad I can help you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.