I tried creating a seperate table that just had month values from now ontill 2010 and then I outter joined the two and created a crosstab query, but there were two problems: a blank project would be listed (so that all months would show up) and I couldn't sort the column headings appropriately 'MMM-YYYY' looks like Jan-2005;Jan-2006;Jan-2007;etc...
Let me know if I need to clarify anything? Thanks in advance for your thoughts/help!
If you format your months as 2005-01, 2005-02...2005-12 etc then they will sort correctly. You could get a query to generate this format from your current month field quite easily (create as a text field).
If I understand your other problem, you want to see a complete set of months regardless of whether there is any data ? If you create a full set of dummy data:
Then run your cross-tab and then filter out the dummy line.
Well I don't think it's possible to filter at the report level? But what I did figure out is that I can filter out the dummy project with another query utilizing the crosstab query as the source...
The next trick part is to ensure that the Dates being used are always accurate, as we run on different fiscal months here and there is no access to the calendar electronically. I'll just have to store the fiscal month as user input and run off of that.