We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

URGENT! Crystal Report Suppressed Data removed from Summary Value

maidstone
maidstone asked
on
Medium Priority
555 Views
Last Modified: 2012-08-13
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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hi maidstone,

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

Author

Commented:
Thats what I figured, but how do you determine that a record has been suppressed using a formula?
CERTIFIED EXPERT

Commented:
Why do you need to know? - Running Totals work on printed records only.

Pete
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
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!





MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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
CERTIFIED EXPERT

Commented:
You need to explain how you are suppressing, then.
Is this using a conditional formula?  (If so what is it?)

Pete

Author

Commented:
There can be more than one duplicate item for a PO line.

I'm just suppressing the Ledger amount if it's a duplicate.
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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?

MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
A better guess would be Ledger Line....
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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?
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
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 --
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
Glad you got it worked out.

MikeV
Commented:
PAQd, 500 points refunded.

DarthMod
CS Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.