How do I write a report showing difference between 2 sets of data?

[MLZ]

Hi,

I'm fairly new to CR 8.5...Need to write a report that shows the difference between 2 sets of data:

set 1 : data from yesterday
set 2 : data from today

The fields from these 2 sets are the same. I need to show the records that were in set 2 but not in set 1 and vice-versa.

Would a subreport help in this case ?
Any suggestion is appreciated,
mlz

 

[lbass]

I think you could do this by selecting both dates in your record select statement and then grouping on a unique ID field and then going to report->edit selection formula->GROUP and entering:

count({table.ID},{table.ID}) = 1

This would only return IDs with one instance during the two day time period.

-LB

 

[MLZ]

Hi lbass,

Thanks for your reply. Tried your suggestion but I get an error "Summary/running total field could not be created.".
Maybe I've missed a step. Please advise.

I also want to add to my problem description. I need to display the actual fields in the records that are the different between the 2 sets (or 2 tables). In SQL, I would have used "MINUS". In Crystal Reports, what is the equivalent of "MINUS" ? Please advise.

Thanks,
mlz

 

[lbass]

If these fields are actually from two different tables, I would try something like this. First, do a left join from {table1.ID} to {table2.ID}, and group on {table1.ID}. Then lay out your table1 fields in detail_a, and your table2 fields in detail_b. For each table2 field in detail_b, go to format field->common->suppress->x+2 and enter:

{table1.field} = {table2.field}

Then go to format section->edit selection formula->GROUP and enter:

not({table1.ID} = {table2.ID} and
{table1.field1} = {table2.field1} and
{table1.field2} = {table2.field2} and
{table1.field3} = {table2.field3}) //etc., until all fields are included in the "not" statement

This should eliminate groups with all fields equal.

Then you would need to repeat this in a subreport in order to capture those records in table2 but not in table1. In the subreport, you would do a left join from table2 to table1, group on {table2.ID}, and place the table2 details in the detail section. For this one, in order to eliminate records already picked up in the main report, you'd want to add the following at the beginning of your record select statement:

isnull({table1.ID})

Disclaimer--I can't really test this and there might be a better way.

-LB