How to force 0's in place of null values..

Hi,
I am using an MDX query which generates null values,Instead of null values I
need to display 0 's.
I have tried the below options but none is working fine for me,
please give me any help/suggestion/URL.Its really urgent !!
=Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
=iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
=Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
Thanks and Regards,
Rajesh Yennam.
HA India.This what I use and it works fine.
Good Luck!
=iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
message news:RajeshYennam@.discussions.microsoft.com:
> Hi,
> I am using an MDX query which generates null values,Instead of null values I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||I asked the same thing in the Olap-news group
(microsoft.public.sqlserver.olap). Here are the answers I got:
***
tawargerip@.hotmail.com <tawargerip@.hotmail.com>:
use IIF( isempty(<Measure name>),0,<Measure name>)
you may need to create a calculated member for each measure like this
and
Cymryr <Cymryr@.hotmail.com>:
Function CoalesceEmpty is the right way to do it, see BOL
***
The CoalesceEmpty looks promising, but I haven't got around to implement it
yet.
Kaisa M. Lindahl
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:BE2BF79D-CDBC-488D-9B31-6F9132507717@.microsoft.com...
> Hi,
> I am using an MDX query which generates null values,Instead of null values
I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||Thanks for your quick response John.Actually this also not working for me. If
you find any alternative solution please post it.
Thanks & Regards,
Rajesh Yennam,
HA India.
"John Geddes" wrote:
> This what I use and it works fine.
> Good Luck!
> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>
>
> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
> message news:RajeshYennam@.discussions.microsoft.com:
> > Hi,
> > I am using an MDX query which generates null values,Instead of null values I
> > need to display 0 's.
> > I have tried the below options but none is working fine for me,
> > please give me any help/suggestion/URL.Its really urgent !!
> >
> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> >
> > Thanks and Regards,
> > Rajesh Yennam.
> > HA India.
>
>|||Put the null detection code in a function in the Code element of the report.
The problem with IIF is that it evaluates all conditions and throws on NULL.
As simple if then else statement will work.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:CBD98285-010A-4EB9-8DFE-2DBF0875C898@.microsoft.com...
> Thanks for your quick response John.Actually this also not working for me.
> If
> you find any alternative solution please post it.
> Thanks & Regards,
> Rajesh Yennam,
> HA India.
> "John Geddes" wrote:
>> This what I use and it works fine.
>> Good Luck!
>> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>>
>>
>> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
>> message news:RajeshYennam@.discussions.microsoft.com:
>> > Hi,
>> > I am using an MDX query which generates null values,Instead of null
>> > values I
>> > need to display 0 's.
>> > I have tried the below options but none is working fine for me,
>> > please give me any help/suggestion/URL.Its really urgent !!
>> >
>> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
>> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
>> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
>> >
>> > Thanks and Regards,
>> > Rajesh Yennam.
>> > HA India.
>>