Difficult Date Grouping 2

[paddydog]

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...

 

[hilfy]

Have you tried adding a second reference to your table with an alias ("table2" below). You then link from the original ("table1" below) to the aliased table on contract and reference number. You'll do your calculations based on table1.Date_Added and table2.Date_Removed.

-Dell

 

[paddydog]

hilfy - thanks for trying, but that doesn't work either. I still get a detail section that has only one instance of the contract reference with the start- and end-dates (where applicable).

Obviously I need a list of start- and end-dates with reference numbers attached. Are there any of the link types or other settings I could change to force the right result?

 

[synapsevampire]

You might create a Union query either within the Database->SHOW SQL QUERY or by using a View or Stored Procedure on the database to return the proper data.

I'll assume SQL Database means SQL Server, SQL is a programming language common to most databases.

create view MyView as
select RefNo, DateAdded, 'Add' Type from table
union all
select RefNo, DateRemoved, 'Remove' Type from table

This will give you a list of all reference numbers and dates in a single column with a Type in case you need that.

Now you can use this as the main data source in the report to get all of th ref numbers and dates, then join it to your other tables or include them in the UNION query.

Hopefully this will get you there.

-k

 

[paddydog]

Mr vampire,

I would like to try your suggestion - sounds promising - but for some reason the 'Show SQL Query' option is greyed out on my menu. Any idea why that might be?

For info., CR version is Full Professional, would this make a difference.

Re. you comment, the DB is SQL Server, my mistake.

Thanks again...

paddydog

 

[paddydog]

Follow-up:

I've created a new, basic version of my original report and can now see the SQL statement it creates.

Now I'm stuck again. I don't know what to do next. Should I be replacing the full statement with the following? And what should the 'Add' Type and 'Remove' Type bit do?

create view MyView as
select RefNo, DateAdded, 'Add' Type from table
union all
select RefNo, DateRemoved, 'Remove' Type from table

(I realise I have to change the table/field names, etc)

If I do this, I get a 'not supported' message back.

Would appreciate some expansion on synapsevampire's suggestion. I'm sure it will work but I'm not clear on how to implement it (I'm just an accountant!)

cheers...