Set Aggregation value if null due to Filter Condition?

jverly01
jverly01
edited February 11, 2022 in Analytics #1
<p>I've got an aggregation (SUM) on a field that is set to aggregate on a group in BIRT 2.6.2. The problem is there are times when the aggregation may not find any fields to sum up, based on the Filter Condition.</p>
<pre class="_prettyXprint">
Column Binding: total_hours
Diplay Name: Total Hours
Data Type: Float
Function: SUM
Expression: dataSetRow["regularhrs"]
Filter Condition: dataSetRow["schedstart"] > row["date1"] && dataSetRow["schedstart"] < row["date2"]
Aggregate On: crewIDGrp (Group)</pre>
<p>I know the aggregation works because the summary will return values when there is data between the two date ranges. But when no records exist in the date range, the summary returns NULL instead of 0.00.</p>
<p> </p>
<p>Any suggestions on how to get the aggregate to return 0.00 in the event no data meets the Filter Condition?</p>

Comments

  • micajblock
    micajblock E
    #2
    <p>Do you want to just display 0.00? If yes use mapping. Another option is to create another column binding and have the expression:</p>
    <pre class="_prettyXprint">
    if (row["total_hours"] == null) {
      0;
    }
    else {
      row["total_hours"];
    }</pre>
  • jverly01
    jverly01
    #3
    <p>That's exactly what I ended up doing. The 2nd element will do a check on the actual aggregation and if the aggregation is null, set its value to zero.</p>

Categories

image
OpenText CE Products
Magellan Text Mining
Docs & APIs