How to make an 'exclusion table' show all values when nothing is selected?

[#1]

Hi.

I am basically creating a straight table which shows all values which the user has not selected.

I have 2 dimensions: Colour and ProductID;

and 1 expression: sum(Sales).

I used the expression:  sum (  {  1  -  (   <Colour>  *  <ProductID>     )  }  Sales )

This works fine, i.e. if I select Colour = Blue and ProductID = 3 --> the tables shows everything except for this record.

Problem is: when I have not made any selections, the table doesn't show anything.

I tried:

= if ( isnull ( GetCurrentSelections() ),      

     sum(Sales),     

     sum (  {  1  -  (   <Colour>  *  <ProductID>     )  }  Sales )   )

This works. Only now, when I do make selections, they don't disappear anymore (but I want them to). Their Sales values just become zero, but they stay in the table.

Other information:

It is a requirement that I not suppress zeros in the table (because some Sales value are originally zero and I need to see this information).

Thanks for your help!

[swuehl · Accepted solution]

Instead of the one expression with the conditional if() statement, you can create two conditionally enabled expressions, first one with condition

not isnull(GetCurrentSelections())

and expression

= sum (  {  1  -  (   <Colour>  *  <ProductID>     )  }  Sales )

second one with condition

isnull(GetCurrentSelections())

and expression

=sum(Sales)

[#3]

It works exactly like I wanted!

Thank you so so much!!

[swuehl]

You're welcome.

Instead of

{  1  -  (   <Colour>  *  <ProductID>     )  }

I think

{1-$}

should be enough, correct?

[#5]

Yup, that works.

Thanks!