• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

Suppressing details

Hello,
I have report and need to suppress all rows with the same cost codes.... I thought this would be as simple at going to section expert and details.. Selecting suppression formula and entering......
{table1.CostCode1} = {table2.CostCode2}

This does not work and shows all fields...any idea.

Rich
0
RichardH1976
Asked:
RichardH1976
1 Solution
 
vastoCommented:
Do you want to show just records from table1 missing in table2 , records from table 2 missing in table 1 or records missing in one or the other table ?

For example, if you want to show the records in table1 missing in table2 the sql will be

select ....
FROM table1 left join table2 on  table1.CostCode1 = table2.CostCode2
where table2.CostCode2 is null

you need to change the link beween the tables in the database expert to LEFT JOIN and add  {table2.CostCode2} = Null to the record selection formula.
0
 
mlmccCommented:
Adding a table2 selection criteria, causes Crystal to treat the join as INNER.

How do you have the tables joined?

mlmcc
0
 
James0628Commented:
What are you really trying to see, or not see?  Without knowing more about your tables, etc., "all rows with the same cost codes" is pretty vague.

 James
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
RichardH1976Author Commented:
Hi All,

I have the following

Account (table1)    Name (table1)  Description (table1)   Cost Code (table1) Cost Code (table2)

I want to suppress all fields which have the same cost codes and just leave the ones which have different codes.

Rich
0
 
mlmccCommented:
Do you need the records for anything else in the report?
You could select as
{Table1.CostCode} <> {Table1.CostCode}

Since there are 2 tables how are they joined?

mlmcc
0
 
mlmccCommented:
Are the 2 fields the same type?

Are they perhaps padded one left and the other right?

mlmcc
0
 
RichardH1976Author Commented:
Hi Mlmcc,

The tables are linked through a few tables but works fine.  I have want to suppress the whole row where the two cost codes are the same.    The report should show mismatch cost codes so we can react on them.
0
 
RichardH1976Author Commented:
both the fields are the same data type.
0
 
RichardH1976Author Commented:
Thanks for your help this works.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now