Passing a Parameter as an Exception

[spelhank]

Hello all,

Here is a challenge. I am working with Crystal Reports 8.5 trying create a report that uses two different tables of data related to the same subject, but not identical. The two tables can be linked by the common field &quot;Budget Code&quot; to the subreport, however, I want the subreport to only show those items that are NOT in the main report. I have tried changing the Record Selection formula to <> or &quot;not equal to&quot;, but that doesn't seem to work.

Here is an example:

Main Table:

Budget Code Amount

001 $3,000
002 $2,000

SubReport:

Budget Code Amount

001 $3,000

I don't want the budget code 001 to display in the Subreport. I have tried passing the values as a shared variable, but because the two tables are not equal, the detail in the main table displays as a summary in the subreport.

Thanks for any suggestions you can give.

spelhank

 

[synapsevampire]

What does &quot;I want the subreport to only show those items that are NOT in the main report.&quot; mean?

The solution is likely Crystal dependent, and possibly database, consider posting technical information when requesting it.

And &quot;data related to the same subject, but not identical&quot; is an oxymoron, it's one or the other, hence the term realtional data.

If you're linking by the budget code, you can check for the amount and exclude that data if that's the intent, but I suspect that what you want differs.

With the example demonstrated you might pass a shared variable from the main to allow for only displaying rows which are not the same value by using a suppression formula.

-k

 

[Madawc]

I'm not sure I understand spelhank, but I assume he uses table A in the main report, and wants the subreport to show items that are in table B but not table A.

On this assumption, and within Crystal 8.5, the subreport should have a left-outer join from table B to table A. Then for each detail line, suppress
not isnull(tableA.accno)

There may be a better way, using database links, but this will work.

Madawc Williams
East Anglia, Great Britain

 

[IdoMillet]

I don't see a need for any type of join in this case.

The linked subreport can use Table B and simply get the code from the main report as a link. Turn OFF the default linking mechanism (using &quot;=&quot and instead go into the record selection formula in the subreport and enter a &quot;<>&quot; condition.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[spelhank]

Thanks for all the input. I discovered my mistake. I was trying to link the two tables in the subreport by a common field, and NOT the primary keys.

I finally linked the two by budget code ID instead of Budget Code, then suppressed the detail line with: Budget code from table A = Budget code ID from Table B.

It was something MadawC said that got me to thinking about the field I was trying to link. Since this is a proprietary database, and I usually use .ttx files instead of tables, I do not have that much experience in linking tables. Thanks for all your help!

 

[synapsevampire]

If they both use budget code id, that IS a common field. The fact that they're keys just further qualifies them as such.

-k