Grouping of counts?

[FrankInBerlin]

Hi everybody,

the Problem:
I have customer IDs that can be contated many times.
Each time the customer is contacted the date is entered in the DB.
Now I want to find out how many customers have been contacted x times.

The DB looks like this:
Customer ID Date
1 1.1.2002
1 10.1.2002
2 2.1.2002
3 5.1.2002
.
.
.

The report should look like this:

Number of Customers contacted 1 time: 2
Number of Customers contacted 2 time: 1
.
.
Nummber of Customers contacted x time: x

I guess this is pretty simple, but can't figure it out.

Any Ideas?

Regards,
Frank

 

[Madawc]

Group customers by customer IDs, and count the number of customers in each group.

Then add a second group, using this count. (You may need to use a formual field rather than a group count).

The totals you want can be the group totals for the second group. Hide the rest. Madawc Williams
East Anglia
Great Britain

 

[FrankInBerlin]

Hi Madawc,

can you be bit more specific on the formula?

If I have the count field in the formula, the formula is not given as an option for the grouping.

With my best,
Frank

 

[Madawc]

Try adding to the count at the level of detail records. Create two formula fields, field A says add 1 to field B. Field B should then be available for further use.

Consult

http://www.kenhamady.com/news0207.html

for an explanation of the limitation and hints about possible ways round
Madawc Williams
East Anglia
Great Britain

 

[FrankInBerlin]

I am very sorry, maybe my brain is on vacation..
I don't have a clue what you are talking about.
I tried it for an hour and I read the Hamady stuff getting the impression that it cannot be done.

Should I use groups or not?
How do I write a formula counting records without using the count funcion?
What is the code in field A what in in B?

Maybe you can formulate it for dummys?

with my best,

Frank

 

[Madawc]

Field A and Field B are Formula Fields. There is a check to stop Formula Fields referring themselves, even indirectly.

I've done similar work-rounds, but I've not done anything exactly like it, and it may indeed be impossible in Crystal, which is not a true programming language and requires that you fit in with its methods. Madawc Williams
East Anglia
Great Britain

 

[SmpTraining]

This can be done by maintaining your own variables.

Your report should be grouped on CustomerID and you should insert a count of the dates.

You would need a formula to update variables placed at the group footer section.

Another set of formulas would be used to display the results in the report footer.

For this to be totally flexible, you would need to use arrays. Let me know if you want further help with this Frank. Steve Phillips, Crystal Consultant

 

[FrankInBerlin]

SMPhillips,

further help would be wunderful.
I never worked with arrays in CR.

I thought it would be a simple problem to solve, but it turns out to be much more complicated than I antipicated.

With my best,

Frank

 

[SmpTraining]

OK. I'll give you all the help you need but I don't have a finished worked out solution to hand. Therefore syntax might need to be checked!

1. Create a formula to reset your array and place it in the report header section

@ResetVars
// Reset the array variable with 5 elements (to start with)
WhilePrintingRecords;
numberVar Array summaryArray := (0,0,0,0,0)

2. Create a formula to add to the array and place in the customer ID group footer (where the count of dates per customer is)

In the following formula I've used <count of dates for this customer>, you should use the actual summary field with the count in. It should already be available in the 'fields' list if it's in the report.

@AddtoVars
// Add one to the appropriate array element
WhilePrintingRecords;
NumberVar Array summaryArray;
//
//Will this count cause an array-out-of bounds error?
//
if <count of dates for this customer> > UBound (summaryArray) then
reDim Preserve summaryArray[<count of dates for this customer>];
//
// Now we know we have enough elements in the array
// Lets add one to the right one
//
summaryArray[<count of dates for this customer>] := summaryArray[<count of dates for this customer>] +1;


3. We need a neat way of displaying the results now. Here's something basic to begin with. Place it in the report footer section

@ShowResults
// Show the results of the array
WhilePrintingRecords;
NumberVar Array summaryArray;
NumberVar i;
StringVar myText;

for i = 1 to UBound(summaryArray)
myText := myText & &quot;Customers with &quot; & ToText(i,0,&quot;&quot & &quot; records are : &quot; & ToText(summaryArray,0,””) & &quot;.&quot; & chr(13)
next i;

myText;

This may need a little tweaking but should get us started.
Steve Phillips, Crystal Consultant

 

[FrankInBerlin]

Dear Steve,

this works!

I could not get the part with the Redim working (I always got he error message that an array must be indexed) but I just initialised the array with more elements (20 instead of 5) and deleted the redim statement. No problem! (We are not contacting our coustomer that often )

This was very helpful!

Thankx a lot,

Frank