PDA

View Full Version : Null in Calculated Fields



andymcmanus
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.
Thanks....

jhyde
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?

avix
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.
Andreas

andymcmanus
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'
select
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.
Andy....

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

jhyde
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.

andymcmanus
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??

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

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

uramisten
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..)
Ati
-----
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?