?
Solved

Suppress if duplicated makes error in total on CR 8.0

Posted on 2003-12-02
9
Medium Priority
?
465 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:kirshan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 1

Accepted Solution

by:
MsLim earned 75 total points
ID: 9863816
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 9863830
Unfortunately I believe that is the way CR was designed.

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

mlmcc
0
 
LVL 1

Expert Comment

by:MsLim
ID: 9864148
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
Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

 

Author Comment

by:kirshan
ID: 9871107
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
 
LVL 1

Expert Comment

by:MsLim
ID: 9871592
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
 

Author Comment

by:kirshan
ID: 9871660
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
 
LVL 1

Expert Comment

by:MsLim
ID: 9872404
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
 

Author Comment

by:kirshan
ID: 10109422
thanks msLim
for u r help
0
 
LVL 1

Expert Comment

by:MsLim
ID: 10109575
Thanks
Glad I can help you
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

741 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