Including zero-sales months.

[Frank Martin]

I use Microsoft Graph2000 based on crosstab queries to visualize monthly
sales.

I cannot figure out how to include zero-sales months in the queries (and
hence the charts) and I wonder if there is a standard method for doing this.

Please help, Frank

 

[Dale Fye]

Frank,

If the months are your column headers, then you can use the Column headings
property of the Query to add in the months that have no values.

Open your query in design view, place the cursor in the portion of the view
where the tables are displayed, right click on and select properties.
In the Column headings property type "JAN", "FEB", "MAR", or whatever values
your report currently provides for the months. These values must appear
exactly as they show up in the query, or in the underlying data. If you
misspell one, none of the data for that header will appear.

If the months are the rows, it is a little more difficult. If that is the
case, reply back and I'll give you my best guess on how to format that. If
so, include the SQL for your current query, and a sample of what the output
should look like.

Dale

 

[Frank Martin]

Thank you. I have tried this, but all you get is 12 months. My records go
back to 1999 and my charts can show trends etc.

The crosstab queries I use for these charts have a date format of yyyymm and
so are immortal.

I did solve this problem once by putting a dummy field (which is always = 0)
present in the crosstab so that every month is displayed, but I have
forgotten how I did it, and anyway it was very complicated.

Regards, Frank

 

[Guest]

I have a similar task as above with expense instead of sales. For example I
have a query that shows twenty vehicles with repair invoices. Each Vehicle
form references this query to show a bar chart depicting the repair cost for
that vehicle by month. The month is not a column header just a date field for
each invoice record. The X axis of this chart is Month the Y Axis is Cost.
This works well but the month lable disappears from the X axis if there are
no invoices that fall within that month. I need to keep all the months
showing on the chart to depict the zero value even if there are no invoices
for that month. My query for the chart looks like this:

SELECT (Format([Date],"mmm"" '""yy")) AS Expr1,
Sum(VehicleMaintenance.Total) AS [Monthly Cost]
FROM VehicleMaintenance
GROUP BY (Format([Date],"mmm"" '""yy")), (Year([Date])*12+Month([Date])-1)
ORDER BY (Year([Date])*12+Month([Date])-1);

Any help would be great.
KC