Average in Cross-Tab

jlshk

I made a report by Cross-Tab to show sales amount per shop per date. In Grand Total, I wish to show the average amount instead of sum total. But I found it often happens error, I guess it should be caused by Null value when some shops haven't sales amount the date. Any method to avoid that?

Thanks a lot!

gpi

Try to set TfrxReport.EngineOptions.ConvertNulls to True

jlshk

yes. I'd set ConverNulls to True already. But it seems not work.

gpi

Can you attach modified report from FRDemo with problem?

technisoft

jlshk wrote: »

I made a report by Cross-Tab to show sales amount per shop per date. In Grand Total, I wish to show the average amount instead of sum total. But I found it often happens error, I guess it should be caused by Null value when some shops haven't sales amount the date. Any method to avoid that?

Thanks a lot!

If you are using a query to extract the data then you may be able to convert nulls to any other value in it.
For Firebird it would be
SELECT COALESCE( ColumnValue, 0 ) FROM Table

returning 0 if ColumnValue is null.

jlshk

Thank you for your answers. I success to converted the Null value to zero. But another question happen, my boss require the zero amount should not be counted in average. Is it possible to do that?