×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

(OP)
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

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

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;

'' 
3. Create the following 3 formula and place them in the report footer:

CODE --> {@Count1}

whilePrintingRecords;
Global NumberVar c1 

CODE --> {@Count2}

whilePrintingRecords;
Global NumberVar c2 

CODE --> {@Count3}

whilePrintingRecords;
Global NumberVar c3 

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

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

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

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

(OP)
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 :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!