kirshan
asked on
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
5
2
3
5 (invisible)
_________
total 15
but I need 10
regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.
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
FROM (Alldates LEFT JOIN table_Purchase ON (Alldates.Product_ID = table_Purchase.Priduct_ID)
__________________________
this is demo to illustrate real problem
I think Mr MsLim's comments to make a formula field may work.
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.
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.
ASKER
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
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
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
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
ASKER
thanks msLim
for u r help
for u r help
Thanks
Glad I can help you
Glad I can help you
One way to solve this may be to SELET DISTINCT records.
mlmcc