Skip to Content

Crosstab Report Column by Month

Jan 16 at 04:01 PM


avatar image

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



10 |10000 characters needed characters left characters exceeded


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?



* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abhilash Kumar
Jan 16 at 06:08 PM

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.


10 |10000 characters needed characters left characters exceeded