Comparing two rows and their column values 1

[sumeet22]

Is is possible to do something like follows in Crystal Report ?

ID fname lname add1 city state zip
Row 1: 1 abc def 123 st York FL 21025
Row 2: 2 abe ttt 123 st Penn FL 21025

Given the above scenario, is it possible to compare items row by row and column by column and display only the things that have changed. For example lname changed from 'def' to 'ttt', city changed from 'York' to 'Penn'.

Display a label 'lname changed from 'def' to 'ttt' on lname field, display a label 'city changed from 'York' to 'Penn'.

Can this be done in Crystal Report ?

Help !!!!

 

[lbass]

You can display only the items that have changed by formatting each item to suppress if duplicated (format->field->common->suppress if duplicated).

To create a message that highlights changes, you can set up a formula like the following:

stringvar x;
stringvar y;

if not onfirstrecord and
{table.group} = previous({table.group}) then
x := if {table.lname} <> previous({table.lname}) then
"Last name has changed from "+ previous({table.name}) + " to "+ {table.name};
y :=
if {table.city} <> previous({table.city}) then
"City has changed from "+ previous({table.city}) + " to "+ {table.city};
x + (if len(x) <> 0 then "; " else "") + y

I assumed you might have a group. If you don't, change the formula to: if not onfirstrecord then...

-LB

 

[sumeet22]

Thank you for your reply, however, please note that it works for only 2 rows. I have to process more than 2 rows.

Typical example:


Columns
-------
ID FName LName City State
Row 1: 1 abc xyz 123 NY
Row 2: 1 abc yyy 567 NY
Row 3: 1 abc ccc 567 Null
Row 4: 1 abc ccc 567 Null

Compare Row 1 and 2 which will give LName and City changed. Compare 2nd and 3rd row which will give LName and State (State was deleted). Compare 3rd and 4th which will give nothing.

How is this possible in Crystal Report formula. This is one hell of a complicated comparision.

Help !!

Thanks in advance.
Sumeet22

 

[lbass]

No, actually, this will work for all rows. Did you try it?

-LB

 

[sumeet22]

Yes I tried and it didn't seem to work for the situation I have.

Let's say in my example, I only need to show 'Last name has changed from xyz to yyy. City has changed from 123 to 567 for the first row on the detail section to the user. The second row should display 'Lname changed from 'yyy' to 'ccc' and 'State NY was delete'. The fields that are equal has to disappear including their titles (field names).

Out of those 4 records only 2 rows should be displayed. Plus the changed values.

Pls help.

 

[lbass]

First of all you should have expanded my formula to include all fields, e.g, you should have 5 variables. Since you are now indicating that fields might be null, you should change the formula to look like this (you still will need to add the other variables):

stringvar x;
stringvar y;

if not onfirstrecord and
{table.group} = previous({table.group}) then
(
x := if isnull({table.lname}) and not(previousisnull({table.lname})) then "Last name has been deleted" else
if {table.lname} <> previous({table.lname}) then
"Last name has changed from "+ previous({table.name}) + " to "+ {table.name};
y := if isnull({table.city}) and not(previousisnull({table.city})) then "City has been deleted" else
if {table.city} <> previous({table.city}) then
"City has changed from "+ previous({table.city}) + " to "+ {table.city}
);
x + (if len(x) <> 0 then "; " else "") + y

If you use the field "suppress if duplicated" you should get a display like:


ID FName LName City State
Row 1: 1 abc xyz 123 NY
Row 2: yyy 567 NY Message
Row 3: ccc Null Message

I would think you would want the first record as a baseline, but if not, you can suppress the entire line by going to the section expert->details->suppress->x+2 and entering;

{table.ID} <> previous({table.ID})

That leaves you with:


ID FName LName City State
Row 2: yyy 567 NY Message
Row 3: ccc Null Message

You can then suppress each column label with a formula like the following for the ID label:

distinctcount({table.ID},{table.group}) = 1

-LB

 

[sumeet22]

Thank you dude. Had to tweak the code a little bit but it seems to be working just fine. Thanks for the great help.
Sum

 

[sumeet22]

Here is another scenario:

Columns
-------
ID FName LName City State
Row 1: 1 abc xyz 123 NY
Row 2: 1 abc yyy 567 NY
Row 3: 2 qqq ccc 345 Null
Row 4: 2 qqq ccc 345 Null

How is it possible to compare items within different id groups ?

The first comparison between ids 1 and the second comparision between ids 2. Let's say for instance for id 2, nothing has changed, how to suppress only those two rows with ids 2 ?

Thanks.

 

[lbass]

Are you no longer suppressing the first record of an ID? Previously you only wanted to show the record that changed, while suppressing the initial record. If you are still using that method, the first record for ID 2 would be suppressed using the formula in the section expert:

{table.ID} <> previous({table.ID})

The second line would be suppressed because each field is a duplicate of the first row, and you have used field suppression (suppress if duplicated) on each field.

-LB

 

[sumeet22]

Thanks for the valuable info. Had to tweak here and there but seems to work.

Another question, After getting done developing on the following scenario:

SQL Servername: ABC
DatabaseName: XYZ

The prodcution server is as follows:

SQL Server: NNN
Database: XYZ

How to synchronize the data source on the report so it will not prompt you to type the server and database names when moved to prodcution ?

Thanks for the info.

 

[lbass]

Please start a new thread on this--I'm not sure of the answer.

-LB

 

[sumeet22]

Hi,

Columns
-------
ID FName LName City State
Row 1: 1 abc xyz 123 NY
Row 2: 1 abc yyy 567 NY
Row 3: 1 abc ccc 567 Null
Row 4: 1 abc ccc 567 Null

Given the above scenario, the idea you gave perfectly works. I am using a booleanvar called 'changed' to suppress the non changed rows. It works on the detail section and suppresses the non changed rows. However, let's say all the above record fields did not change. In this case I have to suppress the whole report. My formula :

shared booleanvar changed;
if changed=false then
true //suppress

works just fine for the detail section. However, the report header still shows up. I need to suppress the report header also. This is a sub report. There are few other sub reports and the same condition applies to all.

Please help.

Thanks in advance.

 

[lbass]

You could try the following. Create a formula that concatenates all fields {@concat}:

totext({table.ID})+{table.FName}+{table.LName}+{table.city}+{table.state}

Then for the report header section suppression formula use:

distinctcount({@concat}) = 1

-LB

 

[sumeet22]

Thanks for the response. I tried your formula but it doesn't seem to work. Regardless of anything changed or not or even when there is only one row, the header still shows up.

I am running out of ideas.

Pls hlp.

 

[lbass]

I tested this out and it works here. This assumes that the report header is the one IN the subreport. I would check to see if there are other suppression criteria that are being used in the subreport. Try adding a formula in the subreport header:

distinctcount({@concat})

And see what the result is. Also note whether it is null--you might need to change the concat formula to test each field for nulls.

-LB

 

[sumeet22]

You are right I needed to test for the nulls which I did and the formula worked for the scenario where the id is the same for all records but for the scenario below, I believe it didn't work:

Columns
-------
ID FName LName City State
Row 1: 1 abc xyz 123 NY
Row 2: 2 abc yyy 567 NY
Row 3: 2 abc ccc 567 Null
Row 4: 3 abc ccc 567 Null

It is a little complicated. How would the
distinctcount({@concat}) formula work for the above scenario as it will find distinct. I need to apply this logic within different id groups.

Is it possible to do so ?

thanks.

 

[lbass]

I don't know what you are asking. The distinctcount ({@concat}) solution was ONLY for the report header when all columns were the same across rows.

-LB

 

[jac807]

Could some one help me plz:
I want a summary report in the following format

A.
1
2
3
B.
4
5
C
D
E
In the database A,B,C,D,E, 1,2,3 belong to the same field.

Some ones help is highly appreciated.

Thanks,
Jack

 

[lbass]

Please start a new thread, since this is a different topic.

-LB