Grouping Not Working.

[baycolor]

I'm using CR XI. And CR Server to batch run report.

I have a simple list report about three columns. The report is dealing with some one-to-many issues by concatenating data into one column.

EXAMPLE: An account can have many opportunities associated with it. Each opportunity has an estimated close date. And each opportunity has a last modified date.

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

I'm grouping on the account number and I'm using a group header, detail and group footer formulas on the estimated close date to concatenate the estimated close date months into the second column of the report.

Now my user wants the last modified date added. And only the most recent last modified date should be shown for
an account (the latest modified date on any of the opportunities assigned to an account). Want this:

[tt]
Account EstCloseMonth LastModDate
12 Nov, Jan 11/15/07
13 Jan, Feb 9/1/07
14 Mar 1/1/2005
[/tt]

To do this I added a group header, detail and footer formula to act on the last modified date field. Here are those formulas:
[tt]
HEADER FORMULA
WhilePrintingRecords;
Global StringVar LastModDate;

DETAIL FORMULA
WhilePrintingRecords;
global StirngVar LastModDate;

LastModDate := LastModDate + ToText({CRAL_V_TEAM_OPPS.team_opp_modification_date})

FOOTER FORMULA
WhilePrintingRecords;
global StringVar LastModDate;

LastModDate
[/tt]
NOTE: I know the above doesn't limit to the most recent last modified date like I'm after but I'm stepping back from that goal for the moment. I'm trying to understand why when the above runs the value in the LastModDate field for the last row on the report is a concatenation of all last mod dates from all opportunities.

Why isn't the grouping by account number making the concatenation reset? I'm thinking once I figure this out I won't have any problem with my original detail formula that will get me the latest mod date within an account grouping of opportunities - which is really:

[tt]
If Not IsNull({CRAL_V_TEAM_OPPS.team_opp_modification_date}) then
If {CRAL_V_TEAM_OPPS.team_opp_modification_date} > LastModDate then
LastModDate := {CRAL_V_TEAM_OPPS.team_opp_modification_date}
Else
LastModDate
[/tt]

Thanks in advance!!!

 

[dgillz]

you need a reset formula in your group header. This does not happen automatically.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[baycolor]

FYI I realize why I was getting every value when I concatenated the text date values. My HEADER formula wasn't resetting to empty. Changed it to:

Global StringVar LastModDate := "";

and only all opportunities associated with an account were in the LastModDate field.

However this did not fix my problem when I attempted to get the most recent last mod date.

[tt]
HEADER FORMULA CAN BE THIS
WhilePrintingRecords;
Global DateVar LastModDate;

OR
WhilePrintingRecords;
Global DateVar LastModDate = Cdate(1900, 01, 01);

DETAIL FORMULA
WhilePrintingRecords;
global DateVar LastModDate;

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

FOOTER FORMULA
WhilePrintingRecords;
global DateVar LastModDate;

LastModDate
[/tt]

As I understand it the date variable declared in the header will default to NULL so I don't really need to initialize it with a dummy value. But either way when I use the formulas above I end up with the wrong date in my LastModDate field.

Almost still looks like the grouping isn't resetting still I proved that it does reset when I did the text concatenation.

Not even sure how to test for what's going on. Could this have something to do with using dates?

Thanks

 

[lbass]

Use a reset formula like this:

WhilePrintingRecords;
DateVar LastModDate := date(0,0,0);

Change your detail formula to:

WhilePrintingRecords;
DateVar LastModDate;

If IsNull({CRAL_V_TEAM_OPPS.team_opp_modification_date}) then
LastModDate := LastModDate else
If {CRAL_V_TEAM_OPPS.team_opp_modification_date} > LastModDate then
LastModDate := CRAL_V_TEAM_OPPS.team_opp_modification_date};

You don't really have to declare the variables as global, as this is the default.

-LB