none
Exclude NULL values from SUM and AVG calculation

    Question

  • Hi,

    I have column in report that contains some NULL values. When i perform SUM,MAX,MIN or AVG calculation on this column the NULL values are treated as '0' and included in calculation. Is there any way to exclude them while calculating aggregate functions? 

    As a result MIN calculation on values (NULL,0.7,0.5,0.9) gives me output as 0 when it should have been 0.5 

    Can someone please help ?

    Thanks and Regards,

    Oliver D'mello

    Tuesday, October 21, 2014 11:45 AM

Answers

  • Hi,

    I have column in report that contains some NULL values. When i perform SUM,MAX,MIN or AVG calculation on this column the NULL values are treated as '0' and included in calculation. Is there any way to exclude them while calculating aggregate functions? 

    As a result MIN calculation on values (NULL,0.7,0.5,0.9) gives me output as 0 when it should have been 0.5 

    Can someone please help ?

    Thanks and Regards,

    Oliver D'mello

    Hi Oliver,

    You can just exclude those record having null values, in the where clause like

    ...
    where column1 is not null


    Many Thanks & Best Regards, Hua Min

    Monday, October 27, 2014 5:09 AM

All replies

  • I think thats because you are applying some kind of casting on the field otherwise it will ignore NULLs by default in SSRS as it corresponds to value of Nothing in SSRS

    Can you try taking out the casting part? ie like Cint(),Cdec() etc


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, October 21, 2014 11:58 AM
  • Yeap,

    Include a filter to exclude NULL values from resultset.

    SELECT SUM(Field) WHERE Field IS NOT NULL

    • Proposed as answer by Deric Ferreira Tuesday, October 21, 2014 11:58 AM
    Tuesday, October 21, 2014 11:58 AM
  • As pointed out by Visakh16 aggregate  functions except COUNT ignore NULL. You might have used ISNULL or some casting on columns in the query.

    Krishnakumar S

    Tuesday, October 21, 2014 12:04 PM
  • Hi,

    Thanks for your reply.

    I am not using any casting function. The expression i am using is as mentioned below. Am i missing something here

    =(Sum(-1 * Fields!SomeField.Value)  * 100 ) / ReportItems!SomeField.Value

    Thanks and Regards,

    Oliver D'mello  

    Wednesday, October 22, 2014 4:04 AM
  • Hi Deric,

    I cannot filter out the NULL values in SQL query as i need to show NULL values as blank in SSRS report while excluding them from all calculations. 

    Thanks and Regards,

    Oliver D'mello

    Wednesday, October 22, 2014 4:06 AM
  • Hi Oliver,

    As Vishak said "SSRS will ignore NULLs by default as it corresponds to value of Nothing ".

    For the expression you mentioned above .. tyr this

    =(Sum(-1 * (IsNothing(Fields!SomeField.Value),0,Fields!SomeField.Value)  * 100 ) / ReportItems!SomeField.Value

    Wednesday, October 22, 2014 4:21 AM
  • Hi Srinivas,

    But the expression you have mentioned will give me 0 as column value in case of NULL. I want blank instead.

    Thanks and Regards,

    Oliver D'mello

    Wednesday, October 22, 2014 6:26 AM
  • Srinivas,

    You are faccing a conceptual problem: if the NULL or BLANK values should be used on your calculations you couldn't return it on your resultSet!

    The value 0 is not a problem when using SUM, MIN, MAX. You can't use blank if you need to use an aggregation function!

    • Proposed as answer by Deric Ferreira Wednesday, October 22, 2014 10:54 AM
    Wednesday, October 22, 2014 10:54 AM
  • See this example it returns 0.5 as you wanted

    CREATE TABLE #t (id REAL)

    INSERT INTO #t VALUES (NULL)
    INSERT INTO #t VALUES (0.7)
    INSERT INTO #t VALUES (0.5)
    INSERT INTO #t VALUES (0.9)

    SELECT MIN(id) FROM #t
    0.5


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 22, 2014 11:16 AM
  • Hi Oliver,

    According to your description, you want to ignore the NULL values when you perform aggregation functions.

    In this scenario, aggregate functions always ignore the NULL values, because their operation objects are non-null values. So I would like to know if you have assigned “0” for NULL values. I would appreciate it if you could provide some screenshots about your expressions or reports.

    Besides, we have tested in our environment using  Min() function. The expression returns the minimum value among the non-null numeric values. Please refer to the screenshots below:

    Reference:
    Min Function (Report Builder and SSRS)
    Aggregate Functions Reference (Report Builder and SSRS)

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu

    • Proposed as answer by Deric Ferreira Wednesday, October 22, 2014 12:34 PM
    Wednesday, October 22, 2014 12:30 PM
    Moderator
  • Hi Qiuyun,

    Thanks for your explanation.

    I can confirm that following is the expression that is used.  

    =(Sum(-1 * Fields!SomeField.Value)  * 100 ) / ReportItems!SomeField.Value

    I think maybe the multiplication and division operation would be causing the problem here. A normal SUM would work fine. Any idea on this ?

    Thanks and Regards,

    Oliver D'mello

    Monday, October 27, 2014 4:13 AM
  • I can confirm that following is the expression that is used.  

    =(Sum(-1 * Fields!SomeField.Value)  * 100 ) / ReportItems!SomeField.Value

    This is the expression you used in your report. May be you can check the underlying data set to see if any expression is used in the data set to convert NULL to 0.

    --sIbu

    Monday, October 27, 2014 5:04 AM
  • Hi,

    I have column in report that contains some NULL values. When i perform SUM,MAX,MIN or AVG calculation on this column the NULL values are treated as '0' and included in calculation. Is there any way to exclude them while calculating aggregate functions? 

    As a result MIN calculation on values (NULL,0.7,0.5,0.9) gives me output as 0 when it should have been 0.5 

    Can someone please help ?

    Thanks and Regards,

    Oliver D'mello

    Hi Oliver,

    You can just exclude those record having null values, in the where clause like

    ...
    where column1 is not null


    Many Thanks & Best Regards, Hua Min

    Monday, October 27, 2014 5:09 AM