DMR - Null value over aggregate

[Robl]

Hi,

I have a data item like this;
aggregate ([People] within set [Houses])
+
aggregate ([People] within set [Hotels])

The problem is, sometimes there are no people in hotels, this means the value is null, which nulls the entire data item.
Normally I'd use "isnull" but that's obviously relational.
I have made it work by wrapping each aggregate in "Case when aggregate... is null then 0 else aggregate...

But it's not very elegant.
Is there a neater way to make an empty aggregate return 0?

[rockytopmark]

Well, while its not an "elegant" solution, per se... it works.  I use a similar implementation quite often, so while not elegant, its effective. Interested to hear if there are others that indeed have something more elegant.

[srmoure]

There must be a better way to do it. I'm experiencing similar issue after the upgrade from 10 to 11.

[srmoure]

I found the solution to this issue :

Treat Nulls as Zeros within Calculations
By default, the Dynamic Query Mode propagates nulls in calculations. However, it is possible to override the default behaviour for DMR packages through configuration settings similar to those for pure OLAP data sources.
Impacts: Calculations on data items that contain null data values.
Usage: This set of parameters controls whether or not null data values are treated as zeros when used in calculations. If the parameters are enabled, 100 + null would result in 100. If the parameters are disabled, 100 + null would result in null. By default, these parameters are disabled.
Interoperability with other parameters: None
Setting these parameters: The parameters are available within the file c10\configuration\xqe\dmr.properties as shown here (with the default settings):
null.plus.operator=null
null.minus.operator=null
null.multiply.operator=null
null.divide.numerator=null
null.divide.denominator=null
null.modulo.dividend=null
null.modulo.divisor=null
Show less
To enable this feature, change the null values to zero as follows:
null.plus.operator=zero
null.minus.operator=zero
null.multiply.operator=zero
null.divide.numerator=zero
null.divide.denominator=zero
null.modulo.dividend=zero
null.modulo.divisor=zero
Show less
These changes will be picked up once the IBM Cognos 10 service is restarted. After the restart, this change will affect all queries against any DMR data source through IBM Cognos 10. In a distributed environment, this change will need to be made on all IBM Cognos 10 servers performing data access.