Count Customer with Number of Calls Attempt 1 time, 2 time, 3 times 2

[ker0k3n0]

Hello I am trying to summarize my call data based on customer ID on # of Attempt (1 time, 2 times, 3 times)
I have a call table that contain the following data sample

CallID CustID CallDate
1 C01 1/2/2018
2 C02 1/2/2018
3 C03 1/2/2018
4 C01 1/3/2018
5 C01 1/4/2018
6 C03 1/5/2018

I want to show result as
# of customer with 1 attempt = 1 //C02 was attempted 1 time
# of customer with 2 attempt = 1 //C03 was attempted 2 times
# of customer with 3 attempt = 1 //C01 was attempted 3 times

Can you please guide me how to achieve this using formula and counter? I need to show the result in Page Header.

Thank you,
Phoebe

 

[pmax9999]

I did it this way:

1. Create group on CustID;
2. Create the following formula and add it to the group header:
[Code {@var:Count}]
WhilePrintingRecords;
Global NumberVar c1;
Global NumberVar c2;
Global NumberVar c3;

If Count ({Table.CallID}, {Table.CustID}) = 1
Then c1 := c1 + 1;

If Count ({Table.CallID}, {Table.CustID}) = 2
Then c2 := c2 + 1;

If Count ({Table.CallID}, {Table.CustID}) = 3
Then c3 := c3 + 1;

''
[/Code]
3. Create the following 3 formula and place them in the report footer:
[Code {@Count1}]
whilePrintingRecords;
Global NumberVar c1
[/Code]

[Code {@Count2}]
whilePrintingRecords;
Global NumberVar c2
[/Code]

[Code {@Count3}]
whilePrintingRecords;
Global NumberVar c3
[/Code]

The final 3 formula will return the number of customers with 1, 2 and 3 calls respectively. Assuming it is possible/likely for customers to call more than 3 times you may need to expand on these to capture customers with more than 3 calls.

The results can only be returned in the report footer, so if you need the results in the report header this could only be achieved by placing these formulas in a sub report and placing the sub report in the report header of a 'container' report. An alternative would be to use a database view, stored procedure or Crystal Command to push the summary calculations back to the database engine but this is more complex to explain in these forums and is database dependent.

Hope this helps.

Cheers
Pete

 

[lbass]

Another way to do this is by creating a SQL expression {%callcnt} for the count per customer:

(
select count(a.`callID`)
from `Call` a
where a.`custID`=`Call`.`custID`
)

The syntax and punctuation depend upon your database and CR version. If the SQL expression editor doesn't accept this, remove the "a." from within the count() and see if that works.

I think you will need a group on customer ID in the main report (even if suppressed). Then insert a crosstab in your report header and add {%callcnt} as the row field and distinctcount of {Call.CustID} as your summary field. This will return the # of customers per specific count.

If you need more help, please identify your CR version and your database (or provide a sample of your SQL statement by going into database->show SQL query and copying the statement and pasting it here.

-LB

 

[ker0k3n0]

Thank you Pete!!! I followed your method and it works beautifully! As always I appreciate the help from this site and especially lbass I am a long time follower