Solved

Crystal - Ranking System

Posted on 2011-09-20
17
392 Views
Last Modified: 2012-08-14
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
Comment
Question by:PushtiLeela
  • 10
  • 4
  • 3
17 Comments
 
LVL 19

Expert Comment

by:GJParker
ID: 36568177
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
 

Author Comment

by:PushtiLeela
ID: 36568475
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 36570127
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
 

Author Comment

by:PushtiLeela
ID: 36573941
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
 
LVL 19

Expert Comment

by:GJParker
ID: 36574112
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
 

Author Comment

by:PushtiLeela
ID: 36574299

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
 
LVL 100

Expert Comment

by:mlmcc
ID: 36575689
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
 

Author Comment

by:PushtiLeela
ID: 36576060
In this case, the conversation with Tom.

BTW, I have Topline Designer addon to ACT. I don't know if that matters any.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 100

Expert Comment

by:mlmcc
ID: 36578211
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
 

Author Comment

by:PushtiLeela
ID: 36580880
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
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 36581008
Check out the attached, does this give you what you want ? call-activity-rpt-1-.rpt
0
 

Author Comment

by:PushtiLeela
ID: 36581292
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
 

Author Comment

by:PushtiLeela
ID: 36581326

One more questions.
Do you have any suggestions for a resource/book to help with writing basic code focusing on ranking?
0
 

Author Comment

by:PushtiLeela
ID: 36581374
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 36581896
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
 

Author Comment

by:PushtiLeela
ID: 36581976
Thank You.
I am using Basic syntax.
As you can see, I'm still learning the basics.
0
 

Author Closing Comment

by:PushtiLeela
ID: 36581994
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now