I posted this in the formulas forum, but didn't get much joy. Thought maybe it was the wrong place to post it, so here goes...
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...