running total question?

[aconover]

Using Crystal Reports 8.5, I'm running a query from an Informix database. The query consists of
period
billing attorney
timekeeper
client number
invoice number
worked hours

and some others. The result should be for each period there will be an invoice. That invoice will have a dollar amount with multiple worked hour entries for each timekeeper. I want the report to sum the worked hours by timekeeper-clientnumber-invoice-period.

If I run this query right out of Informix (dbaccess) I can select distinct records and sum the worked hours in that fashion. If I try this in Crystal, (summing worked hours), it will sum all worked hours for all timekeepers.

Basically if Joe had two time entries in January (5 hours and 2 hours) for client XYZ, I want the query to sum his hours (7 hours) and not include Nancy who had one time entryof 2 hours for the same month and client.

Here is a copy of the query (not from Crystal)

SELECT DISTINCT
period_yymm.p_yymm,
matter.mbillaty,
timecard.ttk,
ledgfee.lfcode,
timecard.tinvoice,
ledgfee.lfamnt,
timecard.tmatter,
matter.mprac,
matter.mdept,
Sum(timecard.tworkhrs),
timekeep.tkstdcst
FROM
elite.ledger ledger,
elite.ledgfee ledgfee,
elite.matter matter,
elite1.period_yymm period_yymm,
elite.timecard timecard,
elite.timekeep timekeep
WHERE
timecard.tinvoice = ledger.linvoice AND
timecard.tmatter = matter.mmatter AND
timecard.ttk = timekeep.tkinit AND
ledger.lindex = ledgfee.lindex AND
period_yymm.pe = ledger.lperiod AND
((period_yymm.p_yymm>= pd_start And
period_yymm.p_yymm <= pd_end) AND
(ledger.llcode In ('PAY','PAY-LEG','WOFF')) AND
(ledgfee.lfindex Not In (1776,1777,1778,1779,1780,1781)))
GROUP BY
period_yymm.p_yymm,
matter.mbillaty,
timecard.ttk,
ledgfee.lfcode,
timecard.tinvoice,
ledgfee.lfamnt,
timecard.tmatter,
matter.mprac,
matter.mdept,
timekeep.tkstdcst

What I want returned is the sum of worked hours for each timekeeper for each client, for each invoice, for each period. I'm thinking I should be using a running total (which is new to me). But I don't know how to set that up with out setting up groups in Crystal. I wouldn't know how to group this, since I need all the raw data returned and displayed.


Hopefully this makes sense to you. Thank you.

 

[synapsevampire]

Create a View on the Informix database and use that.

-k

 

[aconover]

I'm not familar with that and would rather handle this with Crystal.

 

[Madawc]

Running totals allow for groups but will work without them. And you can select records using formulas.

Another possibility is one or more subreports in the report footer. These could group and summarise with details suppressed, while the raw data is shown in the main report.

Madawc Williams
East Anglia, Great Britain