Duplicate record needed for accurate dollar amount 1

[jaguar00]

I've been trouble-shooting for 3 days and now I'm throwing in the towel. But not before posting to "Crystal Gods"!

I use CR10 and my report includes several fields where I need to use the Select Distinct Records option but I have a value field that I'm summarizing. The problem is that if there is a duplicate value within the items being summed, it's only summing it once instead of each time.

Example:

I'm grouping on {tbl_Quotes.QuoteKey} and within each distinct quote there may be several items with the same value in the {Sum of tbl_QuoteLineItems.NetPrice} but because I've used the Select Distinct Records option, my dollar amounts aren't correct.

HELP!!!!!

 

[synapsevampire]

If you perform a Database->Select Distinct Records, you're not getting the duplicate values into the report at all, that's why you won't get those summed.

Rather than lengthy text descriptions of your environment, consider posting technical information:

Database/connectivity used
Example data
Expected output

Don't show what doesn't work, or what you think might work, just give an example of what's in the table(s) involved, and what you expect to be output.


I'd guess that you're getting some record inflation as a result of the joins, but it's impossible to tell without knowing something about the data and intent.

-k

 

[lbass]

How do you know that values are incorrectly being eliminated? There must be an associated ID field of some sort that allows you to distinguish duplicated values from values that should be included in the summary. If you add the ID field to the canvas (even if you suppress it), it will make the record distinct and the correct values should contribute to the summary.

-LB

 

[jaguar00]

Thanks lbass! I knew the values were being eliminated by checking each record for the data being calculated. Once I found it, I noticed it had 2 entries for a dollar value of $90.00 but the report only calculated one of them.

I added the ID field as you suggested and it worked. Thanks so much! I'm still kinda new to all this reporting stuff so every day is a learning experience.

Thanks again for all your help!

jaguar00