Sorting problem based on groups 1

[triad66]

Am using Crystal 10 and MS SQL, linking two tables: invoice master table and line items table. Each invoice can have one or many line items.

I created an invoice form amd am trying to sort invoices by a combination of line items contained within each invoice. There is no field within the tables which can be used to sort, so I created a formula to sort the line items by. I have two groups: group 1 is the invoice number.
group 2 is a the formula which is based on line item combinations.

The problem I'm having is trying to supress the entire invoice (group1) based on the line items grouped in group 2.
I'm hitting a wall, any help is much appreciated!

 

[synapsevampire]

Since you didn't share the formula used for the GROUP 2, nor what the criteria is for suppression, it's hard to help you.

Try supplying technical information:

Example data
Expected output

-k

 

[triad66]

Here is the formula:
if {invoice.ITEMNMBR} = '600-6003'
or {invoice.ITEMNMBR} = '200-6000'
or {invoice.ITEMNMBR} = '600-5007'
or {invoice.ITEMNMBR} = '600-8500'
or {invoice.ITEMNMBR} = '200-3100'
or {invoice.ITEMNMBR} = '200-6000'
or {invoice.ITEMNMBR} = '600-5002'
then 'A'
else 'B'

The formula works within the group 2. I can see the items on the invoices grouped correctly. The results I'm after are to suppress the printing of the invoices if:
there are any group B items on the invoice.

I've tried conditionally suppressing the report sections based on whether the group B subtotal exists or is zero. What happens is the group B items are suppressed, but the invoice prints with the group A items only instead of supressing the entire invoice.

 

[lbass]

Create another formula {@B}:

if {@yourformula} = "B" then 1 else 0

Then use the following for suppression:

sum({@B},{table.group}) = 0

...where {table.group} is the group you want to suppress.

-LB

 

[triad66]

I tried the formula, lbass, and it does suppress the group B items on the invoice. But what I'm trying to do is suppress the invoice completely IF it contains group B items, with the result of invoices which only contain group A items.

 

[synapsevampire]

Go into the Report->Edit Selection Formula->Record and use something like:

{invoice.ITEMNMBR} = '600-6003'
or {invoice.ITEMNMBR} = '200-6000'
or {invoice.ITEMNMBR} = '600-5007'
or {invoice.ITEMNMBR} = '600-8500'
or {invoice.ITEMNMBR} = '200-3100'
or {invoice.ITEMNMBR} = '200-6000'
or {invoice.ITEMNMBR} = '600-5002'

This will now only pull rows into the report which are of type A, so no suppression is required.

-k

 

[lbass]

Sorry, I had the sign reversed. It should be:

sum({@B},{table.InvoiceID}) <> 0

...or whatever your invoice group field is. You need to place this formula in the suppression area of all group and detail sections.

Or, you could do a group selection formula INSTEAD of using suppression. Go to report->edit selection formula->GROUP and enter:

sum({@B},{table.InvoiceID}) = 0

-LB

 

[triad66]

That won't work for me, sv, because it prints the invoices which have 'b' items, only suppresses the 'b' items, returning the wrong invoice total.

Sorry about the weak explanation so far. Let me try another example. Say I have three invoices currently on the report, and they sort by invoice number(group 1) as follows:

INVOICE # 10001
A
600-6003
200-6000
B
999-1000
800-4488

INVOICE # 10002
A
600-8500
200-6000

INVOICE # 10003
B
700-1010
800-4999

I would like to not print any invoices which contain any items but 'A' items, so my results are:

INVOICE # 10002

This would be great, or if I could sort so that those invoices with only 'A' items sort first, followed by 'B' items, then 'A and B' with the following results:

INVOICE # 10002
INVOICE # 10003
INVOICE # 10001

Each of these invoices need to show all the line items, so I can't suppress the 'B' records from the report or the details won't be correct for those invoices with both 'A and B' items.

 

[lbass]

Jim,

Did you see my suggestion? I think the group selection option is the best. Then use running totals to get the summaries, since non-group selected records would contribute to regular summaries. You do not have to add any special formula to the evaluation area for the running totals to be correct.

-LB

 

[triad66]

I tried it, lbass, and it returns only the 'A' recordset. This works when there are only 'A' records on the invoice. But my problem is gives me an incorrect invoice when there are 'B' items on the invoice, since it removes those items.

I need to just show invoices with 'A' items only, or be able to sort the invoices by group 2, with the sort order being 'A' invoices, 'B' invoices, and then 'A and B' invoices. The primary sort being group1 'Invoice Number' prevents me from being able to do this.

 

[lbass]

Now I AM confused, since you earlier said you only wanted to print those invoices that ONLY had A items. My suggestion does NOT remove B items from an invoice, it selects only those invoices containing ONLY A items--so there would BE no B items on the invoices shown.

-LB

 

[triad66]

Your not confused, I may have applied your suggestion incorrectly. I'm going to try it again.

 

[lbass]

If you want to show all invoices and sort in A,B,AB order, then you could insert a group on {@yoursortformula}. Then right click on {@yoursortformula} in the details section and insert a distinctcount at the invoice group level. Insert a second summary, a maximum, also at the invoice group level. Then go to report->topN/Group Sort and choose:
"distinctcount of {@yoursortformula}" and then "maximum of {@yoursortformula}, both in ascending order.

This would be an alternative to using suppression or group selection. It seems as though you are considering two different displays--one with only the groups having only A records, and the other with all groups, but in the A,B,AB order. My earlier suggestion would work for the first display.

-LB

 

[triad66]

Thanks, lbass, the suggestion worked great!