Grouping and Previous Problem

[baycolor]

CR XI and CR Server for batch processing.

I have a report that's grouping on account. I use header, footer, and detail formulas to handle some one to many issues. Each account can have many opportunities (opps) associated with it and each opportunity has an estimated close date and a last modified date associated with it. Current output is below showing concatenated month values from the opportunity estimated close dates.

[tt]
Account EstCloseMonth
12 Nov, Jan
13 Jan, Feb
14 Mar
[/tt]

Now the customer wants a Last Modified Date column added but only wants to see the latest Last Modified Date for an account. Example output:

[tt]
Account EstCloseMonth LastModDate
12 Nov, Jan 11/13/07 <--- 2 opps exist for this
13 Jan, Feb 10/11/07 acct and this is the
14 Mar 1/23/07 latest last mod date
from the 2 opps.
[/tt]

I added the following three formulas:

[tt]
HEADER FORMULA ADDED:
WhilePrintingRecords;
Global DateVar LastModDate;

DETAIL FORMULA ADDED:
WhilePrintingRecords;
global DateVar LastModDate;

If {CRAL_V_TEAM_OPPS.team_opp_modification_date} >
Previous({CRAL_V_TEAM_OPPS.team_opp_modification_date})
then
LastModDate := {CRAL_V_TEAM_OPPS.team_opp_modification_date}
Else
LastModDate

FOOTER FORMULA ADDED:
WhilePrintingRecords;
global DateVar LastModDate;
LastModDate
[/tt]

I'm grouping by account and I need to compare the last modified date in the account group thats being processed to the last modified date in the previously processed record (in the same group of course). I have no idea how many opportunities will be associated with an account but I don't think it matters as in the detail formula I'm always assinging the greatest last modified date to the LastModDate variable.

I know that I have some account groups that the formulas above are and aren't working for. For example the following last modified dates exist for one account:

[tt]
10/01/2007
10/01/2007
02/28/2007
08/03/2007
10/01/2007
10/01/2007
11/16/2007 <-- based upon my formulas I expect to see this
10/01/2007 value being printed for this account group
10/01/2007 what I get is 10/01/2007.
[tt]

The strange thing is if I look at other groups of data this is working properly. Following are from another account group:

[tt]
04/16/2007
04/23/2007
10/30/2007
11/12/2007
11/12/2007 <-- based upon my formulas I expect to see this
value printed for this account group and
that's what I get.
[/tt]

Do I need to be doing some OnFirstRecord stuff or something to get all account groups to work properly?

I'm stumped now. Thanks in advance.

 

[BeeBear]

Hi,

Not sure if I'm on the right track, but your modified dates list that doesnt work, seems to have the 10/01/2007 date as the last record in the group, and the next account group also has the 11/12/2007 as the last record in the group.
So maybe it is just displaying the last record in the group each time? Can you view the detail level and see where the variable field stops changing?

As a possible solution, are you able to resort the detail to sort by {CRAL_V_TEAM_OPPS.team_opp_modification_date}, in descending order (the latest date first), then just select the first instance in the group using this formula:

WhilePrintingRecords;
global DateVar LastModDate;

If onfirstrecord or {account} <> previous({account}) then
LastModDate := {CRAL_V_TEAM_OPPS.team_opp_modification_date}
Else
LastModDate := LastModDate;
LastModDate;

Note - replace {account} in my formula above with the name of your account grouping field.

Hope this helps.

 

[baycolor]

You are on the right track alright - you flat out solved the problem. I added account (ASC) and last modified date (DESC) on the sort expert screen. The formula I already had then worked but I changed to the one you wrote as it made more sense.

Still don't understand what was wrong with what I was doing initially but oh well. Many thanks!!!

 

[lbass]

Please don't dual post. This is the same issue as your other post. The reason the above method worked is that testing for the first record or change in group caused a reset that could have been accomplished in a group header formula.

-LB