Year to date & Last Year to date (spanning over 2 calendar years) 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 Sequel view to select the following data
Date, 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 a user specifing the begin dates and end dates i.e.(Sept 2004 - March 2005 or to any date) I should display the current reporting year's amount (sept04 -March05) and previous year's amount (sept03 -March04) and difference alongside each other. How do i achieve this?

SAMPLE OUTPUT...
(Sept04-march05) (sept03-march04)
Current Year Previous Year Diff
INCOME: Beef 12,000 10,000 2,000
EXPENSES: Beef 5,000 4,000 1,000

Ps: Currently am using a record selection formula and record selection that works only from Jan05 -march05 ( gives all values for 2004 and 2003 as NULL)

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

current record selection formula...
(
(
{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)
)
)

.. and my formulas are:

YTD AMOUNT
if {table.date} in YearToDate and {table.date} <Minimum(MonthToDate) then {table.amount} else 0

LYTD AMOUNT
if {table.date} in LastYearYTD and {table.date} <Minimum(LastYearMTD) then {table.amount}else 0


Please assist!

 

[Madawc]

Use formula fields to define the prior dates by subtracting whole years: e.g.
@PriorStart

DateAdd("yyyy", -1, {?sdate})

@PriorEnd

DateAdd("yyyy", -1, {?edate})

Then select

{table.date} in [{?sdate} to {?edate}]
or
{table.date} in [@PriorStart to @PriorEnd]

Madawc Williams (East Anglia, UK)

 

[kagee]

Thanks Madawc for the comment,

tried the stuff but didnt work - still shows ZEROz for last years values yet the DB had data.

Where do i put the code. In the record selection formula or in the formula that i place in the form.

Please assist!

 

[Madawc]

The first two should go as formula fields. The third in the record selection.

Try displaying the formula fields, see if they show what thye should.

Madawc Williams (East Anglia, UK)

 

[kagee]

Still not working... all figures from Sept-Dec 2004 show 0 whereas those from Jan-march 05 have the correct values

Just wondering; Is there something wrong with the two formulas i am using:

YTD AMOUNT
if {table.date} in YearToDate and {table.date} <Minimum(MonthToDate) then {table.amount} else 0

LYTD AMOUNT
if {table.date} in LastYearYTD and {table.date} <Minimum(LastYearMTD) then {table.amount}else 0

In my report, am using above the two formulas in my detailed section and making the necessary group summaries from them.

Please assist!

 

[Madawc]

Try running totals. Add conditionally, using the formuals I showed earlier.

Madawc Williams (East Anglia, UK)

 

[kagee]

Tried and cant quite figure it out, any directions
(am rather new to Crystal and seriously in need for training though can't afford it)
- Forums my only way out ..

Pls assist!

 

[Madawc]

There are several ways to find totals: running totals, summary totals, grand totals and variables. Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total. Or else use the Field Explorer, the icon that is a grid-like box.

It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

Within a running total, the second section is Evaluate, and includes the option to use a formula.

Madawc Williams (East Anglia, UK)