Show only the first transaction date for each customer

[MASTERMIKE]

I am doing a report listing transaction dates for each customer. I want to show only the first transaction date for each customer, and if possible (but not required), group by first transaction date, so that I have a list of customers with a first trasaction date of 01/01/2002, a list with a first date of 01/02/2002, etc. . I know this should be easy, but I seem to be in a mental black hole.

 

[Bunting]

What about the following :
- you sort out the date in descending order
- then you limit the number of record per group .

To do so , you have to SORT the order of the date , to make sure that the
most recent one will be displayed first, then you enter the following
formula to limit the number of record per group at one.

Formula :
@Formula1

WhilePrintingRecords;
NumberVar Number := 0;

@Formula2

WhilePrintingRecords;
NumberVar Number;
Number := Number + 1;

3. Place @Formula1 in the Group Header. Format the formula to Suppress.

4. Place @Formula2 in Details A. Format the SECTION (not the formula) to
Suppress.

5. Format the Details B section to conditionally Suppress by selecting the
X+2 button INSTEAD of the selecting the Suppress box. This launches the
conditional formatting Formula Editor.

6. Enter this formula:

{@formula2} > 1

When you preview the report you will only see 1 record per group.

 

[Ngolem]

So they may have several transaction dates but you only want the first one...ie: the oldest

Not as simple as it sounds...If you are using CR SQL Designer then it would not be so bad as you can create a query that would bring back the Minimum Transaction date along with the other data...that would then make a easy report to write.

Otherwise, through direct linking you are drawing in virtually all the transactions for the clients...

if that is ok then group by Transaction date ascending

Now we want the first date only so once a client has been processed we want to ignore all other references.

Create an array called ClientID in an initialization formula and place it in the report header suppressed

@Initialization

//I'll assume this is a string...initialize the array
//for 50% more clients than you expect to have to a max of
//1000 (if more than 1000 then use 2 arrays)
WhilePrintingRecords;
stringVar array ClientID := ["","","","","","","","","","",
"","","","","","","","","","",
..(additional)..
"","","","","","","","","",""];
numberVar ClientIDCounter := 0;

Set up your report in the normal groupings of Transaction date (Ascending), Client

In the conditional suppress for all display sections of the report (header,detail and footers) place the following formula

WhilePrintingRecords;
{table.ClientID} in ClientID

this will suppress all details if we have reported this clientID earlier

Now in the footer of the clientid group, place a formula called ClientArray_Update: Suppress it in the footer

@ClientArray_Update
WhilePrintingRecords;
stringVar array ClientID;
numberVar ClientIDCounter;

if not {table.ClientID} in ClientID then
(
ClientIDCounter := ClientIDCounter + 1;
ClientID[ClientIDCounter] := {table.ClientID};
);

That should do the trick...the only drawback is the number of records you will be processing.

Hope this helps

Jim
JimBroadbent@hotmail.com

 

[balves]

These are ridiculously complicated answers.

Create a group for the customer.

Insert a running total (summary field). Choose the date field, select Minimum as the function and choose Reset on every group.

Place the running total field on the group footer.

 

[Ngolem]

This NOT so ridiculous IF you read his problem CORRECTLY

HE WISHES TO GROUP BY TRANSACTION DATE!

A summary function cannot be used in a grouping formula!

I would suggest you completely understand the problem before disparaging the solutions of others

Your solution does not work

Jim

 

[balves]

Better yet, maybe he should write a C++ program to create the report.

> I am doing a report listing transaction dates for each
> customer. I want to show only the first transaction date > for each customer.

The customer is the group. Not the transaction date. And you can simply put the Min(date) in the group footer.

> if possible (but not required), group by first
> transaction date, so that I have a list of customers with > a first trasaction date of 01/01/2002, a list with a
> first date of 01/02/2002, etc. .

This is the reverse of what's required above. You can only group by customer, then transaction date OR by transaction date, then customer.

Or you can create a sub-report for the second pseudo-requirement and place the sub-report (group by transaction date) at the bottom on the original report.

 

[Ngolem]

No...you can group on transaction date...and then customer

you suppress customer data when it is repeated.

I'll stand by my approach...of which I have used in the past.

 

[Roadkill150]

Hi! Here is my approach to this issue:
1.group by customer name
1. group on your transaction date in ascending order
2. right click on the group 2 in the left margin and select format section
3. select the formula box next to suppress
4. enter Not OnFirstRecord in your formula editor
5. repeat procedure for group footer
Hopefully this helps!
Cheers,
-Bruce
Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[Roadkill150]

Hi! Here is my approach to this issue:
1.group by customer name
2. group on your transaction date in ascending order
3. right click on the group 2 in the left margin and select format section
4. select the formula box next to suppress
5. enter Not OnFirstRecord in your formula editor
6. repeat procedure for group footer
Hopefully this helps!
Cheers,
-Bruce
Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[Ngolem]

RoadKill

I don't think this works

OnFirstRecord means the first record of the complete dataset...not the set of data associated with the group. I think you will end up suppressing all but the first record.

 

[lyanch]

The method Jim has suggested will work. The main problem that I see with it is the inevitable growth. In two months they will want the same report, but now they only see the new ones for the last month.

I would seriously consider a stored procedure for this (or Crystal Query if you may... I prefer procedures). This is a very simple query in TSQL and you won't have to download all those records to your Crystal Machine when (esp as time goes on) you will want so few of them.

Lisa

 

[Ngolem]

Thank you Lisa

Yes I agree the problem is the amount of data processed. An external query or proceedure would be preferable. Both of which I mentioned in my response....rather the query I mentioned....didn't think of using a proceedure.

The delima of this report is that he wants the oldest date so he always must search back to the begining of his data base.