Data Issue

[rafeman]

Hi, I have a report that is needed which gives the summary of sales for each month. In crystal you can do this by grouping by the date field then tick 'The section will be printed for each month'.

My issue is the report needs to also show the months where there is no sales. The transaction table will not have a record for that month at all where zero.

Is there a way of doing this in crystal or will there have to be a SQL data view to mimic the zero value data?

Thanks

 

[TeyBrady]

You'll have to do something in the SQL. Crystal doesn't report on data that isn't there. I think there are some older posts discussing ways of manufacturing the data.

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of DATE set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that any selection on the second table must do an IsNull test first, otherwise you'll not get the dates with no records, null value for the second record.)

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. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria - in your case, month.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[rafeman]

thanks for your replies. Will try adding a table