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

GridCalculatedColumn with null datafields not displaying value

7 Answers 166 Views
Grid
This is a migrated thread and some comments may be shown as answers.
The Wire
Top achievements
Rank 1
The Wire asked on 15 Apr 2010, 05:00 PM
I'm doing some testing with the RadGrid CalculatedColumn.  It's a timesheet application so I'd like to use the GridCalculatedColumn to total the hours for the week (row) and sum aggregate to total the hours for the day (column).  Some days won't have hours so the datafield will pull in NULL.  The sum aggregates are working fine but the GridCalculatedColumn is not displaying a value. 

Here is the code for my columns:
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Sun" DataType="System.Decimal" HeaderText="SUN" /> 
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Mon" DataType="System.Decimal" HeaderText="MON" /> 
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Tue" DataType="System.Decimal" HeaderText="TUE" /> 
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Wed" DataType="System.Decimal" HeaderText="WED" /> 
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Thu" DataType="System.Decimal" HeaderText="THU" /> 
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Fri" DataType="System.Decimal" HeaderText="FRI" /> 
<rad:GridNumericColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataField="Hours_Sat" DataType="System.Decimal" HeaderText="SAT" /> 
<rad:GridCalculatedColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}" DataFormatString="{0:F}" DataFields="Hours_Sun, Hours_Mon, Hours_Tue, Hours_Wed, Hours_Thu, Hours_Fri, Hours_Sat" Expression="{0}+{1}+{2}+{3}+{4}+{5}+{6}" DataType="System.Decimal" HeaderText="WEEK TOTAL" /> 

In one row example, there are hours in only Monday and Tuesday.  If I changed the DataFields to "Hours_Mon, Hours_Tue" and the Expression to "{0}+{1}" then the CalculatedColumn works correctly.  Am I only allowed to do a calculation on 2 data fields or is it running into a problem with the null values for the rest of the days? Am I missing a property in my columns?

7 Answers, 1 is accepted

Sort by
0
Radoslav
Telerik team
answered on 20 Apr 2010, 03:08 PM
Hello Wire,

Indeed, I verified that the issue exists in the current version of the controls. Our developers are aware and they will start working on the resolution of this issue. I will notify you as soon as I have more information on this matter.

Sincerely yours,
Radoslav
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
JUAN
Top achievements
Rank 1
answered on 21 Jul 2010, 11:04 PM
I have the same issue, there is any workaround?
Thank you very much.

Best Regards,
Juan Carlos.
0
Radoslav
Telerik team
answered on 26 Jul 2010, 02:19 PM
Hi Juan,

To achieve the desired functionality you could try using the following expression:
<telerik:GridCalculatedColumn Aggregate="Sum" FooterAggregateFormatString="{0:F}"
DataFormatString="{0:F}" DataFields="Hours_Sun, Hours_Mon, Hours_Tue" Expression="{0} + (({1} == null) ? 0 : {1})+ {2} " DataType="System.Decimal" HeaderText="WEEK TOTAL" />

Additionally I am sending you a simple example. Please check it out and let me know if it helps you.

Kind regards,
Radoslav
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
JUAN
Top achievements
Rank 1
answered on 26 Jul 2010, 02:35 PM
Thank you very much Radoslav, that's exactly I need.
I have and other question:
If I need to average the columns (A Double Data Type) which it value is > than 0
How is the expression?

Best Regards,
Juan Carlos.
0
Radoslav
Telerik team
answered on 29 Jul 2010, 10:13 AM
Hello Juan,

To achieve the desired functionality you could try using the following expression:
Expression="({0} + {1} + {2} ... + {n})/n-1". For example: If you have GridCalculatedColumn which shows avg of other three columns, the Expression will be:
<telerik:GridCalculatedColumn Aggregate="Avg" FooterAggregateFormatString="{0:F}"
DataFormatString="{0:F}"  DataFields="Hours_Sun, Hours_Mon, Hours_Tue" Expression="({0} + {1} + {2})/3" DataType="System.Decimal" HeaderText="WEEK TOTAL" />

Please give it try and let me know if you experience any problems.

Kind regards,
Radoslav
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Augusto
Top achievements
Rank 1
answered on 27 Aug 2010, 03:47 PM
Hello.

I had the same problem with decimal values retrieved from a SQL query.
What I wanted was to have have empty cells when the value was 0 (not a "0" displayed) and have the sum of the columns in a GridCalculatedColumn field.

We have the problem when the values retrieved from the query are NULL. The GridCalculatedColumn doesn't display the sum when NULL values are in the expression fields. The workaround is to replace NULL values with zero and to change the display format in the grid:

SQL query
select isnull(total_sales_jan, 0) as M01, isnull(total_sales_Feb, 0) as M02, ...

Grid definition
<telerik:GridNumericColumn DataField="M01" DataType="System.Decimal" HeaderText="Jan"
    SortExpression="M01" UniqueName="M01" HeaderStyle-Width="70px" DataFormatString="{0:###,###,####}"
    Aggregate="Sum" FooterStyle-HorizontalAlign="Right" FooterStyle-Font-Bold="true" />
<telerik:GridNumericColumn DataField="M02" DataType="System.Decimal" HeaderText="Feb"
    SortExpression="M02" UniqueName="M02" HeaderStyle-Width="70px" DataFormatString="{0:###,###,####}"
    Aggregate="Sum" FooterStyle-HorizontalAlign="Right" FooterStyle-Font-Bold="true"  />
...
<telerik:GridCalculatedColumn DataType="System.Decimal" UniqueName="TotalYear"
    HeaderStyle-Width="80px" HeaderText="Total"
    DataFields="M01,M02,M03,M04,M05,M06,M07,M08,M09,M10,M11,M12" 
    Expression="{0}+{1}+{2}+{3}+{4}+{5}+{6}+{7}+{8}+{9}+{10}+{11}"
    DataFormatString="{0:###,###,###0}"
    Aggregate="Sum"  />

The format {0:###,###,####} for the M01, M02, ..., M12 columns allows to not display blanks instead of "0".

Regards
Augusto
0
Radoslav
Telerik team
answered on 01 Sep 2010, 02:00 PM
Hello Augusto,

Thank you for sharing the solution - I am sure it will help the community. Additionally as a small token of gratitude for your involvement I have updated your Telerik points.

Kind regards,
Radoslav
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Tags
Grid
Asked by
The Wire
Top achievements
Rank 1
Answers by
Radoslav
Telerik team
JUAN
Top achievements
Rank 1
Augusto
Top achievements
Rank 1
Share this question
or