I don't mean to butt in...
Crystal is not my first choice for record selection. If you can write a stored proc, you will find Crystal is much easier to use, as you can your DB to do the heavy lifting of gathering the data that you want to display.
In this case, taking an EndDate, I would create a temp table with the itemnumber, thisYear, lastYear...keeping it simple for the example.
I would do something like:
select partNumber, sum(sold) as thisYear, 0 as lastYear into #compare from tableA group by partNumber where soldDate between datediff(year, -1, @dateEnd) AND @dateEnd
Then
Update #compare
set lastYear = ss.sold
from #compare as c
join (select partNumber, sum(sold) from tableA group by partNumber where soldDate between dateadd(year, -2, @endDate) and dateadd(year, -1, @endDate)) as ss On ss.partNumber = c.partNumber
select * from #compare
What your goal is, for Crystal/any reporting tool, is to get all the data that you need to display in one row of data.
In a stored proc/view, you can easily accomplish this. Joining straight to tables in Crystal, is possible, but so much more difficult, and it might not be possible as you would want to join on different date ranges.
If you are advanced at SQL, leverage that skill and the database software to gather what you need, rather than coercing Crystal to do it. Let SQL do the data retrieval and Crystal the data display....play to each parts strengths.
Hope this helps.
IP Logged
