Suppresing details based on running totals

[horizon2000]

Hello,

In the output bellow I need to suppress all the details based on the outcome of the running total (I think) located in GF4

GH1 XXXX
GH2 NonCash Items
GH3 MSUS_XXXX
GH4 Security ID
D TSM123 US 1.98
D TSM123 US 1.98
D TSM123 THEM 1.98
GF4 ????

Basically, 1.98 is a price.
I need to check if the price is the same for all the records in whole group G4.
There is couple of conditions:
1. I need to have at least one record from US and at least one record from THEM (if US or THEM does not exist I do not suppress)
2. If the price is 0 then I do not suppress any details in whole G4 group.

I tried to insert another group G5 to divide details into THEM and US, calculate both of them compare them and if they are the same then suppress all details from whole G4.
The problem I have is that I am using Running Total filed and it evaluates fields after each G5. If both G5s in one G4 are the same then it suppresses the second G5 and leaves the first G5 not suppressed.
Tried a number of ways (all of them revolve around running total and sum/average/std dev/variance ect) and do not see any solution for it.
Any help would be very appreciated.

Thanks,
Robert

 

[synapsevampire]

Please don't mix technical terms:

"In the output bellow I need to suppress all the details based on the outcome of the running total (I think) located in GF4"

Do you mean that you want to suppress the detail section based on a Running Total amount, or the "details", meaning the data displayed (use the proper terms) within the group footer?

Running Totals complete after the details section, so that won't work if you want to suppress the details.

You can use a formula such as:

minimum({table.field},{table.groupfield}) to determine if there is a zero (assuming zero is the smallest amount). And use minimum({table.field},{table.groupfield}) = maximum({table.field},{table.groupfield}) to determine if all values are the same in the group.

To see if you have a US and a THEM, create 2 formulas:
@US
If {table.field} = "US" then
1
else
0

@THEM
If {table.field} = "THEM" then
1
else
0

Now in the Report->Selection Formula-Group place:

(
sum({@US},{table.group}) = 0
or
sum({@them},{table.group}) = 0
)

Anyway, this should prove helpful towards the requirements.

Successful posts tend to include:

Crystal version (your IT mgmt and you don't post your software version???)
Database/connectivity
Example data (what's being returned)
Expected output

-k

 

[lbass]

Try going to report->selection formula->GROUP and entering:

distinctcount({table.usthemfield},{table.securityID}) < 2 or
sum({table.price},{table.securityID}) = 0 or
minimum({table.price},{table.securityID}) <> maximum({table.price},{table.securityID})

-LB

 

[horizon2000]

My appology for the not poperly/missing information/structured post. My excuse is that I am v. frustrated not bing able to resolve this problem.

To make it right:
The CR version is 9.2 (developer)
CR conects to Oracle DB v 9.2

Here is a sample of raw data
GH4 TSM_21866
D TSM_21866 [TAB]US[TAB][TAB]4,000.00[TAB][TAB][TAB]43.4700[TAB]173,880.00[TAB]
D TSM_21866 [TAB]US[TAB][TAB]-115,724.00[TAB][TAB]43.4700[TAB]-5,030,522.00[TAB]
D TSM_21866 [TAB]THEM[TAB]-111,724.00[TAB][TAB]43.4700[TAB]-4,856,642.28[TAB]
GF4 TSM_21866[TAB][TAB][TAB][TAB][TAB]0.00[TAB][TAB][TAB]0.00[TAB][TAB][TAB]0.28[TAB][TAB][TAB]
GH4 TSM_231
D TSM_231 200,000.00 0.2300 46,000.00
D TSM_231 200,000.00 0.2300 46,000.00
GF4 TSM_231 0.00 0.00 0.00
GH4 TSM_23486
D TSM_23486 US -78,081.00 29.7100 -2,319,787.00
GF4 TSM_23486 -78,081.00 -29.71 -2,319,787.00
GH4 TSM_21867
D TSM_21867 [TAB]US[TAB][TAB]6,000.00[TAB][TAB][TAB]3.7000[TAB]22,200.00[TAB]
D TSM_21867 [TAB]US[TAB][TAB]-4,000.00[TAB][TAB]3.7000[TAB]-14,800.00[TAB]
D TSM_21867 [TAB]THEM[TAB]-1,000.00[TAB][TAB]3.4700[TAB]-3,470.00[TAB]
GF4 TSM_21867[TAB][TAB][TAB]1000.00[TAB][TAB]-0.2300[TAB][TAB]3,930.00[TAB][TAB]

It should display the following (it should display any items that have non zero value in the GF4 area):
GH4 TSM_23486
D TSM_23486 US -78,081.00 29.7100 -2,319,787.00
GF4 TSM_23486 -78,081.00 -29.71 -2,319,787.00
GH4 TSM_21867
D TSM_21867 [TAB]US[TAB][TAB]6,000.00[TAB][TAB][TAB]3.7000[TAB]22,200.00[TAB]
D TSM_21867 [TAB]US[TAB][TAB]-4,000.00[TAB][TAB]3.7000[TAB]-14,800.00
D TSM_21867 [TAB]THEM[TAB]-1,000.00[TAB][TAB]3.4700[TAB]-3,470.00[TAB]
GF4 TSM_21867[TAB][TAB][TAB]1000.00[TAB][TAB]-0.2300[TAB][TAB]3,930.00[TAB][TAB]


Instead it displays the following:

GH4 TSM_21866
D TSM_21866 [TAB]US[TAB][TAB]4,000.00[TAB][TAB][TAB]43.4700[TAB]173,880.00[TAB]
D TSM_21866 [TAB]US[TAB][TAB]-115,724.00[TAB][TAB]43.4700[TAB]-5,030,522.00[TAB]
GF4 TSM_21866[TAB][TAB][TAB][TAB][TAB]0.00[TAB][TAB][TAB]0.00[TAB][TAB][TAB]0.28[TAB][TAB][TAB][/B]
0.00 0.00 0.00 [/B]
GH4 TSM_23486
D TSM_23486 US -78,081.00 29.7100 -2,319,787.00
GF4 TSM_23486 -78,081.00 -29.71 -2,319,787.00
GH4 TSM_21867
D TSM_21867 [TAB]US[TAB][TAB]6,000.00[TAB][TAB][TAB]3.7000[TAB]22,200.00[TAB]
D TSM_21867 [TAB]US[TAB][TAB]-4,000.00[TAB][TAB]3.7000[TAB]-14,800.00[TAB]
GF4 TSM_21867[TAB][TAB][TAB]1000.00[TAB][TAB]-0.2300[TAB][TAB]3,930.00[TAB][TAB][/B]

Thanks for the lesson. I am going to use your advise synapsevampire and let you know.

Thanks a lot,
Robert

 

[horizon2000]

Thank you guys for quick responses.

Both solutions worked.
Your help was really appreciated.

Robert