Calculated Dimension to be used?

[#1]

I have two intentory files, a file that contains master info (item #, desc, product line and category) and a file that contains qty info(warehouse, qty, ave price)

I have joined them by the item number and have created a straight table and a pivot table.  The pivot table has product line and category as dimensions.  I would like to limit the product lines displayed without using a list box (i.e. always omit TOOL and SUPP) can I use a calculated dimension to do this.

I also have a straight table listing the items selected.  Unfortunately it is also listing the items without an onhand quantity.  How can I limit this?  I have attached a copy of the object. 

Thanks in advance for any assistance given.

[swuehl · Accepted solution]

Hi,

for limiting the dimension, you have to possibilities:

- either use a calculated dimension like

=if(not match(ProductLine,'TOOL','SUPP'),ProductLine)

- or use a set expression in (all of) your expressions, e.g.

=sum({<ProductLine -= {SUPP,TOOL} >}QtyOnHand)

(please note that you can't use any of these expressions in a table box, that's the object you put in your sample file, not a pivot table)

In the straight table, you won't get any rows with zero onhand qty and its related sum(QtyOnHand * IM2_AveCost), if suppress zero values in presentation tab is checked (and it was checked in the sample and I haven't noticed any lines without onhand quantity). Maybe I misunderstood your question, could you point me to a row which is showing the issue?

Regards,

Stefan

[#3]

swuehl,

Thanks for the solution. As far as the straight table, I checked the box in the Presentation tab - Omit if field is null, and that solved the issue.  Once again thanks for your help.