The classic solution to this problem is to use one of a couple SQL tricks. Note that this does mean that you either need the ability to modify the original database, or you need to be able to use the Add Command function when selecting your datasource.
The simplest and most efficient solution is to create a calendar table in your database. This is a very simple table, which simply holds the months of the year (well, specifically, the first day of each month, as it needs to be an actual date value). Create an outer join between your data and the calendar table, with the join condition being something like MONTH(Calendar.MyMonth) = MONTH(MyData.MyDate). This will return NULL values for any month for which you do not have data.
Another option is to force records through the use of a UNION query. You can do this through the Add Command function. Your SQL would look something like:
SELECT blah1, blah2, blah3, MyDate
FROM MyData
UNION ALL
SELECT ' ' AS blah1, 0 AS blah2, ' ' AS blah3, '01/01/2007' AS MyDate
FROM MyData
WHERE {insert criteria to ensure only one record returned}
UNION ALL
SELECT ' ' AS blah1, 0 AS blah2, ' ' AS blah3, '02/01/2007' AS MyDate
FROM MyData
WHERE {insert criteria to ensure only one record returned}
UNION ALL
SELECT ' ' AS blah1, 0 AS blah2, ' ' AS blah3, '03/01/2007' AS MyDate
FROM MyData
WHERE {insert criteria to ensure only one record returned}
et cetera, et cetera, et cetera...
This is clumsy, and it creates a series of blank records which you have to manage. But, it will give you a record for each month, as you need.
IP Logged
