Year to date & Last Year to date Records selection in Crystal 1

[kagee]

Hi there,

Am stuck with a major financial reports and need some technical assistance:

Crystal version: 8.5
Database and connectivity: SQL Server

Sample data:
Am using a view to select the following data
Account No, Account Desc, Amounts

The SQL view is able to return both income amounts and expenses amounts. The main difference between the incomes and the expenses is the account No. (incomes are '61110*' while expenses are '61190*')

Expected output:
I need to achieve the following.

1. By specifing the begin dates and end dates (Jan-date) I should display the current year's amount and previous year's amount and difference alongside each other. How do i achieve this?

e.g account Desc current Year previous Year Diff
Beef 120 100 20
Chicken 200 175 25


2. How do I build a formula that is able to separate income amounts from expenses amounts and show the SUMS of the two in different lines
e.g
current Year previous Year Diff
INCOME: Beef 12,000 10,000 2,000
EXPENSES: Beef 5,000 4,000 1,000

Currently am using an IF statement that doesn't work
IF {table.Account}like "61110*" then SUM({table.amount}) else 0

--the IF returns the whole SUM of both incomes & expenses yet i want total of INCOMES alone!

3. The dates in my view are currently hard-wired (jan-date). How do i make it dynamic to allow users select the start date and end dates and still display current year and previous year's data as described above?


Please assist

MIKE

 

[kskid]

When you say the the user specifies the start and end date, what do you want for YTD if the start and end date spans different years (i.e. start date = 12/15/2004 and end date = 1/20/2005? Or will always be the 1st and last day of the month?

Or do you want to report everything through the last full month or current date based on when the report is refreshed?

I use 8.5 against an Oracle database and 90% of my reports involving YTD summaries are through the last full month. With that assumption, then your record selection would include

(
(
{table.date} in YearToDate and
{table.date} < Minimum(MonthToDate)
) or
({table.date} in LastYearYTD and
{table.date} < Minimum(LastYearMTD)
)
)

Depending on where you want the totals to appear, you can create running totals or formulas to perform the calculations. Are you wanting detail and summary information? You were too vague on this part.

-LW

 

[kagee]

Thanks Kskid for your comments..

The year starts 1 Sept and runs to 31 August. The report can be run any day ( but i'd prefer to work with end-of-month dates)

Am very new to Crystal but my boss doesnt want to pay for training and i must do this thing else am sunk!

I got a view that pulls out all the data i need for the entire period. (date, accountNo., Accountdesc, Amount)
But i havent got a way to split it so that it shows values for last year in column 1 and current year values in column 2 ( the difference column is a formula)

Also, is it advisable to use a view in this case, or should i switch to stored procedures?

Ps Assist.

MIKE

 

[kagee]

Hi again,

I was following some answers you gave to shummy1 on YTD and LYTD and have managed to separate the two columns. Thanks man! But am still having problems....
======
How do i get the user to specify both the start date(month & year) and end dates. (the start month need not be Jan)

Currently am using a parameter ?date:
(
{table.date} in [dateserial(year({?date}),
1,
1) to
dateserial(Year({?date)),
Month({?date})+1,
1-1)
) or
(
{table.date} in [dateserial(year({?date})-1,
1,
1) to
dateserial(Year({?date))-1,
Month({?date})+1,
1-1)
)
)

======
Also, my data shows in two rows yet i want it in one row:
LYTD(2004) YTD(2005)
Beef Jan 2004 120 0
Beef Jan 2005 0 150

I want it in one row:
LYTD(2004) YTD(2005)
Beef Jan 120 150

==================

Thanks,
MIKE

 

[kskid]

Try this.

{?sdate} = parameter name of start date
{?edate} = parameter name of end date


(
(
{table.date} in [dateserial(year({?sdate}),
month({?sdate}),
1) to
dateserial(Year({?edate)),
Month({?edate})+1,
1-1)
) or
(
{table.date} in [dateserial(year({?sdate})-1,
month({?sdate}),
1) to
dateserial(Year({?edate))-1,
Month({?edate})+1,
1-1)
)
)

Cheers,
-LW

 

[MrBillSC]

To resolve your two rows problem (one for current year and one for last year), group on month but not year. Create a formula for current year:
If {@recordYr} = {@currentYr} then {table.expenamt} else 0

Then create a similar formula for previous year.
Sum the two formulas in the month group footer.
MrBill