Solved

Assign running totals based on two fields in a table

Posted on 2011-02-19
2
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Expert Comment

by:msd1305
ID: 34935725
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
ID: 34935755
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 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