Skip to Content
avatar image
Former Member

Crosstab Report Column by Month

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

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    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

  • Get RSS Feed

1 Answer

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

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded