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

How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])

How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])

How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael

How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael