Need a monthly activity report.

[paddydog]

Using CR 8.5 and SQL database.

My data is laid out as follows, e.g.:

Reference No. Date Added Date Removed
00001 31/12/2002 15/05/2004
00002 01/03/2003 01/07/2004
00003 01/04/2004
00004 01/06/2004 01/09/2004

I'm trying to create a report that looks like this, e.g:

Month Brought Quantity Quantity Net Gain/
(or date) Forward Added Removed Loss

01/01/2004 300 10 -12 -2
01/02/2004 298 22 -15 7
01/03/2004 305 15 -15 0

I'm usually OK with Crystal, and if the data was laid out as a list of gain/loss events with a date attached, I would be fine - just use groups.

I'm stuck on this one though...can anybody point me in the right diection?

Thanks.

 

[Madawc]

Sorry, I don't follow the question. What's stopping you using groups - note that they can be based on a choice of dates: i.e. end date if there is one, or else date added. Amd where are the quantities held?

Madawc Williams (East Anglia)

 

[paddydog]

Madawc,

Thanks for your reply, sorry if I wasn't clear. My point is that the way the data is laid out is preventing me from using groups.

The references are contracts and the dates refer to start- and end-dates. There is no quantity field as such. If I group on start-date and count the number of contracts started in a particular month, the count of end-dates will include only contracts with start-dates in that month.

Perhaps it is better explained with an illustration:

Contract 1 starts 01/01/2004 and ends 01/04/2004.
Contract 2 starts 01/01/2004 and has no end-date yet.

If I group on start-date, my report with count subtotals look like this:

Month B/Fwd Started Ended Gain/Loss
Contract 1 1 -1 0
Contract 2 1 0 1
-------------------------------------------------------
January 2004 0 2 -1 1

The "-1" in the ended column being the contract that doesn't actually finish until 01/04/2004 - I want that "event" to appear and be subtotalled in the April 2004 group. The same would happen if I gouped on end-date. Is this a better explanation?

So far I can pull the report together by exporting the listing to Excel and using formulae or pivot table, I just thought there must be a way to handle this in Crystal. I just can't think how...