Looping process

[idehen]

I do apolgise if this already looks like what's probably been answered in other post, i just couldn't get any that closely match what i am trying to do.

Basically i have my table and fields as:


Date Change reason Amount
28/11/2006 Reinstatement 450
27/11/2006 Sale -50
23/11/2006 Add grant 100
22/11/2006 lapse -450
16/11/2006 Grant 600

what i want to do is get information where the change reason of the negative table that precedes the reinstatement. only where it's a lapse.

Basically i know sale is a negative just before reinstatement but i want a loop to ignore that unless it's a lapse negative.

Any ideas please.

 

[lbass]

So what result would you expect to see based on your sample?

-LB

 

[Charliy]

So if I'm reading you Correctly you would want to see the Lapse -450 and Reinstatement 450 for this particular person.

Someone with a Lapse but no Reinstatement should not show up.

If that's true, then you need to change your sort so the dates are descending, and group by whatever the other field is that you're not showing us - the salesman or customer id or whoever is si that lapeses.

Then suppress your detail. Set up some shared variables to capture what it is that you want to print for this group.

Whenever I do something like this where 2 things have to be true to print the Group Footer, I set up 2 shared numbervar's and set each to 1 when the respective piece is found - then use a print suppress formula in the group footer of got1 + got2 < 2

 

[idehen]

Thanks Charliy,

You have read correctly, what i am looking for is where there is a reinstatement for a customer id, i want to see the lapse that effected the reinstatement basically. There could be another lapse after the reinstatement but anything after the reinstatement is not important just lapse action if any that happened before reinstatement.

My example relates to just one customer information. I have more than 200 customers am trying do this for.

You suggestion:
I tried the sort until i got lost at the shared variable section and not sure how to create this if you don't mind providing sample formula.


Many thanks again.

 

[Madawc]

I think you're trying to get Crystal to do more than it easily can manage. It is a reporting tool, and allows you to produce neat-looking reports very quickly. It isn't a full programming language, though it does include a lot of options that you can control in a subtle way.

You might manage it with running totals you check in the group footer.

An alternative is to select the table more than once, have it separately for lapses, reinstatement etc. Then you can cross-link. Try it and see.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I'm not sure why you didn't answer my question, but if you really only want to see records where there is both a reinstatement and a lapse, then why not set the selection criteria to limit to those two reasons?

{table.changereason} in ["reinstatement","lapse"]


Then you could go to report->selection criteria->GROUP and enter:

distinctcount({table.reason},{table.customerID}) = 2

This would show only those customers with both a reinstatement and a lapse.

-LB

 

[idehen]

Thanks everyone for your help. Thought i also add that i am using CR.10.

Madawc: Thanks for your suggestion. I tried your it and it worked for some customers and others kept pulling through other change reason like sale.

I know there is a limit to Crystal's capabilities, but was just checking if it was possible before i assume it wasn't.

Ibass: Thanks for your suggestion also. I did respond to you question in my post reply to Charliy saying, what he assumed was the expected result i wanted.

I tried your suggestion as well and this came closer to what i am looking for.

Only problem is that it returns customers who do have a lapse but never had a reinstatement reason.

if it helps, I am grouping all my data first by customer id, Date and then by change reason. All group level compressed except change reason level. I have used group change reason because i found that some customers can have a reinstatement and lapse done on same date.

Appreciate the help. Many thanks again

 

[kskid]

Assuming an Oracle database, create following command from Database Expert

Select table.customer_id,
table.date,
table.amount,
From table
where table.reason = 'reinstatement';

Link the table to the command. Your record select formula should look like this

{command.customer_id} = {table.customer_id} and
{table.reason} = "lapse" and
{table.date} >= {command.date}

That should give you all reinstatements with a matching lapse

-lw

 

[kskid]

Or I guess you could link the table to itself as an alias called lapse.

{table.customer_id} = {lapse.customer_id} and
{table.reason} = "reinstatement" and
{lapse.reason} = "lapse" and
{lapse.date} >= {table.date}

 

[Charliy]

I'm going to make another assumption here - that there is some kind of date range on this report. So if for instance you are running the report for August and you get a Reinstatement on Aug 4 and a Lapse on Aug 30, you don't want to see that combination because there is no cause/effect relationship.

kskids's method will work in this case, you just need to put the SQL instructions into Command in the Database Expert. And SQL varies depending on the database you're using.

The other way to set up shared variables. Step 1 is to create an initializing formula:
//@init
shared stringvar Lapse := " ";
shared stringvar Reins := " ";
shared numbervar foundL :=0 ;
shared numbervar foundR :=0

You'll place that in the report header (print suppressed).
You also create a second group footer below the section where the data prints and put the formula there.

Next you would create an evaluation formula to put in the Detail band:

// @eval
IF {table.field} = "lapse" and foundR = 0
then (Lapse := <the date etc.> ; foundL :=1)
else
if {table.field} = "reinstated" and foundL = 1
then (Reins := <the date etc.> ; foundR :=1)
else (Reins:=Reins; Lapse:=Lapse ; foundL:=foundL;foundR:+foundR

This only updates Reinstated if you previously found a Lapse, and will not update Lapse If you've already found a Reinstatement. Where I'm indicating <date etc> you would put a totext of the date, the dollar amount, the customer ID, anything else you want to go eith the Lapse or Reinstatement.

Then you just need formulas to show the stringvars in the group footer.

//@shoLapse
shared stringvar Lapse

//@shoReins
shared stringvar Reins

put those in the Group Footer A, with the Init formula in the non-printing Group Footer B.

 

[lbass]

I think you implemented my suggestion incorrectly as it would have ONLY displayed people who had both a reinstatement and a lapse. Please reread my suggestion.

-LB

 

[idehen]

Many many thanks for all the input.

Ibass,
I tried your suggestion again and it returned both lapse and reinstatement correctly. However it was returning laspses irrespective of whether it was pre or post reinstatement date. Though my example didn't show that there could be lapse before or after reinstatement date and this was my fault for not providing that much detail initially.However many thanks for your help.You got me very close to the solution.

KsKID,
I tried your first suggestion and it worked perfect. Many thanks for your help as you also guessed correctly, i am using Oracle. Thanks thanks thanks.

Charliy,
Many many thanks also. After i tried KsKID's suggestion, i got so excited at finally cracking the problem i am yet to implement your suggestion. Just to say i will be testing this as well soon as i get the report out. Phew! but much appreciated.

Thanks alot again guys. really really appreciate all the help and your time. Fantastic forum.