Solved

Assign running totals based on two fields in a table

Posted on 2011-02-19
2
372 Views
Last Modified: 2012-05-11
I have a two tables Employees and Tickets.  An employee can either be the primary owner of a ticket, be a co-owner of ticket, or be both (owns the ticket entirely as both owner and co-owner or have a null co-owner).  If the employee owns the ticket in its entirety, then they get a full ticket count.  If they are either the primary or co-owner, then they are to only receive 1/2 credit.

How should I join my tables and graph the total ticket count for each employee?

EMPLOYEE TABLE
EMPID          NAME
1111            SallyJ
2222            JohnG
3333            DebraT

TICKET TABLE
TICKETID        OWNER        CO-OWNER
1                     1111             null
2                     1111             2222
3                      1111            1111
4                     2222             2222
5                     2222             1111
6                     2222             3333
7                     3333             3333

With the data above, this should be the result:
SallyJ (1111) - 3
JohnG (2222) - 2.5
DebraT(3333) - 1.5
0
Comment
Question by:c0fee
2 Comments
 
LVL 4

Expert Comment

by:msd1305
Comment Utility
Easiest way would be to create 2 running total fields.
1) Calculate all full tickets (name: all_full_tickets)
Field to summarize: TICKETID
Type of summary: Distinct Count
Evaluate -> Use a formula -> isnull(CO-OWNER) or (OWNER = CO-OWNER)
Reset -. On Change of Group - > {TicketTable.Owner}

2) Calculate all half tickets (name: all_half_tickets)
Field to summarize: TICKETID
Type of summary: Distinct Count
Evaluate -> Use a formula -> (not isnull(CO-OWNER)) and (OWNER != CO-OWNER)
Reset -. On Change of Group - > {TicketTable.Owner}

3) Create a formula field "TotalTickets" with the formula    all_full_tickets + (all_half_tickets * 0.5 )
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
That will get the totals but since you used running totals, they can't be used in a graph.

To set this up you need to use a COMMAND as the data source
SQL similar to

SELECT EMPLOYEETABLE.EmpId, EMPLOYEETABLE.Name,
TICKETTABLE.TICKETID , TICKETTABLE.OWNER,  TICKETTABLE.COOWNER          
FROM EMPLOYEETABLE INNER JOIN TICKETTABLE
ON  EMPLOYEETABLE.EmpId = TICKETTABLE.OWNER        
UNION
SELECT EMPLOYEETABLE.EmpId, EMPLOYEETABLE.Name,
TICKETTABLE.TICKETID , TICKETTABLE.COOWNER,  TICKETTABLE.OWNER
FROM EMPLOYEETABLE INNER JOIN TICKETTABLE
ON  EMPLOYEETABLE.EmpId = TICKETTABLE.COOWNER  

In the report add a formula
If IsNull({TICKETTABLE.COOWNER)}) then
    1.0
Else
    0.5

You can then use the EmpId and this formula in the graph

mlmcc
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now