Duplicate records in Count

[CLKCLK]

I am working with a Magic database used for Help Desk Ticketing. I am trying to get a count of closed tickets by our tier2 help desk without being sent to Tier 3. I say without being sent to Tier3 because when an issue is escalated to Tier 3 a work order is created. After the Work Order is closed by tier 3 then Tier 2 closes the Ticket. The problem I am having is the only way to see if a Work Order has been created is to look at the Ticket Details Table and say Not (Action_ID = WO_OPEN)this causes me to get duplicate records with every other action in the details table for each ticket #

This is the forumla Tier2Count I am using for my count.

IF NOT ({Details.Action ID} = "WO_OPEN")
and {Closed Group:} = "TIER2"
then
1
else
0

Then I insert a summary of Tier2Count.

I have tried a solution I found on this site
this in a running total
onfirstrecord or
(
{Incident.Incident #} <> previous ({Incident.Incident #})
)

and

{Incident.Incident #} = previous({Incident.Incident #})
in the section expert for the detail section suppression.

It has worked for me in the past but not on this particular formula.

Any advice will be appreciated.

Thanks-

CLK

 

[lbass]

I would approach this differently. Your formula is just checking for records that aren't WO_OPen--it doesn't mean that that record doesn't exist.

Instead use a formula like this:

//{@WO_Open}:
IF {Details.Action ID} = "WO_OPEN" then
1

Then insert a running total distinctcount of ticketID, with an evaluation formula like this:

sum({@WO_Open},{table.ticketID}) = 0 and
{table.closedgroup} = "Tier 2"

-LB

 

[CLKCLK]

The tickets with work orders are showing up in both counts. The ones at the bottom have Work Orders, but they are also showing up in the zero count for no work orders.

1,359,951
0.00
1,359,959
0.00
1,360,039
0.00
1,360,133
0.00
1,360,248
0.00
1,360,373
0.00
1,360,386
0.00
1,360,566
0.00
1,360,611
0.00

These have work orders
1,359,951
1.00
1,359,959
1.00
1,360,248
1.00
1,360,373
1.00

Any thoughts on how to fix this. The count should be 5 in this case. These are the tickets without work orders associated with them
1,360,611
1,360,566
1,360,386
1,360,133
1,360,039

CLK

 

[lbass]

You need to display a sample of detail level results that shows the values of the fields for tickets and the work order open field. The running total should be placed in the group footer for tickets. You should also identify your groups (the fields you are grouping on). Are you saying the tickets appear in more than one group?

-LB

 

[CLKCLK]

This running total required me to group by @WO_OPEN and table.ticketID

sum({@WO_Open},{table.ticketID}) = 0 and
{table.closedgroup} = "Tier 2"

On the WO_OPEN group is assigning a 0 or a 1 to the tickets. That is what the 1 and 0 is in the post above and they WO are showing up in both groups.

thanks

CK

 

[lbass]

No, that running total requires only a group on ticketID.

-LB

 

[CLKCLK]

Here is the detail section. All tickets have the action ID HD_Open, it is system generated when a ticket is created.

Incident # WO_OPEN Action ID
1,359,951 0.00 HD_OPEN
1,359,959 0.00 HD_OPEN
1,360,039 0.00 HD_OPEN
1,360,133 0.00 HD_OPEN
1,360,248 0.00 HD_OPEN
1,360,373 0.00 HD_OPEN
1,360,386 0.00 HD_OPEN
1,360,566 0.00 HD_OPEN
1,360,611 0.00 HD_OPEN

1,359,951 1.00 WO_OPEN
1,359,959 1.00 WO_OPEN
1,360,248 1.00 WO_OPEN
1,360,373 1.00 WO_OPEN

 

[CLKCLK]

I changed the group to just group by TicketID, but it is still showing all 9 records even though 4 of those have work orders opened.

CK

 

[CLKCLK]

It is working now. When I supressed the details and put the running total in the report footer it works.

Thanks so much LB