Selecting specific info from mostly similar records 1

[dwcats67]

I'm using CR 8.5 on SQL Database, and I've only been working with CR for a little over a month. I've searched the forums, but I can't find anything just like this situation.

There's an Agent field (field.agent). I've got one field (field.status) that tells how many seconds an Agent was on a certain phone/work status (Available, Away From Desk, At Lunch, etc..). Another field (field.queue) tells the phone queues he/she is assigned to (Queue1, Queue2, Queue3, etc..). My problem is that each queue shows the same number of minutes on a status when in preview. The details line looks like:

(field.Agent) (field.queue) (field.status)
Agent One Queue One 530 (*)
Agent One Queue Two 530
Agent One Queue Three 530
Agent One Queue One 278 (*)
Agent One Queue Two 278
Agent One Queue Three 278
Agent Two Queue Two 1018 (*)
Agent Two Queue Four 1018
Agent Two Queue Two 439 (*)
Agent Two Queue Four 439

I need to be able to select only the first lines (denoted by (*) above) and keep everything else off the report (no suppression). I've tried grouping by Agent and by Queue and using selection on the queue; however, the problem I have is that different agents are assigned different queues (Agent One's first queue is One, while Agent Two's first queue is Two). I just want the first one listed for each Status field to be printed.

Please let me know if you need any more info, and thank you very much for any responses.

dwcats67

 

[lbass]

If {field.queue} displays as the string you've shown above, I think you'll have to use a group selection formula or record selection, rather than using a subquery in the SQL statement, since you will need to convert the field so that you can order records meaningfully.

You can create a formula {@queue}:

if {field.queue} = "Queue One" then 1 else
if {field.queue} = "Queue Two" then 2 else
if {field.queue} = "Queue Three" then 3 else
if {field.queue} = "Queue Four" then 4 //etc.

Group on {field.agent} and on {field.status} and then go to report->edit selection formula->GROUP and enter:

{@queue} = minimum({@queue},{field.status})

This should return one record per status field.

-LB

 

[dwcats67]

Thank you, lbass, for your answer.

This did, indeed, separate out the first line as requested; however, I had hoped to be able to sum up those numbers for each agent (I see now that I forgot to post that originally). Any suggestions as to how to do that now?

Thank you again.

dwcats67

 

[synapsevampire]

You'll probably get better results if you post example data and expected output.

Do you intend to SUM or COUNT?

This looks like a problem with the database, in that you are getting multiple field.status for each agent, with 1 per field.queue

Since you don't care about which field.queue it is, try just grouping by the agent, insert the status, right click it and select insert summary maximum for the group.

Then to sum this amount, create a formula to perform the sum in the group footer:

whileprintingrecords;
numbervar SumStatus := SumStatus+<insert the maximum that was created from the list of report fields>;

The in the report footer use:
whileprintingrecords;
numbervar SumStatus

-k

 

[lbass]

My interpretation was that dwcats67 wanted the first in the series of queue fields (from lowest value to highest). If that is the case, then to do the calculations, insert a running total using the running total editor. Select the field you want to sum, e.g.,{field.status}, choose sum, evaluate for each record, reset on change of group (Agent). Place this in the Agent group footer (it will not calculate correctly in the group header). If you want to do a grand total across all agents, create a second running total the same way, except change the reset to "Never."

-LB

 

[synapsevampire]

Works the same way, LB, just order by the queue and display in the group header instead.

I think that the idea was to only show one row to avoid dupes, and then sum or count it. Or if they always want Queue 1, they can just hard code that as text. I would just limit the rows in the report using Report->Edit Selection Formula-:Record:

{field.queue} = "Queue One"

That way there aren't any dupes and you can just right click and do sums.

I'm not certain though... Without example data and expected output we're always subject to interpretation of a text description, which almost always results in multiple posts.

-k

 

[dwcats67]

I apologize for the delay in getting back to you.

My intention is to sum the information from each first line that I have indicated with a * (count is not needed).

Here is a more detailed example with info that may be more helpful to you:

{field.agent} {field.queue} {field.status} {field.time}
Aaron L. Tech Support 530 (*) 4:30
Aaron L. Customer Service 530 4:30
Aaron L. Sales 530 4:30
Aaron L. Tech Support 278 (*) 4:46
Aaron L. Customer Service 278 4:46
Aaron L. Sales 278 4:46
Jenna K. Customer Service 1018 (*) 4:28
Jenna K. Inventory 1018 4:28
Jenna K. Customer Service 439 (*) 4:46
Jenna K. Inventory 439 4:46

For Aaron, I need to get to a total of 778 (summing the first lines with the *). Jenna needs to have a sum total of 1457. I can't group by times (Aaron and Jenna both have a call at the same time but for different lengths). I also can't just hard code as "Queue One" because Aaron's first queue (Tech Support) is different than Jenna's first queue (Customer Service).

Nothing I've tried has worked yet. Any more suggestions would be greatly appreciated.

dwcats67

 

[synapsevampire]

Stating that you intend to sum those with an asterisk means nothing from a data standpoint unless you have a field which has an asterisk.

Are you stating that you only want to sum the very first record of the same name and time, and only in the natural order that the rows are returned, even though all of the records have identical values? If you took any one of them, the results would be identical, so this makes no sense.

You also didn't state what you want the output to look like.

Insert a group for the name and then for the time, use the 3 formula method:

Name group header formula:
whileprintingrecords;
numbervar NameTimecount:=0

Time group header formula
whileprintingrecords;
numbervar NameTimecount := NameTimecount+{field.status}

Name group footer formula for display:
whileprintingrecords;
numbervar NameTimecount

-k

 

[lbass]

If your goal is just obtain the sum, then group on {field.agent} and insert a running total using the running total editor. Select {table.status}, sum, evaluate on change of field {field.time} and reset on change of group (agent).

-LB

 

[dwcats67]

lbass,

Your advice was just what I needed. I'd been trying running totals, and the idea of evaluating on change of field just escaped me for some reason (hiding in plain sight). Thank you!

Thank you, too, synapsevampire. I also appreciate your advice.

dwcats67