View Full Version : Null in Calculated Fields

08-31-2003, 07:23 PM
Hi, I am new to MONDRIAN (quite impressed though!).
I have a MDX query that creates a calculated field as field_a / field_b. When either of these fields is NULL I get NaN in my output rows and I am unable to suppress these rows from the result set.
I've tried using isempty, iif and IS NULL functions but then the jsp containing the query throws an error - iif(, ,) not found, and similar for the other functions. Is there some trick to this???
All help appreciated.

09-01-2003, 09:00 PM
Can you reproduce this with a query against FoodMart -- that would help a lot.
These NULL values: do they arise because no rows fall into the cell, or because there are rows, but their measure columns are NULL?

09-02-2003, 08:13 AM
I have the same problem (but no foodmart at hand):
with member [A] as '[B] + [C]'
select {[A], [B], [C]} on columns,
non empty {whatever} on rows
from cube
where (lots of slicers for sparse result)
Now, if B and C are null, A is 0.0 and the row is not suppressed.

09-02-2003, 07:01 PM
Thanks for the feedback.
I have done some further investigation and it appears I get this problem when either the divisor or the dividend is zero (not null as I originally stated). Although I suspect that it would happen for nulls as well.
Ideally, I would filter out the offending rows (rows with 0 as a value) from the query prior to the calculation being performed, but I don't know how to do this either. My query looks as follows:
with MEMBER Measures.PrelimPassRate as
'(Measures.[Preliminary] / Measures.[Attempt Preliminary])', format_string = '##0.00;(##0.00);ZERO;NULL;Nil'
NON EMPTY {[Measures].[Attempt Preliminary],[Measures].[Preliminary],Measures.[PrelimPassRate],
[Measures].[Junior], [Measures].[Attempt Junior],
[Measures].[Senior], [Measures].[Attempt Senior],
[Measures].[Beginner], [Measures].[Water Discovery],
[Measures].[Intermediate], [Measures].[SS Junior],
[Measures].[Water Wise],
[Measures].[Swim Survive], [Measures].[Water Awareness], [Measures].[SS Senior],
[Measures].[Water Sense], [Measures].[Twelve Plus]} ON columns,
NON EMPTY Crossjoin({[IntermProgram].[All IntermPrograms]}, {[IntmVenue].[All IntmVenues]}) ON rows
from [IntermVenues]
Once again any help gratefully accepted.

09-02-2003, 09:31 PM
Thanks for the test-case. I adapted it to FoodMart and raised bug 799652.

09-02-2003, 09:43 PM
I tried using Iif as a workaround:
with member [Measures].[Price] as
' Iif(IsEmpty([Measures].[Store Sales]),
[Measures].[Unit Sales],
[Measures].[Store Sales] / [Measures].[Unit Sales]) '
select {[Measures].[Price], [Measures].[Store Sales], [Measures].[Unit Sales]} on columns,
{[Customers].[All Customers].[USA].[CA].[San Francisco].Children} on rows
from Sales
and Filter as a workaround:
with member [Measures].[Price] as
' [Measures].[Store Sales] / [Measures].[Unit Sales] '
select {[Measures].[Price], [Measures].[Store Sales], [Measures].[Unit Sales]} on columns,
Filter({[Customers].[All Customers].[USA].[CA].[San Francisco].Children},
Not(IsEmpty([Measures].[Store Sales]))) on rows
from Sales
and no luck. These issues are bugs too. Looks like we have several problems with handling of NULL values.

09-03-2003, 03:27 PM
Hi Julian,
Thanks for the effort you have put in to looking at this. I am really impressed with the turn around time for responses to issues raised - how long would I be waiting if it was Oracle I wonder?
I guess the next question is when might the bug be fixed?
I can see lots of potential for Mondrian within the organisation I work in (Education Department of Western Australia). We've been using COGNOS products up to now but it becomes very expensive to deploy this stuff on the WEB.
I'm new to MDX so I don't really understand the syntax too well, how does the first work around (Iif) remove the empty values - shouldn't there be a Not in there somewhere so it only does it if it isnt empty??

06-18-2004, 06:53 AM
did you find a workaround for your problem ?
I'm looking for a solution for the same problem.
Thanks in advance,

06-18-2004, 11:06 AM
This has been fixed some time ago. Try current JPivot-1.2.0

10-10-2004, 12:50 AM
I'm using jpivot 1.2.1 and still having this problem.. was the fix in jpivot, or in Mondrian? (I made some changes to mondrian - adding a formatLocale for Hungary etc, and have still been using that jar.... I had assumed that was still current since no new mondrian has been released since then..)
with member [Measures].[Remi %] as 'IIf(IsEmpty([Measures].[átvett]), [Measures].[remittenda], ([Measures].[remittenda] / [Measures].[átvett]))', format_string = IIf(((([Measures].[remittenda] / [Measures].[átvett]) * 100.0) < 50.0), "|#.00%|style='green'", IIf(((([Measures].[remittenda] / [Measures].[átvett]) * 100.0) > 50.0), "|#.00%|style='red'", "#.00%"))
and I get 0.00% everywhere where I would like to suppress null results.. any ideas?