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.
Regards,
Jean-Yves
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 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: Code:@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...
|
 1 user thanked epf for this useful post.
|
|
|
|
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.
