Month by Month reporting ...

[StevenK]

I'm working against a database that has a stock balance record at the beginning of each month - when a stock code has a quantity against it (in stock).
If there is no stock at a given month start there would be no record - so I might have the following entries in the table STOCK_TRANS:

01/02/2006 1000
01/03/2006 600
01/05/2006 200
01/06/2006 250

This would mean as of 01/02/2006 I had 1000 in stock, at 01/03/2006 I had 600. Then on 01/04/2006 I had zero in stock (the database works in such a way that it does not create a balance record here as this quantity is zero).
Then on 01/05/2006 I had a quantity again - at 200, then 250 as of 01/06/2006.
The backend database is SQL server.

What I would like to be able to do in my Crystal Report is to have 5 entries from the above - effectively creating a dummy record for the missing entry showing a zero quantity against it (as I need to create a subreport of transaction movements also - but this I can work out later).

Can this be done using the data as above?

Is there some other means in Crystal to generate a month-by-month situation?

It's been a long while since I've worked with Crystal so I'm struggling to recall what is and isn't possible with this.

Any help / pointers would be appreciated.
Thanks in advance,
Steve

 

[Madawc]

A crosstab might be suitbale. As long as there is something in each row and column, the other 'cells' can be blank. WOuld that be OK?

Failing that, you could do a 'mock crosstab', a running total for each relevant month. (Very tedious to write, of course.)

You should give your Crystal version.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

PS. If your database includes a simple list of dates, or something that always has an entry for the month, you could link that left-outer to stock.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[StevenK]

I resolved this by creating a cross reference (VIEW) of all stock codes and all possible dates (first of each month) - which I then anchor'ed out from to set up subreports for the :
- balance record
- transaction / movement records

This has now been set up and appears to operate successfully.

Thanks for the pointers.
Regards,
Steve