Welcome Guest! To enable all features please Login or Register.



Go to last post Go to first unread
#1 Posted : Saturday, February 6, 2016 11:39:49 AM(UTC)

Rank: Member

Groups: Registered
Joined: 4/17/2015(UTC)
Posts: 17

Thanks: 1 times
Dear Support,

I am creating a report with average values and totals. Average values is using the Avg SQL function that disregards NULL values by design, which is great. However, at the total level, NULL values is considered as zero and creating false results. Is there a way to modify the total cell and make it disregard the NULL value?

This is an example:

Jan Feb Mar Apr May Total
100 100 100 100 80

Since May has NULL values in the database, the field showed no data, which is correct. However, when calculating the total, instead of disregarding the field and showing 100, it considered it as 0 and showed 80.


#2 Posted : Monday, February 8, 2016 8:55:00 AM(UTC)

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 819

Thanks: 14 times
Was thanked: 136 time(s) in 132 post(s)
Yes, that is true that NULL values are counted for the average, I wonder if this behavior is good or not and shoild become the default...
However, you can use your Cell Script (in Advanced Properties) to modify the result and re-calculate the average expected:
@using Seal.Model
    ResultCell cell = Model;

    ReportElement element = cell.Element;
    ReportModel reportModel = element.Model;
    Report report = reportModel.Report;
	if (cell.IsTotal && !cell.IsTitle)
		ResultTotalCell totalCell = (ResultTotalCell) cell;
		int cellsCount = totalCell.Cells.Count(i => i.DoubleValue != null);
		totalCell.Value = (cellsCount > 0 ? totalCell.Sum/cellsCount : 0);

That should do it, you just customize the total...
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.