Problem of calculated field in report builder

I am using build in function such as average() in a calculated field against a query column. However, I don't want the empty value(or 0 value) to be counted? How do I do it?

thanks

I believe that the aggregate functions in the reports are just for convenience but aren't meant for much customization. You'll probably be better off if you make the calculation directly in your SQL.

thanks. I agree that I 'd better do it in my SQL.
Unfortunately, My SQL is complex and I create it manually.

Here is a note from help:
"Average - Calculates the average of rows that contain a non-null value in Field Value."

Does that mean if a column is null, the aggr. function will ignore it?

thanks

It sounds like it. So one option is to just null/empty the value in that column instead of leaving it as 0.

I tried, it is not working in that way

Not sure what to say then (I don't use the Report Builder myself). Either what you're doing isn't actually modifying the value to null, or the explanation for what the function does is incomplete, or it actually doesn't do what you're trying to do. How are you nulling/emptying the column values?

Again, this may well be a situation where the proper solution is to actually calculate what you want at the database/SQL level.