Suppressing a section if some fileds are a duplicate 1

[helenv]

(I'm using version 8.5 of Crystal Reports)

I'm trying to suppress the details section if the record is a "duplicate", in that certain fields have the same value, but not every field will necessarily match (it's not a true duplicate record). I was using the the following formula to conditionally supress the details section:

if (Previous ({table.fileda}) = {table.fielda} and Previous ({table.fieldb}) = {table.fieldb} and Previous ({table.field2}) = {table.fieldc}
etc.

but obviously this only works if the order of the fields is such that records that match for fields a, b and c are printed next to each other. This was true originally, but now i need to sort by a field that wont match for the "duplicates" therefore the above formulae doesn't work.

Is there anyway to do this properly?
Any help is much appreciated

 

[kskid]

Just click on the Sort icon on the toolbar and add fields a, b and c and your duplicate formula will work.

Cheers,
-LW

 

[helenv]

Thanks for your reply. Sorry I didn't explain very well..

The probelm is, I need to sort by a different field on the report that isn't included in checking if it's a duplicate record. So if records are sorted in the required order by the extra field, the records with matching values for the 'duplicate' fields a, b & c do not follow each other, because the sorting value will always be different. And therefore the formula doesn't work.

So I think I need another way to identify the duplicates other then by using 'previous' which relies on the order of the records.

Thanks for any help

 

[kskid]

It would help if you provide a sample of the data records and the expected output.

-LW

 

[helenv]

Ok, for example you might have the following records:

Ref Name Location Allowance
1 a North 5
2 b South 4
1 a North 0
5 c East 16

So, you would want the report to only display:

Ref Name Location Allowance
2 b South 4
1 a North 5
1 a East 16

I have grouped by Ref, Name, Location and Allowance in order to get the sort order correct, and the Allowance column is a sum of the Allowances based on the outer 'Allowance' group. If I just group by Ref, Name, Location as I had originally, and make Allowance column show a sum of Allowance based on the outer group Location, I would get

Ref Name Location Allowance
1 a North 5
1 a North 5
2 b South 4
5 c East 16

which is fine, and I could then suppress if (previous ref = ref) and (previous name = name) and (previous location = location) which would work. But, I actually need to sort by allowance, if I do that the records appear like this:


1 a North 0
2 b South 4
1 a North 5
5 c East 16

and the formula using 'previous' doesn't work.

 

[kskid]

Just verifying your post. Is this what you have?

Group 1 - Reference
Group 2 - Name
Group 3 - Location

Question? How are you selecting allowance? is it base on a date or value? And are you wanting the minimum or maximum

Given the above groupings, then click on Select -> Formula Editor -> Group Formula and enter the following

allowance = maximum({allowance},{location})

That should pull the highest value allowance for each Reference, Name, and location

Cheers,

-LW

 

[helenv]

Almost, I had

Group 1 - Reference
Group 2 - Name
Group 3 - Location

then I wanted to sort by allowance first so I added Allowance and moved it to be group 1, so I have:

Group 1 - Allowance
Group 2 - Reference
Group 3 - Name
Group 4 - Location

The allowance is a field in a table. After grouping, the intention was to sum the allowances where group 2,3 and 4 values are the same. And the allowance column would show the sum of the allowances based on group 4. Maximum might work but im not totally sure the 2nd duplicate will be 0..its posible though..

Thanks for all your help!

 

[kskid]

Still confused.

What do you mean by 'outer allowance'? Can you provide more sample data and the outcome you want to achieve based on the data? How many allowances can I have per reference,name and location.

For example, what are the results of the following

Ref Name Location Allowance
1 a North 5
2 b South 4
1 a North 0
5 c East 16
1 a North 20
6 a North 5
1 a North 15

Cheers,
-LW

 

[synapsevampire]

This all loooks liek the long way there to me.

If you have 3 fields which you use to identify the unique rows you wish to display, create a formula which concatenates those fields into one, and then group on that formula.

Now place the data in the group header or footer based on whether you want the top or lowest allowance, and make sure that you sort on the allowance.

The formula might look like:

{table.ref}&{table.name}&{table.location}

-k

 

[helenv]

I'm quite confused now! I tried to simply the probelm as it'd take too long to describe the whole report, I don't think the above idea would work (but thanks!). I think I want to know if it's possible to supress records, within a group, where some values match. So a formula to put in the conditional supress for the details secton that doesn't rely on the order of the records within that group (like using my original 'previous' one does). Is this any clearer?

..In answer to the above, there could be any number of allowances per ref, name and location. So for your example where you had:

Ref Name Location Allowance
1 a North 5
2 b South 4
1 a North 0
5 c East 16
1 a North 20
6 a North 5
1 a North 15

I'd want to show:

2 b South 4
6 a North 5
5 c East 16
1 a North 40

Where the allowances for the same ref, name, location are summed, and then the duplicates are suppressed. So without the suppression (and the ordering) it would look like this:

1 a North 40
1 a North 40
1 a North 40
1 a North 40
2 b South 4
6 a North 5
5 c East 16

because the allowance column shows the sum of the allowances. And i want to suppress those that are duplicates. BUT, I can't group on the sum formaula, so I group on the allowance field from the db table, which means it displays the records in order by their own allowance..

1 a North 40 (real allowance 0)
2 b South 4 (real allowance 4)
6 a North 5 (real allowance 5)
1 a North 40 (real allowance 5)
1 a North 40 (real allowance 15)
5 c East 16 (real allowance 16)
1 a North 40 (real allowance 20)

and using the 'previous' formula to identify the duplicates would still leave:

1 a North 40 (real allowance 0)
2 b South 4 (real allowance 4)
6 a North 5 (real allowance 5)
1 a North 40 (real allowance 5)
5 c East 16 (real allowance 16)
1 a North 40 (real allowance 20)

Getting rid of that one record in the middle (possibly).Any clearer? Thanks for all your help everyone. I probably being very stupid..






and so previous won't suppress any.

 

[lbass]

SV's solution should work--did you try it?

Concatenate the three fields, group on the concatenation formula, insert a sum on your allowance field, and then drag your fields to the group footer and suppress the detail section. Then go to topN/group sort and choose "sum of {table.allowance}", ascending order.

-LB

 

[kskid]

I I understand the post, you basically want a sum of all unique allowances within a given reference, name, and location, right?

Assuming all fields are string fields, use snapsevampire's formula to create a groupname

//@RefLocation
{table.ref}&{table.name}&{table.location}

Group on @RefLocation
Sort on allowance

The results would be

Group Header Header Section
1aNorth
Detail Section
1 a North 0
1 a North 5
1 a North 5
1 a North 15
1 a North 20

Group Header Header Section
2bSouth
Detail Section
2 b South 4

Group Header Header Section
5cEast
Detail Section
5 c East 16

Group Header Header Section
6aNorth
Detail Section
6 a North 5

Now you should be able to create running totals using the previous formula and reset at each group

Will result in

Group Header Header Section
1aNorth
Detail Section
1 a North 0
1 a North 5
1 a North 5
1 a North 15
1 a North 20
Group Footer
40

Group Header Header Section
2bSouth
Detail Section
2 b South 4
Group Footer
4

Group Header Header Section
5cEast
Detail Section
5 c East 16
Group Footer
16

Group Header Header Section
6aNorth
Detail Section
6 a North 5
Group Footer
5

Note. If using previous, don't forget to account for "onfirstrecord"



Cheers,
-LW

 

[helenv]

Thanks, that's really well explained.

I don't really like the idea of concatinating the three fields though - I'm not sure why but it makes me a bit uneasy. I'll think about a bit more..

For example, if you want separate headings in the page header for each of the three concatinated objects, how do you make them line up with their respective detail?

Thanks for your help

 

[kskid]

You don't have to use the concatenated group name.

Just delete or suppress the group name from the report canvas and replace it with {table.ref}, {table.name} and {table.location}.

Cheers,
-LW

 

[helenv]

Of course - that's got it I think

Cheers all

 

[helenv]

No wait! That still doen't work, as I want the sort to be by allwoance primarily, but if you group by the formula that concatenates ref, name and location, you're forced to sort by this group before allowance, so instead of :

2bSouth 4
6aNorth 5
5cEast 16
1aNorth 40

which I want, you get

1aNorth 40
2bSouth 4
5cEast 16
6aNorth 5

which I don't want!

I do want to display the sum of all unique allowances within a given reference, name, and location - but i want to then have them in ascending order by sum of allowance.

But thanks for the dedicated support today!..

 

[kskid]

Can't you use the Report->Top N to get what you need?

-LW

 

[helenv]

That's very clever!

Thanks again

hv