One condition and one formual in same expression in Pivot Table

[#1]

HI guys,

My pivot table looks like this

What I need is  this:

1. I need to hide GroupItem and GroupItemEnd Row

2. I need to make sure that Memo column should not show any value for "Total" Row.

I would like to use RowNo() instead of dimensionality() because dimensions may change for my report.

Regards,

Saurabh

[#2]

In Dimension tab,

you have an option to set a condition for each dimension.

You can assign it to a variable with value 1 and assign it a value 0.

[#3]

can you please give an example?

[sushil353]

Hi,

to remove dimension value..

use if condition in dimension tab as

if (Itemtype='GroupItemEnd',null(),itemtype)

also check the option for supress null values.

HTH

Sushil

[#5]

I have just attached the image...

you may have a look at it to understand my requirement. meanwhile I will try your suggestions.

Thanks.

[jonathandienst]

Hi

If I understand you correctly, then this should work:

Sum(<ItemType-={'GroupItemEnd'} If(RowNo()<>0 and not IsNull(RowNo()), Memo))
+
Sum({<ItemType-={'GroupItemEnd'}>}Memo)

But better still would be:

If(Dimensionality = 0, 0, Sum({<ItemType-={'GroupItemEnd'}>}Memo))

HTH

Jonathan

[#7]

Hi Jonathan,

It is not working....

I have attached the image and also the qvw file.

See if it can help you give me a solution.

[#8]

Hi Sushil,

Unfortunately it is not working.

I have chnaged the description of my problem and also have attached a the QVW file if it can help you.

Saurabh

[sushil353 · Accepted solution]

Hi,

Please find attached..

HTH

Sushil

[#10]

PFA