Summary of Assigned_UID vs Assigned Calls-CR9 1

[JoyCR9]

I want a report that lists our CALL TRACKING stats in a summary format. The database is on a help desk.

I am working with the following tables:
SERVICE_CALL
SERVICE_CALL_EVENT

Table SERVICE_CALL has the following fields which I use:
SERVICE_CALL.ASSIGNED_UID (tech that was assigned the call)
SERVICE_CALL.CREATE_DT (when the call was opened)
SERVICE_CALL.SERVICE_CALL_ID (call number)

Table SERVICE_CALL_EVENT has the following field:
SERVICE_CALL_EVENT.SERVICE_CALL_EVENT_TYPE ---this can be equal to many things. The TYPE I care about is CLOSED

I want column headers of:

1.Assigned_uid
2. #of new calls/assigned_uid
3. #of new&closed calls/assigned uid
4. Percentage of calls closed/assigned uid

My row will be:
assigned_uid

I thought the following formula would give me the number of closed calls per uid:
if {SERVICE_CALL.SVC_CALL_STATE}="CLOSED"
then DistinctCount ({SERVICE_CALL.SERVICE_CALL_ID})

I have GROUP ONE being ASSIGNED_UID and GROUP TWO being SERVICE_CALL_EVENT.SERVICE_CALL_EVENT_TYPE


With the above formula inserted in the assigned uid group, I get the ALL uids, not a tech by tech breakdown. ie: if there were 14 calls in the week for ALL techs, I get 14 in each ASSIGNED_UID not the actual number of calls each tech closed.

I hope that makes sense. I need assistance, again, with formula writing.

Thanks,
Joy

 

[JoyCR9]

Sample Data: (sorry forgot to put it in earlier)

UID #new Calls #closed %closed
Joy 5 5 100
Al 3 1 33
Mike 2 1 50

 

[lbass]

Your formula should look like:

if {SERVICE_CALL.SVC_CALL_STATE}="CLOSED"
then 1 else 0

Then right click on the formula and choose insert summary and choose "sum" for every group. This should work unless you have duplicates. If you have duplicates, you should show sample detail level data that demonstrates the way data is duplicating.

-LB

 

[JoyCR9]

Perfect...again, I was trying to make things harder than they need be.

My next challenge is to add a column for call from previous weeks that are still open (a summary)

For eg: with the sample info I gave earlier:
UID #new Calls #closed %closed
Joy 5 5 100
Al 3 1 33
Mike 2 1 50

I want to add a column: Calls open from previous weeks:

UID #new Calls #closed %closed PrevWksStillOpen
Joy 5 5 100 3
Al 3 1 33 2
Mike 2 1 50 2

I can add it as a subreport appearing below what I currently have, but can't figure out how to have it appear beside what I currently have.

 

[lbass]

I guess you decided to use a subreport because of the different date criteria? You could do this without a subreport by building the date criteria into formulas for each column, but maybe a subreport is just as easy.

If you have grouped your main report by UID, then group your subreport by UID. Suppress all but the group footer in the subreport, link the subreport by UID to the main report, and place it in the group header or footer for UID, wherever you have your summary fields.

-LB

 

[JoyCR9]

I was hoping to have the info appear as an additional column not a subreport.

 

[lbass]

If you followed my suggestion, you could place the subreport next to your other group summary fields so that it appears as another column. Maybe you should explain more.

If you don't want to use a subreport, then you have to expand your record selection formula to include dates from the previous weeks, and then each group summary would be based on summarizing detail level formulas like:

if {SERVICE_CALL.SVC_CALL_STATE}="CLOSED" and
{SERVICE_CALL.CREATE_DT}) in lastfullweek then
1 else 0

You could handle the week definitions in different ways and you haven't indicated how you have done that in your report, so perhaps you could share that.

-LB

 

[JoyCR9]

I didn't know I could use subreports in that manner (in group footers). Always thought they had to be in report footers.

Thanks a bunch!