GridCalculatedColumn with null datafields not displaying value

[The Wire]

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?

[Radoslav]

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.

[JUAN]

I have the same issue, there is any workaround?
Thank you very much.

Best Regards,
Juan Carlos.

[Radoslav]

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

[JUAN]

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.

[Radoslav]

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

[Augusto]

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

[Radoslav]

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