Restrict group results based on details in group

[petejigsaw]

I am trying to design a report to summarise orders.

It has a 'group by' field to summarise the order, its value, customer details etc.

However, I need some way to suppress this 'group by' result where the details within the group for a given order don't include certain product lines.

i.e.

Customer 1 - 4 detail lines, none of which are relevant for the report, so the 'group' result for this customer needs to be surpressed.

Customer 2 - 5 detail lines, one of which is relevant, so the 'group' result for this customer needs to show as normal.

I'm guessing I need some form of conditional suppression, but this isn't my strong point!

Any help gratefully received.

Thanks

 

[petejigsaw]

Just to clarify, here's some sample data:

How it is at present:

Detail 1 Order 123456 Customer Smith Product 1
Detail 2 Order 123456 Customer Smith Product 2
Detail 3 Order 123456 Customer Smith Product 3

Group Order 123456 Customer Smith

Detail 1 Order 123457 Customer Wills Product 1
Detail 2 Order 123457 Customer Wills Product 2

Group Order 123457 Customer Wills


I need to find a way to set the report to exclude Group Results where 'Product 3' does not exist within all the detail field on the order, and show Group Results for all Orders where it does exist, with every detail line showing.

i.e.

Detail 1 Order 123456 Customer Smith Product 1
Detail 2 Order 123456 Customer Smith Product 2
Detail 3 Order 123456 Customer Smith Product 3

Group Order 123456 Customer Smith

 

[lbass]

I guess Order is the groupfield? You should always identify the field you are grouping on.

First create a formula {@Prod3}:

if {table.product} = "Product 3" then 1

Then go to report->selection formula->GROUP and enter:

sum({@Prod3},{table.order}) > 0

If you then need to do any calculations across groups, you should use running totals, since non-group selected records will contribute to the more usual inserted summaries.

-LB

 

[petejigsaw]

Thanks LB

That works well and with grouping on {table.order} gives me the output I want.

However, I now need to be able to display the details of each 'Product 3' that occurs on an order (it can occur more than once) in the order group footer.

Any ideas on how to repeat the detail in the footer for those detail lines where the

if {table.product} = "Product 3" then 1

is true?

Many thanks

 

[lbass]

What details? What would the group footer "details" look like?

-LB

 

[petejigsaw]

Sorry LB

Re the example above:

Detail 1 Order 123456 Customer Smith Product 1
Detail 2 Order 123456 Customer Smith Product 2
Detail 3 Order 123456 Customer Smith Product 3

Group Order 123456 Customer Smith Product 3

Essentially, the group footer needs to contain the summary values (order no, name etc) but also the details of the {table.product} for Product 3 (the product that the formula marks with 1).

Hope that clarifies things?

Thanks

 

[lbass]

Except you said there could be more than one Product 3 with details in a group--so then what would you want to see?

If you have many records with Product 3 per group, you might want to consider inserting a subreport in the group footer that is linked on the group field and which limits all records to Product #3 but otherwise matches your original selection criteria. This is probably the simplest way to go about this.

-LB