records comparison

[analytics2006]

Hi, I am pretty new to crystal report and I having difficulities getting started. Please see the table listed below.
I have a table contains all the historical data and I need to create a report which will show when the "type" column was modified each time between the most recent "date" and the previous date (4/05/07 and 4/13/07). For example, Type was changed from "NAN" to "NSN" between 4/05/07 and 4/13/07. I am thinking I can use subquery to produce this report but I am not sure how to start this. Does anybody know what are the best way to do this in crystal?? I am connecting to a oracle database and using crystal report version 10.

Any help will be greatly appreciated.

thanks

acct# inv# type date

ZX512 a255 CNN 11/16/2006
ZX512 a255 NSN 1/4/2007
ZX512 a255 NAN 4/5/2007
ZX512 a255 NSN 4/13/2007

 

[lbass]

Please do not crosspost--you now have the same thread in three different forums.

-LB

 

[analytics2006]

lbass
I just joined yesterday and I am very sorry, I won't do that next time. Do you have any suggestions on how I can do this report?? thanks always

 

[samilam]

Well, to get the most recent date, you can create a summary, maximum field. That would give you the most recent date. I am not sure about the one record before that though...

 

[analytics2006]

thanks for the suggestions. I have tried using max but couldn't get the results i want. I have diffculites working on this report and can't think of any ways to do this.

 

[lbass]

First insert a group on acct and on inv#. To display only those two most recent records where the type is different, go to report->selection formula->GROUP and enter:

{table.date} in [nthlargest(1,{table.date},{table.inv#}), nthlargest(2,{table.date},{table.inv#})]

Then go to the section expert->details->suppress->x+2 and enter:

(
{table.date} in [nthlargest(1,{table.date},{table.inv#}) and
{table.type} = previous({table.type})
) or
(
{table.date} in [nthlargest(2,{table.date},{table.inv#}) and
{table.type} = next({table.type})
)

Your fields need to be sorted by date ascending.

-LB

 

[analytics2006]

Thanks so much for your help. The formula works and I did look up the "nthlargest" in crystal report help. This is very useful. However, it is outputing the following(current output). How can I make it to output like "output #1". Thanks again for your help.

current output
current type prev_type date
nsn nsn 4/5/07
nsn nan 4/13/07

Output #1

curr_type recent_date prev_type prev_date
nsn 4/13/2007 nan 4/5/2007

 

[lbass]

You should lay out your full requirements in your initial post. This requires a different approach. Instead of using the group selection, you should just create two formulas:

//{@prevtype}:
previous({Table.type})

//{@prevdate}:
previous({table.date})

First sort your report by {table.date} ascending. Then place your detail fields in the group footer for inv#. Add the two formula fields to the group footer also, and then suppress the group header and detail section.

Then go to the section expert->group footer->suppress->x+2 and enter:

{table.type} = previous({table.type})

-LB

 

[analytics2006]

I am so sorry that I didn't layout all the requirements. I will make sure I will put very detailed requirements next time.
I really appreciated your help on this.
I tried your method and I am not getting data (GF2) for a particular record after suppressing the detail section.
I have checked all the criterias and formula and couldn't found anything that will restric this record being output.
Can you please help me??

thanks

output after supressed detail section:

GH1 ZX512
GF1

GH1 Zx513(next record)


Output before supressed detail section:

PH acct# prev type curr type date

GH1 ZX512
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
D------blank
GF2 B455 CNN NSN 4/12/2007
D------blank
D------blank
GF1

 

[lbass]

I tested this before responding, so I think you should take another look at your steps. For further help, you should show exactly what you did to implement my suggestion. Also identify your group fields and show the contents of all formulas.

-LB

 

[analytics2006]

LB,

Thanks so much for spending time to help me and I learned so much. I ended up recreating the report and it worked by using your method. I think what happened to my report was because I was also trying to implement the other requirements that my client requested last min. they requested me to put another criteria into the report which will require changes to the report. i have been trying all day and still couldn't get the output i need when i added the additional criteria. i have tried to put in the section expert->group footer->suppress->x+2 and it didn't work ( {cust.type} = previous({cust.type}) or {acct.opendate} = previous({acct.opendate})with open date sorted asc. I have also tried to move the fields to the group header section but still no luck. Can you please help me with this again?? I have added the account table to the original cust table. Below is the following output I have been trying to achieve. I still need to output the type and date from customer table when the current type is not equal to 1st prev type OR when the open date is not same as the 1st previous open open date.


thanks

curr_type prev_type prev_date curr_date open_date prev_date
nsn nan 4/5/07 4/13/2007 1/1/2007 12/5/2006

 

[lbass]

I would have to see a sample of the data at the detail level.

-LB

 

[analytics2006]

The requirement kept changing and I can't get the original method to work anymore because all the changes.

Can you please help?? Here are the tables I am using and the requirements.

1. return records where trans.type(with most recent date--4/13/2007) < > previous(trans.type) (1st previous date--4/5/2007)
and trans.user not in ["avg234", "xvc434"]

OR

2. return records where acct.date(most recent date -4/10/2007) <> previous(acct.date) (1st previous date --2/28/2007)
and trans.user not in ["avb234", "xvc434"]


trans table

acct# inv# type date(type changed) user
zx512 a2555 ABA 12/2005/2005 avb234
zx512 a2555 cnn 11/16/2006 cvx555
zx512 a255 nsa 1/4/2007 xvc434
zx512 a255 nan 4/5/2007 hjk333
zx512 a255 nsa 4/13/2007 cdf333


acct (detail table)

acct# inv# date
zx512 a2555 1/20/2007
zx512 a255 2/20/2007
zx512 a255 2/28/2007
zx512 a255 4/10/2007


desired output

group by acct#
group by inv#

curr_type prev_type prev_date curr_date open_date prev_Odate
nsn nan 4/5/07 4/13/2007 4/10/2007 3/25/2006

 

[analytics2006]

Can anybody please help me??

thanks