I have a problem that I can't solve. I have a report that I do a count on a records in particular groups.
I created a formula that places products into categories. E.g. knife, fork, spoon = Cutlery, Cup, Plate = Crockery.
I then grouped by this formula. I then do a count of records within each group by date. e.g. Cutlery 6, Crockery 10.
It works fine unless there are no records. If I run this for a certain month where there are Cutlery 10, Crockery 0, it actually only shows the Cutlery 10.
I would like this to show that there were actually zero records for crockery.
What I get is :-
What I want is
I hope I have explained this ok. Any help would be appreciated as it is driving me mad!!
You won't be able to do this based on a formula because the formula requires data.
Is there any way you can add a table to your database where you could set the relationship between the product and the category? If you can do that, you could then left-join FROM that new table to the product table. You would then group by the category from the new table instead of using your formula, which should get you what you're looking for.