• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Crystal - Ranking System

I need to create a report that captures our sales people's daily call activity. Our sales people make outbound calls to contacts w/in a company. We tag if it was a call or email and if it was outbound or inbound. I have to create a chart that shows what the call activity was at the company level (unique orgs.). There is a catch. An actual conversation takes precedence over a left message which takes precedence over an email sent.

for ex. Sales Person A calls John @ company ABC on 9/12 and leaves a v/m. A then calls John again on 9/14 and leaves a v/m. John sends A an email w/ his availability for a call. A calls John on 9/16 and has a conversation.

In my chart, Company ABC would only show up once w/ a 'conversation'. I've done this where I've created a formula or even a cross tab and said only report 'distinct count' but there is no way of knowing which category Crystal is putting company ABC in; does it go in the "Left Message" count, "Email" count or "Conversation" count.
Is there a way to prioritize in Crystal?

-Nancy
0
PushtiLeela
Asked:
PushtiLeela
  • 10
  • 4
  • 3
1 Solution
 
GJParkerCommented:
Rather than making us guess what your data structure is like why don't you show us and example , oh and it's also always good to know what version of CR you are using.
0
 
PushtiLeelaAuthor Commented:
I am using CR ver. 2008

I've attached what I need the outcome to look like. The #'s do not reflect unique orgs.

I have a field that captures the 'outcome' of the activity. I've created a formula (count result) that puts the activity in one of three categories.


if {CUST_Marketing.CUST_Result_074556774} = "Attempt" then
    formula = "Attempt"
elseif {CUST_Marketing.CUST_Result_074556774} = "Left Message: inbound" or _
       {CUST_Marketing.CUST_Result_074556774} = "Left Message: outbound" or _
       {CUST_Marketing.CUST_Result_074556774} = "Left Message: Outbound: Email" or _
       {CUST_Marketing.CUST_Result_074556774} = "Left Message: inbound: email" then
    formula = "Left Message"
elseif {CUST_Marketing.CUST_Result_074556774} = "Complete: inbound" or _
       {CUST_Marketing.CUST_Result_074556774} = "Complete: Inbound: Email" or _
       {CUST_Marketing.CUST_Result_074556774} = "Complete: outbound" or _
       {CUST_Marketing.CUST_Result_074556774} = "Complete: Outbound: Email" then
    formula = "Had a Conversation"
else
    formula = "unknown"
end if

I inserted a cross tab with the date field in the column sec., formula (count result) in the rows sec. and the summarized field is the company.name (I've attached an image that shows the fiels w/ company.name as distinct count).





call-activity-chart.pdf
Capture.JPG
0
 
mlmccCommented:
That is nice but if you have the scenario above the company will be put into at least 3 categories.

If you want the priority system to be used where John gets counted once and for the conversation then we need to know what the raw data looks like for each possible outcome and what the priority scheme is.

mlmcc
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PushtiLeelaAuthor Commented:
That's correct. They get placed into 1, 2 or 3 categories. The priority scheme is  Conversation ranks the highest (#1), Left Message ranks second and Attempt ranks last.
What do you mean by the raw data?
I wrote the code above to make the chart simple and easy to follow and to avoid having 9 different categories.
Does it make sense to write a second code that takes the outcome of that code and ranks them as #1, #2 or #3.
Then if company is in more than one rank group; for ex. #1 & #2, assign it to group #1. If #2 & #3, then assign to group #2, etc.. In this scenario, I would have to work out all the possible combinations. Luckily there are only three groups so it's not so bad. But there has got to be a better, more effective way of doing this.
Please explain what you mean by raw data so I can share that with you.
0
 
GJParkerCommented:
We need to see an example how your data is stored in the database and teh tables, also let us know what DB you are using

Your issue is that you only want to report on the highest ranking event for each customer and the date that event occurred but you are returning all of the events from the database which doesn't enable you to easily use crosstabs or charts..

As a matter of interest what woul dhappen on the example above if sales person A calls John again and leaves a voice mail on 18/9 - which group would you expect that to fall into ?

0
 
PushtiLeelaAuthor Commented:

I am using Sage ACT- 2009.
Yes, I need to capture all of the events in order to track & manage the sales people's daily activity for internal purposes. I also need to provide our 'clients' an overview of how many companies our sales people talked to and show them the highest priority outcome from the call.
I hope this isn't too much detail but hope it helps.
Scenario 1.  Sales Person A calls John - the engineer @ Company ABC on 9/1/11 & leaves a v/m for him. S.P. A makes a note of this in ACT. S.P. A would under the company, at the contact level, select the date that it occured, the person she dialed, what the outcome was (left message: outbound), their interest level (undetermined), regarding (this would be what project this call was regarding - we have several diff. projects that people are working on at one time).
Scenario 2.  S.P. A calls Tom - the IT manager @ Company ABC (same company) on 9/3/11 and talks to him about our services. Tom is interested and wants to see a demo. a demo is scheduled for 9/30/11.   S.P. A notes: under Tom's contact tab, 'Complete: outbound', interested, re: project name, also enters a date in the field called "Demo Scheduled".

The data is being pulled from several diff. tables. I've attached an image that shows the tables. Do you need to see the breakdown of the table- meaning the fields w/in each table?
Image1.JPG
0
 
mlmccCommented:
In light of the 2 scenarios would you want to see both or only the conversation with Tom?

We are trying to determine how to filter the data so you will get the information you need and as little extra data as possible.

mlmcc
0
 
PushtiLeelaAuthor Commented:
In this case, the conversation with Tom.

BTW, I have Topline Designer addon to ACT. I don't know if that matters any.
0
 
mlmccCommented:
Now we really need to see the data structure.  In this case how can we relate the 2 calls together as the same company since they went to different people.

Just need to see the details of the call tables

mlmcc
0
 
PushtiLeelaAuthor Commented:
I have a table: TBL_CONTACT where the contact name and company are listed. I have pulled both of these fields into the rpt.
I've attached the file so you can see the data structure.
call-activity-rpt.rpt
0
 
GJParkerCommented:
Check out the attached, does this give you what you want ? call-activity-rpt-1-.rpt
0
 
PushtiLeelaAuthor Commented:
It sure does. I am amazed you were able to figure this out so quickly.
I do have couple of questions.

1. Can you please explain what this code is doing? I am just learing basic code and would never have thought of writing this.

If {@Rank} = Maximum ({@Rank}, {TBL_CONTACT.COMPANYNAME}) Then
    {TBL_CONTACT.COMPANYNAME}
Else
    ToText({@Null})


2. In the cross tab, what is the logic or reason behind summarizing the count of companyname as well as distinct count of Rank Count?
Is it so if company A had 2 'completes' or 'conversations', the distinct count of Rank Count gives you the unique org. count (counts company A only once).

Thank You sooo much. I am so grateful to you and mlmcc for all your help.

-Nancy
0
 
PushtiLeelaAuthor Commented:

One more questions.
Do you have any suggestions for a resource/book to help with writing basic code focusing on ranking?
0
 
PushtiLeelaAuthor Commented:
I just implemented the changes you suggested. I am getting an error "A Statement is expected here"(in the 2nd line) when I run the Rank Count code.

If {@Rank} = Maximum ({@Rank}, {TBL_CONTACT.COMPANYNAME}) Then
    {TBL_CONTACT.COMPANYNAME}
Else
    ToText({@Null})
0
 
mlmccCommented:
DO you have the formula editor set for Crystal Syntax?

If you are using Basic syntax then

If {@Rank} = Maximum ({@Rank}, {TBL_CONTACT.COMPANYNAME}) Then
    formula = {TBL_CONTACT.COMPANYNAME}
Else
    formula = ToText({@Null})

mlmcc
0
 
PushtiLeelaAuthor Commented:
Thank You.
I am using Basic syntax.
As you can see, I'm still learning the basics.
0
 
PushtiLeelaAuthor Commented:
I am very grateful to have access to all the experts on this site.
This is a very valuable resource to have.

Thank You GJParker & mlmcc.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 10
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now