maidstone
asked on
URGENT! Crystal Report Suppressed Data removed from Summary Value
I have a report that I wish to do a summary on the values, but it's summing ALL values, including those which have been suppressed because of duplication.
Is there any way to remove these suppressed values from the summary total?
Is there any way to remove these suppressed values from the summary total?
ASKER
Thats what I figured, but how do you determine that a record has been suppressed using a formula?
Why do you need to know? - Running Totals work on printed records only.
Pete
Pete
I think we need more info first please.
When you say summary, what kind of summaries are these? Please give us a sample of your data...in the format of the report...so that we can see what you are up against.
Actually, I would recommend using a VARIABLE to handle the summaries... but I'm just going by what I see so far in your comments.
Thanks
MikeV
When you say summary, what kind of summaries are these? Please give us a sample of your data...in the format of the report...so that we can see what you are up against.
Actually, I would recommend using a VARIABLE to handle the summaries... but I'm just going by what I see so far in your comments.
Thanks
MikeV
ASKER
Running total's brought in the records that were supressed.
Example:
PURCHASE ORDER NUMBER: ####### PURCHASE ORDER DATE: #######
PURCHASE ORDER TOTAL: ######
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
PO LINE ENTRY 1 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 2 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 3 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 4 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
__________________________ __________ __________ __________ __________ __________ __________ __________ _________
LEDGER TOTAL: {LEDGER AMOUNT}
The last two ledger amounts are actually duplicates of each other, but there are different {DESCRIPTIONS} {PO LINE #'S}{LINE DESCRIPTIONS} AND {$ AMOUNTS}. The ledger was updated with a TOTAL of the two PO lines.
I have the duplicate ledger information supressed, but it still creeps up in any totals. Unless I do a manual running total. But then that value can only be displayed in the details.
HELP!
Example:
PURCHASE ORDER NUMBER: ####### PURCHASE ORDER DATE: #######
PURCHASE ORDER TOTAL: ######
--------------------------
PO LINE ENTRY 1 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 2 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 3 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 4 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
__________________________
LEDGER TOTAL: {LEDGER AMOUNT}
The last two ledger amounts are actually duplicates of each other, but there are different {DESCRIPTIONS} {PO LINE #'S}{LINE DESCRIPTIONS} AND {$ AMOUNTS}. The ledger was updated with a TOTAL of the two PO lines.
I have the duplicate ledger information supressed, but it still creeps up in any totals. Unless I do a manual running total. But then that value can only be displayed in the details.
HELP!
Are you...or CAN you GROUP on the PO LINE # to achieve the total correctly or can there be MORE than ONE duplicated item for any given PO LINE #?
MikeV
MikeV
You need to explain how you are suppressing, then.
Is this using a conditional formula? (If so what is it?)
Pete
Is this using a conditional formula? (If so what is it?)
Pete
ASKER
There can be more than one duplicate item for a PO line.
I'm just suppressing the Ledger amount if it's a duplicate.
I'm just suppressing the Ledger amount if it's a duplicate.
yeah that won't work.
So what is the LINKING item here? Is it PO LINE #? What data element will group ALL of these amounts together so that we can pick and choose and summarize...them correctly?
So what is the LINKING item here? Is it PO LINE #? What data element will group ALL of these amounts together so that we can pick and choose and summarize...them correctly?
A better guess would be Ledger Line....
Can you revisit your example above and confirm what you said -
You have something like this (ignoring other fields)
PO LINE ENTRY Amount Displayed amount
1 100 100
2 200 200
3 300 300??? or blank
4 300 blank
I don't understand why you only have 2 items added to the total.
Pete
You have something like this (ignoring other fields)
PO LINE ENTRY Amount Displayed amount
1 100 100
2 200 200
3 300 300??? or blank
4 300 blank
I don't understand why you only have 2 items added to the total.
Pete
ASKER
there are two lines in the po entry table, but only one in the gl table (the GL entry was made as a combination of receiving the two lines into the system).
so the Displayed amount would be 600 (300+300) rather than 300.
Make sence?
so the Displayed amount would be 600 (300+300) rather than 300.
Make sence?
So then...create a GROUP on the linking data element...which I'm still not clear about..but I think it would be the PO LINE# or the LEDGER LINE.
Whichever is the main link for both tables, ...create a group on that element.
THEN use the PO Line AMOUNT instead of the LEDGER line amount. I would summarize your PO LINE amount in the normal way as this appears to have the correct amount that can be subtotaled.
MikeV
Whichever is the main link for both tables, ...create a group on that element.
THEN use the PO Line AMOUNT instead of the LEDGER line amount. I would summarize your PO LINE amount in the normal way as this appears to have the correct amount that can be subtotaled.
MikeV
ASKER
I was able to accomplish what I was looking for by using a Sub Report:
-- HEADER --
************************** ********** ********** ********** ***
PURCHASE ORDER NUMBER: ####### PURCHASE ORDER DATE: #######
PURCHASE ORDER TOTAL: ######
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
************************** ********** ********** ********** ***
-- END HEADER --
-- DETAILS --
************************** ********** ********** ********** ***
** SUB REPORT **
////////////////////////// ////////// ////////// ////////// ////////// ////////// ////////// ////////
PO LINE ENTRY 1 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 2 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 3 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 4 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
////////////////////////// ////////// ////////// ////////// ////////// ////////// ////////// ////////
** END SUB REPORT **
************************** ********** ********** ********** **
-- END DETAILS --
-- FOOTER --
************************** ********** ********** ********** **
__________________________ __________ __________ __________ __________ __________ __________ __________ _________
LEDGER TOTAL: {LEDGER AMOUNT}
************************** ********** ********** ********** **
-- END FOOTER --
-- HEADER --
**************************
PURCHASE ORDER NUMBER: ####### PURCHASE ORDER DATE: #######
PURCHASE ORDER TOTAL: ######
--------------------------
**************************
-- END HEADER --
-- DETAILS --
**************************
** SUB REPORT **
//////////////////////////
PO LINE ENTRY 1 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 2 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 3 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
PO LINE ENTRY 4 -- {DESCRIPTION} {PO LINE #} {LINE DESCRIPTION} {$ AMOUNT} -- {LEDGER LINE} {LEDGER AMOUNT}
//////////////////////////
** END SUB REPORT **
**************************
-- END DETAILS --
-- FOOTER --
**************************
__________________________
LEDGER TOTAL: {LEDGER AMOUNT}
**************************
-- END FOOTER --
Glad you got it worked out.
MikeV
MikeV
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to create a running total field to do your summing.
Suppressing data always has the effect you are seeing and running totals is the standard workaround.
Pete