Crosstab YTD query

[h20vrrq]

Hi,

I need to write a report that shows Jan to Current month and must show all months even if there is no data for it. The data itself is taken from two tables with a straightforward 1-to-1 relationship. The tables are Requests_Received and Offers_Made. There will always be a request but not always an offer as they are sometimes declined.

So for Year to date, grouped by month I need to calculate the following (all in one report): -

1) From the Requests table there is a field called date_of_request and I need to know how many requests made per month

2) From the Offers table, there is a field offer_date and I need to know how many offers have been made per month

3) From the offers table, there is a field date_cheque_sent and I need to know how many cheques sent per month

4) Again from the Offers table there is a field called value_of_payment and need to calculate total payments per month

5) A running total of payments made per month.

I'm ok in creating these 5 figures as individual reports and for where there is existing data (if no data in March, then March would not appear on report), but I dont know where to start to generate a report that will always have the months displayed YTD (even if no data) and also haw to calculate the required figures when based on different date fields in different tables (a request could be made in january, offer made in february and finally paid in march, how do I handle this?).

Any help is greatly appreciated.

Thanks,

 

[Madawc]

You should be able to do most of this using a crosstab. It depends a bit on your Crystal version, which you always should give when asking a question.

A crosstab won't give a column when there is no data. One way round is to have a dataset for dates and left-outer link to the real data.

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

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