Data Retrieval

[patryn150]

Crystal Reports XI, reporting off of an AS/400

I am reporting how many times our customers accounts have been touched, how many unique accounts, how many unique touches, etc... to try to figure out how many times our customers make contact with us a single time in a rolling 30 days. The problem I am having is keeping track of the individual reps touches without making a rediculous amount of individual running totals.

My grouping is as such:

Customer Account Number
Date/Time/Reason - Rep #1
Date/Time/Reason - Rep #2
Date/Time/Reason - Rep #3
Date/Time/Reason - Rep #4
End Group

What I need to know is the last person to touch the account and keep a running total of that person without making a ton of formulas.

Here's the BIG hitch, I cannot use subreports because this will eventually be embedded into another report that will link by rep ID for a scorecard. My preferred way (and normal way) of handling this would be groupings, but since I need the grouping by account numbers to determine the order of access and who accessed them.

I obviously can't group by user because, by all accounts, I cannot determine whether they were the last one to access an account, at least by any means that I know.

My databases to use are this:

TLOGMSTR
TLACCT (Account Number)
TLUSER (Username)
TLDATE (Date)
TLTIME (Time)
TLNUM# (Transaction Log Number)

TLOGREAS
TLREAS (Main reason)
TRNUM# (Transaction Log Number, links to TLNUM#)

EMPLOYEES
LOGIN (Username, links to TLUSER)


An actual grouping would end up looking like this:

ACCT: 00000000
02/15/2009 14:00 REP#1
02/15/2009 14:02 REP#2
02/15/2009 14:03 REP#1
02/15/2009 14:07 REP#5


Any info that can be given is highly appreciated.

Thanks!

 

[lbass]

Can't really tell what you are trying to do. At first I thought you were trying to tally each contact by user, but then it appears you only want to tally (by rep?) the most recent contact per account?

Are SQL expressions available to you?

-LB

 

[patryn150]

Yes, SQL is available to me.

You are correct on the second statement, only the last contact by rep.

I've banged my head against a wall for 2 days off and on trying to think of different ways to do this, none of which have been successful.

 

[lbass]

Create a SQL expression {%maxtime} like this:

(
select max(`TLTIME`)
from TLOGMSTR A
where A.`TLACCT` = TLOGMSTR.`TLACCT` and
A.`TLDATE` = TLOGMSTR.`TLDATE`
)

Adjust the punctuation to reflect that used for your datasource.

Create a second SQL expression {%maxdate}:

(
select max(`TLDATE`)
from TLOGMSTR A
where A.`TLACCT` = TLOGMSTR.`TLACCT`
)

Then use a record selection formula like this:

{TLOGMSTR.TLTIME} = {%maxtime} and
{TLOGMSTR.TLDATE} = {%maxdate}

This will return one record per account. Then you can just insert a crosstab in the report footer where you add user as the row, and distinctcount of TLACCT as the summary.

-LB

 

[patryn150]

LB -

Thank you for the SQL info, however, that won't work for what I need to do. Also, I did run into something else while working on this, I'm using 2 sets of databases. 1 for our billing system, 1 for our employees, so the "SQL Expression" option available in Crystal isn't allowed, unless you know how to bypass that restriction.

The report has 2 purposes. The first purpose is to tell me how many accounts have been accessed in the last 30 days and how many times each account has been accessed. It will also give me the number of unique accounts and the number of accounts that were taken care of in 1 attempt.

That part works.

The part I'm having a problem with is what I illustrated above, determining who was the last person to speak to an account holder, effectively resolving the issue, within the last 30 days. This has to be done without losing the structure of the report which allows me to determine the above criteria, yet allow me to calculate the totals by individual rep.

As it stands right now, my 1 work around is to have X running totals to calculate, one for each rep that we have. I was truly trying to avoid that, but it's looking like it might be the only option available.

If you've got any other ways to approach this, please let me know.

Thanks for the help thus far!

 

[lbass]

Please note that I asked you whether SQL expressions were available to you before I made my suggestion.

How many reps are there?

-LB

 

[patryn150]

Yeah, I wasn't paying attention to that. I can make it so that SQL expressions 'are' available, but then I start getting people that are outside of our department, and there's no flag in the billing system as to what department there is. Thus the extra database to give us just our department.

Overall, there's 42 reps that I would be reporting on.

BTW, you're a beast for replying so late, or early I guess, depending on where you are.

 

[lbass]

Here is another approach which is even simpler. First create a formula {@datetime}:

datetime({TLOGMSTR.TLDATE},{TLOGMSTR.TLTIME})

Then create a second formula {@User}:

if {@datetime} = maximum({@datetime},{TLOGMSTR.TLACCT}) then
{TLOGMSTR.TLUSER}

Go to report->selection formula->GROUP and enter:

{@datetime} = maximum({@datetime},{TLOGMSTR.TLACCT})

Then insert a crosstab in the report footer that uses {@User} as the row field, and distinctcount of {TLOGMSTR.TLNUM#}.

Is being a "beast" supposedly a good thing?

-LB

 

[patryn150]

LB -

Yes, being a beast is a good thing in this context.

Trying your solution will likely work for the aspect of getting the information of the last person to touch the account, however, I will lose all the running totals of the rest of my information.

Two of them I can get back as I can use just summary->count on TLACCT and a summary->distinct count to give me total touches and total unique accounts. What I will lose is the number of accounts with repeat instances that are not within 10 minutes of the current instance (running total) and the total unique accounts that have had more than 1 contact.

Since the running total evaluates after the group selection, I only get to evaluate on the single line entries which won't give me what I need for the first step of this report.

I tried some manual "whilereading" running totals, but since I have to use checks against previous/next values, I can't do it.

Stab #3?

Thanks!