Suppress Duplicates in Details of GroupH 3 1

[brizaybrizoke]

Searched for a previous thread but could not find one/deciper formulas.

I'm using Crystal Reports 8.5

I have three groups.
The only details are in group #3.
Group #3 = AA, BB, CC

How do I suppress duplicates in the proceeding GroupH 3 details?


GroupH 1 - 100
GroupH 2 - Red
GroupH 3 - AA
1
2
3

GroupH 3 - BB
1 (want to suppress)
3 (want to suppress)
4

GroupH 3 - CC
2 (want to suppress)
3 (want to suppress)
4 (want to suppress)


Thanks in advance!

 

[Turkbear]

Hi,
What are the source fields for the Groups? Is more thsn one table involved?

Usually the records shown within a group value are unique to that group value and not really duplicates.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

Do a running total count for each detail-line possibility for the whole report. Make it conditional, based on a formula, something like

{detail.value} = 1

In the detail line, conditionally suppress when the count is greater than 1. (If you display it, you'll find it will be one for the first occurance, 2 for the next etc.

This will mean creating a running total for each possibility, no helping that, unfortunately.

In 8.5 you can't duplicate, but you can have a dummy report and copy a formula field to it, change its name and copy it back.

It also occurs to me that possibly a Cross-tab would be useful, though it would not show what you have asked for.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

What are the actual fields you are grouping on? Is group #3 based on some field that has a sequence to it, e.g., a date? What database are you using?

-LB

 

[brizaybrizoke]

This is how I have my report grouped/sorted,

Group 1 = {ORDER STATUS} = 301 (suppressed)
Group 2 = {ORDER TYPE} (There are 8 order types)
Group 3 = {ORDER ZONE} = AA, BB, CC

Ultimately, I want a sum of the {TOTAL LINES} of the first {ZONE} that an order exists in (Group 3).
And, a sum of the first sum for all zones for each {ORDER TYPE} (Group 2).

I'm running into problems when I add the third group {ZONE}. All order details that I've previously suppressed are separated and become visible again.

Before creating Group 3 {ZONE}.
Highlighted is the only data I want to see/be summarized.

After creating Group 3.

If the first {TOTAL LINES} for an order exist in AA, then I only care about the {TOTAL LINES} in AA for said order.
If an order exists in BB and CC, then I only care about the {TOTAL LINES} in BB.
I do not want to see/have summarized the remaining {TOTAL LINES} in any proceeding zones for an order, thus, no duplicates.

Thanks in advance and please let me know if you need more information. I am self-taught and our IT department believes Crystal Reports are antiquated.

 

[lbass]

Create a SQL expression {%minZone} like this (field explorer->SQL expression):

(
select min(A.`Order Zone`)
from table A
where A.`Order Status` = table.`Order Status` and
A.`Order Type` = table.`Order Type`
)

Replace "table" with your actual table name. Change the punctuation to match that used with your database. Then add the expression to your record selection formula:

{table.Order Zone} = {%minZone}

-LB

 

[brizaybrizoke]

I've hit a road bump.

After entering,

SELECT
min(Ordzones."ORDZONES_ZONE")
FROM
Ordzones."ORDZONES_ZONE"
WHERE
Ordhead."ORDHEAD_STATUS" = Ordhead."ORDHEAD_STATUS" and Ordzones."ORDZONES_RCON_TYPE" = Ordzones."ORDZONES_RCON_TYPE"

For my SQL Expression, I receive "ODBC Incorrect syntax near the keyword 'SELECT'."

I have never created a SQL Expression before so I'm not sure what I need to adjust within this query.

 

[lbass]

You should have left the "A's" alone as it represents an alias table that creates the necessary grouping within the expression and you MUST enclose everything in parens:

(
SELECT
min(A."ORDZONES_ZONE")
FROM
Ordzones A, Ordhead B
WHERE
A."LINKING_FIELD" = B."LINKING_FIELD" AND
B."ORDHEAD_STATUS" = Ordhead."ORDHEAD_STATUS" and A."ORDZONES_RCON_TYPE" = Ordzones."ORDZONES_RCON_TYPE"
)

Replace "LINKING_FIELD" with whatever field you are using to link the two tables in your main report.

-LB

 

[brizaybrizoke]

Okay, I got the SQL Expression to take.

(
SELECT
min(A."ORDZONES_ZONE")
FROM
Ordzones A, Ordhead B
WHERE
A."ORDZONES_ORDNO" = B."ORDHEAD_ORDNO" AND
B."ORDHEAD_STATUS" = Ordhead."ORDHEAD_STATUS" and A."ORDZONES_RCON_TYPE" = Ordzones."ORDZONES_RCON_TYPE"
)

I then put it into my Select Expert.

{Ordzones.ORDZONES_ZONE} = {%minZone}

However, the report is only showing orders with {TOTAL LINES} in AA.

Is there something in the formula or Select Expert that removed the orders whose first {TOTAL LINES} existed in BB or CC?

For example, all orders un-crossed in the image above should still be visible because the order number does not exist in the previous zone.

Any ideas?

 

[lbass]

Sorry. I think it should be:

(
SELECT
min(A."ORDZONES_ZONE")
FROM
Ordzones A, Ordhead B
WHERE
A."ORDZONES_ORDNO" = B."ORDHEAD_ORDNO" AND
B."ORDHEAD_STATUS" = Ordhead."ORDHEAD_STATUS" and A."ORDZONES_RCON_TYPE" = Ordzones."ORDZONES_RCON_TYPE" and
A."ORDZONES_ORDNO" = Ordzones."ORDZONES_ORDNO"
)

-LB

 

[brizaybrizoke]

lbass, you've done it again! Thank you so much for your help as always. Now to teach myself how and why that formula works so I can use it in the future.