• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 994
  • Last Modified:

Show all months in a crosstab, even without data?

So I have a crosstab Crystal Reports 10 that shows sales by months, but I can have a customer who say only has an order in January and June, so the crosstab only shows January and June.  Is there a way to get it to show Jan, Feb, ....Nov, Dec, have the correct valus for January and June, and have $0 for the other months?
0
mtuepker
Asked:
mtuepker
1 Solution
 
peter57rCommented:
CR won't report on non-existent data.

You can do this by creatin a 'months' table containing the month numbers and doing a left join from this table to the current datasource - you will have to use the Add Command option and build the full sql statement in order to join on the month numbers.

Select monthnum, originaltable.*
from months left join originaltable
on months.monthnum = month({originaltable.datefield})
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now