Sorting YTD and LYTD values by month

[kagee]

Hi,

Am using Crystal 10 and SQL Server. Each of my reports consist of 3 sub-reports.

Problem at Hand
How do I group YTD values and LYTD values line-by-line per month irrespective of the year?

Current Report Structure:

Group1 : ---> Shows overrall total
Group2 : ---> shows sum of detailed values per month e.g.

MONTH YTD LYTD
Jan 2004 0 50
Feb 2004 0 75
Mar 2004 0 65
Apr 2004 0 99
Jan 2004 120 0
Feb 2004 156 0
Mar 2004 180 0

Details section ---> supressed
This format leads to LONG reports and users can hardly compare the monthly values!

Required Report Structure:
I need to achieve a strucure where its easy to do a monthly comparison of the figures, with little or no user scrolling...

Group2 : ---> shows sum of detailed values per month e.g.

MONTH YTD LYTD
Jan 2004/5 120 50
Feb 2004/5 156 75
Mar 2004/5 180 65
Apr 2004/5 0 99

Details section ---> supressed.

Please assist.

 

[GJParker]

The specifics of this depend on the format your dates are in I f the dates are proper dates then you need to create a group on a formula of months

i.e.

//@MonthNo
Month ({MyTable.DateField})

Create a group on this formula and on the options tab customise the group name field using a formula

MonthName ({@MonthNo})

You will then need to create 2 formulas

//@YTD
If Year(MyTable.Date}) = Year(CurrentDate) Then
{MyTable.Value}
Else
0

//@LYTD
If Year(MyTable.Date}) = Year(CurrentDate)-1 Then
{MyTable.Value}
Else
0

You can then insert summaries on these 2 formulas to show totals for group 2

HTH










Gary Parker
MIS Data Analyst
Manchester, England

 

[dsWA]

Hi,

First group by month regardless of year. You can do this by using a formula to pull the month value from the date, or you may already have this as a separate field.

Once that is done, create two new formulae. One to return a value if year=LY and one to return a value for Current Year, as follows:

For Last Year formula

if {table.year} = 2004 then {table.value} else 0

and Current Year formula

if {table.year} = 2005 then {table.value} else 0

Now summarize each formula at the group level.
You can make it more dynamic by changing the code to be something like:

if {table.year} = (Year(CurrentDate()) - 1) then {table.value} else 0

and

if {table.year} = Year(CurrentDate()) then {table.value} else 0