Suppress Null Value but have static dimension

[#1]

Hi all,

I created a pivot table with two dimensions Dimension1 (1,2,3) and Dimension2(a,b,c) created as:

Dimension1\Dimension2	a	b	c	-
1	1.1	2.2	3.3	0.0
2	4.4	5.5	6.6	0.0
3	7.7	8.8	9.9	0.0
-	0.0	0.0	0.0	0.0

But I want a table as

Dimension1\Dimension2	a	b	c
1	1.1	2.2	3.3
2	4.4	5.5	6.6
3	7.7	8.8	9.9

With ListBox it is showing Dimension1 as 1,2,3 and Dimension2 as a,b,c  but in table it shows null also.

Also to have static dimension I don't want to check suppress Null values in Dimension tab of Chart Properties.

Is there any other way to do it?

[Michiel_QV_Fan]

Why don't you what to use suppress Null. The result will be what you need.

Can you provide more information what you need?

[hic]

On the "Presentation" tab, there is an option "Suppress Zero-values" and one "Suppress missing values". These normally do the trick.

The second alternative is to check "Suppress When value is NULL" on the dimension tab. Could you please elaborate on why you don't want to check this option?

HIC

[mayankraoka]

Hi Anjali,

On script while loading table you can ignore data having null in the filed:

Where  not IsNull(field_name);

Regards,

Mayank

[#5]

Hi Henric,

I don't want to check "Suppress When value is NULL" on the dimension tab  as I want the dimensions to be static with different selections.

Regards,

Anjali

[hic · Accepted solution]

If you by "Static" mean that you always want to show all values of dim 1, you should do the following:

First, instead of

      Sum(Amount)

(or whatever you have as measure) you should use

      0*Sum({1} Amount) + Sum(Amount)

Then suppress the NULL values by checking the appropriate check box.

HIC

[#7]

Thanks Henric for the Help.

It worked the way I wanted.

Regards,

Anjali Gupta