Eliminate duplicates

[Guest_imported]

Hello,

I'm using CR8 and I want to eliminate records from displaying in the subreport if they are in the main report.

I am using the following formula in the subreport:

not onfirstrecord and {table.field} = previous ({table.field});

It doesn't appear the reports are looking at each other.

Is this possible?
Hope you can help me.

 

[synapsevampire]

Your need is a bit vague for me, but I would think that you might best resolve this in the SQL within the subreport. Select using the reverse of what is in the main report?

Subreports can be joined to the main report, but if they are, that means that they have a link, which you don't appear to want.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ccmpbel]

My problem is if the account appears in the main report I don't want it again in the subreport.

 

[Ngolem]

That is a tough chore, ccmpbel, you are going to have to give us more information.

How is the subreport linked to the main report? Where is the subreport placed in the main report? Is it in a footer to catch accounts that were missed??

If it is in the footer....then you can store the account numbers that are processed in the main report, in a shared array....this is then passed to the subreport....now you cannot use this array in the record select of the subreport but you can use it to suppress detail lines of the subreport with a formula in the conditional suppress of the section where the data is displayed.....something like

WhilePrintingRecords;
shared stringvar array accountNumber;

{subrptTable.AccountNumber} in accountNumber

that would probably work.

Give us more detail...if it isn't as I described...but it is certainly doable within Crystal. JimBroadbent@Hotmail.com

 

[ccmpbel]

The subreport is not linked to the main report? I have placed the subreport it in the footer.

I am trying to us a shared variable. Maybe I am not placing the variables in the right locations on the reports.

The data that is displaying on the main report is in GF3.

And I am trying to conditional suppress the data in the subreport with this formula.

WhilePrintingRecords;
shared stringvar array AcctID;
{CACCOUNT.PACCID} in AcctID;

Thanks Carol

 

[Ngolem]

ok...what is main report GF3 based on ...what field? the account ID field for the main report??

layout the structure of your main report...ie: name the values for the groups and show which headers/footers are suppressed and if the detail section is supressed...then I can get a picture of what you are doing.

Also...show the main report formula and position in the report for the initialization of the "used" account number shared array as well as the formula that actually collects the values.
Jim Broadbent

 

[synapsevampire]

From what I've gathered, my original thought holds providing the subreport is displayed at the end of the main report.

The subreport has a record selection which eliminates those that are in the main report, in other words, uses the opposite SQL.

If you are showing subreport data based on data in the main report, then link the reports accordingly, and suppress the subreport for whatever condition suits you.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

that would be ok...if you were using the same tables in both reports but I don't think that is valid if the tables are different. For example: I am looking for all ID's that satisfy a primary objective then looking in another senario for all ID's that met a secondary objective but were not a part of the first grouping.

Under your proposal...I don't think that ID's that failed the first test would have a crack at the second test. Jim Broadbent

 

[synapsevampire]

Ngolem: there are different tables? I didn't understand that to be the case.

If so, then perhaps they can add the appropriate table to the subreport so that they can eliminate them?

<chuckling>

This sounds weirder all the time...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

not really...I have run into this situation often in purchasing type reports.

Often you think that a left outter join would solve your problem but it doesn't since you must have filtering criteria in the outter joined table that kills the outter join and makes it an eqaul join....doing it as a subreport gets around those problems.

if ccmpbel gets back to us with the structure of his report I am confident my approach would work. Jim Broadbent

 

[synapsevampire]

I didn't realize that a filtering criteria could modify a join type, I've never seen that happen, do you have an example where the join type gets altered in the SQL?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

It happens in Crystal in this case


Table 1 Table 2
======= LOJ =======
field1 =======> Field1
field2 Field2
field3
field4

Where Field2 = somevalue

in crystal you can make your record selection formula like this

isnull({Table2.Field2}) or {Table2.Field2} = somevalue


the trouble is that the outter join fails when
{Table2.Field2} = some-other-value

it is fine if there was never-ever an entry for this field or if the field equals a certain value but IF at some time in the history of the table there occured a value for the field the join breaks down.

so making use of subreports gets around this problem.
Jim Broadbent

 

[synapsevampire]

You expect a Where clause to contain this:

isnull({Table2.Field2}) or {Table2.Field2} = somevalue

And expect the return set to have some of these?:

the trouble is that the outter join fails when
{Table2.Field2} = some-other-value

The Where explicitly stated that it must be null OR equal something explicitly, nothing else should be returned.

If you have multiple {Table2.Field2} values that are null or match the Where clause criteria, they will return, so the LO join isn't failing.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

with Crystal...if there are no other possibilities other than equal to or null then all is fine the join works as expected....the wheels fall off when there are other possiblities....that is why the &quot;Subreport approach&quot; is the only solution to this problem.

You can debate the merits...should've...expect...all you want...this is the simple fact of life when working with Crystal....

This is off topic anyway... Jim Broadbent

 

[synapsevampire]

What you intend to display may be a brilliant means to resolve some other concern, I don't know, but it isn't related to a join being altered or failing.

Whenever I read of such a thing, alarms go off because occasionally contracts require that I use Crystal for creating SQL (as opposed to SP's, Views), and if there's a bug somewhere, I need it well documented.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com