How to show empty groups in crystal?

Question

I have a report that shows items by goups.

Lets say he groups are: In Inventory In Process

When there is no data In Inventory, that row is not shown. any idea how to show a row with 0 inventory?

Answer 1

If I'm understanding you correctly, you've got a field {table.inventory} that holds the number of items in inventory that has a null value when the inventory is zero? When you group on this field you're not seeing the rows with null values in this field?

Crystal should still display those rows, just in a group with a null group name. To fix this you can go into the Group Expert -> Select the group in question -> Options -> Options tab -> Select 'customize group name field' -> and then specify a formula as a new group name field where you simply check to see

if isnull({table.inventory}) then 0 else {table.inventory}

Similarly, you can just create a formula that does this and group on that instead.

Answer 2

It sounds as though you are grouping on a field (such as stock item status), where there may be 0 rows returned for certain values of the field (such as In Inventory). The answer is to amend your query to right outer join to a lookup table holding all values of the grouping field, for example as follows:

select lu.status_value           stock_item_status, 
       si.stock_item_status      item_status, 
       si.stock_item_id, 
       coalesce(si.quantity,0)   quantity
from stock_item si
right join stock_item_status lu
on si.stock_item_status = lu.status_value

This will now include a row returned for stock_item_status values with no corresponding stock_items, with null values for all of the stock_item fields.

If you were including a subtotal of stock_item.quantity values for each status, changing this to coalesce(...,0) should ensure that this null value is displayed as 0.