Data being counted multiple times when grouped

[pfheg67]

Using Crystal XI with ODBC. Trying to create a report that Groups call tickets open for each group. The problem is it is counting tickets multiple times in each group because the call # is not unique.
I have 2 GH's
GH1 = {Asgnmnt.GroupName} GH2 is {Calllog.Callid} - i have nothing in details. most data in GH2

My running totals by group are off because it counts 1 ticket multiple times if their are multiple assignments but I want to use {Asgnmnt.ResolveOrder} which would have to be a max but it would have to be a max per ticket or i can also use max {Asgnmnt.DateAssign}&{Asgnmnt.TimeAssign} or there also appears to be unique called {Asgnmnt.HEATSeq} which we could probably use. problem is i can't get any of them to work

My output looks like this

Vendors
102
300
325

Systems
300

Helpdesk
325

I am new to crystal and this is most likely more advanced than what i know but i have tried everything and cannot get the data to appear correctly.

The ticket should only appear once. i have tried a max( Asgmnt.Resolveorder) in selection formula group along with many other different things. tried select distinct( no luck)

Is there a simple solution that someone can help me with. Hopefully i gave you enough detail or explained what i am looking to do.

thanks in advance

 

[lbass]

Please show some sample data at the detail level that shows all the fields you reference and labels them.

-LB

 

[pfheg67]

Current Open Tickets
CallType Category CallID
(GH2)
Description: WIC (E) PC{calllog.category} Miscellaneous {Calllog.Calltype} 00000102{Calllog.Callid}
Description: WIC (E) PC{Calllog.Calltype} PC{calllog.category} 11/20/2009 00000300{Calllog.Callid}
Description: WIC (E) PC{Calllog.Calltype} PC{calllog.category} 11/20/2009 00000325{Calllog.Callid}

(GH1) L2 - Vendors{Asgnmt.GroupName} : 3

Description: WIC (E) PC PC 11/20/2009 00000300
L2 - Systems{Asgnmt.GroupName} : 1

Description: WIC (E) PC PC 11/20/2009 00000325
L1 - Helpdesk{Asgnmt.GroupName} : 1

Total Open Tickets: 3


I just want the summary but i included the detail so you could see it. I have to have a way to evaluate either a field called Max{Asgnmnt.ResolveOrder} or Max (Asgnmnt.AssignDate. i have tried countless different things and nothing works. i have added and removed so many different things in an out of this report I can't possiblely show you everything i have tried.

Basically above - call id 300 and 325 should only appear in 1 group not multiple

I hope this information is enough and it becomes difficult to explain and show.

thank you ahead of time

 

[lbass]

I can't tell what you are showing me, and you haven't shown the two fields you want summarized. What are your groupfields? The L1 and L2 are confusing. Why not just lay it out like this:
ID Name Amt Date
GH1: Company 1
GH2: Department A
Detail: 123 John 24 12/23/2009
456 Sue 13 12/24/2009

You need to show how the fields you want summarized behave in relation to the groups and to the fields you think might be relevant to the running totals so we can see what the problem is.

-LB

 

[lbass]

The problem here is that you are not communicating your field names clearly. You say you want the most recent field per ticket, but I don't know which field is the ticket field. Since a ticket can have two kinds of dates, which date should be used for the most recent?

-LB

 

[pfheg67]

LB

i am working on it. I did some more research last night and found some more specific info in some threads that you had been a part of that might be helpful. I will get back to you asap. I usually am not alloted much time to be working on this so i apologize for the delay in responding

Thanks

 

[pfheg67]

Ok - lets try this again.

Category Type Call id Recv Date
GH1: GroupName(ex: HELPDESK)
GH2: Call id# (ex: 00000300)
Detail WIC PC 00000300 11/20/09

what is happenign is the Call id is being pulled under multiple group names. So ticket 00000300 will show in the HELPDESK group and the SYSTEMS group. The maximum resolve order {Asgnmnt.ResolveOrder} will equal the the current owner of the ticket so I need to do something with that table. I have tried so many different things my head is spinning. I most recently tried something from a thread you had worked with which was very similar but minus the GH1. . I did group - selection formula - group
{Asgnmnt.ResolveOrder} = maximum ({Asgnmnt.ResolveOrder},{CallLog.CallID})

It still duplicated.

look at thread thread767-1452873. basically the same issue but I need to have it break on group name. Call id# should only appear under 1 group

I hope this helps you understand my problem. i apologize if it doesn't. If not i am not sure how else i can explain it. honestly - i am about to give up on it because it shouldn't be this difficult.

have a great holiday and thanks again

 

[lbass]

I can't really work on this right now, but I would need to know the tables you are using, how they are linked and on what fields. When you refer to fields, please identify them like this: {table.field}.

-LB

 

[pfheg67]

no sweat - thanks again.

I will put together the information you requested.

Pfheg67

 

[pfheg67]

I am out of the office but I know the 2 tables i am using are Asgmnt and Calllog. They are linked by whatever crystal uses as default link( i would have to verify) and the only linked field is {table.callid}

Also - i wanted you to understand my Details listed above is included in GH2 not seperated into the details section.

thanks again for trying to assist

 

[lbass]

From what I can tell, I think you can create a SQL expression (field explorer->SQL expression) like this and call it {%currowner}:

(
select max(`ResolveOrder`)
from Asgnmnt A
where A.`CallID` = Asgnmnt.`CallID`
)

Not sure about the punctuation, since I don't know what database you are using.

Then add the following to your record selection formula (NOT group selection):

{%currowner} = {Asgnmnt.CallID}

-LB

 

[pfheg67]

SQL dbase

 

[pfheg67]

ok - i put this in:

(select max(ResolveOrder)
from Asgnmnt A
where A.CallID = Asgnmnt.CallID)

when i got to the 2nd part to put the formula {%Cowner} = {Asgnmnt.CallID} in select expert i get the following message when i try to ok it.

"A number is required here."

The {Table.CallID} field is a varchar - my assumption is i need to convert something to a number? where should i put that in in the sql stmt.

 

[pfheg67]

my other thought is {table.ResolveOrder} is not read yet so that is why it might be giving us an error. I know when i had tried to using formula's with resolve order it was giving me a message something like

"needs to be evaluated later"

that in turn had let me to the group selection formula's.

on paper it seems so simple but clearly it is not.

appreciate your help

 

[lbass]

I'm sorry, that should have been:

{%Cowner} = {Asgnmnt.ResolveOrder}

-LB

 

[pfheg67]

LB

I think you got it. I have to do some more testing in the am but i will be eternally grateful if this is the resolution.

Happy New Year.

I will update you tomorrow.

Thanks again

 

[pfheg67]

LBass,

sorry for the delay but i have been out of the office.

I wanted to take the time to thank you for helping me wiht this. You sql stmt worked and I was able to role this into other reports.

Thanks again for your diligent efforts.

Patrick