Crosstab Report Column by Month

[former_member526829]

Good Morning All;

I have struggled with this report and need some assistance. I have written an automated Crosstab report in Crystal that would extract data for each month on a rolling prior 6 month period. I have a count of certain criminal offense as the rows and month of occurrence. When there are months of no occurrence then the column is skipped, I would like it to show a "0".

Thanks,

Tim

[former_member526829]

Abhilash,

First thank you for the reply. Since I am wanting to have a rolling monthly calendar, can you help me with a formula or script that would give me each month for a, 10 year period?

Thanks,

Tim

[abhilash_kumar]

Hi Tim,

You would need a 'Calendar' table in the database.

If such a table doesn't exist, you would need to handle this in a SQL query. All we need is a table that inserts ALL days on a rolling 6 month period.

You'd then:

1. 'Left Join' FROM this table to the existing table (that generates report data).

2. Replace the Date fields in the Crosstab with the Date Fields from the Calendar table

3. Replace any Date fields in selection formulas/other formulas with the date field from the Calendar table.

-Abhilash