This is a migrated thread and some comments may be shown as answers.

Calculated Column and Null Values

2 Answers 191 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Paul Kavanagh
Top achievements
Rank 1
Paul Kavanagh asked on 19 Oct 2009, 07:44 PM
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 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 2
answered on 20 Oct 2009, 07:33 AM
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.
0
Lynn
Top achievements
Rank 2
answered on 30 Nov 2009, 12:35 PM
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

Tags
Grid
Asked by
Paul Kavanagh
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Lynn
Top achievements
Rank 2
Share this question
or