Calculated Column and Null Values

3 posts, 0 answers
  1. Paul Kavanagh
    Paul Kavanagh avatar
    25 posts
    Member since:
    Oct 2005

    Posted 19 Oct 2009 Link to this post

    I have a grid based on a pivot table returned from SQL server, using the tsql pivot function. The pivot returns null values in some cases. Ideally, I would address null values in the pivot function, but have not figured that out yet.

    This caused two issues in the grid.

    1) Calculated column for totals on the right of the grid. Since Null + Anything = Null, any null value produces. I found that using IsNull in the expression for the calculation works, so I am ok on that one.

    2) Footer agregates - same issue, but I cannot use IsNull in the dataformatstring. So Agregate sums for footers are not working properly.

    Also, the zero's just look better, as opposed to an empty cell.

    So the question is, what is the best/easiest approach for converting null values in grid items to zeros? Can it be done declaritively, or must it be done in code behind, i.e., in the ItemCreated event?

    Thanks,

    Paul

  2. Princy
    Princy avatar
    17382 posts
    Member since:
    Mar 2007

    Posted 20 Oct 2009 Link to this post

    Hello Paul,

    Inorder to display zeroes instead of blank string texts for various columns in teh grid, you can try out the following code in the ItemDataBound event of the grid:
    c#:

    protected void RadGrid_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e) 
        { 
             if (e.Item is GridDataItem) 
            { 
                GridDataItem dataItem = (GridDataItem)e.Item; 
     
                // for the columns bound to integer 
                DataRowView dr = (DataRowView)dataItem.DataItem; 
                if (String.IsNullOrEmpty(dr["integerfield"].ToString()))  
                { 
                    dataItem["integerColumnUniqueName"].Text = "0"
                } 
     
                // for the calculated columns 
                if (dataItem["CalculatedColumnUniqueName"].Text==" "
                { 
                    dataItem["CalculatedColumnUniqueName"].Text = "0"
                }  
            } 
       } 

    Thanks
    Princy.

  3. Lynn
    Lynn avatar
    164 posts
    Member since:
    Sep 2006

    Posted 30 Nov 2009 Link to this post

    I apologize for the lateneess of this reply, but I just saw this item while looking for a solution to a similar problem (null data coming from SQL view but wanting to display a zero (0) instead of leaving the column empty on the screen).

    I tried using the solution recommended, but  had some difficulties making it work in my situation.  In the process, I discovered a much better solution (at least it was much better to me)...

    Instead of trying to handle null's in the grid loading, why not eliminate the nulls altogether?  All you need to do is add a little code to your SQL Server SELECT statement such as:

    "SELECT name, address, ISNULL(orders, 0) AS orders, ISNULL(ordertotalqty, 0) AS ordertotalqty, phone, zipcode, etc."

    By using this method in the SELECT statement, the nulls aren't just handled, they are eliminated and won't cause problems in other code in the same page.

    To be honest, I don't know if this method will work exactly "as is" with databases other than SQL Server or not.  I've never used other database systems except those from Seattle.

    Enjoy!

    Lynn

Back to Top