paddydog
September 9th, 2004, 08:54 AM
I'm new here, hello all, hope someone can help me!
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. However, 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, rather than Jaunuary 2004 group. The same would happen if I grouped on end-date.
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...
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. However, 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, rather than Jaunuary 2004 group. The same would happen if I grouped on end-date.
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...