Removing duplicates from count in group footer

[ITCPhil]

Hi again... this is using Crystal Report 10 connecting to Oracle.

This follows a previous thread where I wanted to know how to post a total count for each item in a group. That works very well now but I still have a problem.

What I am doing here is listing stats for each employees which includes stats such as total amount of incidents opened in a day for each employee. Next to that is a column showing the total number of incidents for all employees. This is followed by 2 similar columns that instead report total number of reassigned incidents by employees.

The issue that arises is that when an incident is reassigned more than once, it ends up counting for each reassignment. (Notice that I am using 2 views with a LO join to the incident view and my reassigned view is to the right.)

For all results that is not a group total, I used distinctcount running totals which worked very well. But for my group totals, it does not work that way.

Example : () is what the number should be
Agents: Team total: Reassignment: Team total :
39 128 (126) 0 4 (2)
48 128 (126) 1 4 (2)
39 128 (126) 1 4 (2)

What happens here is that an incident was reassigned 3 times and counts 3 times in both team total areas. Is there a way to make them count only once? I know a running total works, but I can't get it to print the total of the running total in a group footer to appear for every agent can I?

Note- this is already a subreport, but I should be able to make it the main report if necessary.

Thanks in advance,
Phil

 

[synapsevampire]

If you post example data and expected output it will be clearer. Your posting example output, but without knowing the source data, this is guesswork.

How are reassignements represented in the data?

-k

 

[ITCPhil]

I can post example output but not really data as this is just counting how many items are located in a table while not actually touching any of the data in the table .

The report is just columns showing counts, it goes (horizontally)

GH1 Day of Week 1 :
GF2 Agents : (the following items are just columns in this group)
#count of reference numbers (running total)
@Total count of reference numbers
#count of reassigned reference numbers (running total)
@Total count of reference numbers
GH1 Day of Week 2 :
GF2 Agents :
#count of reference numbers (running total)
@Total count of reference numbers
#count of reassigned reference numbers (running total)
@Total count of reference numbers

What happens is say agent 1 opened 30 incidents but one was reassigned 5 times, the Total count will add 35 instead of 30.

 

[synapsevampire]

You can't count things in a table without touching it.

Someone will come along and help you work it out.

-k

 

[lbass]

What do you mean by:

I know a running total works, but I can't get it to print the total of the running total in a group footer to appear for every agent can I?

Please explain why you cannot just use distinctcount in the running total. If you need to count the same ID for different agents, but uniquely per agent, then you probably need to use a variable to accumulate the running total values across agents.

-LB

 

[ITCPhil]

What I need is to show the total for reassignments for each agents, but the only way I have found is to use a running total which of course goes :
Names : Reassignments : Team reassignments :
Joe 2 reassignments 2
Lucie 4 reassignments 6
Tom 0 reassignments 6
Santa 1 reassignments 7


When what I need is to show in the team reassignment is 7 in every line.

What you say of using a variable to accumulate the running total seems a good choice, but will it work when my report is a subreport? Would that not usually call a subreport?

I have been trying different things to remove duplicates... running totals show the results like I posted here- using next or previous does not allow to summarize their results- using a sum({Agent.Reassignments},{Table.DateRecorded}) counts duplicates, etc. <---- the sum is the simplest solution and this is why I am asking if there was a way in printing that to suppress the duplicate

To give a bit more details, I can get these working if I make it in a separate subreport but the report unfortunately has to be sent in Excel and while I was able to get 3 of the subreports to line up in their own cells, I cannot get the one to work as it has to be overlayed on another subreport. What I am trying to do is get this information on the same subreport that is there without overlaying so it will be able to open correctly in Excel.

 

[lbass]

The variable won't work for your purpose either, as the result would only be available in a higher order group footer. Have you tried going to database->select distinct records?

Regarding the use of subreports, why do you need to overlay one of the subreports, but not the others? Where would you be placing these?

-LB

 

[ITCPhil]

Select distinct record? No....that would be too easy.... I have never even seen this option, can you tell I am learning Crystal the hard way?

Ok...I tried that as I wrote this and it works to a degree but the number I get there is different than the number I get when doing the calculation manually. The odd part is that the differnce is maybe 1-5 so it's doing something funny. I will check it out to see where it's getting the extras.

To overlay the reports, I simplified it here, I have 39 columns based on agent phone stats, agent performance stats and agent remedy based stats... I also have Phone, Web and Email requests where I need to calculate the totals, not just phone. I have the incident report and in there I have the 6 totals which don't work properly so if I do them in a different report, I have to overlay over the other subreport to print everything in their own column.

Still though, select distinct record is a good start!

Thanks and happy holidays,
Phil

 

[lbass]

Can't really follow your explanation about the subreport totals and overlaying, but if you have a set number of columns within the subreport, you should be able to place the subreports side by side within the same section.

You could also try to get totals of distinct records by using a command (database->database expert->your datasource->add command), although I'm not sure how that will work with a complex report. Also the syntax to get a count of distinct records varies by datasource (and I'm not familiar with the variations), but you could try something like:

Select
count(distinct table.`reassignmentID`) as cnt, table.`agent`
From
`table`table
Group by
table.`agent`

You would have to link this by agent to the main table. You would then just need to place {command.cnt} in the detail section.

-LB

 

[ITCPhil]

I tried the command, it made the report run extremely slow and after around 30 minutes of not being able to open Crystal it says failed to extend temp rowset beyond 32 or something along those lines (it closed after)

I looked at the 'Select distinct record' some more and for some reason, there is a fraction, maybe 0.05%, of incidents that appear twice but not everywhere. On Monday my phone incidents are off by 4, web is off by 1 and mail is accurate, but on Wednesday, phone is accurate, web is accurate, but mail is off by 1??

 

[lbass]

It just means that you still have some field that duplicates in some cases for every value you are counting. Looks the subreport is the best route, if you can get the layout to work.

-LB

 

[ITCPhil]

I will try that,

thank you and have a merry long holiday weekend!

Phil

 

[damonkimble]

It's not an exact match, but I believe you can follow the same logic as I did on a previous problem as documented below.

damon

http://tek-tips.com/viewthread.cfm?qid=1167857&page=1