Calc MTD and YTD Totals 1

[IceRuby]

ODBC connection to SQL 2000 database. Using Crystal Version 11

I have created a report to display pay transaction details, these grouped as

Group 1 Pay Entity
Group 2 Grouping Formula –
Taxed Earnings, Untaxed Earnings, SGC, Net Pay
Group 3 Grouping Formula –
Codes (interchange time & deduction codes)

Report data limited by following Record Selection:-
{payroll_transaction.processed_period} in
datetime(2005,07,01) to {?Period End Date}

The end user supplies Pay Period End Date to be reported. This providing detail of last record to be reported up to.

FYI - each payroll transaction is flagged with payroll processing period. Therefore have linked
{payroll_transaction.processed_period} = {?Period End Date}

The report requires Current Pay Totals, MTD Totals and YTD Totals

The Current Pay Total is determined by running total summing payroll transaction.amount when
{t510_payroll_transaction.t510f015_processed_period} =
{?Period End Date}

My issue is the MTD and YTD Totals.
For MTD Totals, I need to calculate values for same month that has been selected within {?Period End Date} e.g if 18/08/05 entered then MTD should be all pays up to 18 Aug 05.

Same for YTD Totals, need to calculate values from 01/07/05 (beginning fin year) up to {?Period End Date} e.g 01/07/05 - 18/08/05

Initially I was utilising MonthToDate function but this no longer works due to end users now wanting ability to run report at any period.
e.g If {payroll_transaction.processed_period} in MonthToDate
Then {t510_payroll_transaction.t510f140_amount}
Else 0


REPORT EXAMPLE - Pay Period End Date 18/8/05
CURRENT PAY MTD YTD
TOTALS TOTALS TOTALS

#GRP1 PAY ENTITY 001
#GRP 2 TAX EARNINGS
#GRP 3 Code 1 {?Pay end date} All between All between
running total 1/8-18/8/05 1/7-18/8/05
#GRP 3 Code 2 459.99 459.99 459.99
#GRP 3 Code 3 125.66 250.66 500.00
#GRP 3 Code 4 0.00 0.00
#GRP 3 Code 5 1.50 59.88

Appreciate your help.

 

[lbass]

For month to date, try:

if {payroll_transaction.processed_period} in
{?Period End Date}-day({?Period End Date})+1 to {?Period End Date} Then
{t510_payroll_transaction.t510f140_amount}
Else 0

For year to date, since you are already limiting records to those which are year to date, you can just add {t510_payroll_transaction.t510f140_amount}. For both the month to date formula and the year to date value, insert summaries at the various group levels.

-LB