Attempting to Sort by Field within a Group

[alex35]

I am attempting to group a list of Matter_No based on the analyst who handled the last stage of a Matter_No.
The fields in question include the following:

Table 1
Matter_No Stage Start Date Attorney
123 2 10/01/2001 John Smith
123 5 10/01/2003 John Doe

154 2 10/01/2001 John Doe
154 5 10/01/2003 John Smith

160 2 10/01/2001 John Smith
160 5 10/01/2003 John Doe

Table 2
Attorney Analyst
John Smith Analyst I
John Doe Analyst II

Table 1 and 2 is linked by Attorney. My approach was to first determine the Maximum (Start Date) for each Matter_No (involves grouping the Matter_No). Using the Report/Edit Selection/Group/ option, I include the criteria that the Maximum (Start Date) = Start Date which results in the following listing:

Matter_No Stage Start Date Attorney Analyst
123 5 10/01/2003 John Doe Analyst II.
154 5 10/01/2003 John Smith Analyst I.
160 5 10/01/2003 John Doe Analyst II.

Next, I changed the primary grouping to the Analyst so that the list is sorted by the Analyst. The listing changed to:

Analyst I
Matter_No Stage Start Date Attorney
123 2 10/01/2001 John Smith
154 5 10/01/2003 John Smith
160 2 10/01/2001 John Smith

Analyst II
Matter_No Stage Start Date Attorney
123 5 10/01/2003 John Doe
154 2 10/01/2001 John Doe
160 5 10/01/2003 John Doe

I realize what is going on, but is there any way to prevent this from happening?

Thanks,
William

 

[Ngolem]

Table 1
Matter_No Stage Start Date Attorney
123 2 10/01/2001 John Smith
123 5 10/01/2003 John Doe

154 2 10/01/2001 John Doe
154 5 10/01/2003 John Smith

160 2 10/01/2001 John Smith
160 5 10/01/2003 John Doe

Table 2
Attorney Analyst
John Smith Analyst I
John Doe Analyst II

*************************************

So if I understand you properly you want the final result to be something like

Analyst I
Matter_No Stage Start Date Attorney

154 5 10/01/2003 John Smith

Analyst II
Matter_No Stage Start Date Attorney

123 5 10/01/2003 John Doe 160 5 10/01/2003 John Doe

This is not as easy as it looks at first...there are a few possible approaches but first I want to ask you...How big is the expected report/analyst...just a few detail lines each or more?

Also I would imagine that the last review of a particular Matter_No. could be something other than Stage 5.

If the data was not too great (ie. <100 items/Analyst) I might approach this using arrays to store the data

I would group the report by

Group 1 : Matter_no
Group 2 : Stage

The footer of Group 1 would contain the information you are looking for....IE...the analyst who last looked at this Matter_No.

Now you would determine the Analyst and save the pertenent info to a arrays for later display in the report summary.

I will assume that you have some handle on how many Analysts there will be and that Analyst and Attorney are not the same thing.

So let us proceed on this basis...I will assume only 2 Analysts possible...You can modify to suit your actual case

You need an initialization formula

@Initialization (Suppressed in report header)

WhilePrintingRecords;
//With all arrays make them 50% larger than you expect them
//to be...ie if you expect 50 details/analyst plan for 75

StringVar array A1_Attorney := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];
StringVar array A1_Matter_no := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];
StringVar array A1_Stage := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];
StringVar array A1_StartDate := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];

StringVar array A2_Attorney := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];
StringVar array A2_Matter_no := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];
StringVar array A2_Stage := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];
StringVar array A2_StartDate := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];

NumberVar Pointer1 := 0;
NumberVar Pointer2 := 0;


Now you will suppress all sections except for the report footer which will be used to display the results

In the Footer for Group 1 (Matter_No) Place the following formula

@GatherInfo

WhilePrintingRecords;

StringVar array A1_Attorney ;
StringVar array A1_Matter_no ;
StringVar array A1_Stage ;
StringVar array A1_StartDate ;

StringVar array A2_Attorney ;
StringVar array A2_Matter_no ;
StringVar array A2_Stage ;
StringVar array A2_StartDate ;

NumberVar Pointer1 ;
NumberVar Pointer2 ;

If {Table2.Analyst} = &quot;Analyst I&quot; then
(
Pointer1 := Pointer1 + 1;
A1_Attorney[Pointer1] := {Table1.Attorney} ;
A1_Matter_no[Pointer1] := {Table1.Matter_no } ;
A1_Stage [Pointer1] := {Table1.Stage } ;
A1_StartDate [Pointer1] :=
ToText({Table1.StartDate},&quot;MM/dd/yyyy&quot; ;
)
else If {Table2.Analyst} = &quot;Analyst II&quot; then
(
Pointer2 := Pointer2 + 1;
A2_Attorney[Pointer2] := {Table1.Attorney} ;
A2_Matter_no[Pointer2] := {Table1.Matter_no } ;
A2_Stage [Pointer2] := {Table1.Stage } ;
A2_StartDate [Pointer2] :=
ToText({Table1.StartDate},&quot;MM/dd/yyyy&quot; ;
);

Now you have all of your data stored in Arrays ready for printing

Now you must create a series of formulas to display the results.

Divide the repott footer into as many sections as there are analysts

In each section you enable the conditional &quot;Suppress &quot; using formula with the pointer value for the corresponding Analyst

eg. for Analyst I it would be : Pointer1 = 0

there is a limiting factor of 254 char/formula so you must plan on how many items you can list on the worst case which in the current example is StartDate but probably could be Attorney

Let us decide that we can allow 20 chars max/attorney this will allow 254/20 = 12 lines of detail/formula. If you expect more than this you must create additional formulas and another subsection for each subsection that you have already created.

So to display the results all display formulas are similar...I will only show you one

@DisplayAnalyst_1_Attorney
WhilePrintingRecords;
StringVar array A1_Attorney ;
numberVar Temp;
StringVar result;

// make sure you have at least 12 elements initialized
For Temp := 1 to 12 do
(
if array A1_Attorney[Temp] <> &quot;&quot; then
result := result + A1_Attorney[Temp] + chr(13) + chr(10)
);

result;

It looks like a lot of formulas but they are easily cloned.

Anyway that is the basic approach I would take in this report.



Jim Broadbent

 

[alex35]

Jim Broadbent,

Unfortunately, the number of items per Analyst is going to be greater than 100 actually over a 1,000 per Analyst. I am not that familar with using arrays, but would assume that I would have to reserve 1000+ places for the Matter_No.

Perhaps to make things more simplier, the intent of this exercise is to make the final list look like this:

Analyst I
154

Analyst II
123
160

Thanks,
~William

 

[kenhamady]

I think you are going to need a primary report that is just a list of analysts. Then a linked subreport that is basically your first report. Link the analyst to get a subreport parameter, but do NOT use this parameter in the record selection formula.

Create a formula called {@Both} that is:

ToText ( {StartDate}, &quot;yyyy-MM-dd&quot + {Analyst.field}

Then in your Group Selection formula use:

Maximum ({@Both} , {Matter}) [ 11 to length ({@Both}) ]
= {?AnalystParameter}

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Ngolem]

the StartDate is not the criteria for selection...I don't think....rather it is the &quot;Stage&quot; .... perhaps I am wrong here.

I don't know if a subreport is such a good idea, Ken. If there is over 1000 items/analyst...that is a lot of hits to a subreport.


Jim Broadbent

 

[Ngolem]

william -

Actually you can create the arrays to a size of 1000, no problem....you don't need anymore info instead of the Matter number???

ie...The attorney = analyst?
Who cares about the stage or startdate values?

Is the size of the Matter No. only 3 digits...find that hard to believe if there are 100's of them...so are these numbers recycled in the next year....what is the max size of this number/char string?

If it is 5 digits you could store 254/5 per formula = 40 (must include space for carriage returns in the formula)

you could have 10 columns of numbers per section...so you could have 3 sections/analyst..we could even have 2 arrays
per analyst so we could handl up to 2000 numbers

Basically the structure I outlined would not change much...the formulas which be changed to the following.

@Initialization (Suppressed in report header)

WhilePrintingRecords;
//each array has 1000 elements (Cloning makes this easy)
StringVar array A1_Matter_no1 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;]; StringVar array A1_Matter_no2 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];

StringVar array A2_Matter_no1 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;]; StringVar array A2_Matter_no2 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];

StringVar array A3_Matter_no1 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;]; StringVar array A3_Matter_no2 := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,.....,&quot;&quot;,&quot;&quot;];

NumberVar Pointer1 := 0;
NumberVar Pointer2 := 0;
NumberVar Pointer3 := 0;

// the above is repeated for as many Attorneys as you exect to have

@GatherInfo

WhilePrintingRecords;

StringVar array A1_Matter_no1;
StringVar array A1_Matter_no2;

StringVar array A2_Matter_no1;
StringVar array A2_Matter_no2;

StringVar array A3_Matter_no1;
StringVar array A3_Matter_no2;

NumberVar Pointer1 ;
NumberVar Pointer2 ;
NumberVar Pointer3 ;

If {Table2.Analyst} = &quot;Analyst I&quot; then
(
Pointer1 := Pointer1 + 1;
If Pointer1 > 1000 then Pointer1 := 1;
If A1_Matter_no1[1000] = &quot;&quot; then
A1_Matter_no1[Pointer1] := {Table1.Matter_no }
else
A1_Matter_no2[Pointer1] := {Table1.Matter_no };
)

else If {Table2.Analyst} = &quot;Analyst II&quot; then
(
Pointer2 := Pointer2 + 1;
If Pointer2 > 1000 then Pointer2 := 1;
If A2_Matter_no1[1000] = &quot;&quot; then
A2_Matter_no1[Pointer1] := {Table1.Matter_no }
else
A2_Matter_no2[Pointer1] := {Table1.Matter_no };
)
else If {Table2.Analyst} = &quot;Analyst II&quot; then
(
Pointer3 := Pointer3 + 1;
If Pointer3 > 1000 then Pointer3 := 1;
If A3_Matter_no1[1000] = &quot;&quot; then
A3_Matter_no1[Pointer1] := {Table1.Matter_no }
else
A3_Matter_no2[Pointer1] := {Table1.Matter_no };
);

//you would continue for as many Analysts as you have


the display would be the same...except you only create displays for the matter number

@DisplayAnalyst_1_Matter_No_1-40
WhilePrintingRecords;
StringVar array A1_Matter_no1;
numberVar Temp;
StringVar result;

// this formula handles the first 40 values
For Temp := 1 to 40 do
(
if array A1_Attorney[Temp] <> &quot;&quot; then
result := result + A1_Attorney[Temp] + chr(13)
);

result;

This is the tedious part...you need 5 sections to handle a total of 2000 entries a total of 50 formulas / analyst

the good news is that it is basically cloning...but this will take a while and is very boring...the other good news is that this should work reasonably fast








Jim Broadbent

 

[kenhamady]

Jim,

My plan is only one subreport per analyst, not per item. The subreport groups all records by case and then selects only the cases where this analyst is listed in the last record of the case. Then you go through the same items again checking to see how many of them have the second analyst in the last record.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lyanch]

The easiest way to do this is with a subselect in your where statement.

add something along the following to your where statement manually (in database -> show SQL query) ..

and table.start_date = (select max(t.start_date) from table t where t.matter_no = table.matter_no)

This will return only the records with the max date.. so no need to do anything fancy to supress the records that aren't. You will need to use your DB's syntax of course.

Lisa

 

[Ngolem]

Ken - sorry I misunderstood...my way though will work...though I concede it is tedious and formula intensive

Jim Broadbent